Bài tập chương 3 cơ sở dữ liệu năm 2024

Bài tập 1: Cơ sở dữ liệu quản lý bán hàng gồm có các quan hệ sau: KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK) Tân từ: Quan hệ khách hàng sẽ lưu trữ thông tin của khách hàng thành viên gồm có các thuộc tính: mã khách àng, họ tên, địa chỉ, số điện thoại, ngày sinh, ngày đăng ký và doanh số (tổng trị giá các hóa đơn của khách àng thành viên này). 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.

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 LIEU ALTER 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 GHICHU ALTER 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_C11 ON KHACHHANG FOR UPDATE AS 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_C11 ON HOADON FOR INSERT,UPDATE AS 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_C12 ON NHANVIEN FOR UPDATE AS 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_C12 ON HOADON FOR INSERT,UPDATE AS 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_C13 ON CTHD FOR DELETE,UPDATE AS 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_C13 ON HOADON FOR INSERT AS 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_C14 ON HOADON FOR INSERT AS 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_C14 ON HOADON FOR INSERT AS 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_C14 ON CTHD FOR INSERT AS 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_C14 ON CTHD FOR DELETE AS 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_C14 ON CTHD FOR UPDATE AS 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_C14 ON HOADON FOR INSERT AS 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_C15 ON KHACHHANG FOR INSERT AS 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_C15 ON KHACHHANG FOR UPDATE AS 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_C15 ON HOADON FOR INSERT AS 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_C15 ON HOADON FOR DELETE AS 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_C15 ON HOADON FOR UPDATE AS DECLARE @TRIGIA_CU MONEY, @TRIGIA_MOI MONEY, @MAKH CHAR(4)

