一个简单的酒店系统的数据库设计

--11,基于顾客号能显示特定日期的顾客消费的服务列表及应付金额
create proc serverlistbyguestid @guestid int ,@time datetime      --统计金额
as
  select serverid,'应付金额'=sum(servercost)
  from serverlist
  where serverid in
     (select serverid
      from serveruse
      where guestid=@guestid and servertime=@time)
  group by serverid
go
  exec serverlistbyguestid 10003,'2004-6-24'

go

--12,统计当前的在住顾客数(利用客人入住时,outdate为空表示客人在住)
create proc guestnumber
as
   select count(*)as guestinroom from guestroom where outdate is null
go
   exec guestnumber
go

--13,基于房号显示客人享用的服务列表
create proc serverbyroom @roomid char(6)
as
begin
  if (select count(*) from guestroom where roomid=@roomid)<>0
     select a.serverid,a.servername,a.servercost from serverlist a
         where serverid in
              (select serverid from serveruse where guestid in
                 (select guestid from guestroom where roomid=@roomid ))
  else
     select  '无效房号或者没被占用的房号'
end 
go
  exec serverbyroom '1001'
  exec serverbyroom '1002'
--输入无效房号或者没被占用的房号
  exec serverbyroom '1003'
go

--14,删除顾客所享用的某项服务--是否应该带上消费日期-- 
create proc deleserveruse @guestname char(6),@servername char(20)
as
  delete serveruse
  where guestid =
         (select guestid from viewguest where guestname=@guestname)

上一页 [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] 下一页

复制本页网址和标题，发送给你QQ/Msn的好友一起分享

上一篇:数据库备份中可能出错的情况及解决办法

下一篇:collate chinese_prc_ci_as null 是什么意思