Bài tập SQL| Quản lý đề tài

Cho cơ sở vật chất dữ liệu mang sơ đồ như sau:

Bài tập

Hãy viết những câu truy vấn sau bằng tiếng nói  SQL
Q1. Cho biết họ tên và mức lương của những thầy giáo nữ.

Q2. Cho biết họ tên của những thầy giáo và lương của họ sau lúc tăng 10%.

Q3. Cho biết mã của những thầy giáo mang họ tên khởi đầu là “Nguyễn” và lương trên $2000 hoặc, thầy giáo là trưởng bộ môn nhận chức sau năm 1995.

Q4. Cho biết tên những thầy giáo khoa Kỹ thuật 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 thầy giáo, 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à thầy giáo chủ nhiệm đề tài.

Q8. Với mỗi khoa cho biết thông tin trưởng khoa.

Q9. Cho biết những thầy giáo của bộ môn “Vi sinh” mang tham gia đề tài 006.

Q10. Với những đề tài thuộc cấp quản lý “Thành phường”, 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à liên hệ của người đấy.

Q11. Tìm họ tên của từng thầy giáo và người phụ trách chuyên môn trực tiếp của thầy giáo đó.

Q12. Tìm họ tên của những thầy giáo được “Nguyễn Thanh Tùng” phụ trách trực tiếp.

Q13. Cho biết tên thầy giáo 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 những công việc của đề tài HTTT quản lý những trường ĐH mang thời kì khởi đầu trong tháng
3/2008.

Q16. Cho biết tên thầy giáo và tên người quản lý chuyên môn của thầy giáo đó.

Q17. Cho những công việc khởi đầu từ từ 01/01/2007 tới 01/08/2007.

Q18. Cho biết họ tên những thầy giáo cùng bộ môn với thầy giáo “Trần Trà Hương”.

Q19. Tìm những thầy giáo vừa là trưởng bộ môn vừa chủ nhiệm đề tài.

Q20. Cho biết tên những thầy giáo 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ố những trưởng khoa mang chủ nhiệm đề tài.

Q23. Cho biết mã số những thầy giáo thuộc bộ môn HTTT hoặc mang tham gia đề tài mã 001.

Q24. Cho biết thầy giáo làm việc cùng bộ môn với thầy giáo 002.

Q25. Tìm những thầy giáo là trưởng bộ môn.

Q26. Cho biết họ tên và mức lương của những thầy giáo.

Q27. Cho biết số lượng thầy giáo viên và tổng lương của họ.

Q28. Cho biết số lượng thầy giáo 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 thầy giáo và số lượng đề tài mà thầy giáo đó tham gia.

Q31. Cho biết tên thầy giáo và số lượng đề tài mà thầy giáo đó làm chủ nhiệm.

Q32. Với mỗi thầy giáo cho tên thầy giáo và số người thân của thầy giáo đó.

Q33. Cho biết tên những thầy giáo đã tham gia từ 3 đề tài trở lên.

Q34. Cho biết số lượng thầy giáo đã 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 những giảng viên.

Q36. Cho biết những thầy giáo mang lương to nhất.

Q37. Cho biết lương cao nhất trong bộ môn “HTTT”.

Q38. Cho biết tên thầy giáo to tuổi nhất của bộ môn Hệ thống thông tin.

Q39. Cho biết tên thầy giáo nhỏ tuổi nhất khoa Kỹ thuật thông tin.

Q40. Cho biết tên thầy giáo và tên khoa của thầy giáo mang lương cao nhất.

Q41. Cho biết những thầy giáo mang lương to nhất trong bộ môn của họ.

Q42. Cho biết tên những đề tài mà thầy giáo Nguyễn Hoài An chưa tham gia.

Q43. Cho biết những đề tài mà thầy giáo 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 thầy giáo khoa Kỹ thuật thông tin mà chưa tham gia đề tài nào.

Q45. Tìm những thầy giáo ko tham gia bất kỳ đề tài nàoQ46. Cho biết thầy giáo mang lương to hơn lương của thầy giáo “Nguyễn Hoài An”

Q47. Tìm những trưởng bộ môn tham gia tối thiểu Một đề tài

Q48. Tìm thầy giáo trùng tên và cùng nam nữ với thầy giáo khác trong cùng bộ môn

Q49. Tìm những thầy giáo mang lương to hơn lương của ít nhất một thầy giáo bộ môn “Kỹ thuật phần mềm”

Q50. Tìm những thầy giáo mang lương to hơn lương của tất cả thầy giáo thuộc bộ môn “Hệ thống thông tin”

Q51. Cho biết tên khoa mang đông thầy giáo nhất

Q52. Cho biết họ tên thầy giáo chủ nhiệm nhiều đề tài nhất

Q53. Cho biết mã bộ môn mang nhiều thầy giáo nhất

Q54. Cho biết tên thầy giáo và tên bộ môn của thầy giáo tham gia nhiều đề tài nhất.

Q55. Cho biết tên thầy giáo tham gia nhiều đề tài nhất của bộ môn HTTT.

Q56. Cho biết tên thầy giáo và tên bộ môn của thầy giáo mang 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 thầy giáo nào mà tham gia đề tài đủ tất cả những chủ đề.

Q59. Cho biết tên đề tài nào mà được tất cả những thầy giáo của bộ môn HTTT tham gia.

Q60. Cho biết tên đề tài mang tất cả giảng viên bộ môn “Hệ thống thông tin” tham gia

Q61. Cho biết thầy giáo nào đã tham gia tất cả những đề tài mang mã chủ đề là QLGD.

Q62. Cho biết tên thầy giáo nào tham gia tất cả những đề tài mà thầy giáo Trần Trà Hương đã tham gia.

