You're Reading a Free Preview Bài tập 1: NHANVIEN (MANV,HOTEN, NGVL, SODT) Tân từ: Mỗi nhân viên bán hàng cần ghi nhận họ tên, ngày vào làm, điện thọai liên lạc, mỗi nhân viên phân iệt với nhau bằng mã nhân viên.SANPHAM (MASP,TENSP, DVT, NUOCSX, GIA) Tân từ: Mỗi sản phẩm có một mã số, một tên gọi, đơn vị tính, nước sản xuất và một giá bán.HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA) Tân từ: Khi mua hàng, mỗi khách hàng sẽ nhận một hóa đơn tính tiền, trong đó sẽ có số hóa đơn, ngày mua, hân viên nào bán hàng, trị giá của hóa đơn là bao nhiêu và mã số của khách hàng nếu là khách hàng thành iên.CTHD (SOHD,MASP,SL) Tân từ: Diễn giải chi tiết trong mỗi hóa đơn gồm có những sản phẩm gì với số lượng là bao nhiêu.  40. *Tìm nước sản xuất sản xuất ít nhất 3 sản phẩm có giá bán khác nhau
CREATE TABLE KHACHHANG( MAKH char(4) PRIMARY KEY, HOTEN varchar(40), DCHI varchar(50), SODT varchar(20), NGSINH smalldatetime, NGDK smalldatetime, DOANHSO money)CREATE TABLE NHANVIEN( MANV char(4)PRIMARY KEY, HOTEN varchar(40), SODT varchar(20), NGVL smalldatetime)CREATE TABLE SANPHAM( MASP char(4)PRIMARY KEY, TENSP varchar(40), DVT varchar(20), NUOCSX varchar(40), GIA money)CREATE TABLE HOADON( SOHD int PRIMARY KEY, NGHD smalldatetime, MAKH char(4) FOREIGN KEY REFERENCES KHACHHANG(MAKH), MANV char(4) FOREIGN KEY REFERENCES NHANVIEN(MANV), TRIGIA money)CREATE TABLE CTHD( SOHD int FOREIGN KEY REFERENCES HOADON(SOHD), MASP char(4) FOREIGN KEY REFERENCES SANPHAM(MASP), SL int, CONSTRAINT PK_CTHD PRIMARY KEY (SOHD,MASP))--I:DINH NGHIA DU LIEUALTER TABLE SANPHAM ADD GHICHU VARCHAR(20)ALTER TABLE KHACHHANG ADD LOAIKH TINYINT ALTER TABLE SANPHAM ALTER COLUMN GHICHU VARCHAR(100)ALTER TABLE SANPHAM DROP COLUMN GHICHUALTER TABLE KHACHHANG ALTER COLUMN LOAIKH VARCHAR(50)ALTER TABLE SANPHAM ADD CONSTRAINT CHECK_DVT CHECK(DVT='CAY'OR DVT='CAI'OR DVT='HOP'OR DVT='QUYEN'OR DVT='CHUC')ALTER TABLE SANPHAM ADD CONSTRAINT CHECK_GIA CHECK(GIA>=500)ALTER TABLE KHACHHANG ADD CONSTRAINT CHECK_NGDK CHECK (NGDK>NGSINH) /*CAU 11*/CREATE TRIGGER UPDATE_KH_C11ON KHACHHANGFOR UPDATEAS DECLARE @NGDK SMALLDATETIME, @NGHD SMALLDATETIME SELECT @NGDK=NGDK FROM INSERTED IF(@NGDK>ANY(SELECT NGHD FROM HOADON A, INSERTED I WHERE A.MAKH=I.MAKH)) BEGIN ROLLBACK TRAN PRINT 'ERROR!NGDK PHAI NHO HON NGHD' END ELSE PRINT' SUCCESSFUL' -------CREATE TRIGGER HD_C11ON HOADONFOR INSERT,UPDATEAS DECLARE @NGDK SMALLDATETIME, @NGHD SMALLDATETIME SELECT @NGDK=NGDK,@NGHD=NGHD FROM INSERTED I, KHACHHANG A WHERE I.MAKH=A.MAKH IF @NGHD<@NGDK BEGIN ROLLBACK TRAN PRINT 'ERROR!NGHD PHAI LON HON NGDK' END ELSE PRINT' SUCCESSFUL'/*CAU 12*/CREATE TRIGGER UPDATE_NV_C12ON NHANVIENFOR UPDATEAS DECLARE @NGVL SMALLDATETIME, @NGHD SMALLDATETIME SELECT @NGVL=NGVL FROM INSERTED IF(@NGVL>ANY(SELECT NGHD FROM HOADON A, INSERTED I WHERE A.MANV=I.MANV)) BEGIN ROLLBACK TRAN PRINT 'ERROR!NGVL PHAI NHO HON NGHD' END ELSE PRINT' SUCCESSFUL' -------CREATE TRIGGER HD_C12ON HOADONFOR INSERT,UPDATEAS DECLARE @NGVL SMALLDATETIME, @NGHD SMALLDATETIME SELECT @NGVL=NGVL,@NGHD=NGHD FROM INSERTED I, NHANVIEN A WHERE I.MANV=A.MANV IF @NGHD<@NGVL BEGIN ROLLBACK TRAN PRINT 'ERROR!NGHD PHAI LON HON NGVL' END ELSE PRINT' SUCCESSFUL'/*CAU 13*/CREATE TRIGGER CTHD_C13ON CTHDFOR DELETE,UPDATEAS DECLARE @SL INT, @SOHD INT SELECT @SL=COUNT(A.SOHD),@SOHD=D.SOHD FROM DELETED D,CTHD A WHERE A.SOHD=D.SOHD GROUP BY D.SOHD IF(@SL<1) BEGIN DELETE FROM HOADON WHERE SOHD=@SOHD PRINT 'DA DELETE CTHD CUOI CUNG CUA HOADON TREN' END ------CREATE TRIGGER HOADON_C13ON HOADONFOR INSERTAS DECLARE @SOHD INT SELECT @SOHD=SOHD FROM INSERTED UPDATE CTHD SET MASP='NONE',SL=0 WHERE SOHD=@SOHD PRINT 'SUCCESSFUL! DE NGHI UPDATE LAI CTHD(MAC DINH:MASP="NONE", SL=0)'/*CAU 14*/CREATE TRIGGER INSERT_HOADON_C14ON HOADONFOR INSERTAS UPDATE HOADON SET TRIGIA=0 WHERE SOHD=(SELECT SOHD FROM INSERTED) PRINT'DA INSERT 1 HOADON VOI TRIGIA BAN DAU LA 0 VND' -------------CREATE TRIGGER UPDATE_HOADON_C14ON HOADONFOR INSERTAS UPDATE HOADON SET TRIGIA=(SELECT TRIGIA FROM DELETED) WHERE SOHD=(SELECT SOHD FROM INSERTED) PRINT'DA UPDATE 1 HOADON VOI TRIGIA KHONG THAY DOI' -------------CREATE TRIGGER INSERT_CTHD_C14ON CTHDFOR INSERTAS DECLARE @SL INT, @GIA MONEY, @SOHD INT SELECT @GIA=GIA,@SL=SL,@SOHD=SOHD FROM INSERTED A, SANPHAM B WHERE A.MASP=B.MASP UPDATE HOADON SET TRIGIA=TRIGIA+@SL*@GIA PRINT'DA INSERT 1 CTHD VA UPDATE LAI TRIGIA CUA HOADON TUONG UNG' --------------CREATE TRIGGER DELETE_CTHD_C14ON CTHDFOR DELETEAS DECLARE @SL INT, @GIA MONEY, @SOHD INT SELECT @GIA=GIA,@SL=SL,@SOHD=SOHD FROM DELETED A, SANPHAM B WHERE A.MASP=B.MASP UPDATE HOADON SET TRIGIA=TRIGIA-@SL*@GIA PRINT'DA DELETE 1 CTHD VA UPDATE LAI TRIGIA CUA HOADON TUONG UNG' -------------------CREATE TRIGGER UPDATE_CTHD_C14ON CTHDFOR UPDATEAS DECLARE @SL_CU INT, @SL_MOI INT, @GIA_CU MONEY, @GIA_MOI MONEY, @SOHD_CU INT, @SOHD_MOI INT SELECT @GIA_CU=GIA,@SL_CU=SL,@SOHD_CU=SOHD FROM DELETED A, SANPHAM B WHERE A.MASP=B.MASP SELECT @GIA_MOI=GIA,@SL_MOI=SL,@SOHD_MOI=SOHD FROM INSERTED A, SANPHAM B WHERE A.MASP=B.MASP IF(@SOHD_CU=@SOHD_MOI) BEGIN UPDATE HOADON SET TRIGIA=TRIGIA+@SL_MOI*@GIA_MOI-@SL_CU*@GIA_CU WHERE SOHD=@SOHD_CU END ELSE BEGIN UPDATE HOADON SET TRIGIA=TRIGIA-@SL_CU*@GIA_CU WHERE SOHD=@SOHD_CU UPDATE HOADON SET TRIGIA=TRIGIA+@SL_MOI*@GIA_MOI WHERE SOHD=@SOHD_MOI END PRINT'DA UPDATE 1 CTHD VA UPDATE LAI TRIGIA CUA HOADON TUONG UNG' -------------------CREATE TRIGGER UPDATE_HOADON_C14ON HOADONFOR INSERTAS DECLARE @GIA_CU MONEY, @GIA_MOI MONEY, @SOHD INT, @SL INT SELECT @GIA_CU=GIA FROM DELETED SELECT @GIA_MOI=GIA FROM INSERTED SELECT @SOHD=SOHD,@SL=SL FROM INSERTED A, CTHD B WHERE A.MASP=B.MASP UPDATE HOADON SET TRIGIA=TRIGIA+@SL*(@GIA_MOI-@GIA_CU) WHERE SOHD=@SOHD PRINT'DA UPDATE 1 HOADON VOI TRIGIA KHONG THAY DOI'/*CAU 15*/CREATE TRIGGER INSERT_KHACHHANG_C15ON KHACHHANGFOR INSERTAS DECLARE @MAKH CHAR(4) SELECT @MAKH=MAKH FROM INSERTED UPDATE KHACHHANG SET DOANHSO=0 WHERE MAKH=@MAKH PRINT 'DA INSERT 1 KHACHHANG MOI VOI DOANHSO BAN DAU LA 0 VND' ----------------CREATE TRIGGER UPDATE_KHACHHANG_C15ON KHACHHANGFOR UPDATEAS DECLARE @MAKH CHAR(4), @DOANHSO_CU MONEY SELECT @MAKH=MAKH FROM INSERTED SELECT @DOANHSO_CU=DOANHSO FROM DELETED UPDATE KHACHHANG SET DOANHSO=@DOANHSO_CU WHERE MAKH=@MAKH PRINT 'DA UPDATE 1 KHACHHANG' ----------------CREATE TRIGGER INSERT_HOADON_C15ON HOADONFOR INSERTAS DECLARE @TRIGIA MONEY, @MAKH CHAR(4) SELECT @MAKH=MAKH,@TRIGIA=TRIGIA FROM INSERTED UPDATE KHACHHANG SET DOANHSO=DOANHSO+@TRIGIA WHERE MAKH=@MAKH PRINT 'DA INSERT 1 HODON MOI VA UPDATE LAI DOANHSO CUA KH CO SOHD TREN' -----------CREATE TRIGGER DELETE_HOADON_C15ON HOADONFOR DELETEAS DECLARE @TRIGIA MONEY, @MAKH CHAR(4) SELECT @MAKH=MAKH,@TRIGIA=TRIGIA FROM DELETED UPDATE KHACHHANG SET DOANHSO=DOANHSO-@TRIGIA WHERE MAKH=@MAKH PRINT 'DA DELETE 1 HODON MOI VA UPDATE LAI DOANHSO CUA KH CO SOHD TREN' ---------------CREATE TRIGGER UPDATE_HOADON_C15ON HOADONFOR UPDATEAS DECLARE @TRIGIA_CU MONEY, @TRIGIA_MOI MONEY, @MAKH CHAR(4) SELECT @MAKH=MAKH,@TRIGIA_MOI=TRIGIA SELECT @MAKH=MAKH,@TRIGIA_CU=TRIGIA FROM DELETED UPDATE KHACHHANG SET DOANHSO=DOANHSO+@TRIGIA_MOI-@TRIGIA_CU WHERE MAKH=@MAKH PRINT 'DA UPDATE 1 HOADON MOI VA UPDATE LAI DOANHSO CUA KH CO SOHD TREN'--------------------------TRUY VAN DU LIEU-------------------------------------------------------------------CAU 1)SELECT MASP,TENSPFROM SANPHAMWHERE NUOCSX='TRUNG QUOC'--CAU 2)SELECT MASP,TENSPFROM SANPHAMWHERE DVT='CAY' OR DVT='QUYEN'--CAU 3)SELECT MASP,TENSPFROM SANPHAMWHERE MASP LIKE 'B%01'--CAU 4)SELECT MASP,TENSPFROM SANPHAMWHERE NUOCSX='TRUNG QUOC' AND GIA BETWEEN 30000 AND 40000--CAU 5)SELECT MASP,TENSPFROM SANPHAMWHERE (NUOCSX='TRUNG QUOC' OR NUOCSX='THAI LAN') AND GIA BETWEEN 30000 AND 40000--CAU 6)SELECT SOHD,TRIGIAFROM HOADONWHERE NGHD='1/1/2007' OR NGHD='2/1/2007'--CAU 7)SELECT SOHD,TRIGIAFROM HOADONWHERE MONTH(NGHD)=1 AND YEAR(NGHD)=2007ORDER BY NGHD ASC,TRIGIA DESC--CAU 8)SELECT A.MAKH,HOTENFROM HOADON A, KHACHHANG BWHERE A.MAKH=B.MAKH AND NGHD='1/1/2007'--CAU 9)SELECT SOHD,TRIGIAFROM HOADON A, NHANVIEN BWHERE A.MANV=B.MANV AND NGHD='28/10/2006' AND HOTEN='NGUYEN VAN B' --CAU 10)SELECT C.MASP, TENSPFROM HOADON A, KHACHHANG B, CTHD C, SANPHAM DWHERE A.MAKH=B.MAKH AND A.SOHD=C.SOHD AND C.MASP=D.MASP AND MONTH(NGHD)=10 AND YEAR(NGHD)=2006 AND HOTEN='NGUYEN VAN A' --CAU 11)SELECT SOHDFROM CTHDWHERE MASP='BB01'OR MASP='BB02'--CAU 12)SELECT SOHDFROM CTHDWHERE (MASP='BB01'OR MASP='BB02') AND SL BETWEEN 10 AND 20--CAU 13)SELECT SOHDFROM CTHDWHERE SL BETWEEN 10 AND 20 AND MASP='BB01' AND SOHD IN ( SELECT SOHD FROM CTHD WHERE MASP='BB02')--CAU 14)SELECT DISTINCT A.MASP,TENSPFROM SANPHAM A, HOADON B, CTHD CWHERE NUOCSX='TRUNG QUOC' OR (B.SOHD=C.SOHD AND C.MASP=A.MASP AND NGHD='1/1/2007') --CAU 15)SELECT MASP, TENSPFROM SANPHAMWHERE MASP NOT IN ( SELECT MASP FROM CTHD)--CAU 16)SELECT MASP, TENSPFROM SANPHAMWHERE MASP NOT IN ( SELECT A.MASP FROM CTHD A, HOADON B WHERE A.SOHD=B.SOHD AND YEAR(NGHD)=2006)--CAU 17)SELECT MASP, TENSPFROM SANPHAMWHERE NUOCSX='TRUNG QUOC' AND MASP NOT IN ( SELECT A.MASP FROM CTHD A, HOADON B WHERE A.SOHD=B.SOHD AND YEAR(NGHD)=2006)--CAU 18)SELECT DISTINCT SOHDFROM CTHD AWHERE NOT EXISTS(SELECT * FROM SANPHAM B WHERE NUOCSX='SINGAPORE' AND NOT EXISTS(SELECT * FROM CTHD C WHERE C.MASP=B.MASP AND C.SOHD=A.SOHD))--CAU 19)--CAU 20)SELECT COUNT(SOHD)FROM HOADONWHERE MAKH IS NULL--CAU 21)SELECT COUNT(DISTINCT MASP)FROM HOADON A, CTHD BWHERE A.SOHD=B.SOHD AND YEAR(NGHD)=2006--CAU 22)SELECT MAX(TRIGIA) [TRI GIA CAO NHAT],MIN(TRIGIA) [TRI GIA THAP NHAT] FROM HOADON--CAU 23)SELECT AVG(TRIGIA)FROM HOADONWHERE YEAR(NGHD)=2006--CAU 24)SELECT SUM(TRIGIA) [DOANH THU]FROM HOADONWHERE YEAR(NGHD)=2006--CAU 27)SELECT *FROM KHACHHANGWHERE DOANHSO IN(SELECT TOP 3 DOANHSO FROM KHACHHANG ORDER BY DOANHSO DESC) --CAU 28)SELECT *FROM SANPHAMWHERE GIA IN(SELECT TOP 3 GIA FROM SANPHAM ORDER BY GIA DESC) --CAU 29)SELECT *FROM SANPHAMWHERE NUOCSX='THAI LAN' AND GIA IN(SELECT TOP 3 GIA FROM SANPHAM ORDER BY GIA DESC) --CAU 30)SELECT *FROM SANPHAMWHERE NUOCSX='TRUNG QUOC' AND GIA IN(SELECT TOP 3 GIA FROM SANPHAM WHERE NUOCSX='TRUNG QUOC' ORDER BY GIA DESC) --CAU 31)SELECT *FROM KHACHHANGWHERE DOANHSO IN(SELECT TOP 3 DOANHSO FROM KHACHHANG ORDER BY DOANHSO DESC) ORDER BY DOANHSO DESC--CAU 32)SELECT COUNT(MASP)FROM SANPHAMWHERE NUOCSX='TRUNG QUOC'--CAU 33)SELECT NUOCSX,COUNT(MASP) [SO SAN PHAM]FROM SANPHAMGROUP BY NUOCSX--CAU 34)SELECT NUOCSX,MIN(GIA) [GIA THAP NHAT], AVG(GIA) [GIA TB], MAX(GIA) [GIA CAO NHAT]FROM SANPHAMGROUP BY NUOCSX--CAU 35)SELECT NGHD,SUM(TRIGIA) [DOANH THU]FROM HOADONGROUP BY NGHD--CAU 36)SELECT MASP,SUM(SL) [TONG SO LUONG]FROM HOADON A, CTHD BWHERE A.SOHD=B.SOHD AND MONTH(NGHD)=10 AND YEAR(NGHD)=2006 GROUP BY MASP --CAU 37)SELECT MONTH(NGHD) THANG,SUM(TRIGIA) [DOANH THU]FROM HOADON A, CTHD BWHERE A.SOHD=B.SOHD AND YEAR(NGHD)=2006 GROUP BY MONTH(NGHD)--CAU 38)SELECT *FROM HOADONWHERE SOHD IN (SELECT SOHD FROM CTHD GROUP BY SOHD HAVING COUNT(DISTINCT MASP)>=4)--CAU 39)SELECT *FROM HOADONWHERE SOHD IN (SELECT SOHD FROM CTHD A, SANPHAM B WHERE A.MASP=B.MASP AND NUOCSX='VIET NAM' GROUP BY SOHD HAVING COUNT(DISTINCT A.MASP)=3)--CAU 40)SELECT *FROM KHACHHANGWHERE MAKH IN (SELECT MAKH FROM HOADON GROUP BY MAKH HAVING COUNT(SOHD)>=ALL(SELECT COUNT(SOHD) FROM HOADON GROUP BY MAKH))--CAU 41)SELECT MONTH(NGHD) THANGFROM HOADONWHERE YEAR(NGHD)=2006GROUP BY MONTH(NGHD)HAVING SUM(TRIGIA)>=ALL(SELECT SUM(TRIGIA) FROM HOADON WHERE YEAR(NGHD)=2006 GROUP BY MONTH(NGHD))--CAU 42)SELECT B.MASP, TENSPFROM SANPHAM A,CTHD B, HOADON CWHERE A.MASP=B.MASP AND B.SOHD=C.SOHD AND YEAR(NGHD)=2006GROUP BY B.MASP,TENSPHAVING SUM(SL)>=ALL(SELECT SUM(SL) FROM CTHD A, HOADON B WHERE A.SOHD=B.SOHD AND YEAR(NGHD)=2006 GROUP BY MASP)--CAU 43)SELECT NUOCSX,MASP, TENSPFROM SANPHAM AWHERE GIA=(SELECT MAX(GIA) FROM SANPHAM B WHERE A.NUOCSX=B.NUOCSX)GROUP BY NUOCSX,MASP,TENSP--CAU 44)SELECT NUOCSXFROM SANPHAMGROUP BY NUOCSXHAVING COUNT(DISTINCT GIA)>=3--CAU 45)SELECT *FROM KHACHHANGWHERE MAKH IN (SELECT A.MAKH FROM HOADON A, KHACHHANG B WHERE A.MAKH=B.MAKH AND DOANHSO IN (SELECT TOP 10 DOANHSO FROM KHACHHANG ORDER BY DOANHSO DESC) GROUP BY A.MAKH HAVING COUNT(SOHD)>=ALL(SELECT COUNT(SOHD) FROM HOADON A, KHACHHANG B WHERE A.MAKH=B.MAKH AND DOANHSO IN (SELECT TOP 10 DOANHSO FROM KHACHHANG ORDER BY DOANHSO DESC) GROUP BY A.MAKH)) Link download bài tập , hướng dẫn giải Xem thêm tài liệu cơ sở dữ liệu |
