Tags:

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

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
									)
									)
5 2 votes
Article Rating
Subscribe
Notify of
guest
64 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
64
0
Would love your thoughts, please comment.x
()
x