create database bai3

create table lop

(Malop nvarchar(10) primary key

,Tenlop nvarchar(30) not null)

create table tinh

(Matinh nvarchar(10) primary key

,Tentinh nvarchar(30) not null)

create table sv

(Masv nvarchar(10) not null

,Hoten nvarchar(10) 

,NS datetime

,GT bit

,Malop nvarchar(10) not null

,Matinh nvarchar(10) not null

,DTB float

,constraint khoachinh primary key (Masv,Malop,Matinh)

,constraint kn1 foreign key(Malop)references lop(Malop)

,constraint kn2 foreign key(Matinh)references tinh(Matinh))

select *

from sv

where DTB<5

select *

from sv

where Matinh in (select Matinh

              from tinh

              where tentinh=N'thái nguyên')

select *

from lop

where Malop  not in (select Malop

                     from sv

                     where matinh in(select matinh

                                     from tinh

                                     where tentinh=N'hà nội'))

create view v1

as

select *

from sv

where DTB= (select Max(DTB)

              from sv)

create view v2

as

select *

from sv a

where DTB in (select Max(DTB)

                from sv

                where sv.malop=a.malop)

create proc t1

as

select Lop.Malop,tenlop,count(sv.malop) as siso

from lop,sv

where lop.Malop=sv.Malop

group by Lop.Malop,tenlop

exec t1

create proc t2

@Tenlop nvarchar(30)

as

delete from sv

where Malop in (select Malop

                from lop

                where tenlop=@tenlop)

create trigger kt1

on sv

for insert

as

if (select DTB

    from inserted)<0 

begin

print N'dữ liệu nhập không hợp lệ'

rollback tran

end

else print N'dữ liệu nhập thành công'

insert into sv

values ('106',N'hoa','4/16/1991','false','a3','2',-2)

declare nhap cursor

for 

  select *

  from sv

  where Malop in(select Malop

                 from lop

                 where tenlop='dtvt')

declare @Masv nvarchar(10),@Hoten nvarchar(30),@NS datetime,@GT bit,@Malop nvarchar(10),@Matinh nvarchar(10),@DTB float

open nhap

print cast(N'Mã SV' as nchar(10))+cast(N'Họ tên' as nchar(30))+cast(N'Năm sinh' as nchar(20))+cast(N'Giới tính' as nchar(10))+cast(N'mã lớp' as nchar(10))+cast(N'Mã tỉnh' as nchar(10))+cast('DTB' as nchar(10))

fetch next from nhap

into @Masv,@Hoten,@NS,@GT,@Malop,@Matinh,@DTB

while @@fetch_status=0

begin

print cast(@Masv as nchar(10))+cast(@hoten as nchar(30))+cast(@NS as nchar(20))+cast(@GT as nchar(10))+cast(@malop as nchar(10))+cast(@matinh as nchar(10))+cast(@DTB as nchar(10))

fetch next from nhap

into @Masv,@Hoten,@NS,@GT,@Malop,@Matinh,@DTB

end

close nhap