BAI 4: QUAN LY DU AN.

--- BÀI TẬP QUẢN LÝ DỰ ÁN

IF DB_ID('QLDA') IS NOT NULL

DROP DATABASE QLDA

CREATE DATABASE QLDA

USE QLDA

--- TAO BANG PHONG BAN

IF OBJECT_ID('PHONGBAN') IS NOT NULL

DROP TABLE PHONGBAN

CREATE TABLE PHONGBAN

(

MAPB VARCHAR(10) NOT NULL PRIMARY KEY,

TENPB NVARCHAR(30) NOT NULL

)

--- TAO BANG NHAN VIEN

IF OBJECT_ID('NHANVIEN') IS NOT NULL

DROP TABLE NHANVIEN

CREATE TABLE NHANVIEN

(

MANV VARCHAR(10) NOT NULL PRIMARY KEY,

TENNV NVARCHAR(30) NOT NULL,

DCNV NVARCHAR(30) NOT NULL,

MAPB VARCHAR(10) NOT NULL

)

--- TAO BANG DU AN

IF OBJECT_ID('DUAN') IS NOT NULL

DROP TABLE DUAN

CREATE TABLE DUAN

(

MADA VARCHAR(10) NOT NULL PRIMARY KEY,

TENDA NVARCHAR(30) NOT NULL,

TGDA SMALLDATETIME,

MAPB VARCHAR(10) NOT NULL

)

--- TAO BANG PHAN CONG

IF OBJECT_ID('PHANCONG') IS NOT NULL

DROP TABLE PHANCONG

CREATE TABLE PHANCONG

(

MADA VARCHAR(10) NOT NULL,

MANV VARCHAR(10) NOT NULL,

PRIMARY KEY(MADA,MANV)

)

------ NHAP LIEU

--- NHAP BANG PHONG BAN

INSERT INTO PHONGBAN VALUES('P01', N'Phòng 1')

INSERT INTO PHONGBAN VALUES('P02', N'Phòng 2')

INSERT INTO PHONGBAN VALUES('P03', N'Phòng 3')

SELECT * FROM PHONGBAN

--- NHAP BANG NHAN VIEN

INSERT INTO NHANVIEN VALUES('NV01', N'NGUYỄN VĂN A', N'QUẬN 3', 'P01')

INSERT INTO NHANVIEN VALUES('NV02', N'NGUYỄN VĂN B', N'QUẬN 3', 'P01')

INSERT INTO NHANVIEN VALUES('NV03', N'NGUYỄN VĂN C', N'QUẬN 6', 'P02')

INSERT INTO NHANVIEN VALUES('NV04', N'NGUYỄN VĂN D', N'QUẬN 1', 'P02')

INSERT INTO NHANVIEN VALUES('NV05', N'NGUYỄN VĂN E', N'QUẬN 2', 'P03')

SELECT * FROM NHANVIEN

--- NHAP BANG DU AN

INSERT INTO DUAN VALUES('D01', N'HẦM NGẦM THỦ THIÊM', '5/22/2010', 'P01')

INSERT INTO DUAN VALUES('D02', N'THỦY ĐIỆN DĂKMIL', '6/1/2010', 'P02')

INSERT INTO DUAN VALUES('D03', N'PHÁ RỪNG CAO BẰNG', '5/1/2010', 'P03')

INSERT INTO DUAN VALUES('D04', N'VẬN CHUYỂN HÊRÔIN', '5/1/2010', 'P03')

SELECT * FROM DUAN

--- NHAP BANG PHAN CONG

INSERT INTO PHANCONG VALUES('D01', 'NV01')

INSERT INTO PHANCONG VALUES('D02', 'NV03')

INSERT INTO PHANCONG VALUES('D02', 'NV04')

INSERT INTO PHANCONG VALUES('D03', 'NV05')

SELECT * FROM PHANCONG

--- CAU 1

SELECT MANV AS MÃ_NHÂN_VIÊN, TENNV AS TÊN_NHÂN_VIÊN

FROM NHANVIEN

WHERE MAPB = 'P01'

--- CAU 2

SELECT A.MANV AS MÃ_NHÂN_VIÊN, TENNV AS TÊN_NHÂN_VIÊN

FROM NHANVIEN A, PHANCONG B

WHERE A.MANV = B.MANV AND MADA = 'D01'

--- CAU 3

SELECT MADA AS MÃ_DỰ_ÁN_CÓ_THỂ_ĐƯỢC_PC

FROM NHANVIEN A, DUAN B

WHERE A.MAPB = B.MAPB AND MANV = 'NV01'

--- CAU 4

SELECT MANV AS MÃ_NHÂN_VIÊN, TENNV AS TÊN_NHÂN_VIÊN

FROM NHANVIEN A, DUAN B

WHERE A.MAPB = B.MAPB AND MADA = 'D01' AND MANV NOT IN (SELECT MANV

FROM PHANCONG

WHERE MADA = 'D01')

--- CAU 6

SELECT MADA AS MÃ_DỰ_ÁN, COUNT(*) AS SỐ_NHÂN_VIÊN

FROM PHANCONG

GROUP BY MADA

--- CAU 7

SELECT MADA AS MÃ_DỰ_ÁN, COUNT(*) AS SỐ_NHÂN_VIÊN

FROM PHANCONG

GROUP BY MADA

HAVING COUNT(*) >= ALL (SELECT COUNT(*)

FROM PHANCONG

GROUP BY MADA

)

--- CÂU 8

SELECT MAPB AS PHÒNG_CÓ_NHIỀU_DỰ_ÁN_NHẤT, COUNT(*) AS SỐ_LƯỢNG

FROM DUAN

GROUP BY MAPB

HAVING COUNT(*) >= ALL (SELECT COUNT(*)

FROM DUAN

GROUP BY MAPB)