CREATE DATABASE QUANLYDOANVIEN

USE QUANLYDOANVIEN

CREATE TABLE TOCONGDOAN(

MATCD CHAR(10) PRIMARY KEY,

TENTCD CHAR(30) NOT NULL

)

CREATE TABLE CONGDOAN(

MACDV CHAR(10) PRIMARY KEY,            --MA CONG DOAN VIEN--

TENCDV CHAR(30) NOT NULL,        --TEN CONG DOAN VIEN--

NGAYSINH SMALLDATETIME,

NGAYV SMALLDATETIME NOT NULL,        --NGAY RA NHAP CONG DOAN--

MATCD CHAR(10) NOT NULL,

CONSTRAINT KN_CONGDOAN FOREIGN KEY (MATCD) REFERENCES TOCONGDOAN(MATCD)

)

CREATE TABLE KHENTHUONG(

MACDV CHAR(10),

MSKT CHAR(10),        --MA SO KHEN THUONG--

LYDO CHAR(40),        --LY DO KHEN THUONG--

NAM INT,            --NAM KHEN THUONG--

CONSTRAINT KC_KHENTHUONG PRIMARY KEY (MACDV,MSKT),

CONSTRAINT KN_KHENTHUONG FOREIGN KEY (MACDV) REFERENCES CONGDOAN(MACDV)

)

/*2.HAY TAO VIEW DE TONG HOP THONG TIN VE CAC CONG DOAN VIEN DA DUOC KHEN THUONG CUA TO

CONG DOAN "HE THONG THONG TIN"*/

CREATE VIEW VD2

AS

SELECT CONGDOAN.* FROM CONGDOAN,KHENTHUONG,TOCONGDOAN

WHERE CONGDOAN.MACDV=KHENTHUONG.MACDV AND CONGDOAN.MATCD=TOCONGDOAN.MATCD

        AND TENTCD='HE THONG THONG TIN'

/*3.HAY TAO VIEW CHO BIET THONG TIN VE CAC CONG DOAN VIEN CHUA TUNG DUOC KHEN THUONG*/

CREATE VIEW VD3

AS

SELECT * FROM CONGDOAN

WHERE MACDV NOT IN (SELECT MACDV FROM KHENTHUONG)

/*4.HAY TAO VIEW DE CHO BIET THONG TIN VE CAC CONG DOAN VIEN DUOC KHEN THUONG VE VIEC HOAN

THANH DE TAI CAP BO*/

CREATE VIEW VD4

AS

SELECT * FROM CONGDOAN

WHERE MACDV IN (SELECT MACDV FROM KHENTHUONG

                WHERE LYDO='HOAN THANH DE TAI CAP BO')

/*5.TAO THU TUC CO THAM SO VAO LA @TENTCD DE DUA RA THONG TIN VE NHUNG DOAN VIEN CUA CHI DOAN TREN*/

CREATE PROC VD5

@TENTCD CHAR(30)

AS

SELECT * FROM CONGDOAN

WHERE MATCD IN(SELECT MATCD FROM TOCONGDOAN

                WHERE TENTCD=@TENTCD)

EXEC VD5 'HOC SINH'

/*6.TAO THU TUC CO THAM SO VAO LA@TENTCD,@NAM DE DUA RA THONG TIN VE NHUNG CONG DOAN VIEN

CUA TO CONG DOAN DA DUOC KHEN THUONG VAO NAM TREN */

CREATE PROC VD6

@TENTCD CHAR(30), @NAM INT

AS

SELECT CONGDOAN.* FROM CONGDOAN,KHENTHUONG,TOCONGDOAN

WHERE CONGDOAN.MACDV=KHENTHUONG.MACDV AND CONGDOAN.MATCD=TOCONGDOAN.MATCD

        AND TENTCD=@TENTCD AND NAM=@NAM

EXEC VD6 'HE THONG THONG TIN', 2009

/*7.TAO THU TUC CO THAM SO VAO LA @TENCD DE XOA THONG TIN VE NHUNG CONG DOAN VIEN THUOC

CHI DOAN TREN*/

CREATE PROC VD7

@TENTCD CHAR(30)

AS

DELETE FROM CONGDOAN

WHERE MATCD IN(SELECT MATCD FROM TOCONGDOAN

                WHERE TENTCD=@TENTCD)

EXEC VD7 'THONG TIN KINH TE'

/*8.TAO TRIGGER DE KIEM TRA VIEC NHAP  DU LIEU CHO BANG KHENTHUONG.NEU NAM KHEN THUONG NHO

HON 0 THI IN RA MAN HINH THONG BAO LOI "DU LIEU NHAP VAO KHONG HOP LE" VA BAN GHI NAY KHONG

 DUOC PHEP NHAP VAO BANG.NGUOC LAI IN RA MAN HINH THONG BAO"DU LIEU DA NHAP THANH CONG" */

CREATE TRIGGER TG8

ON KHENTHUONG

FOR INSERT

AS

IF EXISTS (SELECT NAM FROM KHENTHUONG

            WHERE NAM<0)

BEGIN

    PRINT 'DU LIEU NHAP KHONG HOP LE'

    ROLLBACK TRAN

END

ELSE PRINT 'DU LIEU NHAP THANH CONG'

INSERT INTO KHENTHUONG

VALUES ('CDV3','04','OH MY GOOD',-2010)

/*9.SU DUNG KIEU DU LIEU CURSOR DE TONG HOP THONG TIN VE NHUNG CONG DOAN VIEN CHUA TUNG DUOC

KHEN THUONG TRONG NAM 2007*/

--KHAI BAO--

DECLARE CS9 CURSOR FOR

SELECT * FROM CONGDOAN

WHERE MACDV NOT IN (SELECT MACDV FROM KHENTHUONG

                    WHERE NAM=2007)

--MO--

OPEN CS9

--XU LY MAU TIN--

FETCH NEXT FROM CS9

WHILE @@FETCH_STATUS=0

BEGIN   

    FETCH NEXT FROM CS9

END

--DONG--

CLOSE CS9

--HUY--

DEALLOCATE CS9