SELECT @MAKH=MAKH,@TRIGIA_MOI=TRIGIA FROM INSERTED

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,TENSP FROM SANPHAM WHERE NUOCSX='TRUNG QUOC' --CAU 2) SELECT MASP,TENSP FROM SANPHAM WHERE DVT='CAY' OR DVT='QUYEN' --CAU 3) SELECT MASP,TENSP FROM SANPHAM WHERE MASP LIKE 'B%01' --CAU 4) SELECT MASP,TENSP FROM SANPHAM WHERE NUOCSX='TRUNG QUOC' AND GIA BETWEEN 30000 AND 40000 --CAU 5) SELECT MASP,TENSP FROM SANPHAM WHERE (NUOCSX='TRUNG QUOC' OR NUOCSX='THAI LAN') AND GIA BETWEEN 30000 AND 40000 --CAU 6) SELECT SOHD,TRIGIA FROM HOADON WHERE NGHD='1/1/2007' OR NGHD='2/1/2007' --CAU 7) SELECT SOHD,TRIGIA FROM HOADON WHERE MONTH(NGHD)=1 AND YEAR(NGHD)=2007 ORDER BY NGHD ASC,TRIGIA DESC --CAU 8) SELECT A.MAKH,HOTEN FROM HOADON A, KHACHHANG B WHERE A.MAKH=B.MAKH AND NGHD='1/1/2007' --CAU 9) SELECT SOHD,TRIGIA FROM HOADON A, NHANVIEN B WHERE A.MANV=B.MANV AND NGHD='28/10/2006' AND HOTEN='NGUYEN VAN B' --CAU 10) SELECT C.MASP, TENSP FROM HOADON A, KHACHHANG B, CTHD C, SANPHAM D WHERE 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 SOHD FROM CTHD WHERE MASP='BB01'OR MASP='BB02' --CAU 12) SELECT SOHD FROM CTHD WHERE (MASP='BB01'OR MASP='BB02') AND SL BETWEEN 10 AND 20 --CAU 13) SELECT SOHD FROM CTHD WHERE SL BETWEEN 10 AND 20 AND MASP='BB01' AND SOHD IN ( SELECT SOHD FROM CTHD WHERE MASP='BB02') --CAU 14) SELECT DISTINCT A.MASP,TENSP FROM SANPHAM A, HOADON B, CTHD C WHERE NUOCSX='TRUNG QUOC' OR (B.SOHD=C.SOHD AND C.MASP=A.MASP AND NGHD='1/1/2007') --CAU 15) SELECT MASP, TENSP FROM SANPHAM WHERE MASP NOT IN ( SELECT MASP FROM CTHD) --CAU 16) SELECT MASP, TENSP FROM SANPHAM WHERE MASP NOT IN ( SELECT A.MASP FROM CTHD A, HOADON B WHERE A.SOHD=B.SOHD AND YEAR(NGHD)=2006) --CAU 17) SELECT MASP, TENSP FROM SANPHAM WHERE 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 SOHD FROM CTHD A WHERE 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 HOADON WHERE MAKH IS NULL --CAU 21) SELECT COUNT(DISTINCT MASP) FROM HOADON A, CTHD B WHERE 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 HOADON WHERE YEAR(NGHD)=2006 --CAU 24) SELECT SUM(TRIGIA) [DOANH THU] FROM HOADON WHERE YEAR(NGHD)=2006 --CAU 27) SELECT * FROM KHACHHANG WHERE DOANHSO IN(SELECT TOP 3 DOANHSO FROM KHACHHANG ORDER BY DOANHSO DESC) --CAU 28) SELECT * FROM SANPHAM WHERE GIA IN(SELECT TOP 3 GIA FROM SANPHAM ORDER BY GIA DESC) --CAU 29) SELECT * FROM SANPHAM WHERE NUOCSX='THAI LAN' AND GIA IN(SELECT TOP 3 GIA FROM SANPHAM ORDER BY GIA DESC) --CAU 30) SELECT * FROM SANPHAM WHERE NUOCSX='TRUNG QUOC' AND GIA IN(SELECT TOP 3 GIA FROM SANPHAM WHERE NUOCSX='TRUNG QUOC' ORDER BY GIA DESC) --CAU 31) SELECT * FROM KHACHHANG WHERE DOANHSO IN(SELECT TOP 3 DOANHSO FROM KHACHHANG ORDER BY DOANHSO DESC) ORDER BY DOANHSO DESC --CAU 32) SELECT COUNT(MASP) FROM SANPHAM WHERE NUOCSX='TRUNG QUOC' --CAU 33) SELECT NUOCSX,COUNT(MASP) [SO SAN PHAM] FROM SANPHAM GROUP BY NUOCSX --CAU 34) SELECT NUOCSX,MIN(GIA) [GIA THAP NHAT], AVG(GIA) [GIA TB], MAX(GIA) [GIA CAO NHAT] FROM SANPHAM GROUP BY NUOCSX --CAU 35) SELECT NGHD,SUM(TRIGIA) [DOANH THU] FROM HOADON GROUP BY NGHD --CAU 36) SELECT MASP,SUM(SL) [TONG SO LUONG] FROM HOADON A, CTHD B WHERE 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 B WHERE A.SOHD=B.SOHD AND YEAR(NGHD)=2006 GROUP BY MONTH(NGHD) --CAU 38) SELECT * FROM HOADON WHERE SOHD IN (SELECT SOHD FROM CTHD GROUP BY SOHD HAVING COUNT(DISTINCT MASP)>=4) --CAU 39) SELECT * FROM HOADON WHERE 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 KHACHHANG WHERE 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) THANG FROM HOADON WHERE YEAR(NGHD)=2006 GROUP 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, TENSP FROM SANPHAM A,CTHD B, HOADON C WHERE A.MASP=B.MASP AND B.SOHD=C.SOHD AND YEAR(NGHD)=2006 GROUP BY B.MASP,TENSP HAVING 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, TENSP FROM SANPHAM A WHERE GIA=(SELECT MAX(GIA) FROM SANPHAM B WHERE A.NUOCSX=B.NUOCSX) GROUP BY NUOCSX,MASP,TENSP --CAU 44) SELECT NUOCSX FROM SANPHAM GROUP BY NUOCSX HAVING COUNT(DISTINCT GIA)>=3 --CAU 45) SELECT * FROM KHACHHANG WHERE 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