Cho cơ sở dữ liệu có lược đồ như sau:
Bài tập
Hãy viết các câu truy vấn sau bằng ngôn ngữ SQL
Q1. Cho biết họ tên và mức lương của các giáo viên nữ.
Q2. Cho biết họ tên của các giáo viên và lương của họ sau khi tăng 10%.
Q3. Cho biết mã của các giáo viên có họ tên bắt đầu là “Nguyễn” và lương trên $2000 hoặc, giáo viên là trưởng bộ môn nhận chức sau năm 1995.
Q4. Cho biết tên những giáo viên khoa Công nghệ thông tin.
Q5. Cho biết thông tin của bộ môn cùng thông tin giảng viên làm trưởng bộ môn đó.
Q6. Với mỗi giáo viên, hãy cho biết thông tin của bộ môn mà họ đang làm việc.
Q7. Cho biết tên đề tài và giáo viên chủ nhiệm đề tài.
Q8. Với mỗi khoa cho biết thông tin trưởng khoa.
Q9. Cho biết các giáo viên của bộ môn “Vi sinh” có tham gia đề tài 006.
Q10. Với những đề tài thuộc cấp quản lý “Thành phố”, cho biết mã đề tài, đề tài thuộc về chủ đề nào, họ tên
người chủ nghiệm đề tài cùng với ngày sinh và địa chỉ của người ấy.
Q11. Tìm họ tên của từng giáo viên và người phụ trách chuyên môn trực tiếp của giáo viên đó.
Q12. Tìm họ tên của những giáo viên được “Nguyễn Thanh Tùng” phụ trách trực tiếp.
Q13. Cho biết tên giáo viên là trưởng bộ môn Hệ thống thông tin.
Q14. Cho biết tên người chủ nhiệm đề tài của những đề tài thuộc chủ đề Quản lý giáo dục.
Q15. Cho biết tên các công việc của đề tài HTTT quản lý các trường ĐH có thời gian bắt đầu trong tháng
3/2008.
Q16. Cho biết tên giáo viên và tên người quản lý chuyên môn của giáo viên đó.
Q17. Cho các công việc bắt đầu trong khoảng từ 01/01/2007 đến 01/08/2007.
Q18. Cho biết họ tên các giáo viên cùng bộ môn với giáo viên “Trần Trà Hương”.
Q19. Tìm những giáo viên vừa là trưởng bộ môn vừa chủ nhiệm đề tài.
Q20. Cho biết tên những giáo viên vừa là trưởng khoa và vừa là trưởng bộ môn.
Q21. Cho biết tên những trưởng bộ môn mà vừa chủ nhiệm đề tài
Q22. Cho biết mã số các trưởng khoa có chủ nhiệm đề tài.
Q23. Cho biết mã số các giáo viên thuộc bộ môn HTTT hoặc có tham gia đề tài mã 001.
Q24. Cho biết giáo viên làm việc cùng bộ môn với giáo viên 002.
Q25. Tìm những giáo viên là trưởng bộ môn.
Q26. Cho biết họ tên và mức lương của các giáo viên.
Q27. Cho biết số lượng giáo viên viên và tổng lương của họ.
Q28. Cho biết số lượng giáo viên và lương trung bình của từng bộ môn.
Q29. Cho biết tên chủ đề và số lượng đề tài thuộc về chủ đề đó.
Q30. Cho biết tên giáo viên và số lượng đề tài mà giáo viên đó tham gia.
Q31. Cho biết tên giáo viên và số lượng đề tài mà giáo viên đó làm chủ nhiệm.
Q32. Với mỗi giáo viên cho tên giáo viên và số người thân của giáo viên đó.
Q33. Cho biết tên những giáo viên đã tham gia từ 3 đề tài trở lên.
Q34. Cho biết số lượng giáo viên đã tham gia vào đề tài Ứng dụng hóa học xanh.Q35. Cho biết mức lương cao nhất của các giảng viên.
Q36. Cho biết những giáo viên có lương lớn nhất.
Q37. Cho biết lương cao nhất trong bộ môn “HTTT”.
Q38. Cho biết tên giáo viên lớn tuổi nhất của bộ môn Hệ thống thông tin.
Q39. Cho biết tên giáo viên nhỏ tuổi nhất khoa Công nghệ thông tin.
Q40. Cho biết tên giáo viên và tên khoa của giáo viên có lương cao nhất.
Q41. Cho biết những giáo viên có lương lớn nhất trong bộ môn của họ.
Q42. Cho biết tên những đề tài mà giáo viên Nguyễn Hoài An chưa tham gia.
Q43. Cho biết những đề tài mà giáo viên Nguyễn Hoài An chưa tham gia. Xuất ra tên đề tài, tên người chủ nhiệm đề tài.
Q44. Cho biết tên những giáo viên khoa Công nghệ thông tin mà chưa tham gia đề tài nào.
Q45. Tìm những giáo viên không tham gia bất kỳ đề tài nàoQ46. Cho biết giáo viên có lương lớn hơn lương của giáo viên “Nguyễn Hoài An”
Q47. Tìm những trưởng bộ môn tham gia tối thiểu 1 đề tài
Q48. Tìm giáo viên trùng tên và cùng giới tính với giáo viên khác trong cùng bộ môn
Q49. Tìm những giáo viên có lương lớn hơn lương của ít nhất một giáo viên bộ môn “Công nghệ phần mềm”
Q50. Tìm những giáo viên có lương lớn hơn lương của tất cả giáo viên thuộc bộ môn “Hệ thống thông tin”
Q51. Cho biết tên khoa có đông giáo viên nhất
Q52. Cho biết họ tên giáo viên chủ nhiệm nhiều đề tài nhất
Q53. Cho biết mã bộ môn có nhiều giáo viên nhất
Q54. Cho biết tên giáo viên và tên bộ môn của giáo viên tham gia nhiều đề tài nhất.
Q55. Cho biết tên giáo viên tham gia nhiều đề tài nhất của bộ môn HTTT.
Q56. Cho biết tên giáo viên và tên bộ môn của giáo viên có nhiều người thân nhất.
Q57. Cho biết tên trưởng bộ môn mà chủ nhiệm nhiều đề tài nhất.
Q58. Cho biết tên giáo viên nào mà tham gia đề tài đủ tất cả các chủ đề.
Q59. Cho biết tên đề tài nào mà được tất cả các giáo viên của bộ môn HTTT tham gia.
Q60. Cho biết tên đề tài có tất cả giảng viên bộ môn “Hệ thống thông tin” tham gia
Q61. Cho biết giáo viên nào đã tham gia tất cả các đề tài có mã chủ đề là QLGD.
Q62. Cho biết tên giáo viên nào tham gia tất cả các đề tài mà giáo viên Trần Trà Hương đã tham gia.
Q63. Cho biết tên đề tài nào mà được tất cả các giáo viên của bộ môn Hóa Hữu Cơ tham gia.
Q64. Cho biết tên giáo viên nào mà tham gia tất cả các công việc của đề tài 006.
Q65. Cho biết giáo viên nào đã tham gia tất cả các đề tài của chủ đề Ứng dụng công nghệ.
Q66. Cho biết tên giáo viên nào đã tham gia tất cả các đề tài của do Trần Trà Hương làm chủ nhiệm.
Q67. Cho biết tên đề tài nào mà được tất cả các giáo viên của khoa CNTT tham gia.
Q68. Cho biết tên giáo viên nào mà tham gia tất cả các công việc của đề tài Nghiên cứu tế bào gốc.
Q69. Tìm tên các giáo viên được phân công làm tất cả các đề tài có kinh phí trên 100 triệu?
Q70. Cho biết tên đề tài nào mà được tất cả các giáo viên của khoa Sinh Học tham gia.
Q71. Cho biết mã số, họ tên, ngày sinh của giáo viên tham gia tất cả các công việc của đề tài “Ứng dụng hóa học xanh”.
Q72. Cho biết mã số, họ tên, tên bộ môn và tên người quản lý chuyên môn của giáo viên tham gia tất cả các đề
tài thuộc chủ đề “Nghiên cứu phát triển”.
Q73. Cho biết họ tên, ngày sinh, tên khoa, tên trưởng khoa của giáo viên tham gia tất cả các đề tài có giáo viên “Nguyễn Hoài An” tham gia.
Q74. Cho biết họ tên giáo viên khoa “Công nghệ thông tin” tham gia tất cả các công việc của đề tài có trưởng bộ môn của bộ môn đông nhất khoa “Công nghệ thông tin” làm chủ nhiệm.
Q75. Cho biết họ tên giáo viên và tên bộ môn họ làm trưởng bộ môn nếu có
Q76. Cho danh sách tên bộ môn và họ tên trưởng bộ môn đó nếu có
Q77. Cho danh sách tên giáo viên và các đề tài giáo viên đó chủ nhiệm nếu có
Q78. Xóa các đề tài thuộc chủ đề “NCPT”.
Q79. Xuất ra thông tin của giáo viên (MAGV, HOTEN) và mức lương của giáo viên. Mức lương được xếp theo,quy tắc: Lương của giáo viên < $1800 : “THẤP” ; Từ $1800 đến $2200: TRUNG BÌNH; Lương > $2200:“CAO”
Q80. Xuất ra thông tin giáo viên (MAGV, HOTEN) và xếp hạng dựa vào mức lương. Nếu giáo viên có lương cao
nhất thì hạng là 1.
Q81. Xuất ra thông tin thu nhập của giáo viên. Thu nhập của giáo viên được tính bằng LƯƠNG + PHỤ CẤP. Nếu giáo viên là trưởng bộ môn thì PHỤ CẤP là 300, và giáo viên là trưởng khoa thì PHỤ CẤP là 600.
Q82. Xuất ra năm mà giáo viên dự kiến sẽ nghĩ hưu với quy định: Tuổi nghỉ hưu của Nam là 60, của Nữ là 55.
RÀNG BUỘC TOÀN VẸN
Hãy biểu diễn các ràng buộc toàn vẹn sau:
R1. Tên tài phải duy nhất
R2. Trưởng bộ môn phải sinh sau trước 1975
R3. Một bộ môn có tối thiểu 1 giáo viên nữ
R4. Một giáo viên phải có ít nhất 1 số điện thoại
R5. Một giáo viên có tối đa 3 số điện thoại
R6. Một bộ môn phải có tối thiểu 4 giáo viên
R7. Trưởng bộ môn phải là người lớn tuổi nhất trong bộ môn.
R8. Nếu một giáo viên đã là trưởng bộ môn thì giáo viên đó không làm người quản lý chuyên môn.
R9. Giáo viên và giáo viên quản lý chuyên môn của giáo viên đó phải thuộc về 1 bộ môn.
R10. Mỗi giáo viên chỉ có tối đa 1 vợ chồng
R11. Giáo viên là Nam thì chỉ có vợ chồng là Nữ hoặc ngược lại.
R12. Nếu thân nhân có quan hệ là “con gái” hoặc “con trai” với giáo viên thì năm sinh của giáo viên phải nhỏ hơn năm sinh của thân nhân.
R13. Một giáo viên chỉ làm chủ nhiệm tối đa 3 đề tài.
R14. Một đề tài phải có ít nhất một công việc
R15. Lương của giáo viên phải nhỏ hơn lương người quản lý của giáo viên đó.
R16. Lương của trưởng bộ môn phải lớn hơn lương của các giáo viên trong bộ môn.
R17. Bộ môn ban nào cũng phải có trưởng bộ môn và trưởng bộ môn phải là một giáo viên trong trường.
R18. Một giáo viên chỉ quản lý tối đa 3 giáo viên khác.
R19. Giáo viên chỉ tham gia những đề tài mà giáo viên chủ nhiệm đề tài là người cùng bộ môn với giáo viên đó.
Một số lời giải mẫu
Dưới đây là một số bài giải mẫu,
-- QUẢN LÝ ĐỀ TÀI. -- câu 36: Cho biết những giáo viên có lương lớn nhất SELECT * FROM GIAOVIEN GV WHERE GV.LUONG = (SELECT MAX(LUONG) FROM GIAOVIEN) -- Câu 38: Cho biết tên của giáo viên lớn tuổi nhất của bộ môn hệ thống thông tin SELECT HOTEN FROM GIAOVIEN GV, BOMON BM WHERE BM.TENBM = N'Hệ thống thông tin' AND GV.MABM = BM.MABM AND YEAR(GV.NGSINH) = (SELECT MIN(YEAR(GV1.NGSINH)) FROM GIAOVIEN GV1, BOMON BM1 WHERE BM1.TENBM = N'Hệ thống thông tin' AND GV1.MABM = BM1.MABM ) -- Câu 40: Cho biết tên giáo viên và tên khoa giáo viên có lương cao nhất SELECT GV.HOTEN, K.TENKHOA FROM GIAOVIEN GV, KHOA K, BOMON BM WHERE GV.MABM = BM.MABM AND BM.MAKHOA = K.MAKHOA AND (GV.LUONG = (SELECT MAX(LUONG) FROM GIAOVIEN)) -- Câu 42: Cho biết tên những đề tài mà giáo viên Nguyễn Hoài An chưa tham gia SELECT TENDT FROM DETAI DT WHERE DT.MADT NOT IN (SELECT DT.MADT FROM GIAOVIEN GV,THAMGIADT TG WHERE GV.HOTEN = N'Nguyễn Hoài An'AND GV.MAGV = TG.MAGV ) -- 44: Cho biết tên của những giáo viên khoa Công nghệ thông tin chưa tham gia đề tài SELECT HOTEN FROM GIAOVIEN GV, DETAI DT, BOMON BM, KHOA K WHERE (K.TENKHOA = N'Công nghệ thông tin' AND K.MAKHOA = BM.MAKHOA AND GV.MABM = BM.MABM AND GV.MAGV NOT IN (SELECT MAGV FROM THAMGIADT)) -- 46: Cho biết giáo viên có lương lớn hơn lương của giáo viên Nguyễn Hoài An SELECT GV1.MAGV , GV1.HOTEN FROM GIAOVIEN GV1, GIAOVIEN GV2 WHERE GV1.LUONG > GV2.LUONG AND GV2.HOTEN = N'Nguyễn Hoài An' AND GV1.MAGV != GV2.MAGV -- 48: tìm những giáo viên cùng tên, cùng giới tính với các giáo viên trong cùng bộ môn SELECT * FROM GIAOVIEN GV1, GIAOVIEN GV2 WHERE GV1.HOTEN = GV2.HOTEN AND GV2.PHAI = GV1.PHAI AND GV1.MAGV != GV2.MAGV AND GV1.MABM = GV2.MABM -- 50: tìm những giáo viên có lương lớn hơn tất cả các giáo viên thuộc bộ môn hệ thống thôn tin SELECT * FROM GIAOVIEN GV WHERE GV.LUONG >= ALL (SELECT GV1.LUONG FROM GIAOVIEN GV1, BOMON BM WHERE GV1.MABM = BM.MABM AND BM.TENBM = 'Hệ thống thông tin' ) -- 52: Cho biết tên của giáo viên chủ nhiệm nhiều đề tài nhất. SELECT HOTEN FROM GIAOVIEN GV WHERE GV.MAGV IN (SELECT MAGV FROM GIAOVIEN GV1, DETAI DT1 WHERE GV1.MAGV = DT1.GVCNDT GROUP BY GV1.MAGV HAVING COUNT(*) >= ALL (SELECT GV2.MAGV FROM GIAOVIEN GV2, DETAI DT2 WHERE GV2.MAGV = DT2.GVCNDT GROUP BY GV2.MAGV) ) -- 54: Cho biết tên giáo viên và tên bộ môn của giáo viên tham gia nhiều đề tài nhất SELECT HOTEN, TENBM FROM GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM AND EXISTS (SELECT GV2.MAGV FROM GIAOVIEN GV2, THAMGIADT TG WHERE GV2.MAGV = TG.MAGV AND GV.MAGV = GV2.MAGV GROUP BY GV2.MAGV HAVING COUNT(*) >=ALL (SELECT (COUNT(*)) FROM GIAOVIEN GV3, THAMGIADT TG3 WHERE GV3.MAGV = TG3.MAGV GROUP BY GV3.MAGV) ) -- 56: Cho biết tên giáo viên và tên của giáo viên có nhiều người thân nhất SELECT HOTEN, TENBM FROM GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM AND GV.MAGV IN (SELECT GV.MAGV FROM GIAOVIEN GV, NGUOITHAN NT WHERE GV.MAGV = NT.MAGV GROUP BY GV.MAGV HAVING COUNT(*) >=ALL (SELECT COUNT(*) FROM GIAOVIEN GV, NGUOITHAN NT WHERE GV.MAGV = NT.MAGV GROUP BY GV.MAGV) ) -- Câu 59: Cho biết tên đề tài nào mà được tất cả các giáo viên của bộ môn hệ thống thông tin tham gia SELECT TENDT FROM DETAI DT WHERE DT.MADT IN(SELECT DT.MADT FROM DETAI DT WHERE NOT EXISTS (SELECT GV.MAGV FROM GIAOVIEN GV WHERE GV.MABM = 'HTTT' EXCEPT SELECT TG.MAGV FROM THAMGIADT TG WHERE DT.MADT = TG.MADT ) ) -- Câu 61: Cho biết giáo viên nào đã tham gia tất cả các đề tài có mã chủ đề là QLGD SELECT * FROM GIAOVIEN GV WHERE NOT EXISTS (SELECT DT.MADT FROM DETAI DT WHERE DT.MACD = 'QLGD' EXCEPT SELECT TG.MADT FROM THAMGIADT TG WHERE GV.MAGV = TG.MAGV ) -- Câu 63: Cho biết tên đề tài nào mà được tất cả giáo viên của bộ môn hóa hữu cơ tham gia SELECT TENDT FROM DETAI DT WHERE DT.MADT IN(SELECT DT.MADT FROM DETAI DT WHERE NOT EXISTS (SELECT GV.MAGV FROM GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM AND BM.TENBM = N'Hóa hữu cơ' EXCEPT SELECT TG.MAGV FROM THAMGIADT TG WHERE DT.MADT = TG.MADT ) ) -- Câu 65: Cho biết giáo viên nào đã tham gia tất cả các đề tài của chủ đề ứng dụng công nghệ SELECT * FROM GIAOVIEN GV WHERE NOT EXISTS (SELECT DT.MADT FROM DETAI DT, CHUDE CD WHERE DT.MACD = CD.MACD AND CD.TENCD = N'Ứng dụng công nghệ' EXCEPT SELECT TG.MADT FROM THAMGIADT TG WHERE GV.MAGV = TG.MAGV ) -- Câu 67: Cho biết tên đề tài nào được tất cả giáo viên của khoa CNTT tham gia SELECT TENDT FROM DETAI DT WHERE DT.MADT IN(SELECT DT.MADT FROM DETAI DT WHERE NOT EXISTS (SELECT GV.MAGV FROM GIAOVIEN GV, BOMON BM WHERE GV.MABM = BM.MABM AND BM.MAKHOA = 'CNTT' EXCEPT SELECT TG.MAGV FROM THAMGIADT TG WHERE DT.MADT = TG.MADT ) ) -- 69: Tìm tên các giáo viên được phân công làm tất cả các công việc của đề tài có kinh phí trên 100tr SELECT GV.HOTEN FROM GIAOVIEN GV WHERE GV.MAGV IN (SELECT GV.MAGV FROM GIAOVIEN GV WHERE NOT EXISTS (SELECT DT.MADT FROM DETAI DT WHERE DT.KINHPHI >100000000 EXCEPT SELECT TG.MADT FROM THAMGIADT TG WHERE GV.MAGV = TG.MAGV ) ) -- 71: Cho biết mã số, họ tên, ngày sinh của giáo viên của giáo viên tham gia tất cả các công việc của đề tài ứng dụng xanh SELECT GV.HOTEN, GV.MAGV, GV.NGSINH FROM GIAOVIEN GV WHERE GV.MAGV IN (SELECT GV.MAGV FROM GIAOVIEN GV WHERE NOT EXISTS (SELECT DT.MADT, CV.SOTT FROM DETAI DT, CONGVIEC CV WHERE DT.MADT = CV.MADT AND DT.TENDT = N'Ứng dụng xanh' EXCEPT SELECT TG.MADT FROM THAMGIADT TG WHERE GV.MAGV = TG.MAGV ) )