Q63. Cho biết tên đề tài nào mà được tất cả những thầy giáo của bộ môn Hóa Hữu Cơ tham gia.

Q64. Cho biết tên thầy giáo nào mà tham gia tất cả những công việc của đề tài 006.

Q65. Cho biết thầy giáo nào đã tham gia tất cả những đề tài của chủ đề Ứng dụng kỹ thuật.

Q66. Cho biết tên thầy giáo nào đã tham gia tất cả những đề 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ả những thầy giáo của khoa CNTT tham gia.

Q68. Cho biết tên thầy giáo nào mà tham gia tất cả những công việc của đề tài Nghiên cứu tế bào gốc.

Q69. Tìm tên những thầy giáo được phân công làm tất cả những đề tài mang kinh phí trên 100 triệu?

Q70. Cho biết tên đề tài nào mà được tất cả những thầy giáo của khoa Sinh Học tham gia.

Q71. Cho biết mã số, họ tên, ngày sinh của thầy giáo tham gia tất cả những 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 thầy giáo tham gia tất cả những đề
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 thầy giáo tham gia tất cả những đề tài mang thầy giáo “Nguyễn Hoài An” tham gia.

Q74. Cho biết họ tên thầy giáo khoa “Kỹ thuật thông tin” tham gia tất cả những công việc của đề tài mang trưởng bộ môn của bộ môn đông nhất khoa “Kỹ thuật thông tin” làm chủ nhiệm.

Q75. Cho biết họ tên thầy giáo và tên bộ môn họ làm trưởng bộ môn nếu mang

Q76. Cho danh sách tên bộ môn và họ tên trưởng bộ môn đó nếu mang

Q77. Cho danh sách tên thầy giáo và những đề tài thầy giáo đó chủ nhiệm nếu mang

Q78. Xóa những đề tài thuộc chủ đề “NCPT”.

Q80. Xuất ra thông tin thầy giáo (MAGV, HOTEN) và xếp hạng dựa vào mức lương. Nếu thầy giáo mang lương cao
nhất thì hạng là 1.

Q81. Xuất ra thông tin thu nhập của thầy giáo. Thu nhập của thầy giáo được tính bằng LƯƠNG + PHỤ CẤP. Nếu thầy giáo là trưởng bộ môn thì PHỤ CẤP là 300, và thầy giáo là trưởng khoa thì PHỤ CẤP là 600.

Q82. Xuất ra năm mà thầy giáo 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 trình diễn những 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 mang tối thiểu Một thầy giáo nữ

R4. Một thầy giáo phải mang ít nhất Một số điện thoại

R5. Một thầy giáo mang tối đa 3 số điện thoại

R6. Một bộ môn phải mang tối thiểu 4 thầy giáo

R7. Trưởng bộ môn phải là người to tuổi nhất trong bộ môn.

R8. Nếu một thầy giáo đã là trưởng bộ môn thì thầy giáo đó ko làm người quản lý chuyên môn.

R9. Thầy giáo và thầy giáo quản lý chuyên môn của thầy giáo đó phải thuộc về Một bộ môn.

R10. Mỗi thầy giáo chỉ mang tối đa Một vợ chồng

R11. Thầy giáo là Nam thì chỉ mang vợ chồng là Nữ hoặc trái lại.

R12. Nếu thân nhân mang quan hệ là “con gái” hoặc “con trai” với thầy giáo thì năm sinh của thầy giáo phải nhỏ hơn năm sinh của thân nhân.

R13. Một thầy giáo chỉ làm chủ nhiệm tối đa 3 đề tài.

R14. Một đề tài phải mang ít nhất một công việc

R15. Lương của thầy giáo phải nhỏ hơn lương người quản lý của thầy giáo đó.

R16. Lương của trưởng bộ môn phải to hơn lương của những thầy giáo trong bộ môn.

R17. Bộ môn ban nào cũng phải mang trưởng bộ môn và trưởng bộ môn phải là một thầy giáo trong trường.

R18. Một thầy giáo chỉ quản lý tối đa 3 thầy giáo khác.

R19. Thầy giáo chỉ tham gia những đề tài mà thầy giáo chủ nhiệm đề tài là người cùng bộ môn với thầy giáo đó.

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 thầy giáo mang lương to nhất	SELECT *	FROM GIAOVIEN GV	WHERE GV.LUONG = (SELECT MAX(LUONG)	FROM GIAOVIEN)	-- Câu 38: Cho biết tên của thầy giáo to 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 thầy giáo và tên khoa thầy giáo mang 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à thầy giáo 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 thầy giáo khoa Kỹ thuật thông tin chưa tham gia đề tài	SELECT HOTEN	FROM GIAOVIEN GV, DETAI DT, BOMON BM, KHOA K	WHERE (K.TENKHOA = N'Kỹ thuật 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 thầy giáo mang lương to hơn lương của thầy giáo 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 thầy giáo cùng tên, cùng nam nữ với những thầy giáo 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 thầy giáo mang lương to hơn tất cả những thầy giáo 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 thầy giáo 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 thầy giáo và tên bộ môn của thầy giáo 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 thầy giáo và tên của thầy giáo mang 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ả những thầy giáo 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 thầy giáo nào đã tham gia tất cả những đề tài mang 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ả thầy giáo 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 thầy giáo nào đã tham gia tất cả những đề tài của chủ đề ứng dụng kỹ thuật 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 kỹ thuật' 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ả thầy giáo 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 những thầy giáo được phân công làm tất cả những công việc của đề tài mang 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 thầy giáo của thầy giáo tham gia tất cả những 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 ) )

Leave a Comment

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *