MySQL练习十：Guest House

来源于zoo sql， 整理后分享

下表为结构图，记录了一座旅馆的信息

guest：顾客表，包括顾客编号（id），first_name，last_name，地址信息。

room_type：房间类型表，包括类型编号（single，double，family），描述信息。

room：房间表，包括房间编号，房间类型，最大住宿人数。其中房间类型字段关联room_type中的id字段

rate：房间的每晚费用表，包括房间类型，住宿人数，每晚费用，其中房间类型字段关联room_type的id字段

booking：预定表

如上图第一条记录所示，预订单编号为5001，预订时间2016年11月3日，预订房间号为101（关联room的id字段），预订的顾客编号为1027（关联guest表的id字段），房间类型为single，预计的住宿人数是1人，预计住7晚，到达旅店的时间为13点

根据occupants和room_type_requested字段，连接rate表可以查询每晚的住宿费用，如查询出第一条订单的每晚费用

select b.*, r.amount from booking b, rate r where b.room_type_requested = r.room_type and b.occupants = r.occupancy and b.booking_id = 5001

由于room表中room_type和occupancy组成联合主建，故与booking表连接时，这两个字段都要相等。

extra：额外产生的费用表，包括编号，订单编号，描述信息，费用，其中订单编号关联booking表的id字段。

注意：一张预定单产生的额外费用不一定只产生一条记录，如查询预定单编号为5009的额外费用

select * from extra where booking_id = 5009

结果如下：

可见，这个预定单早餐花费72，电话费用为2.69

select b.arrival_time, g.first_name, g.last_name
    from booking b, guest g
    where g.id = b.guest_id and b.booking_date = '2016-11-05'
    order by b.arrival_time;
3. Look up daily rates. Give the daily rate that should be paid for bookings with ids 5152, 5165, 5154 and 5295. Include booking id, room type, number of occupants and the amount.

示例查询：

1. Show who made the booking for room 101 on 17th Nov 2016，查询在2016年11月17号入住101房间的客户的first_name和last_name

   SELECT first_name,last_name FROM booking JOIN guest ON guest_id = guest.id WHERE room_no=101 AND booking_date='2016-11-17';

2. Show the date of check in and check out and number of nights for booking 5046   查询预定编号为5046的客户入住时间，搬出时间以及住宿天数的信息

   SELECT booking_date AS 'check in',
              DATE_ADD(booking_date,INTERVAL nights DAY) AS 'check out',
              nights
       FROM booking
       WHERE booking_id=5046

简单查询：

1. Guest 1183. Give the booking_date and the number of nights for guest 1183.查询编号为1183的顾客的入住时间和住宿天数

   select b.arrival_time, g.first_name, g.last_name
       from booking b, guest g
       where g.id = b.guest_id and b.booking_date = '2016-11-05'
       order by b.arrival_time;
   

2. When do they get here? List the arrival time and the first and last names for all guests due to arrive on 2016-11-05, order the output by time of arrival.列出在2016年11月5号入住的顾客的到达时间，first_name和last_name, 并按时间从早到晚排列

   select b.arrival_time, g.first_name, g.last_name
       from booking b, guest g
       where g.id = b.guest_id and b.booking_date = '2016-11-05'
       order by b.arrival_time;

3. Look up daily rates. Give the daily rate that should be paid for bookings with ids 5152, 5165, 5154 and 5295. Include booking id, room type, number of occupants and the amount.罗列出预定编号为5152, 5165, 5154, 5295的预定编号，房间类型，入住人数，每日费用

   select b.booking_id, r.room_type, r.occupancy, r.amount
       from booking b, rate r
       where b.room_type_requested = r.room_type and b.occupants = r.occupancy and b.booking_id in (5152, 5165, 5154, 5295);

4. Who’s in 101? Find who is staying in room 101 on 2016-12-03, include first name, last name and address.
      查询2016年12月3日呆在101房间的顾客信息，列出first_name, last_name, 和地址

   select g.first_name, g.last_name, g.address
       from booking b, guest g
       where b.guest_id = g.id and b.room_no = 101 and '2016-12-03' between b.booking_date and DATE_ADD(b.booking_date,INTERVAL b.nights DAY);

5. How many bookings, how many nights? For guests 1185 and 1270 show the number of bookings made and the total number nights. Your output should include the guest id and the total number of bookings and the total number of nights. 查询出编号为1182或者是1270的顾客的预定单条数和总共的住宿天数

   select b.guest_id, count(*), sum(nights)
       from booking b
       where b.guest_id in (1185, 1270) group by b.guest_id;

中等难度：

• Ruth Cadbury. Show the total amount payable by guest Ruth Cadbury for her room bookings. You should JOIN to the rate table using room_type_requested and occupants. 显示Ruth Cadbury这名顾客的总的住宿费用   

1. 列出Ruth Cadbury每个预定单的每天费用和入住天数

   select r.amount, b.nights
       from booking b, guest g, rate r
       where b.room_type_requested = r.room_type and b.occupants = r.occupancy and g.id = b.guest_id and
       g.first_name = 'Ruth' and g.last_name = 'Cadbury'

2. 计算出总的费用

   select sum(r.amount * b.nights)
       from booking b, guest g, rate r
       where b.room_type_requested = r.room_type and b.occupants = r.occupancy and g.id = b.guest_id and
       g.first_name = 'Ruth' and g.last_name = 'Cadbury';

• Including Extras. Calculate the total bill for booking 5128 including extras计算预定编号为5128的总费用(住宿费用 + 额外费用)

1. 计算住宿费用

   select (r.amount * b.nights)
       from booking b, rate r
       where b.room_type_requested = r.room_type and b.occupants = r.occupancy and
       b.booking_id = 5128;

2. 计算额外的费用

    select sum(e.amount) from extra e where e.booking_id = 5128;

3. 上述两个费用相加
   

    select 
           (select (r.amount * b.nights)
           from booking b, rate r
           where b.room_type_requested = r.room_type and b.occupants = r.occupancy and
           b.booking_id = 5128
           )
       +
           (select sum(e.amount) from extra e where e.booking_id = 5128);

• Edinburgh Residents. For every guest who has the word “Edinburgh” in their address show the total number of nights booked. Be sure to include 0 for those guests who have never had a booking. Show last name, first name, address and number of nights. Order by last name then first name. 查询地址中有'Edinburgh'的顾客总住宿天数，列出顾客first_name, last_name, 地址和总天数，如果顾客没有入住，总天数则显示0

1. 查询出地址有'Edinburgh'顾客信息以及住宿天数

   select g.*, b.nights
       from booking b right join guest g
       on b.guest_id = g.id
       where g.address like '%Edinburgh%'

2. 对上表进行处理，如果nights为null，则设为0

   select g.*, case when b.nights is null then 0 else b.nights end
       from booking b right join guest g
       on b.guest_id = g.id
       where g.address like '%Edinburgh%'

3. 计算出每个顾客的总住宿天数

    select g.id, sum(case when b.nights is null then 0 else b.nights end)
       from booking b right join guest g
       on b.guest_id = g.id
       where g.address like '%Edinburgh%'
       group by g.id;

4. 与guest表连接

   select g.last_name, g.first_name, g.address, t_g.nights
       from guest g,
       (
           select g.id as id, sum(case when b.nights is null then 0 else b.nights end) as nights
           from booking b right join guest g
           on b.guest_id = g.id
           where g.address like '%Edinburgh%'
           group by g.id 
       ) t_g
       where g.id = t_g.id;

• Show the number of people arriving. For each day of the week beginning 2016-11-25 show the number of people who are arriving that day. 显示从2016年11月25日开始一周内每天到达的总人数

   select booking_date, count(b.arrival_time) from booking b where b.booking_date between '2016-11-25' and '2016-12-01' group by b.booking_date;

