[CSDL] BT Thực hành 1 truy vấn SQL – Phần 2

Giải bài tập thực hành truy vấn cơ sở dữ liệu quản lý bán hàng

1. Tóm tắt đề bài CSDL Quản lí bán hàng

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 hà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 hà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 biệ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, nhâ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 viên.

CTHD (SOHD,MASP,SL)

Đề bài ở phần trước https://kienthuc24h.com/csdl-bt-thuc-hanh-1-truy-van-sql/

2. Lời giải BT Truy vấn CSDL Quản lí bán hàng

11. Tìm các số hóa đơn đã mua sản phẩm có mã số “BB01” hoặc “BB02”.

SELECT distinct hd.SOHD
FROM HOADON hd, CTHD ct
WHERE hd.SOHD = ct.SOHD AND ct.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 hd.SOHD
FROM HOADON hd, CTHD ct
WHERE hd.SOHD = ct.SOHD AND ct.MASP IN ('BB01','BB02') AND ct.SL BETWEEN 10 AND 20

Tuy nhiên lời giải trên khá thừa, vì chỉ hỏi mã hóa đơn, trên thực tế không cần thiết kết bảng HOADON và CTHD trừ khi cần khai thác thêm thông tin của HOADON như vậy có thể sửa tối ưu cho câu này như sau:

SELECT distinct ct.SOHD
FROM CTHD ct
WHERE ct.MASP IN ('BB01','BB02') AND ct.SL BETWEEN 10 AND 20

13. 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 hd.SOHD
FROM HOADON hd, CTHD ct
WHERE hd.SOHD = ct.SOHD AND ct.MASP='BB01' AND ct.SL BETWEEN 10 AND 20

INTERSECT

SELECT distinct hd.SOHD
FROM HOADON hd, CTHD ct
WHERE hd.SOHD = ct.SOHD AND ct.MASP = 'BB02' AND ct.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 và được bán ra trong ngày 1/1/2007.

SELECT sp.MASP, sp.TENSP
FROM SANPHAM sp, HOADON hd, CTHD ct
WHERE sp.NUOCSX='Trung Quoc' AND hd.NGHD='1/1/2007' AND hd.SOHD = ct.SOHD and sp.MASP=ct.MASP

15. In ra danh sách các sản phẩm (MASP,TENSP) không bán được.

Sử dụng NOT IN

SELECT sp.MASP,sp.TENSP
FROM SANPHAM sp
WHERE sp.MASP NOT IN ( SELECT ct.MASP FROM CTHD ct )

Sử dụng NOT EXISTS

SELECT sp.MASP,sp.TENSP
FROM SANPHAM sp
WHERE not exists ( SELECT ct.MASP FROM CTHD ct WHERE sp.MASP=ct.MASP)

16. In ra danh sách các sản phẩm (MASP,TENSP) không bán được trong năm 2006.

Sử dụng NOT IN

SELECT sp.MASP,sp.TENSP
FROM SANPHAM sp
WHERE sp.MASP not in ( SELECT ct.MASP FROM HOADON hd, CTHD ct WHERE hd.SOHD = ct.SOHD AND YEAR(hd.NGHD)=2006)

Sử dụng EXISTS

SELECT sp.MASP,sp.TENSP
FROM SANPHAM sp
WHERE not exists ( SELECT ct.MASP FROM CTHD ct, HOADON hd WHERE HD.SOHD=CT.SOHD AND sp.MASP=ct.MASP AND  YEAR(hd.NGHD)=2006)

Sử dụng EXCEPT

SELECT MASP, TENSP
FROM SANPHAM

EXCEPT

