Difference between revisions of "Guest House Assessment Easy"

From SQLZOO
Jump to: navigation, search
(Created page with "Easy Assessment Questions <div class='qu'> <p class=imper'>Guest 1183. Give the booking_date and the number of nights for guest 1183.</p> <source lang='sql' class='def'> SELEC...")
 
Line 7: Line 7:
 
  WHERE room_no=101 AND booking_date='2016-11-17'
 
  WHERE room_no=101 AND booking_date='2016-11-17'
 
</source>
 
</source>
<source class='ans'>
+
<pre class=output>
SELECT booking_date, nights
+
+--------------+--------+
FROM booking
+
| booking_date | nights |
WHERE guest_id=1183;
+
+--------------+--------+
</source>
+
| 2016-11-27  |      5 |
 +
+--------------+--------+
 +
</pre>
 
</div>
 
</div>
  
Line 18: Line 20:
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
<source class='ans'>
+
<pre class=output>
SELECT arrival_time,first_name,last_name  
+
+--------------+------------+-----------+
FROM booking JOIN guest ON booking.guest_id=guest.id
+
| arrival_time | first_name | last_name |
WHERE booking_date='2016-11-05'
+
+--------------+------------+-----------+
ORDER BY arrival_time;
+
| 14:00        | Lisa      | Nandy    |
</source>
+
| 15:00        | Jack      | Dromey    |
</div>
+
| 16:00        | Mr Andrew  | Tyrie    |
 
+
| 21:00        | James      | Heappey  |
<div class='qu'>
+
| 22:00        | Justin    | Tomlinson |
<p class=imper'>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.</p>
+
+--------------+------------+-----------+
<source lang='sql' class='def'>
+
</pre>
</source>
 
<source class='ans'>
 
SELECT booking_id,room_type_requested,occupants,amount
 
FROM booking JOIN rate ON (room_type_requested = room_type AND occupants=occupancy)
 
WHERE booking_id IN (5152,5165,5154, 5295);
 
</source>
 
 
</div>
 
</div>
  
Line 41: Line 37:
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
<source class='ans'>
+
<pre class=output>
SELECT first_name, last_name,address
+
+------------+---------------------+-----------+--------+
FROM booking JOIN guest ON booking.guest_id=guest.id
+
| booking_id | room_type_requested | occupants | amount |
WHERE booking_date = '2016-12-03' AND room_no=101;
+
+------------+---------------------+-----------+--------+
</source>
+
|      5152 | double              |        2 |  72.00 |
 +
|      5154 | double              |        1 |  56.00 |
 +
|      5295 | family              |        3 |  84.00 |
 +
+------------+---------------------+-----------+--------+
 +
</pre>
 
</div>
 
</div>
  
Line 51: Line 51:
 
<p class=imper'>Who’s in 101? Find who is staying in room 101 on 2016-12-03, include first name, last name and address.
 
<p class=imper'>Who’s in 101? Find who is staying in room 101 on 2016-12-03, include first name, last name and address.
 
</p>
 
</p>
<source lang='sql' class='def'>
+
<pre class=output>
</source>
+
+------------+-----------+-------------+
<source class='ans'>
+
| first_name | last_name | address    |
</source>
+
+------------+-----------+-------------+
 +
| Graham    | Evans    | Weaver Vale |
 +
+------------+-----------+-------------+
 +
</pre>
 
</div>
 
</div>
  
Line 62: Line 65:
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
<source class='ans'>
+
<pre class=output>
SELECT guest_id, COUNT(nights), SUM(nights)  
+
+----------+---------------+-------------+
FROM booking
+
| guest_id | COUNT(nights) | SUM(nights) |
WHERE guest_id IN (1185,1270)
+
+----------+---------------+-------------+
GROUP BY guest_id;
+
|    1185 |            3 |          8 |
</source>
+
|    1270 |            2 |          3 |
 +
+----------+---------------+-------------+
 +
</pre>
 
</div>
 
</div>

Revision as of 22:16, 21 November 2016

Easy Assessment Questions

Guest 1183. Give the booking_date and the number of nights for guest 1183.

SELECT first_name,last_name
FROM booking JOIN guest ON guest_id = guest.id
 WHERE room_no=101 AND booking_date='2016-11-17'
+--------------+--------+
| booking_date | nights |
+--------------+--------+
| 2016-11-27   |      5 |
+--------------+--------+

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.

+--------------+------------+-----------+
| arrival_time | first_name | last_name |
+--------------+------------+-----------+
| 14:00        | Lisa       | Nandy     |
| 15:00        | Jack       | Dromey    |
| 16:00        | Mr Andrew  | Tyrie     |
| 21:00        | James      | Heappey   |
| 22:00        | Justin     | Tomlinson |
+--------------+------------+-----------+

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.

+------------+---------------------+-----------+--------+
| booking_id | room_type_requested | occupants | amount |
+------------+---------------------+-----------+--------+
|       5152 | double              |         2 |  72.00 |
|       5154 | double              |         1 |  56.00 |
|       5295 | family              |         3 |  84.00 |
+------------+---------------------+-----------+--------+

Who’s in 101? Find who is staying in room 101 on 2016-12-03, include first name, last name and address.

+------------+-----------+-------------+
| first_name | last_name | address     |
+------------+-----------+-------------+
| Graham     | Evans     | Weaver Vale |
+------------+-----------+-------------+

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.

+----------+---------------+-------------+
| guest_id | COUNT(nights) | SUM(nights) |
+----------+---------------+-------------+
|     1185 |             3 |           8 |
|     1270 |             2 |           3 |
+----------+---------------+-------------+