Difference between revisions of "Guest House Assessment Hard"

From SQLZOO
Jump to navigation Jump to search
Line 81: Line 81:
<div class='qu'>
<div class='qu'>
<p class=imper'>
<p class=imper'>
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.
Gross income by week. Money is collected from guests when they leave. For each Thursday in November and December 2016, show the total amount of money collected from the previous Friday to that day, inclusive.
</p>
</p>
<source lang='sql' class='def'>
<source lang='sql' class='def'>
</source>
</source>
<pre class=output>
<pre class=output>
+------------+----------+
+------------+---------------+
| next_thur  | SUM(mi)  |
| Thursday  | weekly_income |
+------------+----------+
+------------+---------------+
| 2016-11-03 |     NULL |
| 2016-11-03 |         0.00 |
| 2016-11-10 | 12608.94 |
| 2016-11-10 |     12608.94 |
| 2016-11-17 | 13552.56 |
| 2016-11-17 |     13552.56 |
| 2016-11-24 | 13490.69 |
| 2016-11-24 |     12929.69 |
| 2016-12-01 | 12125.14 |
| 2016-12-01 |     11685.14 |
| 2016-12-08 | 13093.79 |
| 2016-12-08 |     13093.79 |
| 2016-12-15 | 8975.87 |
| 2016-12-15 |       8975.87 |
| 2016-12-22 | 1395.77 |
| 2016-12-22 |       1395.77 |
| 2016-12-29 |     NULL |
| 2016-12-29 |         0.00 |
+------------+----------+
| 2017-01-05 |          0.00 |
+------------+---------------+
</pre>
</pre>
</div>
</div>


[[Guest House|Database Description]] | [[Guest House Assessment Easy|Easy Problems]] | [[Guest House Assessment Medium|Medium Problems]] | [[Guest House Assessment Hard|Hard Problems]]
[[Guest House|Database Description]] | [[Guest House Assessment Easy|Easy Problems]] | [[Guest House Assessment Medium|Medium Problems]] | [[Guest House Assessment Hard|Hard Problems]]

Revision as of 15:38, 25 October 2018

Database Description | Easy Problems | Medium Problems | Hard Problems

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


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 rooms are being vacated that day by floor number. Show all days in the correct order.

+------------+-----+-----+-----+
| 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 |
+------------+-----+-----+-----+


Free rooms? List the rooms that are free on the day 25th Nov 2016.

+-----+
| id  |
+-----+
| 207 |
| 210 |
| 304 |
+-----+

Single room for three nights required. A customer wants a single room for three consecutive nights. Find the first available date in December.

+-----+------------+
| id  | MIN(i)     |
+-----+------------+
| 201 | 2016-12-11 |
+-----+------------+


Gross income by week. Money is collected from guests when they leave. For each Thursday in November and December 2016, show the total amount of money collected from the previous Friday to that day, inclusive.

+------------+---------------+
| Thursday   | weekly_income |
+------------+---------------+
| 2016-11-03 |          0.00 |
| 2016-11-10 |      12608.94 |
| 2016-11-17 |      13552.56 |
| 2016-11-24 |      12929.69 |
| 2016-12-01 |      11685.14 |
| 2016-12-08 |      13093.79 |
| 2016-12-15 |       8975.87 |
| 2016-12-22 |       1395.77 |
| 2016-12-29 |          0.00 |
| 2017-01-05 |          0.00 |
+------------+---------------+

Database Description | Easy Problems | Medium Problems | Hard Problems