--Cau 1

Create database QuanLyThi

use QuanLyThi

create table ThiSinh

MSTS int primary key (MSTS),

Ho nvarchar(50),

Ten nvarchar(20),

NgaySinh smalldatetime,

SBD char(50),

PhongThi int

create table MonThi

MSMon int primary key (MSMon),

TenMon nvarchar(20)

create table DiemMonThi

MSTS int references ThiSinh(MSTS),

MSMon int references MonThi(MSMon),

DiemThi smallint,

primary key (MSTS,MSMon)

--Cau 2

alter table ThiSinh add

constraint uni_SBD unique(SBD)

alter table DiemMonThi add

constraint chk_DiemThi check (DiemThi between 0 and 10 )

--Cau 3

create view vwBoThi

as

select * 

from ThiSinh 

where MSTS not in (select MSTS from DiemMonThi )

create view vwKetQuaThi

as

select TOP 100 PERCENT ThiSinh.MSTS ,Ho,Ten,NgaySinh,PhongThi,SUM(DiemThi) as TongDiem

from ThiSinh,DiemMonThi

where ThiSinh.MSTS = DiemMonThi.MSTS

group by ThiSinh.MSTS,Ho,Ten,NgaySinh,PhongThi

order by PhongThi,ThiSinh.MSTS

--Cau 4

create Trigger utrg_SuaPhongThi on ThiSinh for update

as

if(select count(ThiSinh.PhongThi) from ThiSinh ,inserted b

where ThiSinh.PhongThi = b.PhongThi )>25

print ' so thi sinh khong the lon hon > 25'

rollback tran

end

--Cau 5

create proc sp_SBD

as

select MSTS,Ho,Ten,NgaySinh,PhongThi,SBD=ROW_NUMBER() over

(order by Ten,Ho)

from ThiSinh