Tạo bài viết
Đăng ký
02/10/2018, 14:24

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

Bài viết này là phần 3 trong 3 bài của Series Cơ sở dữ liệu Cơ sở dữ liệu Đề thi GK Cơ sở dữ liệu 2017 – ĐH Công Nghệ Thông tin UIT [CSDL] BT Thực hành 1 truy vấn SQL – Phần 1 [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ài viết này là phần 3 trong 3 bài của Series Cơ sở dữ liệu

Cơ sở dữ liệu
  • Đề thi GK Cơ sở dữ liệu 2017 – ĐH Công Nghệ Thông tin UIT
  • [CSDL] BT Thực hành 1 truy vấn SQL – Phần 1
  • [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')
1
2
3
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
1
2
3
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

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
1
2
3
4
5
6
7
8
9
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
1
2
3
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 )
1
2
3
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)
1
2
3
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)
1
2
3
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)
1
2
3
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
1
2
3
4
5
6
7
8
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
1
2
3
4
5
6
7
8
9
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 ) )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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 ) )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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
1
2
3
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
1
2
3
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
1
2
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
1
2
3
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
1
2
3
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 )
1
2
3
4
5
6
7
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 )
1
2
3
4
5
6
7
8
9
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 )
1
2
3
4
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
1
2
3
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 )
1
2
3
4
5
6
7
8
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 )
1
2
3
4
5
6
7
8
9
10
SELECT SANPHAM.MASP, SANPHAM.TENSP
FROM SANPHAM
WHERE SANPHAM.NUOCSX='Thai Lan'
AND
SANPHAM.GIA

Trịnh Tiến Mạnh

27 chủ đề

6824 bài viết

Có thể bạn quan tâm
+1
Các chủ đề đang được quan tâm
phần mềm lập trình php | lập trình web php + mysql | khóa học lập trình php | tự học lập trình php | học php cơ bản thachpham | Code24h | Cho thuê phòng trọ hà nội | Cho thuê phòng trọ bình thạnh | Cho thuê phòng trọ | Cho thuê nhà trọ
Đăng ký

Đăng ký nhận thông báo

Các bài học thú vị sẽ được gửi đến inbox của bạn

HỖ TRỢ HỌC VIÊN
VỀ CODE24H
HỢP TÁC VÀ LIÊN KẾT
KẾT NỐI VỚI CHÚNG TÔI
TẢI ỨNG DỤNG TRÊN ĐIỆN THOẠI

CCode 24h, code mọi lúc, mọi nơi

© Copy right 2018 - 2024