CREATE DATABASE QUANLYNHANVIEN

USE QUANLYNHANVIEN

CREATE TABLE PHONG(

MAP CHAR(10) PRIMARY KEY,

TENP CHAR(30) NOT NULL,

DIACHIP CHAR(30),

SDT CHAR(15)

CREATE TABLE NHANVIEN(

MANV CHAR(10) PRIMARY KEY,

TENNV CHAR(40) NOT NULL,

DIACHI CHAR(40),

MAP CHAR(10),

LUONG INT,    --LUONG CUA NHAN VIEN--

CONSTRAINT KN_NHANVIEN FOREIGN KEY(MAP) REFERENCES PHONG(MAP)

CREATE TABLE NGOAINGU(

MANN CHAR(10) PRIMARY KEY,

TENN CHAR(30) NOT NULL

CREATE TABLE TDNN(        --TRINH DO NGOAI NGU--

MANV CHAR(10),

MANN CHAR(10),

TRINHDO CHAR(30) NOT NULL,

CONSTRAINT KC_TDNN PRIMARY KEY(MANV,MANN),

CONSTRAINT KN_TDNN FOREIGN KEY(MANV) REFERENCES NHANVIEN(MANV),

CONSTRAINT KN1_TDNN FOREIGN KEY(MANN) REFERENCES NGOAINGU(MANN),

--2.TAO VIEW DE TONG HOP THONG TIN VE MOI PHONG HIEN TAI CO BAO NHIEU NHAN VIEN--

CREATE VIEW VD2

AS

SELECT PHONG.MAP,TENP,DIACHIP,SDT,COUNT(NHANVIEN.MAP) AS SONV

FROM PHONG,NHANVIEN

WHERE PHONG.MAP=NHANVIEN.MAP

GROUP BY phong.MAP,TENP,DIACHIP,SDT

--3/ Tạo View để tổng hợp thông tin về các nhân viên chưa tích luỹ được trình độ ngoại ngữ nào--

CREATE VIEW VD3

AS

SELECT * FROM NHANVIEN

WHERE MANV NOT IN (SELECT MANV FROM TDNN)

--4/ Tạo View để tổng hợp thông tin về các nhân viên có lương cao nhất trong phòng họ làm việc.--

CREATE VIEW TG

AS

SELECT MAP,MAX(LUONG) AS LUONGCAONHAT

FROM NHANVIEN

GROUP BY MAP

CREATE VIEW LUONGMAX

AS

SELECT NHANVIEN.*

FROM NHANVIEN,TG

WHERE NHANVIEN.MAP=TG.MAP AND LUONG=LUONGCAONHAT

/*5/ Tạo thủ tục có tham số vào @TenNN và @TrinhDo để đưa ra danh các nhân viên biết ngoại ngữ

và trình độ trên. */

CREATE PROC VD5

@TENN CHAR(30),@TRINHDO CHAR(30)

AS

SELECT * FROM NHANVIEN

WHERE MANV IN (SELECT MANV FROM TDNN

                WHERE TRINHDO=@TRINHDO AND MANN IN (SELECT MANN FROM NGOAINGU

                                                        WHERE TENN=@TENN))

EXEC VD5 'TIENG ANH','A'

--6/ Tạo thủ tục có tham số vào @TenP để đưa ra tổng số nhân viên hiện có trong phòng này--

ALTER PROC VD6

@TENP CHAR(20)

AS

SELECT  PHONG.MAP,COUNT(NHANVIEN.MAP) AS SONV

FROM PHONG,NHANVIEN

WHERE PHONG.MAP=NHANVIEN.MAP AND TENP=@TENP

GROUP BY PHONG.MAP

EXEC VD6 'ABC'

/*7/ Tạo thủ tục có tham số vào là @TenNN, @TenP và @TrinhDo để đưa ra danh các nhân viên

 ở phòng này biết ngoại ngữ và trình độ ứng với các tham biến đã cho */

CREATE PROC VD7

@TENNN CHAR(30),@TENP CHAR(20),@TRINHDO CHAR(30)

AS

SELECT NHANVIEN.* FROM NHANVIEN,PHONG,TDNN,NGOAINGU

WHERE NHANVIEN.MANV=TDNN.MANV AND PHONG.MAP=NHANVIEN.MAP

     AND NGOAINGU.MANN=TDNN.MANN AND TENN=@TENNN AND TENP=@TENP AND TRINHDO=@TRINHDO

EXEC VD7 'TIENG ANH','ABC','A'

/*8/ Tạo thủ tục có tham số vào @TenP để đưa ra danh các nhân viên có lương cao nhất của phòng này*/

CREATE PROC VD8

@TENP CHAR(20)

AS

SELECT NHANVIEN.*

FROM NHANVIEN,TG,PHONG

WHERE NHANVIEN.MAP=TG.MAP  AND TG.MAP=PHONG.MAP AND LUONG=LUONGCAONHAT AND TENP=@TENP

EXEC VD8 'ABC'

/*9/ Tạo Trigger để kiểm tra khi nhập dữ liệu vào bảng NhanVien phải đảm bảo lương của nhân viên

phải >0 */

CREATE TRIGGER tg9

ON NHANVIEN

FOR INSERT

AS

IF EXISTS (SELECT LUONG FROM NHANVIEN

            WHERE LUONG<0)

BEGIN

    PRINT 'DU LIEU NHAP KHONG HOP LE'

    ROLLBACK TRAN

END

ELSE PRINT 'DU LIEU NHAP THANH CONG'

INSERT INTO NHANVIEN

VALUES('NV6','HAI','HA NOI','P04','7000')

--10/ Dùng kiểu dữ liệu CURSOR để tăng lương cho mỗi nhân viên lên 10%.--

--KHAI BAO--

DECLARE CS10 CURSOR FOR

SELECT MANV,TENNV,DIACHI,MAP,(LUONG*1.1) AS LUONGMOI

FROM NHANVIEN

GROUP BY MANV,TENNV,DIACHI,MAP,LUONG

--MO--

OPEN CS10

--XU LY--

PRINT 'DANH SACH NHAN VIEN SAU KHI TANG LUONG LEN 10% LA:'

PRINT 'MANV        TENNV            DIACHI              MAP        LUONGMOI'

DECLARE @MANV CHAR(10),@TENNV CHAR(20),@DIACHI CHAR(20),@MAP CHAR(10),@LUONG INT

FETCH NEXT FROM CS10

INTO @MANV,@TENNV,@DIACHI,@MAP,@LUONG

WHILE @@FETCH_STATUS=0

BEGIN

    PRINT @MANV+@TENNV+@DIACHI+@MAP+CONVERT(CHAR(10),@LUONG)

    FETCH NEXT FROM CS10

    INTO @MANV,@TENNV,@DIACHI,@MAP,@LUONG

END

--DONG--

CLOSE CS10

--HUY--

DEALLOCATE CS10

--11/ Dùng kiểu dữ liệu CURSOR để đưa ra danh sách các nhân viên có ngoại ngữ tiếng anh trình độ C--

--KHAI BAO--

DECLARE CS11 CURSOR FOR

SELECT NHANVIEN.MANV,TENNV,DIACHI,MAP,LUONG

FROM NHANVIEN,TDNN,NGOAINGU

WHERE NHANVIEN.MANV=TDNN.MANV AND TDNN.MANN=NGOAINGU.MANN

        AND TENN='TIENG ANH' AND TRINHDO='C'

--MO--

OPEN CS11

--XU LY--

PRINT 'DANH SACH NHAN VIEN CO NGAOI NGU TIENG ANH TRINH DO C LA:'

PRINT 'MANV        TENNV            DIACHI              MAP        LUONG'

DECLARE @MANV CHAR(10),@TENNV CHAR(20),@DIACHI CHAR(20),@MAP CHAR(10),@LUONG INT

FETCH NEXT FROM CS11

INTO @MANV,@TENNV,@DIACHI,@MAP,@LUONG

WHILE @@FETCH_STATUS=0

BEGIN

    PRINT @MANV+@TENNV+@DIACHI+@MAP+CONVERT(CHAR(10),@LUONG)

    FETCH NEXT FROM CS11

    INTO @MANV,@TENNV,@DIACHI,@MAP,@LUONG

END

--DONG--

CLOSE CS11

--HUY--

DEALLOCATE CS11