create database bai7

create table dmphong

(MaP char(10) primary key

,TenP nvarchar(30) not null)

create table dmts

(Mats char(10) primary key

,Tents nvarchar(30) not null

,DVT nvarchar(10)

,Gia money not null

,SL int)

create table phanphoi

(Mats char(10) not null

,MaP char(10) not null

,Soluong int

,NgayPP datetime

,Ghichu nvarchar(max)

,constraint khoachinh primary key (Mats,MaP)

,constraint kn1 foreign key(Mats)references dmts(Mats)

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

create view v1

as

select *

from dmts

where gia=(select Max(gia)

           from dmts)

create view v2

as

select *

from dmts

where mats not in(select Mats

                  from phanphoi)

create proc t1

@TenP nvarchar(30)

as

select *

from dmts

where Mats in (select Mats

               from phanphoi

               where MaP in(select MaP

                             from dmphong

                             where TenP=@TenP))

exec t1 N'VIP'

create proc t2

@Tents nvarchar(30)

as

select *

from dmphong

where MaP in(select MaP

              from phanphoi

              where Mats in(select Mats

                            from dmts

                            where Tents=@Tents))

exec t2 N'điều hòa'

create trigger kt1

on dmts

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'

insert into dmts

values ('a7',N'đèn',N'cái',-2,20)