create database bai12

create table khach

(MaK char(10) primary key

,TenK nvarchar(30) not null

,Diachi nvarchar(30)

,DT char(15))

create table phong

(MaP char(10) primary key

,TenP nvarchar(30) not null

,LoaiP nvarchar(20)

,Gia money)

create table datphong

(MaK char(10) not null

,MaP char(10) not null

,Ngaynhan datetime

,Ngaytra datetime

,constraint khoachinh primary key (MaK,MaP)

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

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

create proc tang

as

update phong

set Gia=(Gia+(gia*5/100))

exec tang

create view v1

as

select *

from khach

where maK in(select maK

             from datphong

             where datediff(dd,getdate(),ngaytra)>=0)

create view v2

as

select khach.maK,TenK,phong.MaP,Gia,NgayNhan,NgayTra,ThanhTien=(datediff(dd,NgayNhan,NgayTra))*Gia

from khach,phong,datphong

where khach.MaK=datphong.MaK and datphong.MaP=phong.MaP

create view v3

as

select khach.MaK,TenK,Max(datediff(dd,NgayNhan,NgayTra)) as SoNgay 

from khach,datphong d

where khach.MaK=d.MaK

group by khach.MaK,TenK

create proc t1

as

select *

from phong

where MaP not in(select MaP

                 from datphong)

exec t1

create proc t2

as

select *

from khach

where MaK in (select MaK

              from datphong

              where datediff(dd,ngaytra,getdate())=0)

exec t2 

create trigger kt1

on phong

for insert

as

if (select Gia

    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'

create trigger kt2

on datphong

for insert

as

if (select NgayNhan

    from inserted)>(select NgayTra

                     from inserted)

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 datphong

values ('a3','3','12/4/2011','11/4/2011')

declare nhap cursor

for 

select *

from khach

where MaK in (select MaK

              from datphong

              where datediff(dd,ngaytra,getdate())=0)

declare @MaK char(10),@TenK nvarchar(30),@Diachi nvarchar(30),@DT char(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