The document creates databases and tables to manage a courier service. It creates tables for branches, customers, shipments, accessories, and receivers. It then inserts data into these tables, defines primary and foreign keys, and performs some updates and selects to query the data. Relationships are established between the tables to link shipments to customers, accessories, and receivers.
The document creates databases and tables to manage a courier service. It creates tables for branches, customers, shipments, accessories, and receivers. It then inserts data into these tables, defines primary and foreign keys, and performs some updates and selects to query the data. Relationships are established between the tables to link shipments to customers, accessories, and receivers.
The document creates databases and tables to manage a courier service. It creates tables for branches, customers, shipments, accessories, and receivers. It then inserts data into these tables, defines primary and foreign keys, and performs some updates and selects to query the data. Relationships are established between the tables to link shipments to customers, accessories, and receivers.
The document creates databases and tables to manage a courier service. It creates tables for branches, customers, shipments, accessories, and receivers. It then inserts data into these tables, defines primary and foreign keys, and performs some updates and selects to query the data. Relationships are established between the tables to link shipments to customers, accessories, and receivers.
Download as DOCX, PDF, TXT or read online from Scribd
Download as docx, pdf, or txt
You are on page 1/ 2
COURIER MANAGEMENT
create database courier;
create table BRANCH(b_id int, name varchar(10),location varchar(20)); create table CUSTOMER(c_id int null , cu_name varchar(20), city varchar(20),state varchar(10),street_no int ,date datetime ) create table CUSTOMER_CONTACT(c_id int,contact bigint) create table ACCESSORY(au_id int , au_name varchar (15),weight int ,quatity int); create table SHIPMENT(tracking_no int null,cost int) create table RECEIVER(r_id int ,name varchar (15),city varchar(15),state varchar(15),street int) create table RECEIVER_CONTACT(r_id int,r_contact_no bigint) alter table BRANCH alter column b_id int not null; alter table BRANCH add primary key (b_id); alter table CUSTOMER alter column c_id int not null; alter table CUSTOMER add primary key(c_id); alter table ACCESSORY alter COLUMN au_id int not null; alter table ACCESSORY add primary key(au_id); alter table SHIPMENT alter COLUMN tracking_no int not null; alter table SHIPMENT add primary key( tracking_no ); alter table RECEIVER alter column r_id int not null; alter table RECEIVER add primary key(r_id); alter table CUSTOMER_CONTACT alter column contact bigint NOT NULL; alter table CUSTOMER_CONTACT add primary key(contact); alter table receiver alter column r_id int not null; alter table receiver add primary key(r_id); alter table receiver_contact alter column r_contact_no bigint not null; alter table receiver_contact add primary key(r_contact_no) alter table shipment add c_id int; alter table shipment add au_id int; alter table SHIPMENT add foreign key(au_id) references ACCESSORy(au_id); alter table SHIPMENT add foreign key (c_id) references customer(c_id); alter table CUSTOMER_CONTACT add foreign key(c_id) references customer(c_id); alter table receiver_contact add foreign key (r_id)references receiver(r_id) select * from BRANCH; insert into BRANCH(b_id ,location) values( 102, 'near 6th road ' ) insert into BRANCH values(103, 'TCS MULTAN','near chungi_no 10') insert into BRANCH values(104,'TCS lahore','near minar_e_pakistan') insert into BRANCH values(105,'TCS muzaffargarh','thal chock') update BRANCH set name='TCS rawalpindi'where b_id=102; alter table BRANCH alter column location varchar(35) insert into BRANCH values(106,'TCS muzaffargarh','purani chungi M.GARH') insert into CUSTOMER values(5001,'Riaz Qamer','muzaffargarh','punjab',5,6/2/2013) select * from CUSTOMER; update CUSTOMER set date='2017-3-2' where c_id=5001; insert into CUSTOMER values(5002,'ali','multan','punjab',4,'2013-6-10') insert into CUSTOMER values (5003,'zain','lahore','punjab',1,'2014-6-2') insert into CUSTOMER values(5004,'sharukh','queta','sindh',1,'2014-7-12') insert into CUSTOMER values(5005,'zain','queta','sindh',2,'2017-4-01') select * from CUSTOMER_CONTACT; insert into CUSTOMER_CONTACT values(5001,'03027354374') insert into customer_contact values(5001,'06622510152') insert into CUSTOMER_CONTACT values(5002,'03027279967') insert into CUSTOMER_CONTACT values(5002,'03014055158') insert into CUSTOMER_CONTACT values(5003,'03238784500') insert into CUSTOMER_CONTACT values(5004,'03017508089') insert into CUSTOMER_CONTACT values(5005,'03007482823') select * from ACCESSORY; insert into ACCESSORY values(2001,'normal',1,3) insert into ACCESSORY values(2002,'sensitive',2,2) insert into ACCESSORY values(2003,'heavy',45,1) insert into ACCESSORY values(2004,'normal',2,1) insert into ACCESSORY values(2005,'normal',3,2) select * from SHIPMENT; insert into SHIPMENT values(2131,150,5001,2001) insert into SHIPMENT values(2132,300,5002,2002) insert into SHIPMENT values(2133,4500,5003,2003) insert into SHIPMENT values(2134,100,5004,2004) insert into SHIPMENT values(2135,75,5001,2001) insert into SHIPMENT values(2136,130,5005,2005) select * from RECEIVER; insert into RECEIVER values(9001,'hamza','barglay','calyfornia',8) insert into RECEIVER values(9002,'saad','idaho','bois',3) insert into RECEIVER values(9003,'ali','idaho','bois',7) insert into RECEIVER values(9004,'zeeshan','mississipi','jackson',7) insert into RECEIVER values(9005,'basit','mississipi','jackson',9) insert into RECEIVER_CONTACT values(9001,03006862821) insert into RECEIVER_CONTACT values(9002,03007682321) insert into RECEIVER_CONTACT values(9003,03377279967) insert into RECEIVER_CONTACT values(9004,03022510155) insert into RECEIVER_CONTACT values(9005,03067685574) insert into RECEIVER_CONTACT values(9001,03067571878) insert into RECEIVER_CONTACT values(9001,03166939101) select * from RECEIVER_CONTACT;
