CREATE DATABASE QUANLYDIEM

USE QUANLYDIEM

CREATE TABLE SINHVIEN(

MASV CHAR(10) PRIMARY KEY,

HOTENSV CHAR(40) NOT NULL,

NGAYSINH SMALLDATETIME,

DIACHI CHAR(40),

DVHT INT        --DON VI HOC TRINH--

CREATE TABLE MON(

MAM CHAR(10) PRIMARY KEY,

TENM CHAR(30)

CREATE TABLE DIEM(

MAM CHAR(10),

MASV CHAR(10),

PHACH CHAR(10),        --SO PHACH--

DIEM FLOAT,        --DIEM THI--

CONSTRAINT KC_DIEM PRIMARY KEY(MAM,MASV,PHACH),

CONSTRAINT KN_DIEM FOREIGN KEY (MAM) REFERENCES MON(MAM),

CONSTRAINT KN1_DIEM FOREIGN KEY (MASV) REFERENCES SINHVIEN(MASV)

/*2.TAP VIEW DE TONG HOP THONG TIN VE SINH VIEN CO DIEM THI CAO NHAT CUA MON "CSDL"*/

CREATE VIEW VD2

AS

SELECT * FROM SINHVIEN

WHERE MASV IN (SELECT MASV FROM DIEM

                WHERE DIEM IN (SELECT MAX(DIEM) FROM DIEM)

                        AND MAM IN (SELECT MAM FROM MON

                                        WHERE TENM='CSDL'))

/*3.TAO VIEW DE TONG HOP THONG TIN VE SINH VIEN CO DIA CHI O "THAI NGUYEN"*/

CREATE VIEW VD3

AS

SELECT * FROM SINHVIEN

WHERE DIACHI='THAI NGUYEN'

/*4.TAO VIEW CHO BIET THONG TIN VE CAC SINH VIEN SINH TRUOC NAM 1980*/

CREATE VIEW VD4

AS

SELECT * FROM SINHVIEN

WHERE YEAR(NGAYSINH)<1980

/*5.TAO THU TUC CO THAM SO DAU VAO LA @TENM DE DUA RA DANH SACH CAC SINH VIEN CO DIEM THI

CHUA DAT CUA MON HOC TREN*/

ALTER PROC VD5

@TENM CHAR(30)

AS

SELECT * FROM SINHVIEN

WHERE MASV IN (SELECT MASV FROM DIEM

                WHERE DIEM<5 AND MAM IN (SELECT MAM FROM MON

                                            WHERE TENM=@TENM))

EXEC VD5 'JAVA'

/*6.TAO THU TUC CO THAM SO DAU VAO @DIEM DE DUA RA DANH SACH CAC SINH VIEN CO DIEM THI

THAP HON DIEM TREN CUA MON HOC "CSDL"*/

CREATE PROC VD6

@DIEM FLOAT

AS

SELECT * FROM SINHVIEN

WHERE MASV IN (SELECT MASV FROM DIEM

                WHERE DIEM<@DIEM AND MAM IN (SELECT MAM FROM MON

                                            WHERE TENM='CSDL'))

EXEC VD6 9

/*7.TAO TRIGGER DE KIEM TRA VIEC NHAP  DU LIEU CHO BANG DIEM.NEU DIEM NHO HON 0 HOAC DIEM

LON HON 10 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 DIEM

FOR INSERT

AS

IF EXISTS (SELECT DIEM FROM DIEM

            WHERE DIEM<0 OR DIEM>10)

BEGIN

    PRINT 'DU LIEU NHAP KHONG HOP LE'

    ROLLBACK TRAN

END

ELSE PRINT 'DU LIEU NHAP THANH CONG'

INSERT INTO DIEM

VALUES ('M3','SV1','P07',12)

/*8.DUNG KIEU DU LIEU CURSOR DE DUA RA MAN HINH DANH SACH CAC SINH VIEN CO DIEM THI CAO

NHAT CUA CAC MON HOC*/

--TAO VIEW TRUNG GIAN--

ALTER VIEW MAXDIEM

AS

SELECT MAM,MAX(DIEM) AS DIEMCAONHAT

FROM DIEM

GROUP BY MAM

--KHAI BAO--

DECLARE CS8 CURSOR FOR

SELECT DIEM.MAM,SINHVIEN.MASV,HOTENSV,NGAYSINH,DIACHI,DVHT,DIEM

FROM SINHVIEN,MAXDIEM,DIEM

WHERE SINHVIEN.MASV=DIEM.MASV AND DIEM.MAM=MAXDIEM.MAM AND DIEM=DIEMCAONHAT

GROUP BY DIEM.MAM,SINHVIEN.MASV,HOTENSV,NGAYSINH,DIACHI,DVHT,DIEM

--MO--

OPEN CS8

--XU LY MAU TIN--

PRINT 'DANH SACH SINH VIEN CO DIEM THI CAO NHAT CUA CAC MON HOC LA'

PRINT 'MAM      MASV      TENSV                            NGAYSINH                DVHT    DIEM'      

DECLARE @B1 CHAR(10),@B2 CHAR(10),@B3 CHAR(20),@B4 SMALLDATETIME,@B5 CHAR(20),@B6 INT,

@B7 FLOAT

FETCH NEXT FROM CS8

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

WHILE @@FETCH_STATUS=0

BEGIN

    PRINT @B1+@B2+@B3+CONVERT(CHAR(15),@B4)+@B5+CONVERT(CHAR(10),@B6)+CONVERT(CHAR(5),@B7)

    FETCH NEXT FROM CS8

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

END

--DONG--

CLOSE CS8

--HUY--

DEALLOCATE CS8