Thử giải đề thi SQL giữa kỳ. Mọi người tham khảo

Đề thi

Yêu cầu:
1. Hãy viết lệnh SQL để tạo cấu trúc các bảng trên với số lượng 1 lần mượn không quá 3 quyển.
2. Lập danh sách những độc giả nam của lớp MM04A có mượn sách ở thư viện.
3. Lập danh sách những độc giả của lớp MM04A có mượn sách ở thư viện nhưng chưa trả, gồm có các trường sau: MaDG, Hoten, Masach, Tensach, Ngaymuon, Ngayhen.
4. Thống kê trong thư viện có bao nhiêu sách của mỗi tác giả.
5. Lập danh sách liệt kê ngày có tổng số lượng sách được mượn lớn hơn tổng số lượng sách mà nhân viên “NV02” cho mượn.
6. Tính tổng số sách do độc giả “Nguyễn Văn An” mượn nhưng chưa trả.
7. Thêm một bản ghi mới vào bảng DMSach; dữ liệu phù hợp nhưng không được nhập giá trị null.
8. Thay đổi ngày hẹn trả của độc giả “DG01” thành 1/7/2010; và xóa sách “Toán kinh tế” trong bảng DMSach.
9. Tính số sách mượn trung bình trong 1 ngày.
10. Liệt kê những sách trong thư viện nhưng chưa từng có người mượn, sắp xếp theo thứ tự tăng dần của tác giả.
11. Hiển thị thông tin về cuốn sách được mượn nhiều nhất gồm: Masach, Tensach, tacgia.
12. Hiển thị những cuốn sách lượng mượn trung bình trong 1 lần lớn hơn 2.
13. Hiển thị những cuốn sách được mượn trong tháng 2 năm 2011.
14. Tính tổng số lượng sách được mượn trong tháng 3 năm 2010.
15. Hiển thị những độc giả chưa bao giờ mượn sách.



Giải: (câu 9 thua, nên không đưa vô)

PHP:


-- bai tap quan ly thu vien
-- cau 1--Hãy viết lệnh SQL để tạo cấu trúc các bảng trên với số lượng 1 lần mượn không quá 3 quyển.
CREATE TABLE DMSACH
(MASACH varchar(10) not null constraint FK_DMS primary key,
TENSACH varchar(100) not null,
TACGIA varchar(50) not null,
NHAXB varchar(100),
NAMXB float(10),
SOTRANG float(10));

CREATE TABLE DOCGIA
(MADG varchar(10) not null constraint FK_DG primary key,
HOTEN varchar(100) not null,
NGAYSINH datetime,
GIOITINH varchar(10),
DIACHI varchar(100),
DIENTHOAI varchar(20));

CREATE TABLE MUONTRA
(MADG varchar(10) not null references DOCGIA(MADG),
MASACH varchar(10) not null references DMSACH(MASACH),
MANV varchar(10) not null,
NGAYMUON datetime not null,
NGAYHEN datetime,
NGAYTRA datetime,
SOLUONG float(10) not null check(SOLUONG<=3),
GHICHU varchar(100),
constraint FK_DKM primary key (MADG,MASACH,NGAYMUON));

INSERT INTO DMSACH
VALUES('TM02','Quan tri du an','Nguyen Quang Dung','NXB Tai Chinh',2010,300);

INSERT INTO DOCGIA
VALUES('DG01','Nguyen Van An',12/01/1990,'Nam','MM04A',0904636232);

-- cau 2--Lập danh sách những độc giả nam của lớp MM04A có mượn sách ở thư viện. 

SELECT  DOCGIA.HOTEN, DOCGIA.DIACHI, DOCGIA.GIOITINH
FROM DOCGIA, MUONTRA
WHERE DOCGIA.MADG = MUONTRA.MADG and DOCGIA.GIOITINH='Nam' and DOCGIA.DIACHI='MM04A'
GROUP BY DOCGIA.HOTEN, DOCGIA.DIACHI, DOCGIA.GIOITINH

-- Cau 3--Lập danh sách những độc giả của lớp MM04A có mượn sách ở thư viện nhưng chưa trả, gồm có các trường sau: MaDG, Hoten, Masach, Tensach, Ngaymuon, Ngayhen.
SELECT MUONTRA.MADG, DOCGIA.HOTEN, MUONTRA.MASACH, DMSACH.TENSACH, MUONTRA.NGAYMUON, MUONTRA.NGAYHEN
FROM DMSACH, MUONTRA, DOCGIA
WHERE MUONTRA.MADG = DOCGIA.MADG and DMSACH.MASACH = MUONTRA.MASACH and DOCGIA.DIACHI = 'MM04A'
and MUONTRA.NGAYTRA is null
GROUP BY MUONTRA.MADG, DOCGIA.HOTEN, MUONTRA.MASACH, DMSACH.TENSACH, MUONTRA.NGAYMUON, MUONTRA.NGAYHEN

--Cau 4--Thống kê trong thư viện có bao nhiêu sách của mỗi tác giả.
SELECT DMSACH.TACGIA, COUNT(DMSACH.MASACH) as SOLUONG
FROM DMSACH
GROUP BY DMSACH.TACGIA


