<span class="text_page_counter">Trang 1</span><div class="page_container" data-page="1">

<small>1</small> ĐẠI HỌC DUY TÂN TRƯỜNG KHOA HỌC MÁY TÍNH KHOA CƠNG NGHỆ THƠNG TIN

</div><span class="text_page_counter">Trang 2</span><div class="page_container" data-page="2">

MỤC LỤC:

1. Phân tích yêu cầu và thiết kế mơ hình cơ sở dữ liệu………...2

1.1. Mơ tả bài tốn………..2

1. PHÂN TÍCH U CẦU VÀ THIẾT KẾ MƠ HÌNH CƠ SỞ DỮ LIỆU

Mỗi khách hàng có một mã khách hàng (makh) duy nhất, có các thơng tin như họ tên (hoten), giới tính (phai), và số điện thoại (sdt). Mỗi mặt hàng có một mã hàng (mah) duy nhất, có thơng tin như tên hàng (tenh) và đơn vị tính (dvt). Mỗi hóa đơn có một số hóa đơn (mahd) duy nhất, ngày lập hóa đơn (ngayhd) và mỗi hóa đơn liên kết với một khách hàng thông qua mã khách hàng (makh). Mỗi chi tiết hóa đơn có thơng tin về số lượng (soluong) và đơn giá (dongia), mỗi chi tiết hóa đơn liên quan đến một hóa đơn thơng qua số hóa đơn (mahd) và mỗi chi tiết hóa đơn liên quan đến một mặt hàng thông qua mã hàng (mah).

</div><span class="text_page_counter">Trang 4</span><div class="page_container" data-page="4">

2. BIỂU THỨC ĐẠI SỐ QUAN HỆ:

</div><span class="text_page_counter">Trang 5</span><div class="page_container" data-page="5">

σ<small>ngayhd≥′2023−01−01′ ngayhd≤′2023−12−31′(∧</small> Hoadon) 2.4. Lấy những mặt hàng có sl bán>=20:

2.5. Lấy những mặt hàng có trong hóa đơn 1: π<small>mahd,mah,tenh</small>(Chitiet⋈<small>Chitiet.mah=Hang.mah</small>Hang)⋈<small>mahd=′1′{1}</small> 2.6. Lấy những mặt hàng mà khách hàng 1 đã mua:

Hoadon⋈<small>Hoadon.mahd=Chitiet.mahd</small>Chitiet⋈<small>Chitiet.mah=Hang.mah</small>Hang)⋈<small>makh=′1′{1}</small> 2.7. Lấy những hóa đơn mua với sl>50:

π<small>mah,mahd,tenh,soluong</small>(Chitiet⋈<small>Chitiet.mah=Hang.mah</small>Hang)⋈<small>soluong</small>>50σ<small>soluong</small>>50(Chitiet) 2.8. Lấy những mặt hàng chưa ai mua:

π<small>mah,tenh</small>(Hang)−πmah,tenh(Chitiet⋈<small>Chitiet.mah=Hang.mah</small>Hang) 3. CÂU LỆNH SQL:

Tạo cơ sở dữ liệu 3.1. Tạo bảng khachhang