• How many guests? Show the number of guests in the hotel on the night of 2016-11-21. Include all those who checked in that day or before but not those who have check out on that day or before. 显示2016年11月21日住在酒店的顾客数，包括2016年11月21日当日或之前入住的顾客，不包括2016年11月21日当天或之前退房的顾客

  select sum(b.occupants)
      from booking b 
      where b.booking_date <= '2016-11-21'  and DATE_ADD(b.booking_date,INTERVAL b.nights DAY) > '2016-11-21';

挑战查询：

•  Coincidence. Have two guests with the same surname ever stayed in the hotel on the evening? Show the last name and both first names. Do not include duplicates. 查询出在旅店至少同住了一个晚上的相同last_name, 不同first_name的顾客信息，列出last_name, first_name, 去除重复的纪录

1. 首先，查询出具有相同last_name, 不同first_name的顾客信息

   select g1.last_name, g1.first_name, g2.first_name
   	from guest g1, guest g2 where g1.last_name = g2.last_name and g1.id < g2.id;

2. 查询这些顾客的入住时间和退房时间，需要连接booking表

   select g1.first_name, b1.booking_date,  DATE_ADD(b1.booking_date,INTERVAL b1.nights DAY), g2.first_name, b2.booking_date,  DATE_ADD(b2.booking_date,INTERVAL b2.nights DAY)
   	from guest g1, guest g2, booking b1, booking b2
   where g1.id = b1.guest_id and g2.id = b2.guest_id and g1.last_name = g2.last_name and g1.id < g2.id;

3. 共住同一晚的意思是，顾客1的入住时间在 顾客2的入住时间到退房时间(不包括退房时间) 或者 顾客2的入住时间在 顾客1的入住时间到退房时间(不包括退房时间)
   

   select g1.first_name, b1.booking_date,  DATE_ADD(b1.booking_date,INTERVAL b1.nights DAY), g2.first_name, b2.booking_date,  DATE_ADD(b2.booking_date,INTERVAL b2.nights DAY)
       from guest g1, guest g2, booking b1, booking b2
       where g1.id = b1.guest_id and g2.id = b2.guest_id and g1.last_name = g2.last_name and g1.id < g2.id
   and ((b1.booking_date >= b2.booking_date and b1.booking_date <  DATE_ADD(b2.booking_date,INTERVAL b2.nights DAY)) or (b2.booking_date >= b1.booking_date and b2.booking_date <  DATE_ADD(b1.booking_date,INTERVAL b1.nights DAY))) ;

4. 整理上表，去除多余信息，并加distinct
   

    select distinct g1.last_name, g1.first_name, g2.first_name
       from guest g1, guest g2, booking b1, booking b2
       where g1.id = b1.guest_id and g2.id = b2.guest_id and g1.last_name = g2.last_name and g1.id < g2.id
   and ((b1.booking_date >= b2.booking_date and b1.booking_date <  DATE_ADD(b2.booking_date,INTERVAL b2.nights DAY)) or (b2.booking_date >= b1.booking_date and b2.booking_date <  DATE_ADD(b1.booking_date,INTERVAL b1.nights DAY))) ;

• Check out per floor. The first digit of the room number indicates the floor – e.g. room 201 is on the 2nd floor. For each day of the week beginning 2016-11-14 show how many guests are checking out that day by floor number. Columns should be day (Monday, Tuesday ...), floor 1, floor 2, floor 3.查询从2016年11月14日到2016年11月20日为止每层办理退房的数量

1. 查询2016年11月14日第一层退房的数量

    select count(*) from booking b where DATE_ADD(b.booking_date,INTERVAL b.nights DAY) = i and left(b.room_no, 1) = '1';

