Tài liệu Bài tiểu luận cơ sở dữ liệu

BỘ CÔNG THƯƠNG TRƯỜNG ĐẠI HỌC CÔNG NGHIỆP TPHCM KHOA CÔNG NGHỆ THÔNG TIN ĐỀ TÀI: Quản Lý Cửa Hàng Sữa Giảng viên hướng dẫn : Từ Thị Xuân Hiền Sinh viên thực hiện : Lê Anh Tuấn – 15054691 Phạm Văn Thiện – 15047151 Lớp : ĐHTH11B QUẢN LÝ ĐẠI LÝ SỮA: Để quản lý Đại Lý Sữa các vấn đề trong phạm vi quản lý bao gồm: sản phẩm sữa (SUA), Loại Sữa (LS), Nhà Cung Cấp (NCC), Khánh Hàng (KH), Hóa Đơn (HOADON), Nhân Viên (NV). Trên mỗi hóa đơn chúng ta có thể tìm thấy tất cả thông tin về sản phẩm sữa,hãng, nhà cung cấp ,khách hàng ,nhân viên, giá cả và các thông tin cần thiết khác. Mỗi sản phẩm sữa đều được in mã sản phẩm (MA) để phân biệt ,mô tả sản phẩm : Tên sữa (SUA), giá bán (GIA), ngày sản xuất (NSX), hạn sử dụng (HSD), nhà cung cấp (NCC). Có rất nhiều hộp sữa cùng loại cùng tên,chức năng,giá cả,tên ,cách sử dụng,..... phải có mã để phân biệt chung với nhau). Trên thị trường hiện nay có rất nhiều những mặt hàng sữa, tùy vào nhu cầu mà mỗi khách hàng lựa chọn cho mình một sản phẩm phù hợp với họ. Ta sẽ có nhiều hãng sản phẩm: ABBOT, NUTIFOOD, ANMUM, VINAMILK,.....Mỗi hãng sẽ có rất nhiều loại sản phẩm: Phomai, sữa chua , sữa tươi, kem, nước ép trái cây, sữa đặc..... Trong từng hãng mỗi sản phẩm sẽ có tên (TEN), giá (GIA), thương hiệu (THSP) , xuất xứ (XX), đối tượng sử dụng (ĐTSD), thông tin sản phẩm (TTSP), hạn sử dụng (HSD), quy cách đóng gói (QCĐG), hướng dẫn sử dụng (HDSD). Một cửa hàng sữa thì có ít nhất 3 nhân viên bán hàng nên mỗi nhân viên đều phải có các thông tin để phân biệt :Mã nhân viên (MA NV),tên (TEN), ngày sinh(NS), số CMND (CMND), địa chỉ (ĐC),số điên thoại liên hệ(SĐT), địa chỉ mail (MAIL). Một nhân viên tư vấn nhiều loại sữa cho khách khi khách hỏi. Mỗi khách hàng khi mua hàng đều được ghi các thông tin trên hóa đơn: Mã khách hàng (MA KH), tên khách hàng (TEN), số điện thoại(SĐT), địa chỉ(ĐC), địa chỉ mail (MAIL). một khách hàng có thể mua nhiều loại sữa. Tùy vào từng loại sữa ngoại nhập (NGN) hay nội nhập (NN) sản phẩm sữa được cửa hàng lấy từ nhiều nhà cung cấp.Mỗi nhà cung cấp phải cung cấp các thông tin như Mã nhà cung cấp (MA NCC)tên công ty (CTY), địa chỉ liên hệ ( ĐC), số điện thoại (SĐT), Fax (FAX), địa chỉ mail (MAIL) .Một công ty có thể cung cấp nhiều loại sữa. Mỗi hóa đơn mua hàng của khách hàng cần phải ghi đầy đủ các thông tin: Mã số hóa đơn (MAHĐ), tên sữa (SUA) , mã sữa (MA), hãng sữa (HANG), số lượng (SOLUONG), đơn Giá (GIA), ngày mua (NGAYMUA),Số tài khoản Cửa hàng bán sữa (STK), hình thức thanh toán (THANHTOAN). Mỗi khách hàng có một hóa đơn riêng. 2/ Sơ Đồ ER Tên THSP XX NgN NCC Gía QCĐG n NSX HSD Loại Có Có Sản Phẩm ĐTSD 1 Hãng 1 1 n n n NN TTSP Mua MA NCC 1 Tên KH SĐT KH C C ĐC 1 Địa chỉ CTY MAIL 1 FAX NCC Tên NV n MAIL Có Nhập SĐT CMND Nhân Viên Mã NV 1 xuất Số Lượng 1 n Địa chỉ n Hóa Đơn Hãng Thanh Toán Ngày mua Tên KH Mã HĐ MAIL n Nhận STK SĐT Tên NV Địa chỉ k Ngày sinh 3/ Sơ Đồ Quan Hệ Sản Phẩm (MA, SUA, GIA, NSX, HSD, NCC) Hãng Sữa (MA, SUA, GIA, TTSP, XX, ĐTSD, QCĐG, HDSD) Loại Sữa (MA, SUA, NGN, NN) Hóa Đơn (MA HĐ, SUA, MA, HANG, SOLUONG, GIA, NGAYMUA, STK, THANHTOAN) Khánh Hàng (MA KH, TEN, SĐT, ĐC, MAIL) Nhân Viên(MANV, TEN, NS, CMND, ĐC, SĐT, MAIL) Nhà Cung Cấp( MA NCC, CTY, ĐC, SĐT, FAX, MAIL) 4/Thực Hành SQL CREATE DATABASE CuaHangSua ON PRIMARY ( NAME = CuaHangSua_Data, FILENAME='F:\Cuahangsua_data.mdf', SIZE=20MB, MAXSIZE=40MB, FILEGROWTH=1MB ) LOG ON ( NAME=CuaHangSua_Log, FILENAME='F:\Cuahangsua_log.ldf', SIZE=4MB, MAXSIZE=8MB, FILEGROWTH=1MB ) USE CuaHangSua CREATE TABLE nhacungcap ( macongty NVARCHAR(10) NOT NULL CONSTRAINT pk_nhacungcap PRIMARY KEY(macongty), tencongty NVARCHAR(60) NOT NULL, tengiaodich NVARCHAR(30) NOT NULL, diachi NVARCHAR(60) NOT NULL, dienthoai NVARCHAR(20)NOT NULL, fax NVARCHAR(20) NOT NULL, email NVARCHAR(50) NOT NULL ) CREATE TABLE loaisua ( maloaisua INT NOT NULL CONSTRAINT pk_loaisua PRIMARY KEY(maloaisua), tenloaisua NVARCHAR(20) NOT NULL ) CREATE TABLE SUA ( masua NVARCHAR(10) NOT NULL CONSTRAINT pk_SUA PRIMARY KEY(masua), tensua NVARCHAR(50) NOT NULL, macongty NVARCHAR(10) NOT NULL, maloaisua INT NOT NULL, soluong INT NOT NULL, donvitinh NVARCHAR(30) NOT NULL, giasua MONEY NOT NULL ) CREATE TABLE nhanvien ( manhanvien NVARCHAR(10) NOT NULL CONSTRAINT pk_nhanvien PRIMARY KEY(manhanvien), ho NVARCHAR(20) NOT NULL , ten NVARCHAR(10) NOT NULL , ngaysinh DATETIME NOT NULL , ngaylamviec DATETIME NOT NULL , diachi NVARCHAR(60) NOT NULL , dienthoai NVARCHAR(20) NOT NULL , luongcoban MONEY NOT NULL , phucap MONEY NOT NULL ) CREATE TABLE khachhang ( makhachhang NVARCHAR(10) NOT NULL CONSTRAINT pk_khachhang PRIMARY KEY(makhachhang), tencongty NVARCHAR(60) NOT NULL , tengiaodich NVARCHAR(30) NOT NULL , tenkhachhang NVARCHAR(30) NOT NULL, diachi NVARCHAR(60)NOT NULL , email NVARCHAR(50) NOT NULL, dienthoai NVARCHAR(20) NOT NULL , fax NVARCHAR(20) NOT NULL ) CREATE TABLE dondathang ( sohoadon INT NOT NULL CONSTRAINT pk_dondathang PRIMARY KEY(sohoadon), makhachhang NVARCHAR(10)NOT NULL, manhanvien NVARCHAR(10) NOT NULL, ngaydathang SMALLDATETIME NOT NULL, ngaygiaohang SMALLDATETIME NOT NULL, ngaychuyenhang SMALLDATETIME NOT NULL, noigiaohang NVARCHAR(60)NOT NULL ) CREATE TABLE chitietdathang ( sohoadon INT NOT NULL , masua NVARCHAR(10) NOT NULL , giaban MONEY NOT NULL , soluong SMALLINT NOT NULL , mucgiamgia REAL NOT NULL, CONSTRAINT pk_chitietdathang PRIMARY KEY(sohoadon,masua) ) ALTER TABLE SUA ADD CONSTRAINT fk_masua_loaisua FOREIGN KEY (maloaisua) REFERENCES loaisua(maloaisua) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT fk_masua_nhacungcap FOREIGN KEY (macongty) REFERENCES nhacungcap(macongty) ON DELETE CASCADE ON UPDATE CASCADE ALTER TABLE dondathang ADD CONSTRAINT fk_dondathang_khachhang FOREIGN KEY (makhachhang) REFERENCES khachhang(makhachhang) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT fk_dondathang_nhanvien FOREIGN KEY (manhanvien) REFERENCES nhanvien(manhanvien) ON DELETE CASCADE ON UPDATE CASCADE ALTER TABLE chitietdathang ADD CONSTRAINT fk_chitiet_dondathang FOREIGN KEY (sohoadon) REFERENCES dondathang(sohoadon) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT fk_chitiet_sua FOREIGN KEY (masua) REFERENCES SUA(masua) ON DELETE CASCADE ON UPDATE CASCADE /*Chen du lieu vao bang nha cung cap*/ INSERT INTO nhacungcap(macongty,tencongty,tengiaodich,diachi,dienthoai,email,fax) VALUES('CTY1','Cong Ty Che Bien Thuc Pham Bao Long','HOP DONG','91,Pham Van Hai,Tan Binh,TPHCM','0812532548','[email protected]','0852647895') INSERT INTO nhacungcap(macongty,tencongty,tengiaodich,diachi,dienthoai,email,fax) VALUES('CTY2','Cong Ty Che Bien Thuc Pham Bluesky','HOA DON','91,Pham Van Hai,Binh Thanh,TPHCM','0812532858','[email protected]','0852856895') INSERT INTO nhacungcap(macongty,tencongty,tengiaodich,diachi,dienthoai,email,fax) VALUES('CTY3','Cong Ty TNHH ASIA FOOD','HOA DON','25,Le Lai,Quan 1,TPHCM','088956548','[email protected]','0857858895') /*Chen du lieu vao bang loai sua*/ INSERT INTO loaisua(maloaisua,tenloaisua) VALUES(101,'XO') INSERT INTO loaisua(maloaisua,tenloaisua) VALUES(102,'AP') INSERT INTO loaisua(maloaisua,tenloaisua) VALUES(103,'Hun') INSERT INTO loaisua(maloaisua,tenloaisua) VALUES(104,'LiLac') INSERT INTO loaisua(maloaisua,tenloaisua) VALUES(105,'Insulin') INSERT INTO loaisua(maloaisua,tenloaisua) VALUES(106,'Nf') INSERT INTO loaisua(maloaisua,tenloaisua) VALUES(107,'Matro') INSERT INTO loaisua(maloaisua,tenloaisua) VALUES(108,'Panama') INSERT INTO loaisua(maloaisua,tenloaisua) VALUES(109,'vinamilk') /*Chen du lieu vao bang sua*/ INSERT INTO SUA(masua,tensua,maloaisua,soluong,donvitinh,giasua,macongty) VALUES('S01','Sua XO',101,15,'Hop',250000,'CTY1') INSERT INTO SUA(masua,tensua,maloaisua,soluong,donvitinh,giasua,macongty) VALUES('S02','Sua ALPHA',102,20,'Hop',240000,'CTY2') INSERT INTO SUA(masua,tensua,maloaisua,soluong,donvitinh,giasua,macongty) VALUES('S03','Sua Hunama',103,10,'Hop',280000,'CTY3') INSERT INTO SUA(masua,tensua,maloaisua,soluong,donvitinh,giasua,macongty) VALUES('S04','Sua Lilac',104,15,'Hop',2650000,'CTY1') INSERT INTO SUA(masua,tensua,maloaisua,soluong,donvitinh,giasua,macongty) VALUES('S05','Sua Insulin',105,5,'Thung',300000,'CTY2') INSERT INTO SUA(masua,tensua,maloaisua,soluong,donvitinh,giasua,macongty) VALUES('S06','Sua Nemaerfalm',106,10,'Hop',270000,'CTY3') INSERT INTO SUA(masua,tensua,maloaisua,soluong,donvitinh,giasua,macongty) VALUES('S07','Sua Manamahetro',107,6,'Thung',230000,'CTY1') INSERT INTO SUA(masua,tensua,maloaisua,soluong,donvitinh,giasua,macongty) VALUES('S08','Sua Panama',108,15,'Hop',260000,'CTY2') INSERT INTO SUA(masua,tensua,maloaisua,soluong,donvitinh,giasua,macongty) VALUES('S09','Sua vinamilk',109,15,'Hop',250000,'CTY3') /*Chen du lieu vao bang nhan vien*/ INSERT INTO nhanvien(manhanvien,ho,ten,ngaysinh,ngaylamviec,luongcoban,phucap,diachi,dien thoai) VALUES('NV1','Le Van','Hiep','09/12/1992','03/28/2015',3500000,800000,'12,Le Duc Tho,Go Vap,HCM','0985214941') INSERT INTO nhanvien(manhanvien,ho,ten,ngaysinh,ngaylamviec,luongcoban,phucap,diachi,dien thoai) VALUES('NV2','Nguyen Thi','Hang','10/12/1993','02/28/2015',3500000,900000,'21,Le Lai,Quan 1,HCM','0985214958') INSERT INTO nhanvien(manhanvien,ho,ten,ngaysinh,ngaylamviec,luongcoban,phucap,diachi,dien thoai) VALUES('NV3','Hoang Thi','Thùy','08/05/1994','05/27/2015',3500000,650000,'32,Pham Van Ðong,Binh Thanh,HCM','0985218794') /*Chen du lieu vao bang khach hang*/ INSERT INTO khachhang(makhachhang,tenkhachhang,tencongty,tengiaodich,diachi,dienthoai,ema il,fax) VALUES('KH1','Vu Thi Sen','Cong Ty Che Bien Thuc Pham Bao Long','Hoa Don','98, Vo Van Tan,Quan 3,HCM','01663608013','[email protected]','0852647895') INSERT INTO khachhang(makhachhang,tenkhachhang,tencongty,tengiaodich,diachi,dienthoai,ema il,fax) VALUES('KH2','Ngo Thi Na','Cong Ty Che Bien Thuc Pham Bluesky','Hoa Don','181,Phan Vãn Tri,Go Vap,Binh Thanh,HCM','0965218941','[email protected]','0852856895') INSERT INTO khachhang(makhachhang,tenkhachhang,tencongty,tengiaodich,diachi,dienthoai,ema il,fax) VALUES('KH3','Le Anh Tuan','Cong Ty TNHH ASIA FOOD','Hoa Don','195, Phan Huu Ích,Go Vap,HCM','01674748959','[email protected]','0857858895') INSERT INTO khachhang(makhachhang,tenkhachhang,tencongty,tengiaodich,diachi,dienthoai,ema il,fax) VALUES('KH4','Le Anh Tu','Cong Ty Che Bien Thuc Pham Bao Long','Hoa Don','28, Phan Vãn Tri,Binh Thanh,HCM','01674748956','[email protected]','0852647895') INSERT INTO khachhang(makhachhang,tenkhachhang,tencongty,tengiaodich,diachi,dienthoai,ema il,fax) VALUES('KH5','Hoang Bao Nam','Cong Ty Che Bien Thuc Pham Bluesky','Hoa Don','91,Pham Vãn Ðong,Binh Thanh,HCM','01674897546','[email protected]','0852856895') INSERT INTO khachhang(makhachhang,tenkhachhang,tencongty,tengiaodich,diachi,dienthoai,ema il,fax) VALUES('KH6','Le Sy Hoa','Cong Ty TNHH ASIA FOOD','Hoa Don','22,Nguyen Cý Trinh,Quan 1,HCM','016748989754','[email protected]','0857858895') INSERT INTO khachhang(makhachhang,tenkhachhang,tencongty,tengiaodich,diachi,dienthoai,ema il,fax) VALUES('KH7','Le Thanh Binh','Cong Ty Che Bien Thuc Pham Bao Long','Hoa Don','52,Lê Ðuc Tho,Go Vap,HCM','01674812345','[email protected]','0852647895') INSERT INTO khachhang(makhachhang,tenkhachhang,tencongty,tengiaodich,diachi,dienthoai,ema il,fax) VALUES('KH8','Tran Duc Hoa','Cong Ty Che Bien Thuc Pham Bluesky','Hoa Don','84,Lê Lai,Quan 1,HCM','01674898647','[email protected]','0852856895') INSERT INTO khachhang(makhachhang,tenkhachhang,tencongty,tengiaodich,diachi,dienthoai,ema il,fax) VALUES('KH9','Pham Van Thien','Cong Ty TNHH ASIA FOOD','Hoa Don','99,Pham Vãn Ðong,Binh Thanh,HCM','01674789462','[email protected]','0857858895') /*Chen du lieu vao bang don dat hang*/ INSERT INTO dondathang(sohoadon,makhachhang,manhanvien,ngaydathang,ngaygiaohang,ngayc huyenhang,noigiaohang) VALUES('001','KH1','NV1','03/21/2017','03/22/2017','03/23/2017','98, Vo Vãn Tan,Quan 3,HCM') INSERT INTO dondathang(sohoadon,makhachhang,manhanvien,ngaydathang,ngaygiaohang,ngayc huyenhang,noigiaohang) VALUES('002','KH2','NV2','02/28/2017','03/01/2017','03/02/2017','181,Phan Vãn Tri,Go Vap,Binh Thanh,HCM') INSERT INTO dondathang(sohoadon,makhachhang,manhanvien,ngaydathang,ngaygiaohang,ngayc huyenhang,noigiaohang) VALUES('003','KH3','NV1','02/24/2017','02/25/2017','02/26/2017','195, Phan Huu Ích,Go Vap,HCM') INSERT INTO dondathang(sohoadon,makhachhang,manhanvien,ngaydathang,ngaygiaohang,ngayc huyenhang,noigiaohang) VALUES('004','KH4','NV2','02/18/2017','02/19/2017','02/20/2017','28, Phan Vãn Tri,Binh Thanh,HCM') INSERT INTO dondathang(sohoadon,makhachhang,manhanvien,ngaydathang,ngaygiaohang,ngayc huyenhang,noigiaohang) VALUES('005','KH5','NV1','02/14/2017','02/15/2017','02/16/2017','91,Pham Vãn Ðong,Binh Thanh,HCM') INSERT INTO dondathang(sohoadon,makhachhang,manhanvien,ngaydathang,ngaygiaohang,ngayc huyenhang,noigiaohang) VALUES('006','KH6','NV2','02/13/2017','02/14/2017','02/15/2017','22,Nguyen Cý Trinh,Quan 1,HCM') INSERT INTO dondathang(sohoadon,makhachhang,manhanvien,ngaydathang,ngaygiaohang,ngayc huyenhang,noigiaohang) VALUES('007','KH7','NV3','03/24/2017','03/25/2017','03/26/2017','52,Lê Ðuc Tho,Go Vap,HCM,HCM') INSERT INTO dondathang(sohoadon,makhachhang,manhanvien,ngaydathang,ngaygiaohang,ngayc huyenhang,noigiaohang) VALUES('008','KH8','NV2','04/24/2017','04/25/2017','04/26/2017','84,Lê Lai,Quan 1,HCM') INSERT INTO dondathang(sohoadon,makhachhang,manhanvien,ngaydathang,ngaygiaohang,ngayc huyenhang,noigiaohang) VALUES('009','KH9','NV3','04/22/2017','04/23/2017','04/24/2017','99,Pham Vãn Ðong,Binh Thanh,HCM') /*Chen du lieu vao bang chi tiet dat hang*/ INSERT INTO chitietdathang(sohoadon,masua,giaban,soluong,mucgiamgia) VALUES('001','S01',250000,3,1) INSERT INTO chitietdathang(sohoadon,masua,giaban,soluong,mucgiamgia) VALUES('002','S02',240000,6,2) INSERT INTO chitietdathang(sohoadon,masua,giaban,soluong,mucgiamgia) VALUES('003','S03',280000,4,3) INSERT INTO chitietdathang(sohoadon,masua,giaban,soluong,mucgiamgia) VALUES('004','S04',265000,5,4) INSERT INTO chitietdathang(sohoadon,masua,giaban,soluong,mucgiamgia) VALUES('005','S05',300000,2,5) INSERT INTO chitietdathang(sohoadon,masua,giaban,soluong,mucgiamgia) VALUES('006','S06',270000,3,6) INSERT INTO chitietdathang(sohoadon,masua,giaban,soluong,mucgiamgia) VALUES('007','S07',230000,3,7) INSERT INTO chitietdathang(sohoadon,masua,giaban,soluong,mucgiamgia) VALUES('008','S08',260000,4,8) INSERT INTO chitietdathang(sohoadon,masua,giaban,soluong,mucgiamgia) VALUES('009','S09',250000,7,9) /*Truy Van*/ /*1.Cho biet danh sach cac cong ty cung cap sua cho cua hang*/ SELECT macongty,tencongty,tengiaodich FROM nhacungcap /*2.Cho biet ma sua, ten sua va so luong sua co trong cua hang*/ SELECT masua,tensua,soluong FROM SUA /*3.Ho ten,dia chi va nam bat dau lam viec cua cac nhan vien trong cong ty*/ SELECT ho,ten,year(ngaylamviec) AS namlamviec FROM nhanvien /*4.Dia chi va dien thoai nha cung cap co ten giao dich la hop dong*/ SELECT diachi,dienthoai FROM nhacungcap WHERE tengiaodich='Hop Dong' /*5.Cho biet ma va ten cua san pham sua co gia tri lon hon 250000 va so luong it hon 10*/ SELECT masua,tensua FROM SUA WHERE giasua>250000 AND soluong<10 /*6.Cho biet moi san pham sua trong cua hang do ai cung cap*/ SELECT masua,tensua,nhacungcap.macongty,tencongty,tengiaodich FROM SUA INNER JOIN nhacungcap ON SUA.macongty=nhacungcap.macongty /*7.Cong Ty Che Bien Thuc Pham Bao Long cung cap nhung san pham sua nao*/ SELECT masua,tensua FROM SUA INNER JOIN nhacungcap ON SUA.macongty=nhacungcap.macongty WHERE tencongty='Cong Ty Che Bien Thuc Pham Bao Long' /*8.Loai Sua XO do nhung cong ty nao cung cap va dia chi cac cong ty do la gi*/ SELECT DISTINCT nhacungcap.macongty,tencongty,diachi FROM (loaisua INNER JOIN SUA ON loaisua.maloaisua=SUA.maloaisua) INNER JOIN nhacungcap ON SUA.macongty=nhacungcap.macongty WHERE tenloaisua='XO' /*9.Ten giao dich(khach hang) da dat mua san pham sua hop Insulin cua cua hang*/ SELECT DISTINCT tengiaodich FROM ((SUA INNER JOIN chitietdathang ON SUA.masua=chitietdathang.masua) INNER JOIN dondathang ON chitietdathang.sohoadon=dondathang.sohoadon)INNER JOIN khachhang ON dondathang.makhachhang=khachhang.makhachhang WHERE tensua='Sua Insulin' /*10.don dat hang so 002 do ai dat va nhan vien nao lap,thoi gian, dia diem giao sua o dau*/ SELECT dondathang.manhanvien,ho,ten,ngaygiaohang,noigiaohang FROM nhanvien INNER JOIN dondathang ON nhanvien.manhanvien=dondathang.manhanvien WHERE sohoadon=002 /*11. cho biet tien luong cua hang tra cho moi nhan vien la bao nhieu*/ SELECT manhanvien,ho,ten,luongcoban + CASE WHEN phucap IS NULL THEN 0 ELSE phucap END AS luong FROM nhanvien /*12. Trong don dat hang so 004 dat mua san pham sua nao va so tien khach hang tra cho san pham sua la bao nhieu (so tien tra cho cho san pham sua tinh theo cong thuc sau: SOLUONG×GIASUA SOLUONG×GIASUA×MUCGIAMGIA/100)*/ SELECT a.masua,tensua, a.soluong*giasua*(1-mucgiamgia/100) AS sotien FROM chitietdathang AS a INNER JOIN SUA AS b ON a.masua=b.masua /*13. Hay cho biet khach hang nao lai chinh la doi tac cung cap san pham sua cho cua hang(cung ten giao dich) nha cung cap co nhieu nhan vien mua sua o cua hang */ SELECT makhachhang,tenkhachhang,khachhang.tencongty,khachhang.tengiaodich FROM khachhang INNER JOIN nhacungcap ON khachhang.tengiaodich=nhacungcap.tengiaodich /*14.Trong cua hang co nhung nhan vien nao cung ngay sinh*/ SELECT a.ho,a.ten,b.ho,b.ten,b.ngaysinh FROM nhanvien a INNER JOIN nhanvien b ON a.ngaysinh=b.ngaysinh AND a.manhanvien<>b.manhanvien /*15.Nhung don dat hang nao yeu cau giao hang tai cong ty va don hang do cua cong ty nao*/ SELECT sohoadon,tencongty,tengiaodich,ngaydathang,noigiaohang FROM dondathang INNER JOIN khachhang ON dondathang.noigiaohang=khachhang.diachi /*16. Cho biet ten cong ty, ten giao dich, dia chi va dien thoai cac nha cung cap san pham sua cho cong ty*/ SELECT tencongty,tengiaodich,diachi,dienthoai FROM khachhang UNION ALL SELECT tencongty,tengiaodich,diachi,dienthoai FROM nhacungcap /*17.Nhung san pham sua nao chua tung duoc dat mua */ SELECT masua,tensua FROM SUA WHERE NOT EXISTS (SELECT masua FROM chitietdathang WHERE masua=SUA.masua) /*18. Nhan vien nao trong cua hang chua tung lap bat ki hoa don nao*/ SELECT manhanvien,ho,ten FROM nhanvien WHERE NOT EXISTS (SELECT manhanvien FROM dondathang WHERE manhanvien=nhanvien.manhanvien) /*19.Nhan vien nao cua cong ty co phu cap cao nhat*/ SELECT manhanvien,ho,ten,phucap FROM nhanvien WHERE phucap=(SELECT MAX(phucap) FROM nhanvien) /*20. Tong so tien mà khach hang tra cho cong ty la bao nhieu tren moi don hang*/ SELECT dondathang.sohoadon,dondathang.makhachhang, tencongty,tenkhachhang, SUM(soluong*giaban-soluong*giaban*mucgiamgia/100) FROM (khachhang INNER JOIN dondathang ON khachhang.makhachhang=dondathang.makhachhang) INNER JOIN chitietdathang ON dondathang.sohoadon=chitietdathang.sohoadon GROUP BY dondathang.makhachhang,tencongty, tenkhachhang,dondathang.sohoadon /*21.Trong nam 2017 nhung khach hang nao da dat mua 1 lan*/ SELECT SUA.masua,tensua FROM (SUA INNER JOIN chitietdathang ON SUA.masua=chitietdathang.masua) INNER JOIN dondathang ON chitietdathang.sohoadon=dondathang.sohoadon WHERE YEAR(ngaydathang)=2017 GROUP BY SUA.masua,tensua HAVING COUNT(chitietdathang.masua)=1 /*22 Hay cho biet moi khach hang bo ra bao nhieu tien de mua dat mua hang*/ SELECT khachhang.makhachhang,tencongty,tenkhachhang, SUM(soluong*giaban-soluong*giaban*mucgiamgia/100) FROM (khachhang INNER JOIN dondathang ON khachhang.makhachhang = dondathang.makhachhang) INNER JOIN chitietdathang ON dondathang.sohoadon=chitietdathang.sohoadon GROUP BY khachhang.makhachhang,tencongty,tenkhachhang /*23. Moi nhan vien lap duoc bao nhieu hoa don*/ SELECT nhanvien.manhanvien,ho,ten,COUNT(sohoadon) FROM nhanvien LEFT OUTER JOIN dondathang ON nhanvien.manhanvien=dondathang.manhanvien GROUP BY nhanvien.manhanvien,ho,ten /*24.cho biet tong so tien ma cua hang thu duoc nhung thang dau nam 2017(thoi gian tinh duoc dat theo ngay dat hang)*/ SELECT MONTH(ngaydathang) AS thang, SUM(soluong*giaban-soluong*giaban*mucgiamgia/100) FROM dondathang INNER JOIN chitietdathang ON dondathang.sohoadon=chitietdathang.sohoadon WHERE year(ngaydathang)=2017 GROUP BY month(ngaydathang) /*25. Hay cho biet tien lai ma cong ty thu duoc may thang dau nam 2017 la bao nhieu*/ SELECT c.masua,tensua, SUM(b.soluong*giasua-b.soluong*giasua*mucgiamgia/100)SUM(b.soluong*giasua) FROM (dondathang AS a INNER JOIN chitietdathang AS b ON a.sohoadon=b.sohoadon) INNER JOIN SUA AS c ON b.masua=c.masua WHERE YEAR(ngaydathang)=2017 GROUP BY c.masua,tensua /*26.Hay cho biet tong so luong sua ma moi san pham sua ma cong ty da co(tong so luong sua da co va da ban)*/ SELECT SUA.masua,tensua, SUA.soluong + CASE WHEN SUM(chitietdathang.soluong) IS NULL THEN 0 ELSE SUM(chitietdathang.soluong) END AS tongsoluong FROM SUA LEFT OUTER JOIN chitietdathang ON SUA.masua=chitietdathang.masua GROUP BY SUA.masua,tensua,SUA.soluong /*27.nhan vien nao cua cong ty ban duoc so luong sua nhieu nhat va so luong la bao nhieu*/ SELECT nhanvien.manhanvien,ho,ten,sum(soluong) FROM (nhanvien INNER JOIN dondathang ON nhanvien.manhanvien=dondathang.manhanvien) INNER JOIN chitietdathang ON dondathang.sohoadon=chitietdathang.sohoadon GROUP BY nhanvien.manhanvien,ho,ten HAVING sum(soluong)>=ALL(SELECT sum(soluong) FROM (nhanvien INNER JOIN dondathang ON nhanvien.manhanvien=dondathang.manhanvien) INNER JOIN chitietdathang ON dondathang.sohoadon=chitietdathang.sohoadon GROUP BY nhanvien.manhanvien,ho,ten) /*28.Don hang nao co so luong sua duoc dat mua it nhat*/ SELECT dondathang.sohoadon,SUM(soluong) FROM dondathang INNER JOIN chitietdathang ON dondathang.sohoadon=chitietdathang.sohoadon GROUP BY dondathang.sohoadon HAVING sum(soluong)<=ALL(SELECT sum(soluong) FROM dondathang INNER JOIN chitietdathang ON dondathang.sohoadon=chitietdathang.sohoadon GROUP BY dondathang.sohoadon) /*29. So tien nhieu nhat ma moi khach hang da tung bo ra dat hang la bao nhieu*/ SELECT TOP 1 SUM(soluong*giaban-soluong*giaban*mucgiamgia/100) FROM dondathang INNER JOIN chitietdathang ON dondathang.sohoadon=chitietdathang.sohoadon ORDER BY 1 DESC /*30.Tang gap doi so luong nhung mat hang do Cong Ty Che Bien Thuc Pham Bluesky cung cap */ UPDATE SUA SET soluong=soluong*2 FROM nhacungcap WHERE nhacungcap.macongty=SUA.macongty AND tencongty='Cong Ty Che Bien Thuc Pham Bluesky' /*31.Cho biet moi mot loai sua bao gom nhung san pham sua nao, tong so loai sua cua moi loai sua va tong tat ca san pham sua hien co trong cua hang la bao nhieu*/ SELECT loaisua.maloaisua,tenloaisua,masua,tensua,soluong FROM loaisua INNER JOIN SUA ON loaisua.maloaisua=SUA.maloaisua ORDER BY loaisua.maloaisua COMPUTE SUM(soluong) BY loaisua.maloaisua COMPUTE SUM(soluong) 5/ Database Diagrams