SQLize | PHPize | SQLtest

-- /*=============KHOA==============*/ CREATE TABLE "khoa" ( ma_khoa varchar(2) NOT NULL, ten_khoa varchar(30) NOT NULL, -- KHOA CHINH CONSTRAINT khoa_pkey PRIMARY KEY (ma_khoa) ); COMMENT ON TABLE "khoa" IS 'Khoa'; -- /*==============SINH VIEN============*/ CREATE TABLE "sinh_vien" ( ma_sv varchar(3) not null, ho_sv varchar(15) not null, ten_sv varchar(7)not null, phai varchar(7), ngay_sinh timestamp(6) not null, noi_sinh varchar(20), ma_khoa varchar(2), hoc_bong float, -- KHOA CHINH CONSTRAINT sinh_vien_pkey PRIMARY KEY (ma_sv), -- KHOA NGOAI CONSTRAINT khoa_sinh_vien_fk FOREIGN KEY(ma_khoa) REFERENCES "khoa"(ma_khoa) ON DELETE SET NULL ); COMMENT ON TABLE "sinh_vien" IS 'Sinh Vien'; -- /*===================MON HOC========================*/ CREATE TABLE "mon_hoc" ( ma_mh varchar(2) not null, ten_mh varchar(25)not null, so_tiet int4, -- KHOA CHINH CONSTRAINT mon_hoc_pkey PRIMARY KEY (ma_mh) ); COMMENT ON TABLE "mon_hoc" IS 'Mon Hoc'; -- /*=====================KET QUA===================*/ CREATE TABLE "ket_qua" ( ma_sv varchar(3) not null, ma_mh varchar(2) not null, lan_thi int4, diem decimal(4,2), -- KHOA CHINH CONSTRAINT ket_qua_pkey PRIMARY KEY (ma_sv, ma_mh, lan_thi), -- KHOA NGOAI CONSTRAINT ket_qua_sinh_vien_fk FOREIGN KEY(ma_sv) REFERENCES "sinh_vien"(ma_sv) ON DELETE SET NULL, -- KHOA NGOAI CONSTRAINT ket_qua_mon_hoc_fk FOREIGN KEY(ma_mh) REFERENCES "mon_hoc"(ma_mh) ON DELETE SET NULL ); COMMENT ON TABLE "ket_qua" IS 'Ket Qua'; -- /*==================NHAP DU LIEU====================*/ insert into "mon_hoc"(ma_mh, ten_mh, so_tiet) values ('01', 'Cơ Sở Dữ Liệu', 45), ('02', 'Trí Tuệ Nhân Tạo', 45), ('03', 'Truyền Tin', 45), ('04', 'Đồ Họa', 60), ('05', 'Văn Phạm', 60); insert into "khoa"(ma_khoa,ten_khoa) values ('AV', 'Anh Văn'), ('TH', 'Tin Học'), ('TR', 'Triết'), ('VL', 'Vật Lý'); insert into "sinh_vien"(ma_sv,ho_sv, ten_sv, phai, ngay_sinh, noi_sinh, ma_khoa, hoc_bong) values ('A01', 'Nguyễn Thị', 'Hải', 'Nữ','1990-02-23', 'Hà Nội','TH',130000), ('A02', 'Trần Văn', 'Chính', 'Nam','1992-12-24', 'Bình Định','VL',150000), ('A03', 'Lê Thu Bạch', 'Yến', 'Nữ','1990-02-21', 'TP Hồ Chí Minh','TH',170000), ('A04', 'Trần Anh', 'Tuấn', 'Nam','1990-12-20', 'Hà Nội','AV',80000), ('B01', 'Trần Thanh', 'Mai', 'Nữ','1991-08-12', 'Hải Phòng','TR',0), ('B02', 'Trần Thị Thu', 'Thủy', 'Nữ','1991-01-02', 'TP Hồ Chí Minh','AV',0); insert into "ket_qua"(ma_sv, ma_mh, lan_thi, diem) values ('A01','01',1,3), ('A01','01',2,6), ('A01','02',2,6), ('A01','03',1,5), ('A02','01',1,4.5), ('A02','01',2,7), ('A02','03',1,10), ('A02','05',1,9), ('A03','01',1,2), ('A03','01',2,5), ('A03','03',1,2.5), ('A03','03',2,4), ('A04','05',2,10), ('B01','01',1,7), ('B01','03',1,2.5), ('B01','03',2,5), ('B02','02',1,6), ('B02','04',1,10); -- BAI TAP ==> REQUIREMENTS --@@ A. TRUY XUẤT thông tin --1. truy xuất hết thông tin của tất cả sinh viên hiện có SELECT * FROM sinh_vien; --2. truy xuất hết thông tin các môn học mà sinh viên có thể tham gia SELECT * FROM mon_hoc; --3. update số tiết là 45 ở môn học có mã là [05] SELECT * from mon_hoc where ma_mh = '05'; UPDATE mon_hoc set so_tiet = 45 where ma_mh = '05'; --4. ở sinh viên mã là [B01] và thuộc phái Name đổi tên thành 'Kỳ' UPDATE sinh_vien set ten_sv = 'Kỳ' WHERE ma_sv = 'B01' AND phai = 'Nam'; --5. ở sinh viên mã là [B02] thay đổi ngày sinh là 1990-07-05 UPDATE sinh_vien SET ngay_sinh = '1990-07-05' WHERE ma_sv = 'B02'; --6. tăng học bổng 100.000 VNĐ cho khoa 'Anh Văn' UPDATE sinh_vien SET hoc_bong = hoc_bong + 100.000 WHERE ma_khoa = 'AV'; select * From khoa where ten_khoa = 'Anh Văn'; --7. Xoá kết quả thi lần thứ 2 mà có điểm < 5 SELECT * FROM ket_qua WHERE lan_thi = 2; DELETE FROM ket_qua WHERE diem < 5 AND lan_thi = 2; SELECT * FROM ket_qua Where lan_thi = 2; --8. Xoá sinh viên có học bổng là 0 VNĐ SELECT ma_sv FROM sinh_vien WHERE hoc_bong = 0; DELETE FROM ket_qua WHERE ma_sv IN (SELECT ma_sv FROM sinh_vien WHERE hoc_bong = 0); DELETE FROM sinh_vien WHERE hoc_bong = 0; SELECT * FROM sinh_vien; SELECT * FROM ket_qua; --@@ B.Truy vấn những câu đơn giản -- 9. 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 ma_sv AS "Ma sinh vien", ho_sv AS "Ho sinh vien", ten_sv AS "Ten sinh vien", hoc_bong AS "Hoc bong" FROM sinh_vien ORDER BY ma_sv ASC; --10. 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ữ. SELECT ma_sv AS "Ma sinh vien", ho_sv || ' ' || ten_sv AS "Ho ten sinh vien", phai AS "Phai", ngay_sinh AS "Ngay sinh" FROM sinh_vien ORDER BY phai ASC; --11. 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 ho_sv || ' ' || ten_sv AS "Ho ten sinh vien", ngay_sinh AS "Ngay sinh", hoc_bong AS "Hoc bong" FROM sinh_vien ORDER BY ngay_sinh ASC, hoc_bong DESC; --12. Danh sách các môn học có tên bắt đầu bằng chữ T, gồm các thông tin: Mã môn, Tên môn, Số tiết. SELECT ma_mh AS "Ma mon hoc", ten_mh AS "Ten mon hoc", so_tiet AS "So tiet" FROM mon_hoc WHERE ten_mh LIKE 'T%'; --13. Liệt kê danh sách những sinh viên có chữ cái cuối cùng trong tên là I, gồm các thông tin: Họ tên sinh viên, Ngày sinh, Phái. SELECT ho_sv || ' ' || ten_sv AS "Ho ten sinh vien", ngay_sinh AS "Ngay sinh", phai AS "Phai" FROM sinh_vien WHERE ten_sv LIKE '%i'; --14. Danh sách những khoa có ký tự thứ hai của tên khoa có chứa chữ N, gồm các thông tin: Mã khoa, Tên khoa. SELECT ma_khoa AS "Ma khoa", ten_khoa AS "Ten khoa" FROM khoa WHERE ten_khoa LIKE '_n%'; --15. Liệt kê những sinh viên mà họ có chứa chữ Thị. SELECT ho_sv || ' ' || ten_sv AS "Ho ten sinh vien" FROM sinh_vien WHERE ho_sv LIKE '%Thị%'; --16. Cho biết danh sách những sinh viên có ký tự đầu tiên của tên nằm trong khoảng từ a --đến m, gồm các thông tin: Mã sinh viên, Họ tên sinh viên, Phái, Học bổng. SELECT ma_sv AS "Ma sinh vien", ho_sv || ' ' || ten_sv AS "Ho ten sinh vien", phai AS "Phai", hoc_bong AS "Hoc bong" FROM sinh_vien WHERE ten_sv BETWEEN 'A' AND 'M'; --17. Cho biết danh sách những sinh viên mà tên có chứa ký tự nằm trong khoảng từ a đến --m, gồm các thông tin: Họ tên sinh viên, Ngày sinh, Nơi sinh, Học bổng. Danh sách --được sắp xếp tăng dần theo họ tên sinh viên. SELECT ho_sv || ' ' || ten_sv AS "Ho ten sinh vien", ngay_sinh AS "Ngay sinh", noi_sinh AS "Noi sinh" ,hoc_bong AS "Hoc bong" FROM sinh_vien WHERE ten_sv BETWEEN 'A' AND 'M' ORDER BY ho_sv || ' ' || ten_sv ASC; --18. Cho biết danh sách các sinh viên có học bổng lớn hơn 100,000, gồm các thông tin: Mã --sinh viên, Họ tên sinh viên, Mã khoa, Học bổng. Danh sách sẽ được sắp xếp theo thứ tự --Mã khoa giảm dần. SELECT ma_sv AS "Ma sinh vien", ho_sv || ' ' || ten_sv AS "Ho ten sinh vien", ma_khoa AS "Ma khoa", hoc_bong AS "Hoc bong" FROM sinh_vien WHERE hoc_bong > 100000 ORDER BY ma_khoa DESC; --19. 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 ho_sv || ' ' || ten_sv AS "Ho ten sinh vien", ma_khoa AS "Ma khoa", noi_sinh AS "Noi sinh", hoc_bong AS "Hoc bong" FROM sinh_vien WHERE hoc_bong >= 150000 AND noi_sinh = 'Hà Nội'; --20. Danh sách các sinh viên của khoa Anh văn và khoa Vật lý, gồm các thông tin: Mã sinh --viên, Mã khoa, Phái. SELECT ma_sv AS "Ma sinh vien", ma_khoa AS "Ma khoa", phai AS "Phai" FROM sinh_vien WHERE ma_khoa = 'AV'OR ma_khoa = 'VL'; --21. Cho biết những sinh viên có ngày sinh từ ngày 01/01/1991 đến ngày 05/06/1992 gồm --các thông tin: Mã sinh viên, Ngày sinh, Nơi sinh, Học bổng. SELECT ma_sv AS "Ma sinh vien", ngay_sinh AS "Ngay sinh", noi_sinh AS "Noi sinh", hoc_bong AS "Hoc bong" FROM sinh_vien WHERE ngay_sinh >= '01/01/1991' AND ngay_sinh <= '05/06/1992'; --22. 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 ma_sv AS "Ma sinh vien", ngay_sinh AS "Ngay sinh", ma_khoa AS "Ma khoa" FROM sinh_vien WHERE hoc_bong >= 80000 AND hoc_bong <= 150000; --23. 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 ma_mh AS "Ma mon hoc", ten_mh AS "Ten mon hoc", so_tiet AS "So tiêt" FROM mon_hoc WHERE so_tiet > 30 AND so_tiet < 45; --24. Liệt kê những sinh viên nam của khoa Anh văn và khoa tin học, gồm các thông tin: Mã --sinh viên, Họ tên sinh viên, tên khoa, Phái. -- SELECT ma_sv AS "Ma sinh vien", ho_sv || ' ' || ten_sv AS "Ho ten sinh vien", ten_khoa AS "Ten khoa", phai AS "Phai" FROM sinh_vien, khoa WHERE phai = 'Nam' AND ma_khoa = 'AV' ////////// --25. Liệt kê những sinh viên nữ, tên có chứa chữ N-- SELECT * FROM sinh_vien WHERE phai = 'Nữ' AND ten_sv LIKE '%n%'; --@@ C. Truy vấn sử dụng hàm: year, month, day, getdate, case, …. --26. Danh sách sinh viên có nơi sinh ở Hà Nội và sinh vào tháng 02, gồm các thông tin: Họ --sinh viên, Tên sinh viên, Nơi sinh, Ngày sinh. SELECT ho_sv "Ho sinh vien", ten_sv "Ten sinh vien", noi_sinh "Noi sinh", ngay_sinh "Ngay sinh" FROM sinh_vien WHERE noi_sinh = 'Hà Nội' AND date_part ('month',ngay_sinh) = 2; --27. Cho biết những sinh viên có tuổi lớn hơn 20, thông tin gồm: Họ tên sinh viên, Tuổi,Học bổng. SELECT ho_sv || ' ' || ten_sv AS "Ho ten sv", date_part ('year',now()) - date_part('year',ngay_sinh) AS "Tuoi", hoc_bong AS "Hoc bong" FROM sinh_vien WHERE date_part ('year',now()) - date_part('year',ngay_sinh) >20; --28. 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 ho_sv || ' ' || ten_sv "Ho ten sv", date_part ('year',now()) - date_part('year', ngay_sinh) AS "Tuoi", khoa "Khoa" FROM sinh_vien WHERE date_part ('year',ngay_sinh) BETWEEN 20 AND 25; --29. Danh sách sinh viên sinh vào mùa xuân năm 1990, gồm các thông tin: Họ tên sinh viên, Phái, Ngày sinh. SELECT ho_sv || ' ' || ten_sv AS "Ho ten sv", phai "Phai", ngay_sinh "Ngay sinh" FROM sinh_vien WHERE date_part ('year',ngay_sinh) = 1990 AND date_part('month',ngay_sinh) IN (1,2,3); --30. 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 ma_sv "Ma sinh vien", phai "Phai", ma_khoa "Ma khoa", CASE WHEN hoc_bong > 50000 then 'Hoc bong cao' ELSE 'Muc trung bình' END AS "Muc hoc bong" FROM sinh_vien; --@@ D. Truy vấn sử dụng hàm kết hợp: max, min, count, sum, avg và gom nhóm --32. Cho biết tổng số sinh viên của toàn trường SELECT count(ma_sv) AS "Tong sinh vien" FROM sinh_vien; --33. Cho biết tổng sinh viên và tổng sinh viên nữ. --đây là cách viết gộp trong bảng SELECT count(ma_sv) AS "Tong sinh vien" FROM sinh_vien WHERE phai LIKE 'Nữ';