CREATE DATABASE QUANLYDUAN

USE QUANLYDUAN

CREATE TABLE NHANVIEN(

MANV CHAR(10) PRIMARY KEY,

HOTEN CHAR(40) NOT NULL,

NGAYS SMALLDATETIME,

GOITINH CHAR(3) --GIOI TINH--

)

CREATE TABLE DUAN(

MADA CHAR(10) PRIMARY KEY,

TENDA CHAR(30) NOT NULL,

NGANSACH MONEY

)

CREATE TABLE THAMGIA(

MADA CHAR(10),

MANV CHAR(10),

TGBD SMALLDATETIME NOT NULL, --THOI GIAN BAT DAU--

TGKT SMALLDATETIME NOT NULL, --THOI GIAN KET THUC--

CONSTRAINT KC_THAMGIA PRIMARY KEY (MADA,MANV),

CONSTRAINT KN_THAMGIA FOREIGN KEY (MADA) REFERENCES DUAN(MADA),

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

)

/*2.tao view de tong hop thong tin ve cac nhan vien chua tham gia bat ky du an nao*/

CREATE VIEW VD2

AS

SELECT * FROM NHANVIEN

WHERE MANV NOT IN (SELECT MANV FROM THAMGIA)               

--3.tao view de tong hop thong tin ve cac du an co ngan sach lon nhat--

CREATE VIEW VD3

AS

SELECT * FROM DUAN

WHERE NGANSACH IN (SELECT MAX(NGANSACH) FROM DUAN)

--tao view de tong hop thong tin ve moi nhan vien da tham gia bao nhieu du an--

CREATE VIEW VD31

AS

SELECT NHANVIEN.MANV,HOTEN,NGAYS,GOITINH,COUNT(MADA) AS SODUAN

FROM NHANVIEN,THAMGIA

WHERE NHANVIEN.MANV=THAMGIA.MANV

GROUP BY NHANVIEN.MANV,HOTEN,NGAYS,GOITINH

--4.tao thu tuc co tham so la @tenda  de dua ra danh sach cac nhan vien tham gia du an tren--

CREATE PROC VD4

@TENDA CHAR(30)

AS

SELECT * FROM NHANVIEN

WHERE MANV IN (SELECT MANV FROM THAMGIA

                WHERE MADA IN (SELECT MADA FROM DUAN

                                WHERE TENDA=@TENDA))

EXEC VD4 'DAU TU'

/* 5.TAO THU TUC CO THAM SO LA @TGKT VA @MADA DE DUA RA DANH SACH CAC NHAN VIEN PHAI HOAN THANH

DU AN DO VAO NGAY TREN*/

ALTER PROC VD5

@TGKT SMALLDATETIME,@MADA CHAR(10)

AS

SELECT * FROM NHANVIEN

WHERE MANV IN (SELECT MANV FROM THAMGIA

                WHERE MADA=@MADA AND CONVERT(CHAR(10),TGKT)=CONVERT(CHAR(10),@TGKT))

DECLARE @TGKT SMALLDATETIME,@MADA CHAR (10)

SET @TGKT='10/10/2010'

EXEC VD5 @TGKT, 'DA1'

/*6.TAO TRIGGER DE KIEM TRA DU LIEU KHI NHAP VAO BANG DUAN.NEU NGANSACH 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 TG6

ON DUAN

FOR INSERT

AS

IF EXISTS (SELECT NGANSACH FROM DUAN

            WHERE NGANSACH<0)

BEGIN

    PRINT 'DU LIEU NHAP VAO KHONG HOP LE'

    ROLLBACK TRAN

END

ELSE PRINT 'DU LIEU NHAP THANH CONG'

/*7.DUNG KIEU DU LIEU CURSOR DE DUA RA DANH SACH NHAN VIEN BAT DAU THUC HIEN DU AN "PHONG

CHONG BENH MAT HOT" TU NGAY 20/11/2005 */

--KHAI BAO--

DECLARE CS7 CURSOR FOR

SELECT * FROM NHANVIEN

WHERE MANV IN (SELECT MANV FROM THAMGIA

                WHERE TGBD='11/20/2005'

                AND MADA IN (SELECT MADA FROM DUAN

                                WHERE TENDA='PHONG CHONG BENH MAT HOT'))

--MO--

OPEN CS7

--XU LY MAU TIN--

FETCH NEXT FROM CS7

WHILE @@FETCH_STATUS=0

BEGIN

    FETCH NEXT FROM CS7

END

--DONG--

CLOSE CS7

--HUY--

DEALLOCATE CS7

/*8.DUNG KIEU DU LIEU CURSOR DE DUA RA DANH SACH CAC DU AN CHUA TUNG CO NHAN VIEN NAO THAM GIA

DANG KY */

--KHAI BAO--

DECLARE CS8 CURSOR FOR

SELECT * FROM DUAN

WHERE MADA NOT IN (SELECT MADA FROM THAMGIA)

--MO--

OPEN CS8

--XU LY MAU TIN--

FETCH NEXT FROM CS8

WHILE @@FETCH_STATUS=0

BEGIN

    FETCH NEXT FROM CS8

END

--DONG--

CLOSE CS8

--HUY--

DEALLOCATE CS8