CREATE DATABASE QUANLYGIAOVIEN

USE QUANLYGIAOVIEN

CREATE TABLE GIAOVIEN(

MAGV CHAR(10) PRIMARY KEY,

TENGV CHAR(40) NOT NULL,

DC CHAR(40),

DT CHAR(15)

)

CREATE TABLE HOCVI(

MAHV CHAR(10) PRIMARY KEY,

TENHV CHAR(30) NOT NULL

)

CREATE TABLE CHUYENNGANH(

MACN CHAR(10) PRIMARY KEY,

TENCN CHAR(30) NOT NULL

)

CREATE TABLE GV_HV_CN(

MAGV CHAR(10),

MAHV CHAR(10),

MACN CHAR(10),

NAM INT,--NAM DAT HOC VI--

CONSTRAINT KC_GV_HV_CN PRIMARY KEY (MAGV,MAHV,MACN),

CONSTRAINT KN_GV_HV_CN FOREIGN KEY (MAGV) REFERENCES GIAOVIEN(MAGV),

CONSTRAINT KN1_GV_HV_CN FOREIGN KEY (MAHV) REFERENCES HOCVI(MAHV),

CONSTRAINT KN2_GV_HV_CN FOREIGN KEY (MACN) REFERENCES CHUYENNGANH(MACN)

)

--2.TAO VIEW DE CHO BIET THONG TIN VE CAC GIAO VIEN CO HOC VI LA "TIEN SY"--

CREATE VIEW VD2

AS

SELECT * FROM GIAOVIEN

WHERE MAGV IN (SELECT MAGV FROM GV_HV_CN

                WHERE MAHV IN (SELECT MAHV FROM HOCVI

                                WHERE TENHV='TIEN SY'))

SELECT * FROM VD2

--3.TAO VIEW DE CHO BIET THONG TIN VE CAC GIAO VIEN CO CHUYEN NGANH "KINH TE"--

CREATE VIEW VD3

AS

SELECT * FROM GIAOVIEN

WHERE MAGV IN (SELECT MAGV FROM GV_HV_CN

                WHERE MACN IN (SELECT MACN FROM CHUYENNGANH

                                WHERE TENCN='KINH TE'))

SELECT * FROM VD3

--4.TAO VIEW DE CHO BIET THONG TIN VE CAC GIAO VIEN CO DIA CHI O "THAI NGUYEN"--

CREATE VIEW VD4

AS

SELECT * FROM GIAOVIEN

WHERE DC='THAI NGUYEN'

/*5.TAO THU TUC CO THAM SO VAO LA @NAM DE DUA RA TEN CUA CAC GIAO VIEN NHAN HOC VI "TIEN

SY" VAO NAM TREN*/

ALTER PROC VD5

@NAM INT

AS

SELECT * FROM GIAOVIEN

WHERE  MAGV IN (SELECT MAGV FROM GV_HV_CN

                WHERE NAM=@NAM AND MAHV IN (SELECT MAHV FROM HOCVI

                                            WHERE TENHV='TIEN SY'))

EXEC VD5 2009

--6.TAO THU TUC NHAP DU LIEU CHO BANG GIAOVIEN--

CREATE PROC VD6

@MAGV CHAR(10),@TENGV CHAR(40),@DC CHAR(40),@DT CHAR(15)

AS

INSERT INTO GIAOVIEN

VALUES(@MAGV,@TENGV,@DC,@DT)

VD6 'GV5','HANG','VINH PHUC','157148'

/*7.TAO THU TUC CO THAM SO VAO LA @TENHV DE DUA RA THONG TIN VE CAC GIAO VIEN CO TEN HOC VI TREN*/

CREATE PROC VD7

@TENHV CHAR(40)

AS

SELECT * FROM GIAOVIEN

WHERE  MAGV IN (SELECT MAGV FROM GV_HV_CN

                WHERE MAHV IN (SELECT MAHV FROM HOCVI

                                            WHERE TENHV=@TENHV))

EXEC VD7 'THAC SY'

/*8.TAO TRIGGER DE KIEM TRA KHI NHAP  DU LIEU  VAO BANG GV_HV_CN.NEU NAM NHAP VAO 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 GV_HV_CN

FOR INSERT

AS

IF EXISTS(SELECT NAM FROM GV_HV_CN

            WHERE NAM<0)

BEGIN

    PRINT 'DU LIEU NHAP KHONG HOP LE'

        ROLLBACK TRAN

END

ELSE PRINT 'DU LIEU NHAP THANH CONG'

INSERT INTO GV_HV_CN

VALUES('GV5','HV2','CN2',-2010)

/*9.DUNG KIEU DU LIEU CURSOR DE DUA RA THONG TIN VE TUNG GIAO VIEN CUA TUNG CHUYEN NGANH*/

--KHAI BAO--

DECLARE CS9 CURSOR FOR

SELECT MACN,GIAOVIEN.MAGV,TENGV,DC,DT

FROM GIAOVIEN,GV_HV_CN

WHERE GIAOVIEN.MAGV=GV_HV_CN.MAGV

GROUP BY MACN,GIAOVIEN.MAGV,TENGV,DC,DT

--MO--

OPEN CS9

--XU LY MAU TIN--

DECLARE @B1 CHAR(10),@B2 CHAR(10),@B3 CHAR(30),@B4 CHAR(30),@B5 CHAR(15)

FETCH NEXT FROM CS9

INTO @B1,@B2,@B3,@B4,@B5

WHILE @@FETCH_STATUS=0

BEGIN

    PRINT @B1+@B2+@B3+@B4+@B5

    FETCH NEXT FROM CS9

    INTO @B1,@B2,@B3,@B4,@B5

END

--DONG--

CLOSE CS9

--HUY--

DEALLOCATE CS9