Bài tập SQL

Tham khảo bài thuyết trình bài tập sql, công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả
Nội dung trích xuất từ tài liệu:
Bài tập SQL• Cho lược đồ quan hệ gồm các quan hệ sau:• GIAOVIEN (MaGV,TenGV,Ngsinh,Diachi,NgVL,SoDT)• DETAI (MaDT, TenDT, NgBD,NgKT,MaGV)• SINHVIEN (MaSV, TenSV, Ngsinh, Gioitinh, MaDT) GIAOVIEN—(MaGV)—DETAI—(MaDT)—SINHVIEN 1• Câu 1.1a) Viết bằng Đại số quan hệ:• Cho biết thông tin về các đề tài bắt đầu hay kết thúc trong năm 2005 (MaDT, TenDT, TenGV). MaGV Q1 ← DETAI GIAOVIEN Q 2 ← Q1 : (YEAR( NgBD) = 2005 ∨ YEAR( NgKT ) = 2005) Q3 ← Q 2[ MaDT , TenDT , TenGV ] 2• Câu 1.1b) Viết bằng ngôn ngữ SQL:• Cho biết thông tin về các đề tài bắt đầu hay kết thúc trong năm 2005 (MaDT, TenDT, TenGV).• SELECT detai.MaDT, TenDT, TenGV FROM detai, giaovien WHERE detai.MaGV = giaovien.MaGV AND (YEAR(NgBD)=2005 OR YEAR(NgKT)=2005)• Xem hàm: YEAR, MONTH 3• Câu 1.2a) Viết bằng ĐSQH:• Cho biết thông tin về các đề tài do giáo viên ở ‘TP.HCM’ (Diachi=’ TP.HCM) hướng dẫn như sau: (MaDT, TenDT, TenGV, MaSV, TenSV). MaGV Q1 ← GIAOVIEN DETAI MaDT Q 2 ← Q1 SINHVIEN Q3 ← Q 2 : ( Diachi = TP.HCM ) Q 4 ← Q3[ MaDT , TenDT , TenGV , MaSV , TenSV ] 4• Câu 1.2b) Viết bằng NN SQL:• Cho biết thông tin về các đề tài do giáo viên ở ‘TP.HCM’ (Diachi=’ TP.HCM) hướng dẫn như sau: (MaDT, TenDT, TenGV, MaSV, TenSV).• SELECT detai.MaDT,TenDT, TenGV, MaSV,TenSV FROM giaovien, detai, sinhvien WHERE giaovien.MaGV = detai.MaGV AND detai.MaDT = sinhvien.MaDT AND Diachi=’ TP.HCM 5• Câu 1.3a) Viết bằng ĐSQH:• In ra các giáo viên (MaGV, TenGV) không hướng dẫn đề tài nào có ngày kết thúc trong tháng 10 năm 2005 Q1 ← DETAI : ( MONTH ( NgKT ) = 10 ∧ YEAR( NgKT ) = 2005) Q 2 ← Q1 [ MaGV ] Q3 ← GIAOVIEN [ MaGV ] Q 4 ← Q3 − Q 2 MaGV Q5 ← Q 4 GIAOVIEN Q6 ← Q5[ MaGV , TenGV ] 6• Câu 1.3b) Viết bằng NN SQL:• In ra các giáo viên (MaGV, TenGV) không hướng dẫn đề tài nào có ngày kết thúc trong tháng 10 năm 2005• SELECT MaGV, TenGV FROM giaovien WHERE MaGV NOT IN (SELECT MaGV FROM detai WHERE YEAR(NgKT)=2005 AND MONTH(NgKT)=10) 7• Câu 1.4a) Viết bằng ĐSQH:• Tìm các sinh viên “nam” có làm chung đề tài với các sinh viên “nu” (MaSV). Q1 ← SINHVIEN : (Gioitinh = nu )[ MaDT ] MaDT Q 2 ← ( SINHVIEN : (Gioitinh = nam ) Q1)[ MaSV ] 8• Câu 1.4b) Viết bằng NN SQL:• Tìm các sinh viên “nam” có làm chung đề tài với các sinh viên “nu” (MaSV).• SELECT MaSV FROM sinhvien WHERE Gioitinh=‘nam’ AND MaDT IN (SELECT MaDT FROM sinhvien WHERE Gioitinh=‘nu’) 9• Câu 1.5b) Viết bằng NN SQL:• Với mỗi đề tài, hãy in thông tin sau: (MaDT,TenDT,SLSV)• SELECT detai.MaDT, TenDT, COUNT(MaSV) AS SLSV FROM detai, sinhvien WHERE detai.MaDT = sinhvien.MaDT GROUP BY detai.MaDT, TenDT• Xem hàm: COUNT, SUM, MIN, MAX, AVG 10• Câu 1.6b) Viết bằng NN SQL:• Với các giáo viên ở ‘TP.HCM’ và các đề tài mà họ hướng dẫn có từ 2 sinh viên trở lên, hãy cho ra thống kê sau: (MaGV,TenGV,TenDT,SLSV)• SELECT detai.MaGV, TenGV, TenDT, COUNT(MaSV) AS SLSV FROM giaovien, detai, sinhvien WHERE giaovien.MaGV = detai.MaGV AND detai.MaDT = sinhvien.MaDT AND Diachi=’ TP.HCM GROUP BY detai.MaGV, TenGV, TenDT HAVING COUNT(MaSV) >= 2 11• Câu 2.1) Phát biểu chặc chẽ ràng buộc sau:• R1: Ngày kết thúc của mỗi đề tài phải sau ngày bắt đầu của đề tài đó.• 1. Phát biểu: dùng ngôn ngữ tân từ biến n bộ ∀ d ∈ DETAI (d.NgBD < d.NgKT)• 2. Bối cảnh: DETAI• 3. Tầm ảnh hưởng: R1 Thêm Xóa Sửa DETAI + - +(NgBD,NgKT) 12• Câu 2.2) Phát biểu chặc chẽ ràng buộc sau:• R2: Khi GV hướng dẫn 1 DT thì ngày bắt đầu của đề tài phải sau ngày vào làm của GV đó.• 1. Phát biểu: dùng ngôn ngữ tân từ biến n bộ ∀ d ∈ DETAI (∃ g ∈ GIAOVIEN (g.MaGV=d.MaGV) ⇒ g.NgVL• Câu 2.3) Phát biểu chặc chẽ ràng buộc sau:• R3: Thuộc tính “SoDT” trong GIAOVIEN phải bằng “số lượng đề tài” mà GV đó hướng dẫn.• 1. Phát biểu: dùng ngôn ngữ tân từ biến n bộ ∀ g ∈ GIAOVIEN d ∈ DETAI (g.SoDT = COUNTd∈DETAI ∧d.MaGV=g.MaGV(d.MaDT)• 2. Bối cảnh: DETAI• 3. Tầm ảnh hưởng: R2 Thêm Xóa Sửa GIAOVIEN - - +(SoDT) DETAI + + +(MaGV)• Xem hàm: COUNT, SUM 14• Câu 3.1) Tối ưu câu trả lời sau: MaGVBt1 = (( DETAI GIAOVIEN ) : (YEAR( NgBD) = 2005)) [ MaDT , TenDT , TenGV ] MaGVBt 2 = ( DETAI : (YEAR( NgBD ) = 2005) GIAOVIEN ) [ MaDT , TenDT , TenGV ]Bt 3 = ( DETAI : (YEAR( NgBD) = 2005)[ MaDT , TenDT , MaGV ] MaGV GIAOVIEN [ MaGV , TenGV ])[ MaDT , TenDT , TenGV ] 15 • Câu 3.2) Tối ưu câu trả lời sau:Bt1 = (( DETAI × GIAOVIEN ) : ( DETAI .MaGV = GIAOVIEN .MaGV ∧ YEAR( NgBD ) = 2005)) [ MaDT , TenDT , TenGV ] MaGVBt 2 = ( DETAI GIAOVIEN ) : (YEAR( NgBD) = 2005))[ MaDT , TenDT , TenGV ] 16• Câu 3.3) Tối ưu câu trả lời sau: MaGVBt1 = ((( DETAI GIAOVIEN ) : (YEAR( NgBD) = 2005)) : ( ...