2. 依次推算出没层的退房数量

   select i, 
       (
   	select count(*) from booking b where DATE_ADD(b.booking_date,INTERVAL b.nights DAY) = i and left(b.room_no, 1) = '1' 
       ), 
       (
   	select count(*) from booking b where DATE_ADD(b.booking_date,INTERVAL b.nights DAY) = i and left(b.room_no, 1) = '2' 
       ),
       (
   	select count(*) from booking b where DATE_ADD(b.booking_date,INTERVAL b.nights DAY) = i and left(b.room_no, 1) = '3' 
       ) 
       from calendar 
       where i between '2016-11-14' and  '2016-11-20';

• Who is in 207? Who is in room 207 during the week beginning 21st Nov. Be sure to list those days when the room is empty. Show the date and the last name. You may find the table calendar useful for this query. 从2016年11月21日起到2016年11月27日止入住顾客的last_name, 如果当天晚上没有人入住，显示NULL

1. 从2016年11月21日起到2016年11月27日止入住顾客的last_name

    select c.i, (select g.last_name from booking b, guest g where g.id = b.guest_id and b.room_no = 207 and b.booking_date <= c.i and DATE_ADD(b.booking_date,INTERVAL b.nights DAY) > c.i ) from calendar c where c.i between '2016-11-21' and '2016-11-27';

2. 对空进行处理 对上表的子查询部分，g.last_name有可能是empty set，但不是null，用ifnull无法使用，上表作为临时表，再套一次查询

   select t.out_time, ifnull(t.last_name, "NULL") from (select c.i as out_time, (select g.last_name from booking b, guest g where g.id = b.guest_id and b.room_no = 207 and b.booking_date <= c.i and DATE_ADD(b.booking_date,INTERVAL b.nights DAY) > c.i ) as last_name from calendar c where c.i between '2016-11-21' and '2016-11-27') t;--用case when 在zoo sql中能通过，猜测zoosql后台使用oracle，没有ifnull方法

• Double room for seven nights required. A customer wants a double room for 7 consecutive nights as some time between 2016-11-03 and 2016-12-19. Show the date and room number for the first such availabilities.在2016-11-03 到 2016-12-19时间内，预定double型房间七天，查询出最早预定的时间和房间号

1. 首先，创建一张临时表，记录每个日期，每个房间有没有顾客住着，有的话显示预定编号，没有的话空着外面两张表calendar和booking用交叉连接，子查询根据外面传过来的房间号，日期，显示是否有顾客住着

   select c.i as i, r.id as id ,  
       (select b.booking_id from booking b where b.room_no = r.id and b.booking_date <= c.i and DATE_ADD(b.booking_date,INTERVAL b.nights DAY) > c.i ) as booking_id 
       from calendar c, room r 
       where c.i between '2016-11-03' and '2016-12-19'

2.  上表中，如果booking_id为空，则显示0 罗列出上表信息，用IFNULL判断，如果booking_id为空，则输出0

   select t1.i as i, t1.id as id, ifnull(t1.booking_id, 0) as booking_id from (select c.i as i, r.id as id ,  (select b.booking_id from booking b where b.room_no = r.id and b.booking_date <= c.i and DATE_ADD(b.booking_date,INTERVAL b.nights DAY) > c.i ) as booking_id from calendar c, room r where c.i between '2016-11-03' and '2016-12-19') t;

3. 将上个步骤产生的表设置为temp  temp表的意思是如果指定日期，指定房间有人住着，显示预定编号，否则显示0
       当然，为方便测试，可以创建temp表，只需要在前面加入create table temp...即可

4. 两张表calendar和booking再次交叉连接,查找符合条件的记录：条件为：根据日期和房间号在 temp表中，接下来的七天，booking_no都等于0
       由于between包括了两段值，所以只需要加6天就表示一星期，all里面的各个值都是0才符合条件

    select c.i, r.id, t.booking_id
       from calendar c, room r, temp t
       where c.i = t.i and r.id = t.id and 0 = all(select t.booking_id from temp t where t.id = r.id and t.i between c.i and DATE_ADD(c.i,INTERVAL 6 DAY) ) 

