create database bai2

create table khach

(MaK nvarchar(10) primary key

,TenK nvarchar(30) not null

,Diachi nvarchar(30)

,DT nvarchar(15))

create table nha

(MaN nvarchar(10) primary key

,TenCN nvarchar(30) not null

,Giathue money

,Diachinha nvarchar(30))

create table hopdong

(SoHD nvarchar(10) not null

,MaN nvarchar(10) not null

,MaK nvarchar(10) not null

,Ngaybd datetime

,Ngaykt datetime

,constraint khoachinh primary key (SoHD)

,constraint kn1 foreign key(MaK)references khach(MaK)

,constraint kn2 foreign key(MaN)references nha(MaN))

select *

from nha

where MaN in (select MaN

              from hopdong)

select *

from khach

where MaK in (select MaK

              from hopdong

              where MaN='104')

select *

from nha

where MaN not in (select MaN

                  from hopdong)

create view v1

as

select *

from nha

where Giathue=(select max(Giathue)

               from nha)

create view v2

as

select *

from khach

where MaK in (select MaK

              from hopdong

              where datediff(dd,ngaykt,getdate())>0)

create view v3

as

select *

from nha

where MaN not in (select MaN

                  from hopdong)

create proc t2

@ngay datetime

as

select *

from khach

where MaK in(select MaK

             from hopdong

             where ngaykt=@ngay)

exec t2 '6/15/2011'

create proc t1

as

select *

from nha

where MaN not in (select MaN

                  from hopdong)or MaN in (select MaN

                                          from hopdong

                                          where datediff(dd,ngaykt,getdate())>0)

exec t1 

create proc t3

@ngay datetime

as

select *

from khach

where MaK in(select MaK

             from hopdong

             where ngaybd=@ngay)

exec t3 '2/14/2011'

create trigger kt1

on nha

for insert

as

if (select Giathue

    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 nha

values ('106',N'hiền',-1,N'tân thịnh')

declare nhap cursor

for 

  select *

  from khach

  where MaK in(select MaK

               from hopdong

               where MaN in(select MaN

                            from nha

                            where Diachinha=N'quyết thắng'))

declare @MaK nvarchar(10),@TenK nvarchar(30),@Diachi nvarchar(30),@DT nvarchar(15)

open nhap

print cast(N'Mã khách' as nchar(10))+cast(N'Tên khách' as nchar(30))+cast(N'Địa chỉ' as nchar(30))+cast(N'Điện thoại' as nchar(15))

fetch next from nhap

into @MaK ,@TenK ,@Diachi,@DT

while @@fetch_status=0

begin

print cast(@MaK as nchar(10))+cast(@TenK as nchar(30))+cast(@Diachi as nchar(30))+cast(@DT as nchar(15))

fetch next from nhap

into @MaK ,@TenK ,@Diachi,@DT

end

close nhap

declare nhap1 cursor

for 

select *

from nha

where MaN not in (select MaN

                  from hopdong)or MaN in (select MaN

                                          from hopdong

                                          where datediff(dd,ngaykt,getdate())>0)

declare @MaN nvarchar(10),@TenCN nvarchar(30),@Giathue money, @Diachinha nvarchar(30)

open nhap1

print cast(N'Mã khách' as nchar(10))+cast(N'Tên khách' as nchar(30))+cast(N'Địa chỉ' as nchar(30))+cast(N'Giá thuê' as nchar(15))

fetch next from nhap1

into @MaN ,@TenCN ,@Giathue,@Diachinha

while @@fetch_status=0

begin

print cast(@MaN as nchar(10))+cast(@TenCN as nchar(30))+cast(@Diachinha as nchar(30))+cast(@Giathue as nchar(10))

fetch next from nhap1

into @MaN ,@TenCN ,@Giathue,@Diachinha

end

close nhap1