Giải bài tập CSDL Bài toán nhân viên văn phòng (Đề CH DHQGHCM)

Giải bài toán CSDL Quản lý nhân viên văn phòng

USE MASTER

GO

DROP DATABASE CH_HT_NHAN_VIEN

GO

CREATE DATABASE CH_HT_NHAN_VIEN

GO

USE CH_HT_NHAN_VIEN

GO

CREATE TABLE CHUCVU

(

       MACHUCVU NVARCHAR(100) PRIMARY KEY,

       TENCHUCVU NVARCHAR(500),

)

GO

CREATE TABLE NHANVIEN

(

       MANHANVIEN NVARCHAR(20) PRIMARY KEY,

       HOLOTNHANVIEN NVARCHAR(100) ,

       TENNHANVIEN NVARCHAR(100) ,

       DIACHI NVARCHAR(100)

)

GO

CREATE TABLE CONGVIEC

(

       SOQUYETDINH INT PRIMARY KEY,

       THANGBATDAU INT,

       NAMBATDAU INT,

       THANGKETTHUC INT,

       NAMKETTHUC INT,

       MANHANVIEN NVARCHAR(20),

       MACHUCVU NVARCHAR(100) 

)

GO

ALTER TABLE CONGVIEC

ADD CONSTRAINT FK_CV1 FOREIGN KEY (MANHANVIEN) REFERENCES NHANVIEN(MANHANVIEN)

GO

ALTER TABLE CONGVIEC

ADD CONSTRAINT FK_CV2 FOREIGN KEY (MACHUCVU) REFERENCES CHUCVU(MACHUCVU)

GO

CREATE TABLE HESOLUONG

(

       MACHUCVU NVARCHAR(100) ,

       SONAMLAMVIEC INT,

       HESO FLOAT

       CONSTRAINT  FP_NHANTIN PRIMARY KEY (MACHUCVU,SONAMLAMVIEC)

)

GO

ALTER TABLE HESOLUONG

ADD CONSTRAINT FK_HSL FOREIGN KEY (MACHUCVU) REFERENCES CHUCVU(MACHUCVU)

GO

------------------------------------INSERT INTO TABLE---------------------------------------------

INSERT INTO CHUCVU VALUES('GD',N'GIÁM ĐỐC')

GO

INSERT INTO CHUCVU VALUES('PGD',N'PHÓ GIÁM ĐỐC')

GO

INSERT INTO CHUCVU VALUES('TP',N'Trưởng phòng')

GO

INSERT INTO CHUCVU VALUES('NV',N'Nhân Viên')

GO

INSERT INTO CHUCVU VALUES('CT',N'Chủ tịch Công Đoàn ')

GO

-----

INSERT INTO NHANVIEN VALUES('NV01',N'Pham Thanh',N'Châu',N' Gò dầu Tây Ninh')

GO

INSERT INTO NHANVIEN VALUES('NV02',N'Nguyễn Thanh',N'An',N' Châu Thành Tiền Giang')

GO

INSERT INTO NHANVIEN VALUES('NV03',N'Lê Khánh',N'Nam',N'Cai Lậy Tiền Giang')

GO

INSERT INTO NHANVIEN VALUES('NV04',N'Lý Ngọc',N'Khanh',N'Cái Bè Tiền Giang')

GO

INSERT INTO NHANVIEN VALUES('NV05',N'Lý Án',N'My',N'An Cư Cái Bè Tiền Giang')

GO

-----

INSERT INTO CONGVIEC VALUES(1,2,2010,2,2015,'NV01','GD')

GO

INSERT INTO CONGVIEC VALUES(6,3,2011,7,2016,'NV02','CT')

GO

INSERT INTO CONGVIEC VALUES(2,3,2011,7,2016,'NV02','PGD')

GO

INSERT INTO CONGVIEC VALUES(3,9,2012,8,2017,'NV03','NV')

GO

INSERT INTO CONGVIEC VALUES(4,10,2013,6,2018,'NV04','TP')

GO

INSERT INTO CONGVIEC VALUES(5,12,2014,12,2019,'NV05','TP')

GO

INSERT INTO CONGVIEC VALUES(7,3,2011,7,2016,'NV02','GD')

GO

INSERT INTO CONGVIEC VALUES(8,3,2011,7,2016,'NV02','TP')

GO

INSERT INTO CONGVIEC VALUES(9,3,2011,7,2016,'NV02','NV')

GO

-----

INSERT INTO HESOLUONG VALUES('GD',20,9.3)

GO

INSERT INTO HESOLUONG VALUES('PGD',25,9.6)

GO

INSERT INTO HESOLUONG VALUES('TP',22,9.0)

GO

INSERT INTO HESOLUONG VALUES('NV',20,8.0)

GO

INSERT INTO HESOLUONG VALUES('GD',2,9.0)

GO

--A/ Liệt kê danh sách gồm mã nhân viên, tên nhân viên làm hơn 2 chức vụ

SELECT CV.MANHANVIEN,NV.HOLOTNHANVIEN+' '+TENNHANVIEN AS HOVATEN

FROM  CONGVIEC CV INNER JOIN NHANVIEN NV

ON CV.MANHANVIEN=NV.MANHANVIEN

GROUP BY CV.MANHANVIEN,NV.HOLOTNHANVIEN+' '+TENNHANVIEN

HAVING COUNT(CV.MANHANVIEN)>=2

--B/ Liệt kê mã nhân viên, họ tên nhân viên mà được làm hết tất cả chức vụ

--

SELECT CV.MANHANVIEN,NV.HOLOTNHANVIEN+' '+TENNHANVIEN AS HOVATEN

FROM  CONGVIEC CV INNER JOIN NHANVIEN NV

ON CV.MANHANVIEN=NV.MANHANVIEN

GROUP BY CV.MANHANVIEN,NV.HOLOTNHANVIEN+' '+TENNHANVIEN

HAVING COUNT(CV.MACHUCVU)>=ALL(SELECT COUNT(*) FROM CHUCVU)

--C/Liệt kê Mã nhân viên, Tên nhân viên có chức vụ là trưởng phòng hay phó phòng

--trong thời gian hơn 2 năm

SELECT NV.MANHANVIEN, NV.TENNHANVIEN

FROM NHANVIEN NV INNER JOIN CONGVIEC CV ON NV.MANHANVIEN=CV.MANHANVIEN

WHERE CV.MACHUCVU IN('TP','NV')

AND (NAMKETTHUC-NAMBATDAU)>2