Work out the start times of bookings for tennis courts
Question
How can you produce a list of the start times for bookings for tennis courts, for the date '2012-09-21'? Return a list of start time and facility name pairings, ordered by the time.
Expected Results
| start | name |
|---|---|
| Fri Sep 21 2012 08:00:00 GMT+0000 (Coordinated Universal Time) | Tennis Court 1 |
| Fri Sep 21 2012 08:00:00 GMT+0000 (Coordinated Universal Time) | Tennis Court 2 |
| Fri Sep 21 2012 09:30:00 GMT+0000 (Coordinated Universal Time) | Tennis Court 1 |
| Fri Sep 21 2012 10:00:00 GMT+0000 (Coordinated Universal Time) | Tennis Court 2 |
| Fri Sep 21 2012 11:30:00 GMT+0000 (Coordinated Universal Time) | Tennis Court 2 |
| Fri Sep 21 2012 12:00:00 GMT+0000 (Coordinated Universal Time) | Tennis Court 1 |
| Fri Sep 21 2012 13:30:00 GMT+0000 (Coordinated Universal Time) | Tennis Court 1 |
| Fri Sep 21 2012 14:00:00 GMT+0000 (Coordinated Universal Time) | Tennis Court 2 |
| Fri Sep 21 2012 15:30:00 GMT+0000 (Coordinated Universal Time) | Tennis Court 1 |
| Fri Sep 21 2012 16:00:00 GMT+0000 (Coordinated Universal Time) | Tennis Court 2 |
| Fri Sep 21 2012 17:00:00 GMT+0000 (Coordinated Universal Time) | Tennis Court 1 |
| Fri Sep 21 2012 18:00:00 GMT+0000 (Coordinated Universal Time) | Tennis Court 2 |
Your Answer
Your Results
Loading database...
Answers and Discussion
select bks.starttime as start, facs.name as name
from
cd.facilities facs
inner join cd.bookings bks
on facs.facid = bks.facid
where
facs.name in ('Tennis Court 2','Tennis Court 1') and
bks.starttime >= '2012-09-21' and
bks.starttime < '2012-09-22'
order by bks.starttime;This is another INNER JOIN query, although it has a fair bit more complexity in it! The FROM part of the query is easy - we're simply joining facilities and bookings tables together on the facid. This produces a table where, for each row in bookings, we've attached detailed information about the facility being booked.
On to the WHERE component of the query. The checks on starttime are fairly self explanatory - we're making sure that all the bookings start between the specified dates. Since we're only interested in tennis courts, we're also using the IN operator to tell the database system to only give us back facility IDs 0 or 1 - the IDs of the courts. There's other ways to express this: We could have used where facs.facid = 0 or facs.facid = 1, or even where facs.name like 'Tennis%'.
The rest is pretty simple: we SELECT the columns we're interested in, and ORDER BY the start time.