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