create database bai1

create table dmkhach

(MaK nvarchar(10) primary key

,TenK nvarchar(30) not null

,Diachi nvarchar(30)

,DT nvarchar(15))

create table dmhang

(MaH nvarchar(10) primary key

,TenH nvarchar(30) not null

,DVT nvarchar(10)

,Soluong int)

create table hdban

(SoHD nvarchar(10) not null

,MaK nvarchar(10) not null

,NgayHD datetime

,Diengiai nvarchar(max)

,constraint khoachinh primary key (SoHD)

,constraint MaK_kn foreign key(MaK)references dmkhach(MaK))

create table CTHD

(SoHD nvarchar(10) not null

,MaH nvarchar(10) not null

,Soluong int

,Dongia money

,constraint khoachinh1 primary key (SoHD,MaH)

,constraint SoHD_kn foreign key (SoHD) references hdban(SoHD)

,constraint MaH_kn foreign key(MaH)references dmhang(MaH))

select *

from dmhang

where MaH in (select MaH

              from CTHD

              where SoHD in(select SoHD

                            from hdban

                            where NgayHD='4/4/2011'))

select *

from dmhang

where MaH not in (select MaH

                  from CTHD

                  where SoHD in(select SoHD

                                from hdban

                                where month(Ngayhd)=5 and year(Ngayhd)=2011))

select *

from dmkhach

where MaK in (select MaK

              from hdban

              where month(Ngayhd)=5 and year(Ngayhd)=2011) and Diachi=N'thanh hóa'

select MaH,sum(CTHD.Soluong)

from CTHD

where SoHD in (select SoHD

               from hdban

               where  month(ngayhd)=4 and year(ngayhd)=2011)

group by MaH

select dmhang.MaH,TenH,sum(CTHD.Soluong)

from dmhang,CTHD,hdban

where dmhang.MaH=CTHD.MaH AND hdban.SoHD=CTHD.SoHD and Diengiai=N'tiếp khách'

group by dmhang.MaH,TenH

select *

from dmkhach

where MaK in(select MaK

             from hdban)

create view v1

as

select dmhang.MaH,TenH,DVT,sum(CTHD.Soluong) as [số lượng bán]

from dmhang,CTHD

where dmhang.MaH=CTHD.MaH 

group by dmhang.MaH,TenH,DVT

create view v2

as

select *

from dmhang

where MaH in (select MaH

              from CTHD

              where soluong=(select max(soluong)

                             from CTHD))

create view v4

as 

select MaH,sum(soluong) as soluong

from CTHD

group by MaH

create view v5

as

select  *

from dmhang

where MaH in (select MaH

              from v4

              where soluong=(select max(soluong)

                             from v4))

create view v3

as

select *

from dmkhach

where MaK in (select MaK

              from hdban

              where ngayhd='5/4/2011')

create proc t2

@SoHD nvarchar(10)

as

select *

from dmhang

where MaH in(select MaH

             from CTHD

             where SoHD=@SoHD)

exec t2 '100'

create proc t1

@ngayhd datetime

as

select *

from dmhang

where MaH in (select MaH

              from CTHD

              where SoHD in(select SoHD

                            from hdban

                            where NgayHD=@ngayhd))

exec t1 '3/4/2011'

create trigger kt1

on CTHD

for insert

as

if (select soluong

    from inserted)<0

or (select Dongia

    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 CTHD

values ('104','b2',2,6000000)

create trigger kt2

on CTHD

for insert

as

if (select soluong

    from inserted)<0

or (select Dongia

    from inserted)<0

begin

print N'dữ liệu nhập không hợp lệ'

rollback tran

end

else 

begin

print N'dữ liệu nhập thành công'

select *

from inserted

update dmhang

set dmhang.soluong=dmhang.soluong-inserted.soluong

from inserted

where dmhang.mah=inserted.mah

end

insert into CTHD

values ('104','b3',2,9000000)

create trigger kt3

on CTHD

for insert

as

if (select inserted.soluong

    from inserted)>(select dmhang.soluong

                    from dmhang,inserted

                    where dmhang.mah=inserted.mah)

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 CTHD

values ('105','b4',2,9000000)

alter table CTHD

disable trigger kt1

alter table CTHD

disable trigger kt2

declare nhap cursor

for 

  select sohd,tongtien

  from hdban

declare @tongtien int,@sohd nvarchar(10)

open nhap

fetch next from nhap

into @sohd,@tongtien

while @@fetch_status=0

begin

set @tongtien=(select sum(soluong*dongia)

               from CTHD

               where sohd=@sohd)

update hdban

set tongtien=@tongtien

where sohd=@sohd

fetch next from nhap

into @sohd,@tongtien

end

close nhap

declare nhap1 cursor

for 

  select *

  from dmhang

  where mah in (select mah

                from cthd

                where sohd in (select sohd

                               from hdban

                               where ngayhd='5/4/2011'))

declare @Mah nvarchar(10),@TenH nvarchar(30),@DVT nvarchar(10),@soluong int

open nhap1

fetch next from nhap1

into @Mah,@TenH,@DVT,@soluong

print cast(N'mã hàng'as nchar(10))+cast(N'tên hàng'as nchar(30))+cast(N'DVT'as nchar(10))+cast(N'số lượng'as nchar(10))

while @@fetch_status=0

begin

print cast(@mah as char(10))+cast(@tenh as nchar(30))+cast(@DVT as nchar(10))+cast(@soluong as char(10))

fetch next from nhap1

into @Mah,@TenH,@DVT,@soluong

end

close nhap1