Download SQL Database Creation and Querying: Sales Database Example and more Exercises Information Technology Management in PDF only on Docsity!
Lab 4
Part 1:Create Database called “Sales” and all its table by SQL
create database Sales create table Customer( customerID char( 10 ) primary key not null, customerName varchar( 50 ), customerPhone varchar( 15 ), customerAddress varchar( 50 ) ); create table Staff( staffID char( 10 ) primary key not null, staffName varchar( 50 ), staffPhone varchar( 15 ), staffAddress varchar( 50 ) ); create table Product( productID char( 10 ) primary key not null, productName varchar( 50 ), Unit varchar( 15 ), Price float, ); create table Bill( billID char( 10 ) primary key not null, billDate date, customerID char( 10 ), staffID char( 10 ), foreign key (customerID) references Customer, foreign key (staffID) references Staff, ); create table BillDetail( billID char( 10 ), productID char( 10 ), quantity float, foreign key (billID) references Bill, foreign key (productID) references Product, constraint PK_BD primary key (billID,ProductID) )
Add data into all tables:
Insert data into Staff table: insert into Staff(staffID, staffName, staffPhone, staffAddress) values('01','Hung','11111111','10 Le Duan'); insert into Staff(staffID, staffName, staffPhone, staffAddress) values('02','Lan','22222222','20 Nguyen Thi Minh Khai'); insert into Staff(staffID, staffName, staffPhone, staffAddress) values('03','Ngoc','33333333','100 Le Loi'); insert into Staff(staffID, staffName, staffPhone, staffAddress) values('04','Hoang','44444444','15 Hai Ba Trung'); go Insert data into Staff table: insert into Customer(customerID,customerName,customerPhone,customerAddress) values('01', 'Quang Minh', '987654321','3 Le Loi'); insert into Customer(customerID,customerName,customerPhone,customerAddress) values('02', 'Quang Teo', '123456789','1 Le Lai'); insert into Customer(customerID,customerName,customerPhone,customerAddress) values('03', 'Minh Beo', '456789123','3 Nam Ky Khoi Nghia'); go Insert data into Staff table: insert into Product(productID, productName, Unit, Price) values('LAP01','Laptop Dell','piece', 500 ); insert into Product(productID, productName, Unit, Price) values('LAP02','Laptop Lenovo','piece', 400 ); insert into Product(productID, productName, Unit, Price) values('IP01','Ipad Apple x','piece', 300 ); insert into Product(productID, productName, Unit, Price) values('IP02','Ipad Samsung','piece', 200 ); go Insert data into Staff table: insert into Bill(billID, billDate, staffID, customerID) values ('111', '05/04/2020','01','02'); insert into Bill(billID, billDate, staffID, customerID) values ('222', '04/04/2020','01','01'); insert into Bill(billID, billDate, staffID, customerID) values ('333', '03/04/2020','03','01'); go Insert data into Staff table: insert into BillDetail(billID,productID,quantity) values('111','LAP01', 5 ); insert into BillDetail(billID,productID,quantity) values('111','LAP02', 10 ); insert into BillDetail(billID,productID,quantity) values('111','IP01', 20 ); insert into BillDetail(billID,productID,quantity) values('222','IP01', 5 ); insert into BillDetail(billID,productID,quantity) values('222','IP02', 3 ); insert into BillDetail(billID,productID,quantity) values('222','LAP01', 30 ); insert into BillDetail(billID,productID,quantity)
select B.billID, B.billDate, C.customerName,S.staffName, P.productName, P.Price, BD.quantity, P.Price * BD.quantity as Amount from Bill B, Customer C, Staff S, Product P, BillDetail BD where B.billID = BD.billID and B.customerID = C.customerID and B.staffID = S.staffID and BD.productID = P.productID; select B.billID, B.billDate, C.customerName,S.staffName, P.productName, P.Price, BD.quantity, P.Price * BD.quantity as Amount from Bill B, Customer C, Staff S, Product P, BillDetail BD where B.billID = BD.billID and B.customerID = C.customerID and B.staffID = S.staffID and BD.productID = P.productID and P.Price * BD.quantity > = 1000 ;
select S.staffID,S.staffName, sum(BD.quantity * P.Price) as Revenue from Staff S, Bill B, BillDetail BD, Product P where S.staffID = B.staffID and B.billID = BD.billID and BD.productID = P.productID group by S.staffID, S.staffName; select C.customerID, C.customerName, SUM(BD.quantity * P.Price) as Total from Customer C, Bill B, BillDetail BD, Product P where C.customerID = B.customerID and B.billID = BD.billID and Bd.productID = P.productID group by C.customerID, C.customerName; select P.productID,P.productName, Sum (BD.quantity) as Quantity, Sum(BD.quantity * P.Price) as Revenue from BillDetail BD, Product P where Bd.productID = P.productID group by P.productID,P.productName go