CREATE TABLE khachhang ( makh NUMBER PRIMARY KEY,

</div><span class="text_page_counter">Trang 6</span><div class="page_container" data-page="6">

CREATE TABLE hang (

mah NUMBER PRIMARY KEY, tenh TEXT,

dvt TEXT );

3.3. Tạo bảng hoadon CREATE TABLE hoadon (

mahd NUMBER PRIMARY KEY,

</div><span class="text_page_counter">Trang 7</span><div class="page_container" data-page="7">

PRIMARY KEY (mahd, mah),

FOREIGN KEY (mahd) REFERENCES hoadon(mahd), FOREIGN KEY (mah) REFERENCES hang(mah) );

3.5. Nhập dữ liệu cho mỗi bảng Khachhang, hoadon, hang: mõi bảng 5 dòng

3.6. Nhập dữ liệu cho bảng chitiet: 10 dòng

*Thực hành truy vấn dữ liệu theo yêu cầu sau: <small>7</small>

</div><span class="text_page_counter">Trang 8</span><div class="page_container" data-page="8">

Truy vấn từ 1 bảng

3.7. Lấy những khách hàng phái Nữ: makh, hoten, phai, sdt SELECT makh, hoten, phai, sdt

FROM khachhang WHERE phai = 'Nữ';

3.8. Lấy những mặt hàng có dvt là “Kg” :mah, tenh, dvt SELECT mah, tenh, dvt

FROM hang WHERE dvt = 'Kg';

3.9. Lấy những hóa đơn trong năm 2023: mahd, nam, makh SELECT mahd, ngayhd, makh

FROM Hoadon

WHERE YEAR(ngayhd) = 2023;

</div><span class="text_page_counter">Trang 9</span><div class="page_container" data-page="9">

3.10. Lấy những mặt hàng có số lượng từ 10 đến 20. Sắp xếp tăng dần theo số lượng: mahd, mah, soluong, dongia

SELECT mahd, mah, soluong, dongia FROM chitiet

WHERE soluong BETWEEN 10 AND 20 ORDER BY soluong ;

3.11. Lấy 2 mặt hàng có đơn giá cao nhất. mah, soluong, dongia SELECT TOP 2 mah, soluong, dongia

SELECT a.makh, mahd, ngayhd, hoten FROM KhachHang a,HoaDon b

WHERE a.makh=b.makh and hoten like"Phạm*"

3.13. Liệt kê những mặt hàng Bia có đơn giá >5000. Mah, tenh, soluong, dongia SELECT ct.mah, h.tenh, ct.soluong, ct.dongia

FROM chitiet ct, hang h

WHERE ct.mah = h.mah AND h.tenh LIKE 'Bia' AND ct.dongia > 5000; <small>9</small>

</div><span class="text_page_counter">Trang 10</span><div class="page_container" data-page="10">

3.14. Liệt kê những hóa đơn trong tháng 10. Mahd, thang, mah, soluong SELECT hd.mahd, MONTH(hd.ngayhd) AS thang, ct.mah, ct.soluong FROM hoadon hd, chitiet ct

WHERE hd.mahd = ct.mahd AND MONTH(hd.ngayhd) = 10;

3.15. Liệt kê những khách hàng mua “Bia”: Makh, mah, tenh, soluong SELECT kh.makh, ct.mah, h.tenh, ct.soluong

FROM khachhang kh, hoadon hd, chitiet ct, hang h

WHERE kh.makh = hd.makh AND hd.mahd = ct.mahd AND ct.mah = h.mah AND h.tenh = 'Bia';

3.16. Liệt kê những khách phái nam có mua “Bia”. Makh, hoten, phai, mah, tenh, soluong

SELECT kh.makh, kh.hoten, kh.phai, ct.mah, h.tenh, ct.soluong FROM khachhang kh, hoadon hd, chitiet ct, hang h

WHERE kh.makh = hd.makh AND hd.mahd = ct.mahd AND ct.mah = h.mah AND kh.phai = 'Nam' AND h.tenh = 'Bia';

</div><span class="text_page_counter">Trang 11</span><div class="page_container" data-page="11">

Truy vấn sử dụng hàm 3.17. Đếm số hóa đơn của mặt hàng “Bia”

SELECT COUNT(DISTINCT hd.mahd) AS SoHoaDon FROM Hoadon hd, Chitiet ct, Hang h

WHERE hd.mahd = ct.mahd AND ct.mah = h.mah AND h.tenh = 'Bia';

3.18. Tính số lượng thấp nhất của mặt hàng “Bia” SELECT MIN(ct.soluong) AS SoLuongThapNhat FROM Hang h, Chitiet ct

WHERE h.mah=ct.mah AND h.tenh = 'Bia';

3.19. Tính tổng tiền đã bán được của mặt hàng “Bia” SELECT SUM(ct.dongia*ct.soluong) AS TongTienBan FROM Chitiet ct, Hang h

WHERE ct.mah = h.mah AND h.tenh = 'Bia';

3.20. Tính đơn giá thấp nhất của mặt hàng “Bia” SELECT MIN(ct.dongia) AS DonGiaThapNhat FROM hang h, chitiet ct

WHERE h.mah=ct.mah AND h.tenh = 'Bia'; <small>11</small>

</div><span class="text_page_counter">Trang 12</span><div class="page_container" data-page="12">

3.21. Tính số lượng trung bình của hóa đơn 2

SELECT AVG(ct.soluong) AS SoLuongTrungBinh FROM Chitiet ct

WHERE ct.mahd = 2;

Truy vấn gộp nhóm

3.22. Tính tổng số lượng đã bán của từng mặt hàng: mah, tenh, tong_sl SELECT h.mah, h.tenh, SUM(ct.soluong) AS tong_sl

FROM Hang h, Chitiet ct WHERE h.mah = ct.mah GROUP BY h.mah, h.tenh;

3.23. Tính tổng tiền của mỗi hóa đơn: Mahd, ngayhd, makh, tong_tien SELECT hd.mahd, hd.ngayhd, hd.makh, SUM(ct. soluong*ct.dongia) AS tong_tien

FROM Hoadon hd, Chitiet ct WHERE hd.mahd = ct.mahd

GROUP BY hd.mahd, hd.ngayhd, hd.makh;

</div><span class="text_page_counter">Trang 13</span><div class="page_container" data-page="13">

3.24. Lấy khách hàng mua với tổng tiền >200.000. Makh, hoten, tongtien SELECT hd.makh, kh.hoten, SUM(ct.soluong*ct.dongia) AS tongtien FROM Hoadon hd, Chitiet ct, Khachhang kh

WHERE hd.makh = kh.makh AND hd.mahd = ct.mahd GROUP BY hd.makh, kh.hoten

HAVING SUM(ct.soluong*ct.dongia) > 200000;

3.25. Lấy mặt hàng bán với trung bình đơn giá >10.000: Mah, tenh, TB_dongia SELECT h.mah, h.tenh, AVG(ct.dongia) AS TB_dongia

FROM Hang h, Chitiet ct WHERE h.mah = ct.mah GROUP BY h.mah, h.tenh HAVING AVG(ct.dongia) > 10000;

<small>13</small>

</div><span class="text_page_counter">Trang 14</span><div class="page_container" data-page="14">

3.26. Lấy những mặt hàng được mua trên 2 lần: Mah, tenh, so_lan SELECT h.mah, h.tenh, COUNT(ct.mahd) AS so_lan FROM Hang h, Chitiet ct

WHERE h.mah = ct.mah GROUP BY h.mah, h.tenh HAVING COUNT(ct.mahd) > 2;

Truy vấn lồng nhau và khác

3.27. Lấy mặt hàng chưa có người mua: Mah, tenh, dvt SELECT h.mah, h.tenh, h.dvt

FROM Hang h

WHERE h.mah NOT IN (SELECT mah FROM Chitiet);

3.28. Lấy hóa đơn có tổng tiền cao nhất

SELECT SUM(Chitiet.thanhtien) AS TongThanhTien FROM Chitiet

WHERE Chitiet.mahd IN (SELECT Hoadon.mahd FROM Hoadon);

</div><span class="text_page_counter">Trang 15</span><div class="page_container" data-page="15">

3.29. Lấy khách hàng mua cả vở kẻ ngang lẫn sách giáo khoa SELECT kh.makh, kh.hoten

FROM Khachhang kh, Hoadon hd, Chitiet ct1, Chitiet ct2, Hang h1, Hang h2 WHERE kh.makh = hd.makh

AND hd.mahd = ct1.mahd AND hd.mahd = ct2.mahd AND ct1.mah = h1.mah AND ct2.mah = h2.mah AND h1.tenh = 'Vở kẻ ngang' AND h2.tenh = 'Sách giáo khoa';

3.30. Lấy hóa đơn mua tất cả cả món hàng SELECT hd.mahd, hd.ngayhd, hd.makh

</div><span class="text_page_counter">Trang 17</span><div class="page_container" data-page="17">

DELETE FROM Hang

WHERE mah NOT IN (SELECT DISTINCT mah FROM Chitiet);

<small>17</small>

</div>