KHACHHANG (MAKH, HOTEN, DCHI, SODT, NGSINH, DOANHSO, NGDK)
NHANVIEN (MANV, HOTEN, SODT, NGVL)
SANPHAM (MASP, TENSP, DVT, NUOCSX, GIA
HOADON (SOHD, NGHD, MAKH, MANV, TRIGIA)
CTHD (SOHD, MASP, SL)
I. Ngôn ngữ định nghĩa dữ liệu (Data Definition Language) – Khởi tạo cơ sở dữ liệu Quản lý bán hàng
1. Tạo các quan hệ và khai báo các khóa chính, khóa ngoại của quan hệ. Cơ sở dữ liệu Quản lý bán hàng
Download Microsoft SQL Server tại đây.
2. Thêm vào thuộc tính GHICHU có kiểu dữ liệu varchar(20) cho quan hệ SANPHAM.
ALTER TABLE SANPHAM ADD GHICHU varchar(20)3. Thêm vào thuộc tính LOAIKH có kiểu dữ liệu là tinyint cho quan hệ KHACHHANG.
ALTER TABLE KHACHHANG ADD LOAIKH tinyint4. Sửa kiểu dữ liệu của thuộc tính GHICHU trong quan hệ SANPHAM thành varchar(100).
ALTER TABLE SANPHAM ALTER COLUMN GHICHU varchar(100)5. Xóa thuộc tính GHICHU trong quan hệ SANPHAM
ALTER TABLE SANPHAM DROP COLUMN GHICHU6. Làm thế nào để thuộc tính LOAIKH trong quan hệ KHACHHANG có thể lưu các giá trị là: “Vang lai”, “Thuong xuyen”, “Vip”, …
ALTER TABLE KHACHHANG ALTER COLUMN LOAIKH VARCHAR(12)
ALTER TABLE KHACHHANG ADD CONSTRAINT CHK_LOAIKH
CHECK (LOAIKH IN ('Vang lai', 'Thuong xuyen', 'Vip'))7. Đơn vị tính của sản phẩm chỉ có thể là (“cay”,”hop”,”cai”,”quyen”,”chuc”)
ALTER TABLE SANPHAM ADD CONSTRAINT CHK_DVT
CHECK (DVT IN ('cay', 'hop', 'cai', 'quyen', 'chuc'))8. Giá bán của sản phẩm từ 500 đồng trở lên.
ALTER TABLE SANPHAM ADD CONSTRAINT CHK_GIA CHECK (GIA >= 500)9. Mỗi lần mua hàng, khách hàng phải mua ít nhất 1 sản phẩm.
ALTER TABLE HOADON ADD CONSTRAINT CHK_MUAHANG CHECK (TRIGIA > 0)10. Ngày khách hàng đăng ký là khách hàng thành viên phải lớn hơn ngày sinh của người đó.
ALTER TABLE KHACHHANG ADD CONSTRAINT CHK_NGDK CHECK (NGDK > NGSINH)11. Ngày mua hàng (NGHD) của một khách hàng thành viên sẽ lớn hơn hoặc bằng ngày khách hàng đó đăng ký thành viên (NGDK).
-- Trigger: thêm và sửa NgHD của HoaDon
CREATE TRIGGER trg_ins_udt_NgHD ON HoaDon FOR Insert, Update AS BEGIN IF (
EXISTS (
SELECT
*
FROM
KhachHang,
inserted
WHERE
KhachHang.MaKH = inserted.MaKH
AND KhachHang.NgDK > inserted.NgHD
)
) BEGIN PRINT 'Error: NgHD phai >= NgDK' ROLLBACK TRANSACTION END END -- Trigger: sửa NGDK của KhachHang
CREATE TRIGGER trg_upd_NgDK ON KhachHang FOR Update AS BEGIN IF (
EXISTS (
SELECT
*
FROM
HoaDon,
inserted
WHERE
HoaDon.MaKH = inserted.MAKH
AND HoaDon.NgHD < inserted.NgDK
)
) BEGIN PRINT 'Error: NgHD phai >= NgDK' ROLLBACK TRANSACTION END END12. Ngày bán hàng (NGHD) của một nhân viên phải lớn hơn hoặc bằng ngày nhân viên đó vào làm.
-- Trigger: thêm và sửa NgHD của HoaDon
CREATE TRIGGER trg_ins_udt_NgBH ON HoaDon FOR Insert, Update AS BEGIN IF (
EXISTS (
SELECT
*
FROM
NhanVien,
inserted
WHERE
NhanVien.MaNV = inserted.MaKH
AND NhanVien.NgVL > inserted.NgHD
)
) BEGIN PRINT 'Error: NgHD phai >= NgVL' ROLLBACK TRANSACTION END END -- Trigger: sửa NGVL của NhanVien
CREATE TRIGGER trg_upd_NgVL ON NhanVien FOR Update AS BEGIN IF (
EXISTS (
SELECT
*
FROM
HoaDon,
inserted
WHERE
HoaDon.MaNV = inserted.MANV
AND HoaDon.NgHD < inserted.NgVL
)
) BEGIN PRINT 'Error: NgHD phai >= NgDK' ROLLBACK TRANSACTION END END13. Mỗi một hóa đơn phải có ít nhất một chi tiết hóa đơn.
-- Trigger: Xóa một CTHD
CREATE TRIGGER trg_del_CTHD ON CTHD FOR Delete AS BEGIN IF (
(
SELECT
COUNT (*)
FROM
deleted
WHERE
SoHD = deleted.SoHD
) = (
SELECT
COUNT (*)
FROM
HoaDon,
deleted
WHERE
deleted.SoHD = HoaDon.SoHD
)
) BEGIN PRINT 'Error: Moi hoa don phai co it nhat 1 CTHD' ROLLBACK TRANSACTION END END14. Trị giá của một hóa đơn là tổng thành tiền (số lượng*đơn giá) của các chi tiết thuộc hóa đơn đó.
15. Doanh số của một khách hàng là tổng trị giá các hóa đơn mà khách hàng thành viên đó đã mua.
-- Trigger: Update DoanhSo của KhachHang
CREATE TRIGGER trg_upd_DoanhSo ON KhachHang FOR Update
AS BEGIN DECLARE @TongTriGia MONEY,
@DoanhSo MONEY
SELECT
@TongTriGia = SUM(TriGia)
FROM
HoaDon,
inserted
WHERE
HoaDon.MaKH = inserted.MaKH
SELECT
@DoanhSo = DoanhSo
FROM
inserted IF (@DoanhSo <> @TongTriGia) BEGIN PRINT(
'Doanh so cua mot khach hang la tong tri gia cac hoa don khach hang thanh vien do da mua'
) ROLLBACK TRANSACTION END ENDII. Ngôn ngữ thao tác dữ liệu (Data Manipulation Language) – CSDL Quản lý bán hàng
1. Nhập dữ liệu cho các quan hệ trên. CSDL Quản lý bán hàng
2. Tạo quan hệ SANPHAM1 chứa toàn bộ dữ liệu của quan hệ SANPHAM. Tạo quan hệ KHACHHANG1 chứa toàn bộ dữ liệu của quan hệ KHACHHANG.
SELECT
* INTO SANPHAM1
FROM
SANPHAM
SELECT
* INTO KHACHHANG1
FROM
KHACHHANG3. Cập nhật giá tăng 5% đối với những sản phẩm do “Thai Lan” sản xuất (cho quan hệ SANPHAM1)
UPDATE
SANPHAM1
SET
Gia = 1.05 * Gia
WHERE
NuocSX = 'Thai Lan'4. Cập nhật giá giảm 5% đối với những sản phẩm do “Trung Quoc” sản xuất có giá từ 10.000 trở xuống (cho quan hệ SANPHAM1).
UPDATE
SANPHAM1
SET
GIA = 0.95 * GIA
WHERE
NUOCSX = 'Trung Quoc'
AND GIA <= 100005. Cập nhật giá trị LOAIKH là “Vip” đối với những khách hàng đăng ký thành viên trước ngày 1/1/2007 có doanh số từ 10.000.000 trở lên hoặc khách hàng đăng ký thành viên từ 1/1/2007 trở về sau có doanh số từ 2.000.000 trở lên (cho quan hệ KHACHHANG1).
UPDATE
KHACHHANG1
SET
LOAIKH = 'Vip'
WHERE
(
NGDK < '1/1/2007'
AND DOANHSO >= 10000000
)
OR (
NGDK >= '1/1/2007'
AND DOANHSO >= 2000000
) III. Ngôn ngữ truy vấn dữ liệu:
1. In ra danh sách các sản phẩm (MASP,TENSP) do “Trung Quoc” sản xuất.
SELECT
MaSP, TenSP
FROM
SanPham
WHERE
NuocSX = 'Trung Quoc' 2. In ra danh sách các sản phẩm (MASP, TENSP) có đơn vị tính là “cay”, ”quyen”.
SELECT
MaSP, TenSP
FROM
SanPham
WHERE
DVT IN ('cay', 'quyen') 3. In ra danh sách các sản phẩm (MASP,TENSP) có mã sản phẩm bắt đầu là “B” và kết thúc là “01”.
SELECT
MaSP, TenSP
FROM
SanPham
WHERE
MaSP LIKE 'B%01' 4. In ra danh sách các sản phẩm (MASP,TENSP) do “Trung Quốc” sản xuất có giá từ 30.000 đến 40.000.
SELECT
MaSP,
TenSP
FROM
SanPham
WHERE
NuocSX = 'Trung Quoc'
AND Gia BETWEEN 30000
AND 400005. In ra danh sách các sản phẩm (MASP,TENSP) do “Trung Quoc” hoặc “Thai Lan” sản xuất có giá từ 30.000 đến 40.000.
SELECT
MaSP,
TenSP
FROM
SanPham
WHERE
NuocSX IN ('Trung Quoc', 'Thai Lan')
AND Gia BETWEEN 30000
AND 40000 6. In ra các số hóa đơn, trị giá hóa đơn bán ra trong ngày 1/1/2007 và ngày 2/1/2007.
SELECT
SoHD,
TriGia
FROM
HoaDon
WHERE
NgHD IN ('1/1/2007', '2/1/2007') 7. In ra các số hóa đơn, trị giá hóa đơn trong tháng 1/2007, sắp xếp theo ngày (tăng dần) và trị giá của hóa đơn (giảm dần).
SELECT
SoHD,
TriGia
FROM
HoaDon
WHERE
MONTH (NgHD) = 1
AND YEAR (NgHD) = 2007
ORDER BY
NgHD ASC,
TriGia DESC 8. In ra danh sách các khách hàng (MAKH, HOTEN) đã mua hàng trong ngày 1/1/2007.
SELECT
DISTINCT KhachHang.MaKH,
HoTen
FROM
KhachHang,
HoaDon
WHERE
KhachHang.MaKH = HoaDon.MaKH
AND NgHD = '1/1/2007' 9. In ra số hóa đơn, trị giá các hóa đơn do nhân viên có tên “Nguyen Van B” lập trong ngày 28/10/2006.
SELECT
SoHD,
TriGia
FROM
HoaDon,
NhanVien
WHERE
HoaDon.MaNV = NhanVien.MaNV
AND HoTen = 'Nguyen Van B'
AND NgHD = '28/10/2006' 10. In ra danh sách các sản phẩm (MASP,TENSP) được khách hàng có tên “Nguyen Van A” mua trong tháng 10/2006.
SELECT
DISTINCT SanPham.MaSP,
TenSP
FROM
SanPham,
CTHD,
KhachHang,
HoaDon WHERE CTHD.MaSP = SanPham.MaSP
AND CTHD.SoHD = HoaDon.SoHD
AND HoaDon.MaKH = KhachHang.MaKH
AND HoTen = 'Nguyen Van A'
AND MONTH (NgHD) = 10
AND YEAR (NgHD) = 200611. Tìm các số hóa đơn đã mua sản phẩm có mã số “BB01” hoặc “BB02”.
SELECT
DISTINCT SoHD
FROM
CTHD
WHERE
MaSP IN ('BB01', 'BB02')12. Tìm các số hóa đơn đã mua sản phẩm có mã số “BB01” hoặc “BB02”, mỗi sản phẩm mua với số lượng từ 10 đến 20.
SELECT
DISTINCT SoHD
FROM
CTHD WHERE MaSP IN ('BB01', 'BB02')
AND SL BETWEEN 10
AND 2013. Tìm các số hóa đơn mua cùng lúc 2 sản phẩm có mã số “BB01” và “BB02”, mỗi sản phẩm mua với số lượng từ 10 đến 20.
SELECT
DISTINCT SoHD
FROM
CTHD
WHERE
MaSP = 'BB01'
AND SL BETWEEN 10
AND 20 INTERSECT (
SELECT
DISTINCT SoHD
FROM
CTHD
WHERE
MaSP = 'BB02'
AND SL BETWEEN 10
AND 20
)14. In ra danh sách các sản phẩm (MASP,TENSP) do “Trung Quoc” sản xuất hoặc các sản phẩm được bán ra trong ngày 1/1/2007.
SELECT
DISTINCT SanPham.MaSP,
TenSP
FROM
HoaDon,
SanPham,
CTHD WHERE HoaDon.SoHD = CTHD.SoHD
AND CTHD.MaSP = SanPham.MaSP
AND (
NuocSX = 'Trung Quoc'
OR NgHD = '1/1/2007'
)15. In ra danh sách các sản phẩm (MASP,TENSP) không bán được.
SELECT
MaSP,
TenSP
FROM
SanPham
WHERE
MaSP NOT IN (
SELECT
MaSP
FROM
CTHD
)16. In ra danh sách các sản phẩm (MASP,TENSP) không bán được trong năm 2006.
SELECT
MaSP,
TenSP
FROM
SanPham
WHERE
MaSP NOT IN (
SELECT
MaSP
FROM
CTHD,
HoaDon WHERE CTHD.SoHD = HoaDon.SoHD
AND YEAR(NgHD) = 2006
)17. In ra danh sách các sản phẩm (MASP,TENSP) do “Trung Quoc” sản xuất không bán được trong năm 2006.
SELECT
MaSP,
TenSP
FROM
SanPham WHERE NuocSX = 'Trung Quoc'
AND MaSP NOT IN (
SELECT
MaSP
FROM
CTHD,
HoaDon WHERE CTHD.SoHD = HoaDon.SoHD
AND YEAR (NgHD) = 2006
)18. Tìm số hóa đơn đã mua tất cả các sản phẩm do Singapore sản xuất.
SELECT
SoHD
FROM
HoaDon
WHERE
NOT EXISTS (
SELECT
*
FROM
SanPham
WHERE
NuocSX = 'Singapore'
AND NOT EXISTS (
SELECT
*
FROM
CTHD
WHERE
CTHD.SoHD = HoaDon.SoHD
AND CTHD.MaSP = SanPham.MaSP
)
)19. Tìm số hóa đơn trong năm 2006 đã mua ít nhất tất cả các sản phẩm do Singapore sản xuất.
SELECT
SoHD
FROM
HoaDon
WHERE
YEAR (NgHD) = 2006
AND NOT EXISTS (
SELECT
*
FROM
SanPham
WHERE
NuocSX = 'Singapore'
AND NOT EXISTS (
SELECT
*
FROM
CTHD
WHERE
CTHD.SoHD = HoaDon.SoHD
AND CTHD.MaSP = SanPham.MaSP
)
)20. Có bao nhiêu hóa đơn không phải của khách hàng đăng ký thành viên mua?
SELECT
COUNT (*)
FROM
HoaDon
WHERE
MaKH IS NULL21. Có bao nhiêu sản phẩm khác nhau được bán ra trong năm 2006.
SELECT
COUNT (DISTINCT MaSP)
FROM
HoaDon,
CTHD WHERE HoaDon.SoHD = CTHD.SoHD
AND YEAR (NgHD) = 200622. Cho biết trị giá hóa đơn cao nhất, thấp nhất là bao nhiêu ?
SELECT
MIN (TriGia) Min_TriGia,
MAX (TriGia) Max_TriGia
FROM
HoaDon23. Trị giá trung bình của tất cả các hóa đơn được bán ra trong năm 2006 là bao nhiêu?
SELECT
AVG (TriGia)
FROM
HoaDon
WHERE
YEAR(NgHD) = 200624. Tính doanh thu bán hàng trong năm 2006.
SELECT
SUM (TriGia)
FROM
HoaDon
WHERE
YEAR (NgHD) = 200625. Tìm số hóa đơn có trị giá cao nhất trong năm 2006.
SELECT
MAX (TriGia)
FROM
HoaDon
WHERE
YEAR (NgHD) = 200626. Tìm họ tên khách hàng đã mua hóa đơn có trị giá cao nhất trong năm 2006.
SELECT
DISTINCT HoTen
FROM
KhachHang,
HoaDon WHERE HoaDon.MaKH = KhachHang.MaKH
AND YEAR (NgHD) = 2006
AND TriGia = (
SELECT
MAX (TriGia)
FROM
HoaDon
WHERE
YEAR (NgHD) = 2006
)27. In ra danh sách 3 khách hàng đầu tiên (MAKH, HOTEN) sắp xếp theo doanh số giảm dần.
SELECT
TOP 3 MaKH,
HoTen
FROM
KhachHang
ORDER BY
DoanhSo DESC28. In ra danh sách các sản phẩm (MASP, TENSP) có giá bán bằng 1 trong 3 mức giá cao nhất.
SELECT
MaSP,
TenSP
FROM
SanPham
WHERE
Gia IN (
SELECT
DISTINCT TOP 3 Gia
FROM
SanPham
ORDER BY
Gia DESC
)29. In ra danh sách các sản phẩm (MASP, TENSP) do “Thai Lan” sản xuất có giá bằng 1 trong 3 mức giá cao nhất (của tất cả các sản phẩm).
SELECT
MaSP,
TenSP
FROM
SanPham
WHERE
NuocSX = 'Thai Lan'
AND Gia IN (
SELECT
DISTINCT TOP 3 Gia
FROM
SanPham
ORDER BY
Gia DESC
)30. In ra danh sách các sản phẩm (MASP, TENSP) do “Trung Quoc” sản xuất có giá bằng 1 trong 3 mức giá cao nhất (của sản phẩm do “Trung Quoc” sản xuất).
SELECT
MaSP,
TenSP
FROM
SanPham
WHERE
NuocSX = 'Trung Quoc'
AND Gia IN (
SELECT
DISTINCT TOP 3 Gia
FROM
SanPham
WHERE
NuocSX = 'Trung Quoc'
ORDER BY
Gia DESC
)31. * In ra danh sách khách hàng nằm trong 3 hạng cao nhất (xếp hạng theo doanh số).
SELECT
TOP 3 *
FROM
KhachHang
ORDER BY
DoanhSo DESC32. Tính tổng số sản phẩm do “Trung Quoc” sản xuất.
SELECT
COUNT (*)
FROM
SanPham
WHERE
NuocSX = 'Trung Quoc'33. Tính tổng số sản phẩm của từng nước sản xuất.
SELECT
NuocSX,
COUNT (*) SoSP
FROM
SanPham
GROUP BY
NuocSX34. Với từng nước sản xuất, tìm giá bán cao nhất, thấp nhất, trung bình của các sản phẩm.
SELECT
NuocSX,
MAX (Gia) Max_Gia,
MIN (Gia) Min_Gia,
AVG (Gia) TB_Gia
FROM
SanPham
GROUP BY
NuocSX35. Tính doanh thu bán hàng mỗi ngày.
SELECT
NgHD,
SUM (TriGia) DoanhThu
FROM
HoaDon
GROUP BY
NgHD36. Tính tổng số lượng của từng sản phẩm bán ra trong tháng 10/2006.
SELECT
SanPham.MaSP,
SUM (SL) SoLuongBan
FROM
SanPham,
HoaDon,
CTHD
WHERE
CTHD.MaSP = SanPham.MaSP
AND CTHD.SoHD = HoaDon.SoHD
AND MONTH (NgHD) = 10
AND YEAR (NgHD) = 2006
GROUP BY
SanPham.MaSP37. Tính doanh thu bán hàng của từng tháng trong năm 2006.
SELECT
MONTH (NgHD) Thang,
SUM (TriGia) DoanhThu
FROM
HoaDon
WHERE
YEAR (NgHD) = 2006
GROUP BY
MONTH (NgHD)38. Tìm hóa đơn có mua ít nhất 4 sản phẩm khác nhau.
SELECT
SoHD
FROM
CTHD
GROUP BY
SoHD
HAVING
COUNT (DISTINCT MaSP) >= 439. Tìm hóa đơn có mua 3 sản phẩm do “Viet Nam” sản xuất (3 sản phẩm khác nhau).
SELECT
SoHD
FROM
CTHD,
SanPham
WHERE
CTHD.MaSP = SanPham.MaSP
AND NuocSX = 'Viet Nam'
GROUP BY
SoHD
HAVING
COUNT (DISTINCT CTHD.MaSP) >= 340. Tìm khách hàng (MAKH, HOTEN) có số lần mua hàng nhiều nhất.
SELECT
KhachHang.MaKH,
HoTen
FROM
KhachHang,
HoaDon
WHERE
KhachHang.MaKH = HoaDon.MaKH
GROUP BY
KhachHang.MaKH,
HoTen
HAVING
COUNT (*) >= ALL (
SELECT
COUNT (*)
FROM
HoaDon
GROUP BY
MaKH
)
41. Tháng mấy trong năm 2006, doanh số bán hàng cao nhất ?
SELECT
MONTH (NgHD)
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)
)42. Tìm sản phẩm (MASP, TENSP) có tổng số lượng bán ra thấp nhất trong năm 2006.
SELECT
TOP 1 WITH TIES SanPham.MaSP,
TenSP
FROM
SanPham,
CTHD,
HoaDon
WHERE
SanPham.MaSP = CTHD.MaSP
AND HoaDon.SoHD = CTHD.SoHD
AND YEAR (NgHD) = 2006
GROUP BY
SanPham.MaSP,
TenSP
ORDER BY
SUM (SL)
43. *Mỗi nước sản xuất, tìm sản phẩm (MASP,TENSP) có giá bán cao nhất.
SELECT
NuocSX,
MaSP,
TenSP
FROM
SanPham SP1
WHERE
EXISTS (
SELECT
NuocSX
FROM
SanPham SP2
GROUP BY
NuocSX
HAVING
SP1.NuocSX = SP2.NuocSX
AND SP1.Gia = MAX (Gia)
)44. 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.
SELECT
NuocSX
FROM
SanPham
GROUP BY
NUOCSX
HAVING
COUNT (DISTINCT GIA) >= 345. *Trong 10 khách hàng có doanh số cao nhất, tìm khách hàng có số lần mua hàng nhiều nhất.
SELECT
*
FROM
KhachHang
WHERE
MaKH IN (
SELECT
TOP 1 WITH TIES HoaDon.MaKH
FROM
(
SELECT
TOP 10 MaKH
FROM
KhachHang
ORDER BY
DoanhSo DESC
) AS A
JOIN HoaDon ON A.MaKH = HoaDon.MaKH
GROUP BY
HoaDon.MaKH
ORDER BY
COUNT (*) DESC
)III. Tổng kết
Qua bài tập CSDL Quản lý bán hàng trên, chúng ta đã phần nào nắm và hiểu được về CSDL, cấu trúc và cách quản lý nó bằng SQL.
Để phát triển xa hơn kỹ năng sử dụng SQL để tạo và thao tác dữ liệu, chúng ta sẽ đến với bài tập tiếp theo: CSDL Quản lý giáo vụ
![[CEH] Module 3 – Phần 7: Dò quét né tránh tường lửa và IDS](https://sinhviencntt.net/wp-content/uploads/2023/02/m3-p7-75x75.png "[CEH] Module 3 – Phần 7: Dò quét né tránh tường lửa và IDS 9")
Comments 1