create database bai6

create table nv

(Manv char(10) primary key

,Hoten nvarchar(30) not null

,NS datetime

,GT bit)

create table Du_an

(MaDA char(10) primary key

,TenDA nvarchar(30) not null

,Ngansach money not null)

create table thamgia

(MaDA char(10) not null

,Manv char(10) not null

,BD datetime

,KT datetime

,constraint khoachinh primary key (MaDA,Manv)

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

,constraint kn2 foreign key(MaDA)references du_an(MaDA))

create view v1

as

select *

from nv

where Manv not in (select Manv

                   from thamgia)

create view v2

as

select *

from du_an

where ngansach=(select Max(ngansach)

               from du_an)

create view v3

as

select nv.Manv,nv.Hoten,count(MaDA) as soduan

from nv, thamgia

where nv.Manv=thamgia.Manv

group by nv.Manv,nv.Hoten

create proc t1

@TenDA nvarchar(30)

as

select *

from nv

where Manv in (select Manv

               from thamgia

               where MaDA in(select MaDA

                             from du_an

                             where TenDA=@TenDA))

exec t1 N'trồng rừng'

create proc t2

@KT datetime,@MaDA char(10)

as

select *

from nv

where Manv in(select Manv

              from thamgia

              where KT=@KT and MaDA=@MaDA)

exec t2 '4/5/2011', 'a2'

create proc t3

as

select *

from dt

where Madt not in(select Madt

              from sv_dt)

exec t3

create trigger kt1

on du_an

for insert

as

if (select ngansach

    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 du_an

values ('a6',N'trồng trọt',-2)

declare nhap cursor

for 

  select *

  from nv

  where Manv in(select Manv

                from thamgia

                where BD='11/14/2010' and MaDA in (select MaDA

                                                   from du_an

                                                   where TenDA=N'chỉnh trang đô thị'))

declare @Manv char(10),@Hoten nvarchar(30),@NS datetime,@GT bit

open nhap

print cast(N'Mã NV' 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))

fetch next from nhap

into @Manv,@Hoten,@NS,@GT

while @@fetch_status=0

begin

print cast(@Manv as nchar(10))+cast(@Hoten as nchar(30))+cast(@NS as nchar(25))+cast(@GT as nchar(10))

fetch next from nhap

into @Manv,@Hoten,@NS,@GT

end

close nhap

declare nhap1 cursor

for 

  select *

  from du_an

  where MaDA not in(select MaDA

                    from thamgia)

declare @MaDA char(10),@TenDA nvarchar(30),@ngansach money

open nhap1

print cast(N'Mã DA' as nchar(10))+cast(N'Tên DA' as nchar(30))+cast(N'Ngân sách' as nchar(30))

fetch next from nhap1

into @MaDA,@TenDA,@Ngansach

while @@fetch_status=0

begin

print cast(@MaDA as nchar(10))+cast(@TenDA as nchar(30))+cast(@ngansach as nchar(14))

fetch next from nhap1

into @MaDA,@TenDA,@Ngansach

end

close nhap1