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 @NGHDANY[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=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