Produce a list of all members who have used a tennis court
Question
How can you produce a list of all members who have used a tennis court? Include in your output the name of the court, and the name of the member formatted as a single column. Ensure no duplicate data, and order by the member name followed by the facility name.
Your Answer
Answers and Discussion Show
select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility
from
cd.members mems
inner join cd.bookings bks
on mems.memid = bks.memid
inner join cd.facilities facs
on bks.facid = facs.facid
where
facs.name in ('Tennis Court 2','Tennis Court 1')
order by member, facility
This exercise is largely a more complex application of what you've learned in prior questions. It's also the first time we've used more than one join, which may be a little confusing for some. When reading join expressions, remember that a join is effectively a function that takes two tables, one labelled the left table, and the other the right. This is easy to visualise with just one join in the query, but a little more confusing with two.
Our second INNER JOIN in this query has a right hand side of cd.facilities. That's easy enough to grasp. The left hand side, however, is the table returned by joining cd.members to cd.bookings. It's important to emphasise this: the relational model is all about tables. The output of any join is another table. The output of a query is a table. Single columned lists are tables. Once you grasp that, you've grasped the fundamental beauty of the model.
As a final note, we do introduce one new thing here: the || operator is used to concatenate strings.
Keyboard shortcuts:
- Alt-h: Show/Hide Help menu
- Alt-r: Run query
- Alt-x: Run selected text as query
- Alt-s: Run query by cursor (delimited by whitespace/semi-colon)
Other hints:
- You can double click on each of the panes of Expected Result/Your answer to quickly resize them.
- If you have trouble remembering the database schema, you can leave this popup open while you work on your answer.
- Don't forget to use the hint button if you're stuck!