Anh Hoàng

42 bundles
1 file5 months ago
2

-- Bài tập

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241
-- Bài tập -- 1. Cho ds nhân viên gồm họ tên, phái. SELECT HONV, TENLOT, TENNV, PHAI FROM NHANVIEN -- 2. Cho ds nhân viên thuộc phòng số 5 SELECT * FROM NHANVIEN WHERE PHG = '5' -- 3. Cho ds nhân viên gồm mã nv, họ tên, phái của các nv thuộc phòng số 5 SELECT MANV, HONV, TENLOT, TENNV, PHAI FROM NHANVIEN WHERE PHG = '5' --4. Danh sach họ tên phái của các nv thuộc phòng ‘nghiên cứu’. SELECT MANV, HONV, TENLOT, TENNV, PHAI FROM NHANVIEN, PHONGBAN WHERE TENPHG = N'Nghiên cứu' AND MAPHG = PHG --5. Cho ds các mã nhân viên có tham gia đề án số 4 hoặc 5. SELECT MANV FROM NHANVIEN, PHANCONG WHERE MADA='4' OR MADA='5' AND MA_NVIEN = MANV --6. Cho ds các mã nhân viên vừa có tham gia đề án số 4 vừa có tham gia đề án số 5. SELECT MANV FROM NHANVIEN, PHANCONG WHERE MADA = '4' INTERSECT SELECT MANV FROM NHANVIEN, PHANCONG WHERE MADA = '5' --7. Cho ds các mã nhân viên có tham gia đề án số 4 mà không có tham gia đề án số 5 SELECT MANV FROM NHANVIEN, PHANCONG WHERE MADA = '4' INTERSECT SELECT MANV FROM NHANVIEN, PHANCONG WHERE MADA != '5' --8. Cho biết danh sách thể hiện mọi nhân viên đều tham gia tất cả các đề án SELECT NHANVIEN.* FROM NHANVIEN, PHANCONG, DEAN WHERE NHANVIEN.MANV = PHANCONG.MA_NVIEN AND PHANCONG.MADA = DEAN.MADA --9. Cho ds các nhân viên và thông tin phòng ban mà nhân viên đó trực thuộc (mã nv, họ tên, mã phòng, tên phòng). SELECT MANV, HONV, TENLOT, TENNV, MAPHG, TENPHG FROM NHANVIEN, PHONGBAN WHERE TRPHG = MANV --10. Cho ds các phòng ban và địa điểm phòng ban (mã pb, tên pb, địa điểm) SELECT PHONGBAN.MAPHG,TENPHG, DIADIEM FROM PHONGBAN, DIADIEM_PHG WHERE PHONGBAN.MAPHG = DIADIEM_PHG.MAPHG --11. Cho danh sách các nhân viên thuộc phòng ‘Nghiên cứu’. SELECT NHANVIEN.* FROM NHANVIEN, PHONGBAN WHERE PHONGBAN.TENPHG=N'Nghiên cứu' AND PHONGBAN.MAPHG = NHANVIEN.PHG --12. Đối với từng nv, cho biết họ tên ngày sinh và tên của nv phụ trách trực tiếp nhân viên đó. SELECT NV.HONV, NV.TENLOT, NV.TENNV, NV.NGSINH, NQL.HONV AS HO_NQL, NQL.TENLOT AS TENLOT_NQL, NQL.TENNV AS TEN_NQL FROM NHANVIEN NV, NHANVIEN NQL WHERE NV.MA_NQL = NQL.MANV --13. Ds nv thuộc phòng 5 có tham gia đề án tên là ‘Sản phẩm X' SELECT NHANVIEN.* FROM NHANVIEN, PHANCONG, DEAN WHERE PHG = '5' AND TENDA = N'San pham X' AND NHANVIEN.MANV = PHANCONG.MA_NVIEN AND PHANCONG.MADA = DEAN.MADA --14. Tương tự 5, thuộc phòng ‘nghiên cứu’ có tham gia đề án tên làACN ‘Sản phẩm X’. SELECT MANV FROM NHANVIEN, PHANCONG, PHONGBAN, DEAN WHERE PHANCONG.MADA = '4' AND TENPHG=N'Nghiên cứu' AND TENDA='San pham X' AND NHANVIEN.MANV = PHANCONG.MA_NVIEN AND PHANCONG.MADA = DEAN.MADA AND DEAN.PHONG = PHONGBAN.MAPHG INTERSECT SELECT MANV FROM NHANVIEN, PHANCONG, PHONGBAN, DEAN WHERE PHANCONG.MADA = '5' AND TENPHG=N'Nghiên cứu' AND TENDA='San pham X' AND NHANVIEN.MANV = PHANCONG.MA_NVIEN AND PHANCONG.MADA = DEAN.MADA AND DEAN.PHONG = PHONGBAN.MAPHG -- 15. GÁN: Cho biết có tất cả bao nhiêu nhân viên SELECT COUNT(MANV) AS SONHANVIEN FROM NHANVIEN -- 16. Cho biết mỗi phòng ban có bao nhiêu nhân viên (MAPB, TENPB, SLNV). SELECT MAPHG, TENPHG, COUNT(PHG) AS SLNV FROM PHONGBAN PB INNER JOIN NHANVIEN NV ON PB.MAPHG = NV.PHG GROUP BY MAPHG, TENPHG -- 17. Cho biết tổng lương, số lượng nv, lương trung bình, lương bé nhất trong toàn công ty SELECT SUM(LUONG) AS TONGLUONG, COUNT(MANV) AS SLNV, AVG(LUONG) AS LUONGTB, MIN(LUONG) AS LUONGBENHAT FROM NHANVIEN -- 18. Ds nhân viên có tham gia đề án SELECT * FROM NHANVIEN NV WHERE EXISTS ( SELECT * FROM PHANCONG PC WHERE PC.MA_NVIEN = NV.MANV ) -- 19. Ds nhân viên không có tham gia đề án nào. SELECT * FROM NHANVIEN NV WHERE NOT EXISTS( SELECT * FROM PHANCONG PC WHERE PC.MA_NVIEN = NV.MANV ) -- 20. Mỗi nv tham gia bao nhiêu đề án với tổng thời gian là bao nhiêu SELECT MANV, COUNT(MADA) AS SLDA, THOIGIAN FROM NHANVIEN NV INNER JOIN PHANCONG PC ON NV.MANV = PC.MA_NVIEN GROUP BY MANV, THOIGIAN -- 21. Ds nv có tham gia đề án tên là ‘Sản phẩm X ’ hoặc ‘Sản phẩm Y’. SELECT DISTINCT MANV, HONV, TENLOT, TENNV, NGSINH, DCHI, PHAI, LUONG, MA_NQL FROM NHANVIEN NV, DEAN DA, PHANCONG PC WHERE DA.MADA = PC.MADA AND NV.MANV = PC.MA_NVIEN AND TENDA = 'San pham X' UNION SELECT DISTINCT MANV, HONV, TENLOT, TENNV, NGSINH, DCHI, PHAI, LUONG, MA_NQL FROM NHANVIEN NV, DEAN DA, PHANCONG PC WHERE DA.MADA = PC.MADA AND NV.MANV = PC.MA_NVIEN AND TENDA = 'San pham Y' -- 22. Ds nv vừa có tham gia đề án tên ‘Sản phẩm X’ vừa có tham gia đề án ‘Sản phẩm Y’ SELECT DISTINCT MANV, HONV, TENLOT, TENNV, NGSINH, DCHI, PHAI, LUONG, MA_NQL FROM NHANVIEN NV, DEAN DA, PHANCONG PC WHERE DA.MADA = PC.MADA AND NV.MANV = PC.MA_NVIEN AND TENDA = 'San pham X' INTERSECT SELECT DISTINCT MANV, HONV, TENLOT, TENNV, NGSINH, DCHI, PHAI, LUONG, MA_NQL FROM NHANVIEN NV, DEAN DA, PHANCONG PC WHERE DA.MADA = PC.MADA AND NV.MANV = PC.MA_NVIEN AND TENDA = 'San pham Y' -- 23. Ds nv có tham gia đề án tên ‘Sản phẩm X’ mà không có tham gia đề án tên là ‘Sản phẩm Y’. SELECT DISTINCT MANV, HONV, TENLOT, TENNV, NGSINH, DCHI, PHAI, LUONG, MA_NQL FROM NHANVIEN NV, DEAN DA, PHANCONG PC WHERE DA.MADA = PC.MADA AND NV.MANV = PC.MA_NVIEN AND TENDA = 'San pham X' EXCEPT SELECT DISTINCT MANV, HONV, TENLOT, TENNV, NGSINH, DCHI, PHAI, LUONG, MA_NQL FROM NHANVIEN NV, DEAN DA, PHANCONG PC WHERE DA.MADA = PC.MADA AND NV.MANV = PC.MA_NVIEN AND TENDA = 'San pham Y' -- 24. Ds nv chỉ có tham gia đề án tên ‘Sản phẩm X’ SELECT DISTINCT MANV, HONV, TENLOT, TENNV, NGSINH, DCHI, PHAI, LUONG, MA_NQL FROM NHANVIEN NV, DEAN DA, PHANCONG PC WHERE DA.MADA = PC.MADA AND NV.MANV = PC.MA_NVIEN AND TENDA = 'San pham Z' AND NOT EXISTS (SELECT DISTINCT MANV FROM NHANVIEN NV, DEAN DA, PHANCONG PC WHERE DA.MADA = PC.MADA AND NV.MANV = PC.MA_NVIEN AND TENDA > 'San pham Z') -- 25. Ds các đề án chỉ do các nv thuộc phòng “Nghiên cứu” thực hiện SELECT TENDA FROM DEAN DA JOIN PHONGBAN PB ON DA.PHONG = PB.MAPHG WHERE TENPHG = N'Nghiên cứu' AND NOT EXISTS (SELECT TENDA, TENPHG FROM DEAN DA JOIN PHONGBAN PB ON DA.PHONG = PB.MAPHG WHERE TENPHG != N'Nghiên cứu') -- 26. Ds các nv có tham gia tất cả các đề án. SELECT DISTINCT MANV FROM NHANVIEN NV JOIN PHANCONG PC ON NV.MANV = PC.MA_NVIEN WHERE NOT EXISTS (SELECT MANV FROM NHANVIEN EXCEPT SELECT MA_NVIEN FROM PHANCONG PC2 WHERE PC.MA_NVIEN = PC2.MA_NVIEN ) -- 27. Ds nv thuộc phòng ‘Nghiên cứu’ có tham gia tất cả các đề án do phòng số 5 chủ trì. SELECT NV.* FROM NHANVIEN NV INNER JOIN PHONGBAN PB ON NV.PHG = PB.MAPHG WHERE TENPHG = N'Nghiên cứu' AND TENPHG = '5' AND EXISTS (SELECT * FROM PHANCONG PC WHERE PC.MA_NVIEN = NV.MANV ) -- 28. Cho biết lương trung bình của các phòng ban (mã, tên, lương TB). SELECT MAPHG, TENPHG, AVG(LUONG) AS LUONG_TB FROM NHANVIEN NV INNER JOIN PHONGBAN PB ON NV.PHG = PB.MAPHG GROUP BY MAPHG, TENPHG -- 29. Cho biết các phòng ban có lương trung bình > 2500. SELECT TENPHG FROM NHANVIEN NV INNER JOIN PHONGBAN PB ON NV.PHG = PB.MAPHG GROUP BY TENPHG HAVING AVG(LUONG) > 2500 -- 30. Cho biết các phòng ban có chủ trì đề án có số nhân viên > 3 và có lương trung bình lớn hơn 2500. SELECT TENPHG FROM PHONGBAN PB, NHANVIEN NV, DEAN DA WHERE NV.PHG = PB.MAPHG AND DA.PHONG = PB.MAPHG GROUP BY TENPHG HAVING AVG(LUONG) > 2500 AND COUNT(MANV) > 3 -- 31. Cho biết nhân viên nào có lương cao nhất trong từng phòng ban. SELECT MAPHG, MAX(LUONG) AS LUONGCAONHAT FROM PHONGBAN PB JOIN NHANVIEN NV ON PB.MAPHG = NV.PHG GROUP BY MAPHG -- 32. Cho biết phòng ban nào có lương trung bình cao nhất, F SELECT MAPHG FROM NHANVIEN NV JOIN PHONGBAN PB ON NV.PHG = PB.MAPHG WHERE NOT EXISTS (SELECT * FROM NHANVIEN NV2 JOIN PHONGBAN PB2 ON NV2.PHG = PB2.MAPHG WHERE AVG(NV2.LUONG) > AVG(NV.LUONG) ) GROUP BY MAPHG -- 33. Cho biết phòng ban nào có ít nhân viên nhất. SELECT MAPHG FROM PHONGBAN PB JOIN NHANVIEN NV ON PB.MAPHG = NV.PHG WHERE COUNT(MANV) <= ALL (SELECT COUNT(MANV) FROM NHANVIEN) -- 34. Cho biết phòng ban nào có đông nhân viên nữ nhất SELECT MAPHG FROM PHONGBAN PB JOIN NHANVIEN NV ON PB.MAPHG = NV.PHG WHERE PHAI = N'Nữ' AND COUNT(MANV) <= ALL (SELECT COUNT(MANV) FROM NHANVIEN WHERE PHAI = N'Nữ') -- 35. Danh sách mã, tên của các phòng ban có chủ trì đề án tên là “SPX” lẫn “SPY”. SELECT MAPHG, TENPHG FROM PHONGBAN PB JOIN DEAN DA ON PB.MAPHG = DA.PHONG WHERE TENDA = N'San pham X' INTERSECT SELECT MAPHG, TENPHG FROM PHONGBAN PB JOIN DEAN DA ON PB.MAPHG = DA.PHONG WHERE TENDA = N'San pham Y' -- 36. Danh sách mã, tên của các phòng ban có chủ trì đề án tên là “SPX” mà không có chủ trì đề án tên là “SPY”. SELECT MAPHG, TENPHG FROM PHONGBAN PB JOIN DEAN DA ON PB.MAPHG = DA.PHONG WHERE TENDA = N'San pham X' AND NOT EXISTS (SELECT MAPHG, TENPHG FROM PHONGBAN PB JOIN DEAN DA ON PB.MAPHG = DA.PHONG WHERE TENDA = N'San pham Y') -- 37. Phân công cho các nhân viên thuộc phòng số 5 tham gia đề án số 10 mỗi người tham gia 10 giờ. UPDATE PHANCONG SET THOIGIAN = THOIGIAN + 10.0 WHERE MA_NVIEN = (SELECT PHG FROM NHANVIEN NV WHERE PHG = '5') AND MADA = '10' -- 38. Xóa tất cả những phân công liên quan đến nhân viên mã là 10. DELETE FROM PHANCONG WHERE MA_NVIEN = '10' -- 39. Xóa tất cả những phân công liên quan đến nhân viên mã là 10 và đề án mã là 20. DELETE FROM PHANCONG WHERE MA_NVIEN = '10' AND MADA = '20' -- 40. Tăng 10% giờ tham gia đề án của nhân viên đã tham gia đề án số 10. UPDATE PHANCONG SET THOIGIAN = THOIGIAN*1.1 WHERE MADA = '10' -- 41. Giảm 15% giờ tham gia đề án của các nhân viên thuộc phòng “Nghiên cứu ”đã tham gia đề án số 10. UPDATE PHANCONG SET THOIGIAN = THOIGIAN*0.85 WHERE MA_NVIEN = (SELECT MANV FROM NHANVIEN NV JOIN PHONGBAN PB ON NV.PHAI = PB.MAPHG WHERE TENPHG = N'Nghiên cứu' ) -- 42. Cho biết mỗi phòng ban định vị ở bao nhiêu nơi. SELECT MAPHG, COUNT(DIADIEM) AS SONOI FROM DIADIEM_PHG GROUP BY MAPHG -- 43. Cho biết những phòng ban định vị ở nhiều nơi. SELECT MAPHG FROM DIADIEM_PHG GROUP BY MAPHG HAVING COUNT(DIADIEM) > 1 -- 44. Danh sách các nhân viên đã tham gia nhiều hơn 3 đề án. SELECT MA_NVIEN FROM PHANCONG PC JOIN DEAN DA ON PC.MADA = DA.MADA GROUP BY MA_NVIEN HAVING COUNT(MA_NVIEN) > 3 -- 45. Cho biết các đề án có nhiều hơn 10 nhân viên tham gia SELECT PC.MADA FROM PHANCONG PC JOIN DEAN DA ON PC.MADA = DA.MADA GROUP BY PC.MADA HAVING COUNT(PC.MADA) > 10