Difference between revisions of "Guest House Assessment Medium"

From SQLZOO
Jump to: navigation, search
Line 21: Line 21:
 
<div class='qu'>
 
<div class='qu'>
 
<p class=imper'>
 
<p class=imper'>
</p>
+
Including Extras. Calculate the total bill for booking 5128 including extras.</p>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>

Revision as of 22:33, 21 November 2016

Guest House Assessment Easy | Guest House Assessment Medium | Guest House Assessment Hard 6

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.

SELECT first_name,last_name
FROM booking JOIN guest ON guest_id = guest.id
 WHERE room_no=101 AND booking_date='2016-11-17'
+--------------------+
| SUM(nights*amount) |
+--------------------+
|             552.00 |
+--------------------+

Including Extras. Calculate the total bill for booking 5128 including extras.

+-------------+
| SUM(amount) |
+-------------+
|      118.56 |
+-------------+

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.

+-----------+------------+---------------------------+--------+
| last_name | first_name | address                   | nights |
+-----------+------------+---------------------------+--------+
| Brock     | Deidre     | Edinburgh North and Leith |      0 |
| Cherry    | Joanna     | Edinburgh South West      |      0 |
| Murray    | Ian        | Edinburgh South           |     13 |
| Sheppard  | Tommy      | Edinburgh East            |      0 |
| Thomson   | Michelle   | Edinburgh West            |      3 |
+-----------+------------+---------------------------+--------+

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.

+------------+----------+
| i          | arrivals |
+------------+----------+
| 2016-11-25 |        7 |
| 2016-11-26 |        8 |
| 2016-11-27 |       12 |
| 2016-11-28 |        7 |
| 2016-11-29 |       13 |
| 2016-11-30 |        6 |
| 2016-12-01 |        7 |
+------------+----------+

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.

+----------------+
| SUM(occupants) |
+----------------+
|             41 |
+----------------+

Guest House Assessment Easy | Guest House Assessment Medium | Guest House Assessment Hard