CREATE DATABASE QUANLYDIEMTHI

USE QUANLYDIEMTHI

create table sinhvien(

    masv nvarchar(10) primary key,

    tensv nvarchar(30) not null,

    ngaysinh datetime not null,

    gioitinh nvarchar(10) not null,

    quequan nvarchar(30), )

create table monhoc(

    mam nvarchar(10) primary key,

    tenm nvarchar(20) not null,

    dvht int )

create table diem(

    mam nvarchar(10) not null,

    masv nvarchar(10) not null,

    diemthi float not null,

constraint kc primary key(mam,masv),

constraint kn1 foreign key(mam) references monhoc(mam),

constraint kn2 foreign key(masv) references sinhvien(masv) )

/*2.TAO VIEW DE TONG HOP THONG TIN VE CAC SINH VIEN CO DIEM THI CAO NHAT CUA MON HOC CO TEN LA

"CO SO DU LIEU"*/

CREATE VIEW VD2

AS

SELECT * FROM SINHVIEN

WHERE MASV IN (SELECT MASV FROM DIEM

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

                       AND MAM IN (SELECT MAM FROM MONHOC

                                    WHERE TENM='CO SO DU LIEU'))

--3.TAO THU TUC CO THAM SO LA @TENM DE DUA RA DANH SACH SINH VIEN PHAI THI LAI MON HOC TREN--

CREATE PROC VD3

@TENM CHAR(20)

AS

SELECT * FROM SINHVIEN

WHERE MASV IN (SELECT MASV FROM DIEM

                WHERE DIEMTHI<5 AND MAM IN (SELECT MAM FROM MONHOC

                                            WHERE TENM=@TENM))

EXEC VD3 'SQL'

 --4.DUNG KIEU DU LIEU CURSOR DE DUA RA TRUNG BINH DIEM THI CUA TUNG MON HOC--

--KHAI BAO--

DECLARE CS4 CURSOR FOR

SELECT MAM,AVG(DIEMTHI) AS DTB

FROM DIEM

GROUP BY MAM

--MO--

OPEN CS4

--XU LY MAU TIN--

FETCH NEXT FROM CS4

WHILE @@FETCH_STATUS=0

BEGIN

FETCH NEXT FROM CS4

END

--DONG--

CLOSE CS4

--HUY--

DEALLOCATE CS4