5. 将temp表嵌入到D过程中，添加限制条件room_type = "double", 最后根据日期排序
   

   select c.i, r.id
       from calendar c, room r
       where r.room_type = 'double' and 0 = all(select t.booking_id from (select t1.i as i, t1.id as id, ifnull(t1.booking_id, 0) as booking_id from (select c.i as i, r.id as id ,  (select b.booking_id from booking b where b.room_no = r.id and b.booking_date <= c.i and DATE_ADD(b.booking_date,INTERVAL b.nights DAY) > c.i ) as booking_id from calendar c, room r where c.i between '2016-11-03' and '2016-12-19') t1) t where t.id = r.id and t.i between c.i and DATE_ADD(c.i,INTERVAL 6 DAY) ) and c.i between '2016-11-03' and '2016-12-13' order by c.i;

• Gross income by week. Money is collected from guests when they leave. For each Thursday in November show the total amount of money collected from the previous Friday to that day, inclusive.
      计算每周的营业额，每周四计算上周5到这周4的营业额，包括周4的营业额。列出11月份周四的日期，这周的营业额

1.  A. 列出11月份周4的日期，以及这种得预定单编号，dayofweek函数判断该日期是周几两张表连接，一张是11月份周四的日期表，另一张是预定表，限制条件，日期为周4，顾客的退房时间属于这一周 注意有些周没有预定信息，故用左连接

   	select t.i, b.booking_id, b.nights
   	from 
   	(
       	    select c.i as i from calendar c where dayofweek(c.i) = 5 and c.i between '2016-11-01' and '2016-12-29'
   	) t 
   	left join booking b on DATE_ADD(b.booking_date,INTERVAL b.nights DAY) between DATE_ADD(t.i,INTERVAL -6 DAY) and t.i

2. 连接rate表 限制条件，房间种类以及人数相同

    select t.i, b.booking_id, b.nights, r.amount
   	from 
   	(
       	select c.i as i from calendar c where dayofweek(c.i) = 5 and c.i between '2016-11-01' and '2016-12-29'
   	) t 
   	left join booking b on DATE_ADD(b.booking_date,INTERVAL b.nights DAY) between DATE_ADD(t.i,INTERVAL -6 DAY) and t.i left join rate r on b.room_type_requested = r.room_type and b.occupants = r.occupancy

3. 连接extra表，查询该预定编号的额外消费

   	select t.i, b.booking_id, b.nights, r.amount, (select sum(e.amount) from extra e where e.booking_id = b.booking_id)
   	from 
   	(
       	select c.i as i from calendar c where dayofweek(c.i) = 5 and c.i between '2016-11-01' and '2016-12-29'
   	) t 
   	left join booking b on DATE_ADD(b.booking_date,INTERVAL b.nights DAY) between DATE_ADD(t.i,INTERVAL -6 DAY) and t.i left join rate r on b.room_type_requested = r.room_type and b.occupants = r.occupancy

4. 计算总结果对日期分组，注意，额外消费可能为空，故使用ifnull

   select t.i, sum(b.nights * r.amount + ifnull((select sum(e.amount) from extra e where e.booking_id = b.booking_id), 0))
   	from 
   	(
   	    select c.i as i from calendar c where dayofweek(c.i) = 5 and c.i between '2016-11-01' and '2016-12-29'
   	) t 
   	left join booking b on DATE_ADD(b.booking_date,INTERVAL b.nights DAY) between DATE_ADD(t.i,INTERVAL -6 DAY) and t.i left join rate r on b.room_type_requested = r.room_type and b.occupants = r.occupancy group by t.i
   

   
   数据库导入文件下载地址：https://download.youkuaiyun.com/download/hielw/10392367