Bài tập sql môn cơ sở dữ liệu số 1
bài tập sql môn cơ sở dữ liẹu
Môn: Kiến trúc máy tính (GTVT) 11 tài liệu
Trường: Trường Đại học Giao thông vận tải 487 tài liệu
Tác giả:
Preview text:
lOMoARcPSD|17548421 BÀI TẬP SQL
Cơ sở dữ liệu (Trường Đại học Giao thông Vận tải) Scan to open on Studeersnel
Studocu is not sponsored or endorsed by any college or university
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421 BÀI TẬP SQL
Hướng dẫn: Kết quả là một file .pdf gồm tất cả các câu, mỗi câu trả lời sẽ
gồm đoạn code sql và phần kết quả sau khi chạy bằng cách chụp màn
hình. Quy tắc đặt tên: TenHoDem_Lớp_BT3_Mã SV.pdf
Bài tập : Cho CSDL về quản lý bán hàng trong file QLSinhVien.sql như hình dưới:
(Hình minh họa ở đề bài )
1. Liệt kê danh sách sinh viên, gồm các thông tin sau: Mã sinh viên, Họ
sinh viên, Tên sinh viên, Học bổng. Danh sách sẽ được sắp xếp theo thứ
tự Mã sinh viên tăng dần.
--1.Liệt kê danh sách sinh viên, gồm các thông tin sau: Mã sinh viên, Họ sinh viên,
--Tên sinh viên, Học bổng. Danh sách sẽ được sắp xếp theo thứ tự Mã sinh viên tăng dần.
SELECT MaSV, HoSV, TenSV, HocBong FROM DSSinhVien ORDER BY MaSV ASC;
2. Danh sách các sinh viên gồm thông tin sau: Mã sinh viên, họ tên sinh
viên, Phái, Ngày sinh. Danh sách sẽ được sắp xếp theo thứ tự Nam/Nữ.
--2.Danh sách các sinh viên gồm thông tin sau: Mã sinh viên, họ tên sinh viên, Phái, Ngày sinh.
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
--Danh sách sẽ được sắp xếp theo thứ tự Nam/Nữ.
SELECT MaSV, CONCAT(HoSV, ' ', TenSV) AS HoTen, Phai, NgaySinh FROM DSSinhVien ORDER BY Phai;
3. Thông tin các sinh viên gồm: Họ tên sinh viên, Ngày sinh, Học bổng.
Thông tin sẽ được sắp xếp theo thứ tự Ngày sinh tăng dần và Học bổng giảm dần.
--3.Thông tin các sinh viên gồm: Họ tên sinh viên, Ngày sinh, Học bổng. Thông tin sẽ được sắp
--xếp theo thứ tự Ngày sinh tăng dần và Học bổng giảm dần.
SELECT CONCAT(HoSV, ' ', TenSV) AS HoTen, NgaySinh, HocBong FROM DSSinhVien
ORDER BY NgaySinh ASC, HocBong DESC;
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
4. Liệt kê các sinh viên có học bổng từ 150,000 trở lên và sinh ở Hà Nội,
gồm các thông tin:Họ tên sinh viên, Mã khoa, Nơi sinh, Học bổng.
--4.Liệt kê các sinh viên có học bổng từ 150,000 trở lên và sinh ở Hà Nội, gồm các thông tin:
--Họ tên sinh viên, Mã khoa, Nơi sinh, Học bổng.
SELECT CONCAT(HoSV, ' ', TenSV) AS HoTenSV, MaKhoa, NoiSinh, HocBong FROM DSSinhVien
WHERE HocBong >= 150000 AND NoiSinh = N'Hà Nội';
5. Danh sách những sinh viên có học bổng từ 80.000 đến 150.000, gồm
các thông tin: Mã sinh viên, Ngày sinh, Phái, Mã khoa.
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
--5.Danh sách những sinh viên có học bổng từ 80.000 đến 150.000, gồm các thông tin: Mã sinh
--viên, Ngày sinh, Phái, Mã khoa.
SELECT MaSV, NgaySinh, Phai, MaKhoa FROM DSSinhVien
WHERE HocBong BETWEEN 80000 AND 150000;
6. Cho biết những môn học có số tiết lớn hơn 30 và nhỏ hơn 45, gồm các
thông tin: Mã môn học, Tên môn học, Số tiết.
--6.Cho biết những môn học có số tiết lớn hơn 30 và nhỏ hơn 45, gồm các thông tin: Mã môn
--học, Tên môn học, Số tiết. SELECT MaMH, TenMH, SoTiet FROM DMMonHoc
WHERE SoTiet BETWEEN 31 AND 44;
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
7. Danh sách những sinh viên có tuổi từ 20 đến 25, thông tin gồm: Họ tên sinh viên, Tuổi, Tên khoa.
--7.Danh sách những sinh viên có tuổi từ 20 đến 25, thông tin gồm: Họ
tên sinh viên, Tuổi, Tên khoa. SELECT
sv.HoSV,sv.TenSV AS [Họ tên sinh viên],
DATEDIFF(YEAR, sv.NgaySinh, GETDATE()) AS Tuoi, k.TenKhoa AS [Tên khoa] FROM DSSinhVien sv INNER JOIN
DMKhoa k ON sv.MaKhoa = k.MaKhoa WHERE
DATEDIFF(YEAR, sv.NgaySinh, GETDATE()) BETWEEN 20 AND 25;
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
8. Cho biết thông tin về mức học bổng của các sinh viên, gồm: Mã sinh
viên, Phái, Mã khoa, Mức học bổng. Trong đó, mức học bổng sẽ hiển thị
là “Học bổng cao” nếu giá trị của field học bổng lớn hơn 500,000 và
ngược lại hiển thị là “Mức trung bình”
--8.Cho biết thông tin về mức học bổng của các sinh viên, gồm: Mã sinh viên, Phái, Mã khoa,
--Mức học bổng. Trong đó, mức học bổng sẽ hiển thị là “Học bổng cao” nếu giá trị của field
--học bổng lớn hơn 500,000 và ngược lại hiển thị là “Mức trung bình”. SELECT MaSV, Phai, MaKhoa, CASE
WHEN HocBong > 500000 THEN 'Học bổng cao' ELSE 'Muc trung binh' END AS MucHocBong FROM DSSinhVien;
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
9. Cho biết tổng số sinh viên của toàn trường
--9.Cho biết tổng số sinh viên của toàn trường
SELECT COUNT(*) AS TongSoSinhVien
10.Cho biết tổng sinh viên và tổng sinh viên nữ.
--10.Cho biết tổng sinh viên và tổng sinh viên nữ SELECT COUNT(*) AS TongSinhVien,
COUNT(*) - COUNT(CASE WHEN Phai = 'Nam' THEN 1 END) AS TongSinhVienNu FROM DSSinhVien;
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
11.Cho biết tổng số sinh viên của từng khoa.
--11.Cho biết tổng số sinh viên của từng khoa
SELECT MaKhoa, COUNT(*) AS TongSinhVien FROM DSSinhVien GROUP BY MaKhoa;
12. Cho biết số lượng sinh viên học từng môn.
--12.Cho biết số lượng sinh viên học từng môn
SELECT MaMH, COUNT(*) AS SoLuongSinhVien FROM KetQua GROUP BY MaMH;
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
13. Cho biết số lượng môn học mà sinh viên đã học(tức tổng số môn học có trong bảng kq)
--13.Cho biết số lượng môn học mà sinh viên đã học(tức tổng số môn học có trong bảng kq)
SELECT COUNT(DISTINCT MaMH) AS TongSoMonHoc FROM KetQua;
14. Cho biết tổng số học bổng của mỗi khoa.
--14.Cho biết tổng số học bổng của mỗi khoa
SELECT MaKhoa, SUM(HocBong) AS TongHocBong FROM DSSinhVien GROUP BY MaKhoa;
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
15. Cho biết học bổng cao nhất của mỗi khoa.
--15.Cho biết học bổng cao nhất của mỗi khoa
SELECT MaKhoa, MAX(HocBong) AS HocBongCaoNhat FROM DSSinhVien GROUP BY MaKhoa;
16. Cho biết tổng số sinh viên nam và tổng số sinh viên nữ của mỗi khoa.
--16.Cho biết tổng số sinh viên nam và tổng số sinh viên nữ của mỗi khoa
SELECT MaKhoa, Phai, COUNT(*) AS SoLuongSinhVien FROM DSSinhVien GROUP BY MaKhoa, Phai; SELECT MaKhoa,
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
SUM(CASE WHEN Phai = 'Nam' THEN 1 ELSE 0 END) AS TongSinhVienNam,
COUNT(*) - COUNT(CASE WHEN Phai = 'Nam' THEN 1 END) AS TongSinhVienNu FROM DSSinhVien GROUP BY MaKhoa;
17. Cho biết những năm sinh nào có 2 sinh viên đang theo học tại trường.
--17.Cho biết những năm sinh nào có 2 sinh viên đang theo học tại trường.
SELECT YEAR(NgaySinh) AS NamSinh, COUNT(*) AS SoSinhVien FROM DSSinhVien GROUP BY YEAR(NgaySinh) HAVING COUNT(*) = 2;
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
18. Cho biết những sinh viên thi lại trên 2 lần.
--18.Cho biết những sinh viên thi lại trên 2 lần.
SELECT MaSV, COUNT(*) AS SoLanThiLai FROM KetQua GROUP BY MaSV HAVING COUNT(*) > 2;
19. Đưa ra điểm trung bình của sinh viên có mã ‘A06’
--19.Đưa ra điểm trung bình của sinh viên có mã ‘A06’
SELECT AVG(ISNULL(Diem, 0)) AS DiemTrungBinh FROM KetQua WHERE MaSV = 'A06';
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
20. Thống kê số học sinh học cho mỗi môn học
--20.Thống kê số học sinh học cho mỗi môn học
SELECT MaMH, COUNT(*) AS SoLuongHocSinh FROM KetQua GROUP BY MaMH;
21. Đưa ra danh sách sinh viên gồm mã sinh viên, họ và tên, ngày sinh,
tên khoa học, điểm trung bình
--21.Đưa ra danh sách sinh viên gồm mã sinh viên, họ và tên, ngày sinh,
tên khoa học, điểm trung bình
SELECT sv.MaSV, sv.HoSV,sv.TenSv, sv.NgaySinh, k.TenKhoa,
COALESCE(AVG(kq.Diem), 0) AS DiemTrungBinh FROM DSSinhVien sv
LEFT JOIN KetQua kq ON sv.MaSV = kq.MaSV
LEFT JOIN DMKhoa k ON sv.MaKhoa = k.MaKhoa
GROUP BY sv.MaSV, sv.HoSV,sv.TenSV, sv.NgaySinh, k.TenKhoa;
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
22. Đưa ra danh sách sinh viên xuất sắc gồm mã sinh viên, họ và tên,
ngày sinh, tên khoa học, điểm trung bình với điểm trunh bình >=9.0
--22.Đưa ra danh sách sinh viên xuất sắc gồm mã sinh viên, họ và tên, ngày sinh, tên khoa học,
--điểm trung bình với điểm trunh bình >=9.0
SELECT sv.MaSV, sv.HoSV,sv.TenSV, sv.NgaySinh, k.TenKhoa,
COALESCE(AVG(kq.Diem), 0) AS DiemTrungBinh FROM DSSinhVien sv
LEFT JOIN KetQua kq ON sv.MaSV = kq.MaSV
LEFT JOIN DMKhoa k ON sv.MaKhoa = k.MaKhoa
GROUP BY sv.MaSV, sv.HoSV,sv.TenSV, sv.NgaySinh, k.TenKhoa
HAVING COALESCE(AVG(kq.Diem), 0) >= 9.0;
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
23. Cho biết thông tin của các sinh viên, gồm: Mã sinh viên,tên sinh viên,
Phái, Mã khoa, Điểm lần 1 môn có mã 01 (nếu có).
--23.Cho biết thông tin của các sinh viên, gồm: Mã sinh viên,tên sinh
viên, Phái, Mã khoa, Điểm
--lần 1 môn có mã 01 (nếu có) SELECT sv.MaSV, sv.HoSV, sv.TenSV, sv.Phai, sv.MaKhoa, kq1.Diem AS DiemLan1_Mon01 FROM DSSinhVien sv LEFT JOIN
KetQua kq1 ON sv.MaSV = kq1.MaSV AND kq1.MaMH = '01';
24. Thêm trường TinhTrang (tình trạng) vào bảng kết quả. Cập nhật dữ
liệu cho trường này biết rằng nếu điểm trung bình (điểm trung bình được
tính như câu 2.3) <4 ghi 0, từ 4 đến dưới 5.5 ghi 1, còn lại không ghi (null).
--24.Thêm trường TinhTrang (tình trạng) vào bảng kết quả. Cập nhật dữ
liệu cho trường này biết
--rằng nếu điểm trung bình (điểm trung bình được tính như câu 2.3) <4 ghi 0, từ 4 đến dưới
--5.5 ghi 1, còn lại không ghi (null) UPDATE KetQua
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421 SET TinhTrang = CASE
WHEN COALESCE((SELECT AVG(Diem) FROM KetQua kq
WHERE kq.MaSV = KetQua.MaSV), 0) < 4 THEN 0
WHEN COALESCE((SELECT AVG(Diem) FROM KetQua kq
WHERE kq.MaSV = KetQua.MaSV), 0) >= 4 AND
COALESCE((SELECT AVG(Diem) FROM KetQua kq WHERE
kq.MaSV = KetQua.MaSV), 0) < 5.5 THEN 1 ELSE NULL END;
25. Xoá tất cả những sinh viên chưa dự thi môn nào.
--25.Xoá tất cả những sinh viên chưa dự thi môn nào DELETE FROM DSSinhVien
WHERE MaSV NOT IN (SELECT DISTINCT MaSV FROM KetQua);
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
26. Xóa những môn mà không có sinh viên học.
--26.Xóa những môn mà không có sinh viên học DELETE FROM DMMonHoc
WHERE MaMH NOT IN (SELECT DISTINCT MaMH FROM KetQua);
27. Thêm vào bảng khoa cột Siso, cập nhật sỉ số vào khoa từ dữ liệu sinh viên.
--27.Thêm vào bảng khoa cột Siso, cập nhật sỉ số vào khoa từ dữ liệu sinh viên UPDATE DMKhoa SET Siso = (
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421 SELECT COUNT(*) FROM DSSinhVien
WHERE DSSinhVien.MaKhoa = DMKhoa.MaKhoa );
28. Tăng thêm 1 điểm cho các sinh viên vớt lần 2. Nhưng chỉ tăng tối đa là 5 điểm
--28.Tăng thêm 1 điểm cho các sinh viên vớt lần 2. Nhưng chỉ tăng tối đa là 5 điểm UPDATE KetQua SET Diem = CASE
WHEN Diem + 1 <= 5 THEN Diem + 1 ELSE Diem END WHERE LanThi = 2;
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com) lOMoARcPSD|17548421
29. Tăng học bổng lên 100000 cho những sinh viên có điểm trung bình là 6.5 trở lên
--29.Tăng học bổng lên 100000 cho những sinh viên có điểm trung bình là 6.5 trở lên UPDATE DSSinhVien SET HocBong = HocBong + 100000
WHERE (SELECT AVG(Diem) FROM KetQua WHERE KetQua.MaSV = DSSinhVien.MaSV) >= 6.5;
30. Thiết lập học bổng bằng 0 cho những sinh viên thi hai môn rớt ở lần 1
--30.Thiết lập học bổng bằng 0 cho những sinh viên thi hai môn rớt ở lần 1 UPDATE DSSinhVien
Downloaded by Nh?t Hoàng ??ng (duongxidaucho@gmail.com)
