Tài liệu Dkyi_mm03a

TRƯỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN HỮU NGHỊ VIỆT HÀN KHOA KHOA HỌC MÁY TÍNH CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM Độc lập - Tự do - Hạnh phúc Đà Nẵng, ngày 09 tháng 03 năm 2011 ĐỀ THI GIỮA HỌC PHẦN Cho cơ sở dữ liệu gồm các bảng như sau: - Bảng DMSACH MASACH TENSACH TACGIA KH01 Tin học đại cương KH02 Thủ thuật Excel 2010 Phạm Quang Huy TM01 Toán kinh tế TM02 Quản trị dự án NHAXB NAMXB SOTRANG Đỗ Công Đức 0 120 NXB Hà Nội 2010 272 Phạm Thúy Diệu NXB Trẻ 2009 57 Nguyễn Quang Dũng NXB tài chính 2010 - Bảng DOCGIA MADG HOTEN NGAYSINH GIOITINH DIACHI DG01 Nguyễn Văn An DIENTHOAI 12/1/1990 nam MM04A 0904636363 DG02 Trần Anh 2/1/1983 nam MM02B 0905020202 DG03 Lê Thị Liễu 1/1/1989 nữ MM04A 0906363636 - Bảng MUONTRA MADG MASACH MANV NGAYMUON NGAYTRA SOLUONG GHICHU NGAYHEN DG01 KH01 NV03 12/2/2010 12/8/2010 2/7/2010 2 DG02 KH02 NV02 12/2/2010 5/7/2010 1 DG01 TM01 NV02 15/2/2011 15/8/2011 2 Qui định khóa chính của các bảng như sau: Bảng DMSACH DOCGIA MUONTRA Khóa chính MaSach MaDG MaSach, MaDG, Ngaymuon Yêu cầu: 1. Hãy viết lệnh SQL để tạo cấu trúc các bảng trên. create table dmsach (masach varchar(20) not null constraint pk primary key, tensach varchar(20), tacgia varchar(20), nhaxb varchar(20), -1- 300 namxb int, sotrang int); create table docgia (madg varchar(20) not null constraint pk1 primary key, hoten varchar(20), ngaysinh datetime, gioitinh varchar(20), diachi varchar(20), dienthoai varchar(20)); create table muontra (madg varchar(20) not null constraint pk2 references docgia(madg), masach varchar(20) not null constraint pk3 references dmsach(masach), manv varchar(20), ngaymuon datetime, ngayhen datetime, ngaytra datetime, soluong numeric, ghichu varchar(20), constraint pk4 primary key (madg,masach,ngaymuon)); 2. Lập danh sách những độc giả chưa trả sách (cột Ngaytra trống) gồm các thông tin: MaDG, Hoten, Masach, Ngaymuon, Ngayhen. select b.madg, b.hoten, a.masach, c.ngaymuon, c.ngayhen, c.ngaytra from dmsach a, docgia b, muontra c where a.masach=c.masach and b.madg=c.madg and ngaytra is null; 3. Lập danh sách những độc giả của lớp MM04A có mượn sách ở thư viện. View gồm có các trường sau: MaDG, Hoten, Masach, Tensach, Ngaymuon, Ngayhen, Ngaytra. select b.madg, b.hoten, a.masach, a.tensach, c.ngaymuon, c.ngayhen, c.ngaytra from dmsach a, docgia b, muontra c where a.masach=c.masach and b.madg=c.madg and b.diachi='mm04a'; 4. Viết lệnh SQL để 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à độc giả có mã là “DG01” đã mượn gồm các cột sau: Ngaymuon, Tongsoluong. 5. Thống kê trong thư viện có bao nhiêu sách của mỗi nhà xuất bản. select a.nhaxb, count(a.masach) as tgsach from dmsach a group by a.nhaxb; 6. Tính tổng số sách do độc giả “Nguyễn Văn An” mượn nhưng chưa trả. select b.hoten, sum(c.soluong) as tongsosach from docgia b, muontra c where b.madg=c.madg and hoten='nguyen van an' and ngaytra is null group by b.hoten; 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('kh03','toan','nhan','nxb nhan',2010,500); 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 ngayhen=1/7/2010 where madg='dg01'; -2- delete from dmsach where tensach='toan kinh te'; 9. Tính số sách mượn trung bình trong 1 ngày. select ngaymuon, avg(soluong) from muontra group by ngaymuon; 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 a.masach ,a.tensach, a.tacgia from dmsach a where a.masach not in(select masach from muontra) 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 a.masach, a.tensach, a.tacgia from dmsach a, muontra c where a.masach=c.masach and c.soluong=(select max(soluong) from muontra, dmsach where dmsach.masach=muontra.masach); 12. Hiển thị những cuốn sách có số lượng mượn trung bình trong 1 lần lớn hơn 2. select masach,avg(soluong) from muontra group by masach having avg(soluong)>=2; 13. Hiển thị những cuốn sách được mượn trong tháng 2 năm 2011. select masach,ngaymuon from muontra where month(ngaymuon)=2 and year(ngaymuon)=2011; 14. Tính tổng số lượng sách được mượn trong tháng 2 năm 2010. select sum(soluong)as tsluong from muontra where month(ngaymuon)=2 and year(ngaymuon)=2010 group by ngaymuon; 15. Hiển thị những độc giả chưa bao giờ mượn sách. select madg, hoten from docgia where madg not in(select madg from muontra) create view abc as select a.masach, a.tensach, a.tacgia from dmsach a, muontra c where a.masach=c.masach; select * from abc; Đề có 7 câu / 02 trang TRƯỞNG BỘ MÔN GV RA ĐỀ -3- Lê Viết Trương Nguyễn Phương Tâm -4-