课程实验1-2--答案
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验一：
使用SQL语句在上述数据库中创建客房标准信息表、订房信息表、客房信息表等，具体表结构如下：（注：要求表字段取英文名）
实验二：
在实验一基础上，用SQL语句完成以下任务：
1.查询有电话的客房类型，按客房类型降序排列；
Select *
From roomtype
Where htelephone=’有’
Order by typename DESC
2.查询所有客户的身份证号、客房号、入住日期、结算日期等信息Select guest_id, roomno, staydate, returndate
From bookin
Order by guest_id
3.查询没有被预定的客房编号；
Select roomno
Form rooms
Where putup=’否’
4.查询没有被预定并有电话的客房信息
Select *
Form rooms r, roomtype t
Where r.typeid=t.typeid and r.putup=’否’ and t.htelephone=’有’
5.查询客房编号和客房类型(用typename表示)
Select roomno, typename
From rooms r, roomtype t
Where r.typeid=t.typeid
6.查询每一种客房类型的总床位数
Select typename, count(roomno)* max(bednum)
From rooms r, roomtype t
Where r.typeid=t.typeid
Group by r.typeid typename
7.查询每一种客房类型的可用床位数
Select typename, count(roomno)* max(bednum)
From rooms r, roomtype t
Where r.typeid=t.typeid and putup=’否’ and (roomno in (select roomno from bookin group by roomno having max(returndate)> max(staydate)))
Group by r.typeid typename
8.查询最近一年客房消费金额最高的客户
Select Top 1 sum(ammount), guest_id
From bookin
Where datediff(yy, getdate(),returndate)<1
Group by guest_id
Order by sum(ammount)
9.查询身份证号为“310222************”的客户最近入住酒店的日期以及客房号，入住
天数信息
Select staydate, roomno, datediff(dd, returndate, staydate)
Form bookin
Where guest_id =’310222************’ and staydate=(select max(staydate) from bookin where guest_id =’310222************’)
10.查询价格高于所有五楼(5**)房间的客房位置
Select roomposition
From rooms
Where rooms.price>(select max(price) from rooms where roomposition like ‘5%’)
11.身份证号为“310222************”的客户离店结算房价应作何SQL操作
Update booking
Set returndate=getdate(), discount=1, amount=datediff(day, getdate(), staydate())*1*roomprice From rooms
Where guest_id=’310222************’and rooms.roomno=booking.roomno and returndate is null
注：staydate()按中午12时计
12.把身份证号为“310222************”的客户住过的所有客房类型加价10%
Update rooms
Set roomprice= roomprice*1.1
Where roomno in (select roomno from bookin where guest_id=’310222************’)