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

Contact for work: 096.1014.106 (Mr. Tiến)

Bài viết này là phần [part not set] trong 3 bài của Series Cơ sở dữ liệu

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”.

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.

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:

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.

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.

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

Sử dụng NOT IN

Sử dụng NOT EXISTS

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

Sử dụng EXISTS

Sử dụng EXCEPT

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

18. Tìm số hóa đơn đã mua tất cả các sản phẩm do Singapore sản xuất.

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.

 

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?

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

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

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?

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

25. Tìm số hóa đơn có trị giá cao nhất trong năm 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.

Hoặc

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

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.

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).

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).

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).

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

33. Tính tổng số sản phẩm của từng nước sản xuất.

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.

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

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

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

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

Sử dụng HAVING

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).

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

Cách khác

Cách khác

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

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.

43. *Mỗi nước sản xuất, tìm sản phẩm (MASP,TENSP) có giá bán cao nhất.

Cách khác

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

Hoặc

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.

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

    • Chào bạn,

      Đầu tiên để xác định hóa đơn nào vào năm 2006 mua tất cả sản phẩm do sing sản xuất thì bạn đưa về hướng giải quyết như sau:

      – Tìm những sản phẩm của Sing mà hóa đơn HD001 chưa mua (Ban đầu mình sẽ giả sử đối tượng xét tới là HD001 nhé)
      + Đầu tiên tìm những sản phẩm mà hóa đơn HD001 đã mua là:

      Tiếp theo, những “sản phẩm của Sing sản xuất mà HD001 chưa mua” sẽ là những sản phẩm ko nằm trong tập bên trên, nên ta có:

      Như vậy, Bây giờ mình chỉ cần xác định HD001 có sản phẩm nào của Sing mà nó chưa mua không? Và là năm 2006 chứ?

      Có nghĩa là nếu tồn tại trong đoạn select ở trên thì nó không thỏa, do tồn tại sản phẩm của Sing mà nó chưa mua.

      Sau khi bạn đã xác định được trên HD001 thì bạn mở rộng ra nhiều hóa đơn bằng cách thay HD001 thành HOADON.SOHD

      • Xin chào các bạn,
        Mình thấy câu 18 hơi khó hiểu, các bạn có thể giúp mình giải nghĩa bằng lời logic suy luận của nó được không?

        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
        )
        )

        • Tôi cũng ko hiểu rõ về phần này. Nhờ cao minh giải thích giúp
          Đoạn này tôi hiểu nhưng [hoadon].[sohd] loại trừ ý 2 lại ra đáp số chưa hiểu nổi
          SELECT *
          FROM SANPHAM
          WHERE NUOCSX= ‘Singapore’ AND MASP NOT IN
          (
          SELECT masp
          FROM CTHD
          WHERE SOHD = HOADON.SOHD and CTHD.MASP = SANPHAM.MASP
          )

    • Để tìm nước có nhiều sản phẩm nhất em có thể Group by theo Mã nước, sau đó dùng Count(MASP) để đếm nhé.
      SELECT NUOCSX, Count(MASP)
      FROM SANPHAM
      GROUP BY NUOCSX

      Lúc này em sẽ được 1 bảng chứa danh sách số sản phẩm của các nước.
      Em có thể sắp xếp lại để lấy kết quả nhé.
      SELECT TOP 1 NUOCSX, Count(MASP) SoLuong
      FROM SANPHAM
      GROUP BY NUOCSX
      ORDER BY count(MASP) DESC

      Tuy nhiên cách trên chỉ lấy được 1 nước duy nhất có số sản phẩm nhiều nhất thôi, còn trường hợp nhiều nước có số lượng sản phẩm giống nhau mà lại lớn nhất thì em có thể dùng truy vấn lồng để lấy nhé.

    • Cho mình hỏi muốn tính số lượng đã bán tưng ứng của từng mặt hàng ta lam sau vay?
      3. Đưa ra số lượng đã bán tương ứng của từng mặt hàng.
      SELECT MAHANG*
      FROM TENHANG, SOHD
      WHERETENHANG.MAH= SOHD.MAH
      GROUP BY MAH

    • Bình luận trước đó em gửi anh nghĩ là lỗi cú pháp á.
      Anh nghĩ nếu dùng truy vấn lồng thì em có thể viết như thế này nhé

      Chúc em học tốt.

  1. SELECT sp.MASP,sp.TENSP
    FROM SANPHAM sp
    WHERE not exists ( SELECT ct.MASP FROM CTHD ct, HOADON hd WHERE sp.MASP=ct.MASP AND YEAR(hd.NGHD)=2006)
    AI GIẢI THÍCH CHO MÌNH CHỖ NÀY ĐƯỢC KHÔNG.TẠI WHERE LẦN 2 ẤY SAO KHÔNG CÓ HD.SOHD=CT.SOHD

    • Bạn đang nói đến câu 16 phải không?
      Mình vừa kiểm tra lại, chính xác là thiếu HD.SOHD = CT.SOHD.
      Mình đã cập nhật lại đáp án câu 16.
      Cảm ơn bạn đã góp ý.

      • AD cho em hỏi với ạ
        Em truy vấn như này thì sai chỗ nào ạ?
        SELECT SP.MASP,SP.TENSP
        FROM SANPHAM SP,HOADON HD,CTHD CT
        WHERE(SP.MASP=CT.MASP AND CT.SOHD=HD.SOHD AND YEAR(HD.NGHD)!=2006)

        • 1. Đưa ra danh sách mặt hàng được bán trong tháng 9 năm 2021
          2. Đưa ra danh sách khách hàng có địa chỉ ở Thái Nguyên và từng mua hàng trong tháng 9 năm 2021
          3. Đưa ra số lượng đã bán tương ứng của từng mặt hàng.

          • 1. Đưa ra danh sách mặt hàng được bán trong tháng 9 năm 2021

            SELECT COUNT (DISIINCT MAH)
            FROM CTHD C INNER JOIN HOADONHANG
            WHERE C. SOHD= H. SOHDON
            AND MONTH(NGAYBD)= ‘9’ AND YEAR (NGAYBD)= ‘2021’

            2. Đưa ra danh sách khách hàng có địa chỉ ở Thái Nguyên và từng mua hàng trong tháng 9 năm 2021
            SELECT KHACHHANG*
            FROM KHACHHANG, HDHANG, DIACHI
            WHERE KHACHHANG.MAK= SHDON.MAK
            AND DIACHI.MAH = HANG.MAH
            AND KHACHHANG. DIACHI= ‘THAINGUYEN’
            AND MONTH(NGAYBD)=’9’ AND YEAR(NGAYBD)= ‘2021’

            3. Đưa ra số lượng đã bán tương ứng của từng mặt hàng.
            SELECT COUNT(MAH) SOLUONG
            FROM TENHANG, SOHD
            WHERE TENHANG.MAH= SOHD.MAH
            GROUP BY MAH

  2. bạn ơi giải thích câu 18 được không.mình không hiểu nó hoạt động thế nào, mình có đọc 1 số tài liệu mà vẫn không hiểu cơ chế hoạt động của phép lồng tương quan thế nào.mình cảm ơn

  3. ANH ƠI XEM LẠI CÂU 18 HỘ E VS Ạ , CÓ 2 SẢN PHẨM SINGAPOR BÁN RA TỪ HÓA ĐƠN 1001 VÀ 1014 , MÀ TRUY VẪN NHƯ TRÊN ĐÁP ÁN CHỈ RA 1001 THÔI A

  4. AD cho em hỏi câu 16 ạ.
    Em viết câu truy vấn như này:
    SELECT SP.MASP,SP.TENSP
    FROM SANPHAM SP,HOADON HD,CTHD CT
    WHERE(SP.MASP=CT.MASP AND CT.SOHD=HD.SOHD AND YEAR(HD.NGHD)!=2006)
    Thì sai chỗ nào ạ?
    Em cảm ơn!

    • Hi em,

      Em viết vậy sai là vì em đang chọn những sản phẩm không được bán ra trong năm 2006, điều đó có nghĩa là nó truy vấn ra những “sản phẩm đã mua ở năm 2005, 2004,….”.

      Nên để hiểu đúng ý đề bài là hiển thị những sản phẩm không được bán trong năm 2006 thì em cần xác định những sản phẩm đã được mua ở năm này, và những sản phẩm không thuộc danh sách đã được mua chính là kết quả bài toán.

      Chúc em học tốt.

  5. Mọi người cho mình hỏi câu 12 tại sao không lấy SOHD ở bảng CTHD mà phải lấy SOHD ở bảng HOADON?
    SELECT DISTINCT CT.SOHD
    FROM CTHD CT
    WHERE CT.MASP IN (‘BB01’, ‘BB02’)
    AND SL BETWEEN 10 AND 20

    • Hi bạn, bạn hỏi làm mình bó tay luôn :’) Phần này lấy số HD ở HoaDon hay ở CTHD cũng như nhau nha bạn. Ở code mình làm ở trên mình có điều kiện hd.SOHD = ct.SOHD dùng để kết 2 bảng này lại nên 2 cái sohd này như nhau nha bạn. Tuy nhiên code của bạn có vẻ như bỏ quên phần “hd.SOHD = ct.SOHD”.
      Bạn tham khảo lại nha, chúc bạn học tốt.

      • Vì tại em mới học môn này nên còn ngơ ngơ anh ạ, hóa ra là nó như nhau :)) Còn code của em em chỉ lấy SOHD ở CTHD thì điều kiện “hd.SOHD = ct.SOHD” này để làm gì đâu anh?

        • à ừa nhỉ. Câu này đúng là không cần kết 2 bảng HoaDon với CTHD. Chỉ kết khi khai thác thêm thông tin của HD đó ví dụ như thêm 1 điều kiện nữa là mua trong năm 2006 chẳng hạn thì cần. 😀

  6. Hi a, anh cho em hỏi B18 sao em làm như bên dưới không ra thế ạ?
    Với, như cách là của B18 ở trên thì cái querry to nhất kiểu khai báo bảng HOADON r thì những cái subquerry có thể tự hiểu được ạ? Lúc e thêm bảng HOADON vào subquerry thì kết quả trả ra nhiều số hóa đơn, nên em k hiểu ra lúc add thêm bảng bên dưới thì nó chạy như nào, lại kiểu chạy duyệt lại 1 lần nữa cái bảng hóa đơn ạ?
    Anh xem giúp em với ạ, mãi k ra :((

    SELECT SOHD
    FROM HOADON
    WHERE
    NOT EXISTS

    (SELECT *
    FROM CTHD CT, SANPHAM SP
    WHERE SOHD=CT.SOHD AND ct.MASP=sp.masp and SP.NUOCSX not in (‘Singapore’))

  7. Em nghĩ là bài 18, 19 nên làm theo kiểu group by thì dễ hiểu hơn :3

    Bài 19:
    –Tạo bảng tạm–
    SELECT CTHD.SOHD, SANPHAM.MASP, NUOCSX INTO BANGTAM2
    FROM SANPHAM, CTHD, HOADON
    WHERE SANPHAM.MASP=CTHD.MASP AND CTHD.SOHD= HOADON.SOHD AND YEAR(HOADON.NGHD)=’2006′

    –Tìm số sản phẩm được sản xuất tại Singapore của mỗi hóa đơn và so sánh với số sp được sx ở Sing–
    SELECT SOHD
    FROM BANGTAM2
    GROUP BY SOHD, NUOCSX
    HAVING NUOCSX=’SINGAPORE’ AND COUNT(MASP) IN
    (SELECT COUNT(NUOCSX)
    FROM SANPHAM
    WHERE NUOCSX=’SINGAPORE’)

  8. Câu 14: MÌnh đâu cần lấy thêm bảng CTHD đâu anh. Em chưa hiểu chỗ Anh lấy thêm bảng CTHD.
    SELECT s.MASP, s.TENSP, NGHD
    FROM dbo.SANPHAM s , dbo.HOADON
    WHERE s.NUOCSX =’Trung Quoc’ AND dbo.HOADON.NGHD=’1/1/2007′

  9. chắc là ad học UIT
    có thể làm giúp em câu này được không
    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 đó
    dùng trigger , RBTV ấy
    câu này thuộc phần I câu 14 trong phần quản lý bán hàng , deadline sắp đến nơi mà em làm vẫn không ra
    em cảm ơn

    • CREATE TRIGGER trg_ins_cthd ON CTHD

      FOR INSERT

      AS

      BEGIN

      DECLARE @SoHD int, @MaSP char(4), @SoLg int, @TriGia money — Lay thong tin cua CTHD vua moi them vao

      SELECT @SoHD = SOH, @MaSP = MASP, @SoLg = SL

      FROM INSERTED

      — Tinh tri gia cua san pham moi them vao HOADON

      SET @TriGia = @SoLg * SELECT GIA FROM SANPHAM WHERE MASP

      — Khai bao mot CURSOR duyet qua tat ca cac CTHD da co sa

      DECLARE cur_cthd CURSOR

      FOR

      SELECT MASP, SL

      FROM CTHD

      WHERE SOHD = @SoHD

      OPEN cur cthd

      FETCH NEXT FROM cur_cthd

      INTO @MaSP, @SoLg

      WHILE (@@FETCH_STATUS = 0)

      BEGIN

      — Cong don tri gia cua tung san pham vao bien TriGia

      SET @TriGia = ©TriGia + @SoLg * SELECT GIA FROM SANPHAM WHERE MASP = @MaSP

      FETCH NEXT FROM cur cthd
      INTO @MaSP, @SoLg
      END

      CLOSE cur_cthd CURSOR
      DEALLOCATE cur_cthd —Tien hanh cap nhat lai tri gia HOADON
      UPDATE HOADON SET TRIGIA = @TriGia WHERE SOHD = @SoHD
      END

  10. Câu 45: Làm theo cách sau có được không anh? Em thấy cùng đáp án
    SELECT TOP 1 h.MAKH, COUNT(SOHD) AS SLMua
    FROM HOADON h
    WHERE h.MAKH IN (SELECT TOP 10 k.MAKH
    FROM KHACHHANG k
    ORDER BY DOANHSO DESC)
    GROUP BY h.MAKH
    ORDER BY SLMua DESC

  11. Cho em hỏi nếu muốn lọc các dữ liệu mà điều kiện là các khách hàng không có số điện thoại thì phải làm ntn ạ? E cám ơn!

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *