create database bai14

create table nv

(Manv char(10) primary key

,Hoten nvarchar(30) not null

,diachi nvarchar(30)

,maP char(10)

,Luong money

,constraint nv_kn foreign key(MaP)references phong(MaP))

create table Phong

(MaP char(10) primary key

,TenP nvarchar(30) not null

,DiachiP nvarchar(30) 

,DT char(15))

create table HV

(MaHV char(10) primary key

,TenHV nvarchar(30) not null)

create table NV_TDHV

(MaNV char(10) not null

,MaHV char(10) not null

,Thoigian datetime

,constraint khoachinh primary key (MaNV,MaHV)

,constraint kn1 foreign key(Manv)references nv(Manv)

,constraint kn2 foreign key(MaHV)references HV(MaHV))

create view v1

as

select *

from nv

where MaNV in (select MaNV

               from NV_TDHV

               where year(thoigian)<2011 and MaHV in(select MaHV

                                                     from HV

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

create view v2

as

select *

from nv

where MaNV not in (select MaNV

                   from NV_TDHV)or MaNV in (select MaNV

                                            from NV_TDHV

                                            where MaHV in (select MaHV

                                                           from hv

                                                           where TenHV<>N'tiến sĩ'))

create proc t1

@TenHV nvarchar(30),@Thoigian datetime

as

select *

from nv

where Manv in (select Manv

               from NV_TDHV

               where MaHV in(select MaHV

                             from HV

                             where TenHV=@TenHV) and thoigian=@thoigian)

exec t1 N'tiến sĩ','4/6/2009'

create trigger kt1

on NV_TDHV

for insert

as

if (select thoigian

    from inserted)>getdate() 

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 NV_TDHV

values ('a6','h1','4/15/2011')

declare nhap cursor

for

  select * 

  from nv 

  where MaNV in(select MaNV

                from NV_TDHV

                where MaHV in(select MAHV

                              from hv

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

declare @Manv char(10),@Hoten nvarchar(30),@diachi nvarchar(30),@MaP char(10),@luong money

open nhap

print cast(N'Mã NV' as nchar(10))+cast(N'Họ tên' as nchar(30))+cast(N'Địa chỉ' as nchar(30))+cast(N'Mã phòng' as nchar(10))+cast(N'Lương' as nchar(10))

fetch next from nhap

into @Manv,@Hoten,@diachi,@MaP,@luong

while @@fetch_status=0

print cast(@Manv as nchar(10))+cast(@Hoten as nchar(30))+cast(@diachi as nchar(30))+cast(@MaP as nchar(10))+cast(@luong as nchar(10))

fetch next from nhap

into @Manv,@Hoten,@diachi,@MaP,@luong

end

close nhap