The CD club is a sports club that offers various facilities such as tennis courts, swimming pools, and gym equipment to its members. The club charges a fee to its members and guests for the use of these facilities. To manage the operations of the club, the club maintains a database that stores information about the members, facilities, and bookings.
Using SQL queries, the club can extract useful insights from the database, such as the number of facilities that charge a fee to members, the list of facilities that charge a fee to members, and the list of facilities that have more than 1000 slots booked.
You want to print out a list of all of the facilities and their cost to members. How would you retrieve a list of only facility names and costs?
SELECT name, membercost
FROM cd.facilities
How can you produce a list of facilities that charge a fee to members?
SELECT * FROM cd.facilities
WHERE membercost <> 0
How can you produce a list of facilities that charge a fee to members, and that fee is less than 1/50th of the monthly maintenance cost? Return the facid, facility name, member cost, and monthly maintenance of the facilities in question.
SELECT facid, name, membercost
FROM cd.facilities
WHERE membercost < (0.02 * monthlymaintenance)
AND membercost > 0
How can you produce a list of all facilities with the word ‘Tennis’ in their name?
SELECT * FROM cd.facilities
WHERE name LIKE ‘%Tennis%’
How can you retrieve the details of facilities with ID 1 and 5? without using the OR operator
SELECT * FROM cd.facilities
WHERE facid IN (1, 5)
How can you produce a list of members who joined after the start of September 2012? Return the memid, surname, firstname, and joindate of the members in question.
SELECT memid, surname, firstname, joindate
FROM cd.members
WHERE joindate > ‘2012-09-01’
How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.
SELECT DISTINCT surname
FROM cd.members
ORDER BY 1
LIMIT 10
You’d like to get the signup date of your last member. How can you retrieve this information?
SELECT Max (joindate) latest_signup_date
FROM cd.members
Produce a count of the number of facilities that have a cost to guests of 10 or more.
SELECT COUNT(*) Guestcost_count
FROM cd.facilities
WHERE guestcost >= 10
Produce a list of the total number of slots booked per facility in the month of September 2012. Produce an output table consisting of facility id and slots, sorted by the number of slots.
SELECT facid, SUM(slots) total_slots
FROM cd.bookings
WHERE starttime >= ‘2012-09-01’
AND starttime < ‘2012-10-01’
GROUP BY facid
ORDER BY 1
Produce a list of facilities with more than 1000 slots booked. Produce an output table consisting of facility id and total slots, sorted by facility id.
SELECT facid, SUM(Slots) total_slots
FROM cd.bookings
GROUP BY facid v HAVING SUM(Slots) > 1000
ORDER BY 1
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.
SELECT starttime AS start, name
FROM cd.bookings b, cd.facilities f
WHERE b.facid = f.facid
AND f.name ILIKE ‘tennis%’
AND b.starttime >= ‘2012-09-21’
AND b.starttime < ‘2012-09-22’
** OR**
SELECT starttime AS start, name
FROM cd.bookings
INNER JOIN cd.facilities
ON cd.facilities.facid = cd.bookings.facid
WHERE name LIKE ‘Tennis%’
AND cd.bookings.starttime >= ‘2012-09-21’
AND cd.bookings.starttime < ‘2012-09-22’
How can you produce a list of the start times for bookings by members named ‘David Farrell’?
SELECT starttime
FROM cd.members
INNER JOIN cd.bookings
ON cd.bookings.memid = cd.members.memid
WHERE firstname LIKE ‘David’
AND surname LIKE ‘Farrell’