Difference between revisions of "Guest House Assessment Hard"

From SQLZOO
Jump to: navigation, search
(Created page with "Guest House Assessment Easy | Guest House Assessment Medium | Guest House Assessment Hard <span id=startAt class=params style='display:none'>11</span> <div class=...")
 
Line 42: Line 42:
 
</div>
 
</div>
  
 
 
 
 
 
<div class='qu'>
 
<p class=imper'>
 
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.
 
</p>
 
<source lang='sql' class='def'>
 
</source>
 
<pre class=output>
 
+-----------+------------+---------------------------+--------+
 
| 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 |
 
+-----------+------------+---------------------------+--------+
 
</pre>
 
</div>
 
  
 
<div class='qu'>
 
<div class='qu'>

Revision as of 22:48, 21 November 2016

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

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 | first_name  |
+-----------+------------+-------------+
| McDonnell | John       | Dr Alasdair |
| Johnson   | Diana      | Gareth      |
| Jones     | Susan Elan | Mr Marcus   |
| Howarth   | Mr George  | Sir Gerald  |
| Davies    | Philip     | David T. C. |
| Lewis     | Clive      | Dr Julian   |
| Evans     | Graham     | Mr Nigel    |
+-----------+------------+-------------+


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.

+------------+-----+-----+-----+
| i          | 1st | 2nd | 3rd |
+------------+-----+-----+-----+
| 2016-11-14 |   5 |   3 |   4 |
| 2016-11-15 |   6 |   4 |   1 |
| 2016-11-16 |   2 |   2 |   4 |
| 2016-11-17 |   5 |   3 |   6 |
| 2016-11-18 |   2 |   3 |   2 |
| 2016-11-19 |   5 |   5 |   1 |
| 2016-11-20 |   2 |   2 |   2 |
+------------+-----+-----+-----+


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.

+------------+-----------+
| i          | last_name |
+------------+-----------+
| 2016-11-21 | McDonnell |
| 2016-11-22 | McDonnell |
| 2016-11-23 | Whitford  |
| 2016-11-24 | Whitford  |
| 2016-11-25 | NULL      |
| 2016-11-26 | NULL      |
| 2016-11-27 | Berry     |
+------------+-----------+

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.

 +-----+------------+
| id  | MIN(i)     |
+-----+------------+
| 201 | 2016-12-11 |
| 301 | 2016-12-13 |
| 101 | 2016-12-18 |
+-----+------------+


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.

+------------+----------+
| next_thur  | SUM(mi)  |
+------------+----------+
| 2016-11-03 |     NULL |
| 2016-11-10 | 12608.94 |
| 2016-11-17 | 13552.56 |
| 2016-11-24 | 13490.69 |
| 2016-12-01 | 12125.14 |
| 2016-12-08 | 13093.79 |
| 2016-12-15 |  8975.87 |
| 2016-12-22 |  1395.77 |
| 2016-12-29 |     NULL |
+------------+----------+

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