HƯỚNG DẪN THỰC HÀNH SQL SERVER TUẦN 5 Lưu ý: Các bạn phải tự làm trước khi xem hướng dẫn. Nếu lời giải của các bạn khác với hướng dẫn bạn cần thực thi cả 2 câu lệnh xem kết quả có giống nhau không, nếu kết quả giống thì bạn đã có 2 cách khác nhau cho một câu hỏi nào đó, nếu kết quả khác thì kiểm tra xem sai ở chỗ nào nhé! --Câu 1: Tìm những nhân viên làm việc ở phòng số 4. select MANV,HONV,TENLOT,TENNV from NHANVIEN where PHG=4 --Câu 2: Tìm những nhân viên có mức lương trên 30000. Select MANV,HONV,TENLOT,TENNV,LUONG from NHANVIEN where LUONG>30000 --Câu 3: Tìm những nhân viên có mức lương trên 25000 ở phòng số 4 hoặc có mức lương trên 30000 ở phòng số 5. select MANV,HONV,TENLOT,TENNV,LUONG,PHG from NHANVIEN where (LUONG>25000 AND PHG=4) OR (LUONG>30000 AND PHG=5) --Câu 4: Cho biết họ tên đầy đủ của các nhân viên ở TPHCM. select HONV+' '+TENLOT+' '+TENNV AS "HO VA TEN",DCHI from NHANVIEN where DCHI LIKE '%TPHCM%' --Câu 5: Cho biết ngày sinh và địa chỉ của nhân viên Dinh Ba Tien. select from HONV+' '+ TENLOT+' '+TENNV AS 'HO VA TEN', NGSINH, DCHI NHANVIEN ThS. Lương Thị Ngọc Khánh – K. CNTT – Đại học Tôn Đức Thắng 1 where HONV='Dinh' AND TENLOT='Ba' AND TENNV='Tien' --Câu 6: Với mỗi phòng ban, cho biết tên phòng ban và địa điểm phòng. select TENPHG,DIADIEM from PHONGBAN,DIADIEM_PHG where PHONGBAN.MAPHG=DIADIEM_PHG.MAPHG --Câu 7: Tìm tên những người trưởng phòng của từng phòng ban. select MAPHG, TENPHG, TRPHG, HONV+' '+ TENLOT+' '+TENNV AS [HO VA TEN] from PHONGBAN,NHANVIEN where PHONGBAN.TRPHG=NHANVIEN.MANV --Câu 8: Tìm TENDA, MADA, DDIEM_DA, PHONG, TENPHG, MAPHG, TRPHG, NG_NHANCHUC. select TENDA, MADA, DDIEM_DA, PHONG, TENPHG, MAPHG, TRPHG, NG_NHANCHUC from DEAN, PHONGBAN where DEAN.PHONG=PHONGBAN.MAPHG --Câu 9: Tìm tên và địa chỉ của những nhân viên phòng ‘nghien cuu’ select HONV,TENLOT,TENNV,DCHI,TENPHG from NHANVIEN,PHONGBAN where MAPHG=PHG AND TENPHG='Nghien cuu' --Câu 10: Tìm tên những nữ nhân viên và tên người thân của họ. select HONV,TENLOT,TENNV,TENTN from NHANVIEN,THANNHAN where NHANVIEN.MANV=THANNHAN.MA_NVIEN AND NHANVIEN.PHAI='Nam' ThS. Lương Thị Ngọc Khánh – K. CNTT – Đại học Tôn Đức Thắng 2 --Câu 11: Giải thích ý nghĩa của hai câu truy vấn sau: select MANV, TENNV from NHANVIEN where MA_NQL is null select MANV, TENNV from NHANVIEN where MA_NQL is not null --Câu 12: Cho biết họ tên và địa chỉ các nhân viên sống ở TPHCM, sắp xếp tăng dần / giảm dần theo tên. select HONV+' '+ TENLOT+' '+TENNV AS [HO VA TEN], DCHI from NHANVIEN where DCHI like '%tphcm' order by TENNV desc --asc: tang dan => [mac dinh] --Câu 13: Cho biết họ tên và mức lương các nhân viên trên 40 tuổi, sắp xếp tăng dần / giảm dần theo lương. select HONV+' '+ TENLOT+' '+TENNV AS [HO VA TEN], LUONG, year(getdate())-year(ngsinh) as tuoi from NHANVIEN where year(getdate())-year(ngsinh)>=40 order by LUONG asc --desc --Câu 14: Hãy cho biết những nhân viên nào tham gia vào các đề án có địa điểm đặt ở ‘Ha noi’. select HONV+TENLOT+TENNV as [Ho va ten],MADA,PHONG from NHANVIEN,DEAN,PHANCONG where MANV=MA_NVIEN and SODA=MADA and DDIEM_DA='Ha noi' --Câu 15: Với các đề án ở ‘TPHCM’ , liệt kê các mã số đề án, mã số phòng ban chủ trì đề án, họ tên trưởng phòng, cũng như ngày sinh và địa chỉ của người ấy. select MADA,MAPHG,HONV+TENLOT+TENNV as [Ho va ten],DCHI,NGSINH, DDIEM_DA from DEAN,PHONGBAN,NHANVIEN where DEAN.PHONG=PHONGBAN.MAPHG ThS. Lương Thị Ngọc Khánh – K. CNTT – Đại học Tôn Đức Thắng and 3 PHONGBAN.TRPHG=NHANVIEN.MANV and DEAN.DDIEM_DA LIKE '%tphcm%' --Câu 16: Với mỗi nhân viên cho biết họ tên nhân viên và họ tên người quản lý trực tiếp của nhân viên. select from where N1.HONV, N1.TENLOT, N1.TENNV, N1.MA_NQL, N2.HONV AS HO_NQL, N2.TENLOT AS TENLOT_NQL, N2.TENNV AS TEN_NQL, N2.MANV NHANVIEN N1, NHANVIEN N2 (N1.MA_NQL = N2.MANV) --Câu 17: Với mỗi nhân viên cho biết họ tên nhân viên và họ tên trưởng phòng của phòng ban mà nhân viên làm việc. select NV.HONV,NV.TENLOT,NV.TENNV, TR.HONV as Ho_trphg,TR.TENLOT as Tenlot_trphg,TR.TENNV as Ten_trphg,TENPHG from NHANVIEN NV,NHANVIEN TR,PHONGBAN where PHONGBAN.MAPHG=NV.PHG and PHONGBAN.TRPHG=TR.MANV --Câu 18: Tìm những nhân viên phòng số 5 có tham gia vào đề án ‘sản phẩm X’ với số giờ làm việc trên 10h. select HONV+' '+ TENLOT+' '+TENNV AS [HO VA TEN],TENDA,PHONG,THOIGIAN from NHANVIEN ,DEAN ,PHANCONG where TENDA='SAN PHAM X' and MADA=SODA and MANV=PHANCONG.MA_NVIEN and PHG=5 and THOIGIAN >10 --Câu 19: Tìm những nhân viên phòng số 5 có tham gia vào đề án ‘sản phẩm X’ và nhân viên này do ‘Nguyen Thanh Tung’ quản lý trực tiếp. select N1. HONV, N1.TENLOT, N1. TENNV,N1.PHG, N2.HONV+' '+N2.TENLOT+' '+N2.TENNV AS 'HO TEN NGUOI QUAN LY' ThS. Lương Thị Ngọc Khánh – K. CNTT – Đại học Tôn Đức Thắng 4 from where NHANVIEN N1 ,DEAN ,PHANCONG ,NHANVIEN N2 DEAN.TENDA='SAN PHAM X' and DEAN.MADA=PHANCONG.SODA and N1.MANV=PHANCONG.MA_NVIEN and N1. PHG=5 and N1.MA_NQL=N2.MANV and N2.HONV='Nguyen' and N2.TENLOT='Thanh' and N2.TENNV='Tung' --Câu 20: Cho biết họ tên nhân viên và tên các đề án nhân viên này tham gia nếu có. select from where HONV,TENLOT,TENNV,TENDA NHANVIEN,PHONGBAN,DEAN PHG=MAPHG and PHONG=MAPHG --Câu 21: Với mỗi đề án, liệt kê tên đề án và tổng số giờ làm việc của những nhân viên tham gia đề án nếu có. select SODA,TENDA,SUM(THOIGIAN) AS TONGTHOIGIAN from PHANCONG,DEAN where DEAN.MADA=PHANCONG.SODA group by SODA,TENDA --Câu 22: Với mỗi nhân viên, cho biết họ tên nhân viên và nhân viên đó có bao nhiêu thân nhân. select HONV,TENLOT,TENNV,COUNT(*) AS 'SO THAN NHAN' from NHANVIEN,THANNHAN where NHANVIEN.MANV=THANNHAN.MA_NVIEN group by HONV,TENLOT,TENNV --Câu 23: Với mỗi phòng ban, liệt kê tên phòng ban và lương trung bình của những nhân viên làm việc trong phòng ban đó. select TENPHG,AVG(LUONG) as 'Luong Trung Binh' from NHANVIEN,PHONGBAN where NHANVIEN.PHG=PHONGBAN.MAPHG group by TENPHG ThS. Lương Thị Ngọc Khánh – K. CNTT – Đại học Tôn Đức Thắng 5 --Câu 24: Cho biết lương trung bình của tất cả nữ nhân viên. select AVG(LUONG) AS LUONGTB from NHANVIEN where PHAI='NU' --Câu 25: Với mỗi phòng ban có mức lương trung bình trên 30000, liệt kê tên phòng ban và số lượng nhân viên trong phòng ban đó. select TENPHG, count(*) as 'So luong nhan vien', avg(luong) as 'luong trung binh' from NHANVIEN,PHONGBAN where NHANVIEN.PHG=PHONGBAN.MAPHG group by TENPHG having avg(LUONG)>30000 --Câu 26: Cho biết các mã đề án và tên đề án có: -nhân viên với họ là ‘Nguyen’ tham gia hoặc -trưởng phòng chủ trì đề án đó với họ là ‘Nguyen’ select MADA, TENDA from PHANCONG, NHANVIEN,DEAN where NHANVIEN.MANV=PHANCONG.MA_NVIEN and DEAN.MADA=PHANCONG.SODA and NHANVIEN.HONV='Nguyen' UNION select MADA,TENDA from DEAN,PHONGBAN,NHANVIEN where DEAN.PHONG=PHONGBAN.MAPHG and PHONGBAN.TRPHG=NHANVIEN.MANV and NHANVIEN.HONV='Nguyen' --Cau 26: Cách khác select MADA,TENDA from PHANCONG,NHANVIEN,DEAN where NHANVIEN.MANV=PHANCONG.MA_NVIEN and DEAN.MADA=PHANCONG.SODA and NHANVIEN.HONV='Nguyen' UNION select MADA,TENDA ThS. Lương Thị Ngọc Khánh – K. CNTT – Đại học Tôn Đức Thắng 6 from DEAN,PHONGBAN where DEAN.PHONG=PHONGBAN.MAPHG and PHONGBAN.TRPHG IN (select MANV from NHANVIEN where NHANVIEN.HONV='Nguyen' and PHONGBAN.MAPHG=NHANVIEN.PHG) --Câu 27: Danh sách những nhân viên có từ 2 thân nhân trở lên. select HONV,TENLOT,TENNV from NHANVIEN where (select COUNT(*) from THANNHAN where NHANVIEN.MANV=THANNHAN.MA_NVIEN)>=2 --Cau 27: Cách khác select HONV,TENLOT,TENNV, count(*) as 'So luong than nhan' from NHANVIEN, THANNHAN where MANV=MA_NVIEN group by HONV,TENLOT,TENNV having count(*)>=2 --Câu 28: Danh sách những nhân viên không có thân nhân nào. select HONV,TENLOT,TENNV from NHANVIEN where NOT EXISTS (select * from THANNHAN where NHANVIEN.MANV=THANNHAN.MA_NVIEN) --Cau 28: Cách khác select HONV,TENLOT,TENNV from NHANVIEN where (select COUNT(*) from THANNHAN where NHANVIEN.MANV=THANNHAN.MA_NVIEN )=0 ThS. Lương Thị Ngọc Khánh – K. CNTT – Đại học Tôn Đức Thắng 7 --Cau 28: Cách khác không có kq nếu không dùng left outer join select HONV,TENLOT,TENNV from NHANVIEN, THANNHAN where NHANVIEN.MANV=THANNHAN.MA_NVIEN group by HONV,TENLOT,TENNV having COUNT(TENTN)=0 --Cau 28: Cách khác => kq khi có dùng left outer join select HONV,TENLOT,TENNV, count(TENTN) as [so luong than nhan] from NHANVIEN left outer join THANNHAN on NHANVIEN.MANV=THANNHAN.MA_NVIEN group by MANV,HONV,TENLOT,TENNV having count (TENTN)=0 --Câu 29: Danh sách những trưởng phòng có tối thiểu một thân nhân (có ít nhất một thân nhân, có từ một thân nhân trở lên). select HONV,TENLOT,TENNV from NHANVIEN where MANV IN (select TRPHG from PHONGBAN where NHANVIEN.MANV=PHONGBAN.TRPHG) AND MANV IN (select MANV from THANNHAN where NHANVIEN.MANV=THANNHAN.MA_NVIEN) --Cau 29: Cách khác select HONV,TENLOT,TENNV from NHANVIEN where exists (select * --TRPHG ThS. Lương Thị Ngọc Khánh – K. CNTT – Đại học Tôn Đức Thắng 8 from PHONGBAN where NHANVIEN.MANV=PHONGBAN.TRPHG) and exists (select * --MANV from THANNHAN where NHANVIEN.MANV=THANNHAN.MA_NVIEN) --Cau 29: Cách khác select HONV, TENLOT, TENNV from PHONGBAN,NHANVIEN where PHONGBAN.TRPHG=NHANVIEN.MANV and exists (select MA_NVIEN from THANNHAN where PHONGBAN.TRPHG=THANNHAN.MA_NVIEN ) --Câu 30: Tìm họ tên của những trưởng phòng không có thân nhân nào. select HONV, TENLOT, TENNV from NHANVIEN where MANV IN (select TRPHG from PHONGBAN where PHONGBAN.TRPHG=NHANVIEN.MANV) AND MANV NOT IN (select MANV from THANNHAN where NHANVIEN.MANV=THANNHAN.MA_NVIEN) --Cau 30: Cách khác select HONV, TENLOT, TENNV from NHANVIEN where exists (select TRPHG from PHONGBAN where PHONGBAN.TRPHG=NHANVIEN.MANV) and not exists (select MANV ThS. Lương Thị Ngọc Khánh – K. CNTT – Đại học Tôn Đức Thắng 9 from THANNHAN where NHANVIEN.MANV=THANNHAN.MA_NVIEN) --Cau 30: Cách khác select HONV, TENLOT, TENNV from PHONGBAN,NHANVIEN where PHONGBAN.TRPHG=NHANVIEN.MANV and not exists (select MA_NVIEN from THANNHAN where THANNHAN.MA_NVIEN=PHONGBAN.TRPHG ) -------------------------------------------------------------------------------------TÀI LIỆU THAM KHẢO 1. Nguyễn Gia Tuấn Anh, Trương Châu Long, Bài tập Cơ sở dữ liệu 2. Slide bài giảng Cơ sở dữ liệu, Khoa Công nghệ thông tin - Đại học 3. Khoa học Tự Nhiên R. Elmasri & S.B. Navathe (2010), Fundamentals of Database Systems, 6th Edition, Addison-Wesley ThS. Lương Thị Ngọc Khánh – K. CNTT – Đại học Tôn Đức Thắng 10 |
