create database bai9

create table gv

(MaGV char(10) primary key

,TenGV nvarchar(30) not null

,Diachi nvarchar(30)

,DT char(15))

create table hv

(MaHV char(10) primary key

,TenHV nvarchar(30) not null)

create table cn

(MaCN char(10) primary key

,TenCN nvarchar(30) not null)

create table GV_HV_CN

(MaGV char(10) not null

,MaHV char(10) not null

,MaCN char(10) not null

,Nam int

,constraint khoachinh primary key (MaGV)

,constraint MaGV_khoangoai foreign key(MaGV)references gv(MaGV)

,constraint MaHV_khoangoai foreign key(MaHV)references hv(MaHV)

,constraint MaCN_khoangoai foreign key(MaCN)references cn(MaCN))

create view view1

as

select *

from gv

where MaGV in (select MaGV

               from GV_HV_CN

               where MaHV in(select MaHV

                             from hv

                             where TenHV=N'tiến sĩ'))

create view view2

as

select *

from gv

where MaGV in (select MaGV

               from GV_HV_CN

               where MaCN in(select MaCN

                             from cn

                             where TenCN=N'khoa học cơ bản'))

create view view3

as

select *

from gv

where Diachi=N'thái nguyên'

create proc namnhanHV

@Nam datetime

as

select *

from gv

where MaGV in (select MaGV

               from GV_HV_CN

               where Nam=@Nam and MaHV in(select MaHV

                                          from hv

                                          where TenHV=N'tiến sĩ'))

exec namnhanhv 2010

create proc nhap

@MaGV nvarchar(10),@TenGV nvarchar(30),@Diachi nvarchar(30),@DT nvarchar(15)

as

insert into gv

values (@MaGV,@TenGV,@Diachi,@DT)

create proc p1

@TenHV nvarchar(30)

as

select *

from gv

where MaGV in (select MaGV

               from GV_HV_CN

               where MaHV in (select MaHV

                              from hv

                              where TenHV=@TenHV))

exec p1 N'thạc sĩ'

create trigger ktndl

on GV_HV_CN

for insert

as

if (select Nam

    from inserted)<0

begin

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

rollback tran

end

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

insert into GV_HV_CN

values ('a5','h3','c4',-3)

declare nhap cursor

for 

  select gv.*, Tencn

  from gv,GV_HV_CN,cn

  where gv.magv=GV_HV_CN.magv and cn.macn=GV_HV_CN.macn

  order by Tencn asc

declare @Magv char(10),@Tengv nvarchar(30),@diachi nvarchar(30),@DT char(15),@TenCN nvarchar(30)

open nhap

print cast(N'Mã GV' as nchar(10))+cast(N'Tên GV' as nchar(30))+cast(N'Địa chỉ' as nchar(30))+cast(N'DT' as nchar(15))+cast(N'Tên CN' as nchar(30))

fetch next from nhap

into @Magv,@Tengv,@diachi,@DT,@TenCN

while @@fetch_status=0

begin

print cast(@Magv as nchar(10))+cast(@Tengv as nchar(30))+cast(@diachi as nchar(30))+cast(@dt as nchar(15))+cast(@Tencn as nchar(30))

fetch next from nhap

into @Magv,@Tengv,@diachi,@DT,@TenCN

end

close nhap