SELECT distinct sp.MASP, sp.TENSP
FROM CTHD ct, HOADON hd, SANPHAM sp
WHERE ct.SOHD = hd.SOHD AND ct.MASP = sp.MASP AND year(hd.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.

Sử dụng EXCEPT

SELECT MASP, TENSP
FROM SANPHAM
WHERE NUOCSX='Trung Quoc'

EXCEPT

SELECT distinct sp.MASP, sp.TENSP
FROM HOADON hd, CTHD ct, SANPHAM sp
WHERE year(hd.NGHD)=2006 and hd.SOHD=ct.SOHD and sp.MASP = ct.MASP

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 MASP NOT IN
    ( 
        SELECT masp
        FROM CTHD
        WHERE 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 MASP NOT IN
    (
        SELECT masp 
        FROM CTHD 
        WHERE SOHD = HOADON.SOHD
    )
)

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(SOHD) as SL
FROM HOADON
WHERE MAKH is null

21. Có bao nhiêu sản phẩm khác nhau được bán ra trong năm 2006.

SELECT count(distinct ct.MASP)
FROM HOADON hd, CTHD ct
WHERE YEAR(hd.NGHD) = 2006 and ct.SOHD=hd.SOHD

22. Cho biết trị giá hóa đơn cao nhất, thấp nhất là bao nhiêu ?

SELECT MAX(HOADON.TRIGIA), MIN(HOADON.TRIGIA)
FROM HOADON

23. 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(hd.TRIGIA)
FROM HOADON hd
WHERE YEAR(hd.NGHD) = 2006

24. Tính doanh thu bán hàng trong năm 2006.

SELECT sum(hd.TRIGIA)
FROM HOADON hd
WHERE YEAR(hd.NGHD) = 2006

25. Tìm số hóa đơn có trị giá cao nhất trong năm 2006.

SELECT SOHD
FROM HOADON
WHERE TRIGIA IN (
SELECT max(hd.TRIGIA)
FROM HOADON hd
WHERE YEAR(hd.NGHD) = 2006
)

26. Tìm họ tên khách hàng đã mua hóa đơn có trị giá cao nhất trong năm 2006.

SELECT kh.HOTEN
FROM KHACHHANG kh, HOADON
WHERE HOADON.MAKH=KH.MAKH AND 
HOADON.TRIGIA in 
(
    SELECT MAX(hd.TRIGIA)
    FROM HOADON hd
    WHERE year(hd.NGHD)=2006
)

Hoặc

SELECT	HOTEN
FROM	KHACHHANG kh, HOADON
WHERE HOADON.MAKH = kh.MAKH and
HOADON.TRIGIA >=ALL ( SELECT max(hd.TRIGIA) FROM HOADON hd WHERE year(hd.NGHD)=2006 )

27. In ra danh sách 3 khách hàng (MAKH, HOTEN) có doanh số cao nhất.

SELECT top 3 MAKH, HOTEN
FROM KHACHHANG 
order by DOANHSO desc

28. 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 sp.GIA
FROM SANPHAM sp
ORDER BY sp.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 SANPHAM.MASP, SANPHAM.TENSP
FROM SANPHAM
WHERE SANPHAM.NUOCSX='Thai Lan' 
AND
SANPHAM.GIA IN
(
SELECT DISTINCT TOP 3 sp.GIA
FROM SANPHAM sp
ORDER BY sp.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 SANPHAM.MASP, SANPHAM.TENSP
FROM SANPHAM
WHERE SANPHAM.NUOCSX='Trung Quoc' 
AND
SANPHAM.GIA IN
(
SELECT DISTINCT TOP 3 sp.GIA
FROM SANPHAM sp
WHERE sp.NUOCSX = 'Trung Quoc'
ORDER BY sp.GIA DESC
)

31. * In ra danh sách 3 khách hàng có doanh số cao nhất (sắp xếp theo kiểu xếp hạng).

SELECT TOP 3 RANK() OVER (order by kh.DOANHSO DESC) ThuHang, *
FROM KHACHHANG kh
ORDER BY kh.DOANHSO DESC

32. Tính tổng số sản phẩm do “Trung Quoc” sản xuất.

SELECT COUNT(MASP)
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(MASP)
FROM SANPHAM
GROUP BY NUOCSX

34. 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 MAX(GIA) CaoNhat, MIN(GIA) ThapNhat, AVG(GIA) TrungBinh
FROM SANPHAM
GROUP BY NUOCSX

35. Tính doanh thu bán hàng mỗi ngày.

SELECT NGHD, SUM(TRIGIA) DoanhThu
FROM HOADON
GROUP BY NGHD

36. Tính tổng số lượng của từng sản phẩm bán ra trong tháng 10/2006.

SELECT ct.MASP, sum(ct.SL)
FROM CTHD ct, HOADON hd
WHERE ct.SOHD=hd.SOHD AND month(NGHD)=10 AND year(NGHD)=2006 
GROUP BY ct.MASP

37. Tính doanh thu bán hàng của từng tháng trong năm 2006.

SELECT month(hd.NGHD) Thang , sum(hd.TRIGIA) Tong
FROM HOADON hd 
WHERE year(hd.NGHD)=2006
GROUP BY month(hd.NGHD)

38. Tìm hóa đơn có mua ít nhất 4 sản phẩm khác nhau.

SELECT *
FROM HOADON
WHERE
SOHD in
(
    SELECT HD.SOHD
    FROM (    SELECT ct.sohd, COUNT(CT.MASP) SL
              FROM CTHD ct
              GROUP BY CT.SOHD) HD
    WHERE HD.SL>=4
)

Sử dụng HAVING

SELECT SOHD, COUNT(MASP)
FROM CTHD
GROUP BY SOHD
HAVING COUNT(MASP)>=4

39. 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 ct.SOHD, count(CT.MASP)
FROM CTHD ct, SANPHAM sp
WHERE ct.MASP = sp.MASP AND sp.NUOCSX='Viet Nam'
GROUP BY ct.SOHD
HAVING count(ct.masp)>=3

40. Tìm khách hàng (MAKH, HOTEN) có số lần mua hàng nhiều nhất.

SELECT TOP 1 HOADON.MAKH, KH.HOTEN
FROM HOADON, KHACHHANG kh
WHERE HOADON.MAKH is not null and HOADON.MAKH = KH.MAKH
GROUP BY HOADON.MAKH, KH.HOTEN
ORDER BY count(HOADON.MAKH) DESC

Cách khác

select makh, count(SOHD)
from hoadon
where makh is not null
group by makh
having count(SOHD)=(
select max(a.slmh)
from
(select makh,count(sohd) slmh
from hoadon
where makh is not null
group by makh) a
)

Cách khác

select makh, count(sohd)
from hoadon
where makh is not null
group by makh
having count(sohd) >= all(
select count(sohd) slmh
from hoadon
where makh is not null
group by makh)

41. Tháng mấy trong năm 2006, doanh số bán hàng cao nhất ?

SELECT MONTH(hd2.NGHD)
FROM HOADON hd2
WHERE year(hd2.NGHD)=2006
GROUP BY MONTH(hd2.NGHD)
HAVING SUM(hd2.TRIGIA)>=ALL
(
   SELECT sum(hd.TRIGIA)
   FROM HOADON hd
   WHERE year(hd.NGHD) = 2006
   GROUP BY month(hd.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 ct1.MASP, sp.TENSP
FROM CTHD ct1, HOADON hd1, SANPHAM sp 
WHERE ct1.SOHD = hd1.SOHD AND year(hd1.NGHD)=2006 and sp.MASP = ct1.MASP
GROUP BY ct1.MASP, sp.TENSP
HAVING sum(ct1.SL) <= ALL
(
    SELECT sum(ct.SL)
    FROM CTHD ct, HOADON hd 
    WHERE ct.SOHD = hd.SOHD AND year(hd.NGHD)=2006
    GROUP BY ct.MASP
)

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 sp1.NUOCSX, sp1.MASP, sp1.TENSP
FROM SANPHAM sp1, 
    (SELECT sp.NUOCSX, max(sp.GIA) giamax
    FROM SANPHAM sp
    GROUP BY sp.NUOCSX) gia_QG
WHERE sp1.NUOCSX = gia_QG.NUOCSX and sp1.GIA=gia_QG.giamax

Cách khác

SELECT sp1.NUOCSX, sp1.MASP, sp1.TENSP
FROM SANPHAM sp1
WHERE sp1.GIA in
(
    SELECT max(sp.GIA)
    FROM SANPHAM sp
    WHERE sp1.NUOCSX = sp.NUOCSX
)

44. Tìm nước sản xuất ít nhất 3 sản phẩm có giá bán khác nhau.

SELECT sp1.NUOCSX
FROM SANPHAM sp1,
(
    SELECT sp.NUOCSX, sp.GIA, count(sp.MASP) SL
    FROM SANPHAM sp
    GROUP BY sp.NUOCSX, sp.GIA
) groupgia

WHERE groupgia.NUOCSX = SP1.NUOCSX AND groupgia.GIA = sp1.GIA
group by sp1.NUOCSX
HAVING count(groupgia.SL)>=3

Hoặc

SELECT sp.NUOCSX
FROM SANPHAM sp
GROUP BY sp.NUOCSX
HAVING count(distinct sp.GIA)>=3

45. *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 hd1.MAKH, DS1.HOTEN
FROM 
(
    SELECT TOP 10 kh1.MAKH, KH1.HOTEN
    FROM KHACHHANG kh1
    WHERE kh1.MAKH is not null
    ORDER BY kh1.DOANHSO DESC
) DS1, HOADON hd1
WHERE DS1.MAKH = hd1.MAKH
GROUP BY hd1.MAKH, DS1.HOTEN

HAVING COUNT(HD1.SOHD)>=
ALL(

    SELECT count(hd.SOHD)
    FROM 
    (
        SELECT TOP 10 kh.MAKH
        FROM KHACHHANG kh
        WHERE kh.MAKH is not null
        ORDER BY kh.DOANHSO DESC
    ) DS, HOADON hd
    WHERE DS.MAKH = hd.MAKH
    GROUP BY hd.MAKH 
)