-- cau 5 --Lập danh sách liệt kê ngày có tổng số lượng sách được mượn lớn hơn tổng số lượng sách mà nhân viên “NV02” cho mượn.
SELECT MUONTRA.NGAYMUON, SUM(MUONTRA.SOLUONG) as TONGMUON
FROM MUONTRA
GROUP BY MUONTRA.NGAYMUON 
HAVING SUM(MUONTRA.SOLUONG)>= (SELECT SUM(MUONTRA.SOLUONG) as TONG
FROM MUONTRA
WHERE MUONTRA.MANV='NV02')

-- cau 6-- Tính tổng số sách do độc giả “Nguyễn Văn An” mượn nhưng chưa trả.
SELECT DOCGIA.HOTEN, SUM(MUONTRA.SOLUONG)
FROM DOCGIA, MUONTRA
WHERE DOCGIA.HOTEN='Nguyen Van An' and DOCGIA.MADG=MUONTRA.MADG and MUONTRA.NGAYTRA is null
GROUP BY DOCGIA.HOTEN

--cau 7--Thêm một bản ghi mới vào bảng DMSach; dữ liệu phù hợp nhưng không được nhập giá trị null.
INSERT INTO DMSACH
VALUES('MM02','Thiet Ke Website voi Joomla!','Bui Tan Viet','VietHanITC',2011,350);

--cau 8-- Thay đổi ngày hẹn trả của độc giả “DG01” thành 1/7/2010; và xóa sách “Toán kinh tế” trong bảng DMSach.
UPDATE MUONTRA
SET MUONTRA.NGAYHEN=01/07/2010 WHERE MUONTRA.MADG='DG01'

DELETE FROM DMSACH WHERE DMSACH.TENSACH='Toan Kinh Te'


-- cau 10-- Liệt kê những sách trong thư viện nhưng chưa từng có người mượn, sắp xếp theo thứ tự tăng dần của tác giả.

SELECT DMSACH.MASACH, DMSACH.TENSACH
FROM DMSACH, MUONTRA 
WHERE DMSACH.MASACH not in (SELECT MUONTRA.MASACH FROM MUONTRA GROUP BY MUONTRA.MASACH)
GROUP BY DMSACH.MASACH, DMSACH.TENSACH
 ORDER BY 2 ASC

-- cau 11 -- Hiển thị thông tin về cuốn sách được mượn nhiều nhất gồm: Masach, Tensach, tacgia.
SELECT MUONTRA.MASACH, DMSACH.TENSACH, DMSACH.TACGIA, MUONTRA.SOLUONG
FROM MUONTRA,DMSACH
WHERE MUONTRA.MASACH = DMSACH.MASACH and MUONTRA.SOLUONG = (select max(MUONTRA.SOLUONG) FROM MUONTRA)
-- cau 12 --Hiển thị những cuốn sách lượng mượn trung bình trong 1 lần lớn hơn 2.

SELECT MUONTRA.MASACH, DMSACH.TENSACH
FROM MUONTRA, DMSACH
WHERE MUONTRA.MASACH = DMSACH.MASACH and MUONTRA.SOLUONG >=2 

-- cau 13 -- Hiển thị những cuốn sách được mượn trong tháng 2 năm 2011.
SELECT MUONTRA.MASACH, DMSACH.TENSACH, MUONTRA.NGAYMUON
FROM MUONTRA, DMSACH
WHERE MUONTRA.MASACH = DMSACH.MASACH and month(MUONTRA.NGAYMUON)=02

-- cau 14 -- Tính tổng số lượng sách được mượn trong tháng 3 năm 2010.
SELECT SUM(MUONTRA.SOLUONG)
FROM MUONTRA, DMSACH
WHERE MUONTRA.MASACH = DMSACH.MASACH and month(MUONTRA.NGAYMUON) = 02 and year(MUONTRA.NGAYMUON) = 2010

-- cau 15-- Hiển thị những độc giả chưa bao giờ mượn sách.
SELECT DOCGIA.MADG, DOCGIA.HOTEN
FROM DOCGIA
WHERE DOCGIA.MADG not in (SELECT MUONTRA.MADG FROM MUONTRA)
GROUP BY DOCGIA.MADG, DOCGIA.HOTEN

Cái đề là 9. Tính số sách mượn trung bình trong "1" ngày. Chứ không phải là "mỗi ngày". Kết quả như chú làm là ra nhiều dòng.
Còn theo tớ hiểu thì tính trung bình một ngày mượn bao nhiêu quyển. Nghĩa là đáp án là một con số. (lấy tổng mượn tất cả chia cho số ngày)

Cô đính chính lại đề rồi. Sửa lại là mỗi

Mình và mấy đứa cũng nghĩ vậy. Cô cũng đồng ý. Nhưng chịu, làm lỗi. Còn cách như Ronaldo thì phải sửa "1" thành "mỗi", lúc này lấy tổng sách cho mượn trong một ngày rồi chia cho số lần mượn

Ronaldo làm được chưa post lên xem với... chẳng bít làm sa cả