Problem: How can you retrieve all the information from the cd.facilities table?
-- Retrieve all the information from the cd.facilities table?
SELECT *
FROM cd.facilities;## facid name membercost guestcost initialoutlay monthlymaintenance
## 1 0 Tennis Court 1 5.0 25.0 10000 200
## 2 1 Tennis Court 2 5.0 25.0 8000 200
## 3 2 Badminton Court 0.0 15.5 4000 50
## 4 3 Table Tennis 0.0 5.0 320 10
## 5 4 Massage Room 1 35.0 80.0 4000 3000
## 6 5 Massage Room 2 35.0 80.0 4000 3000
## 7 6 Squash Court 3.5 17.5 5000 80
## 8 7 Snooker Table 0.0 5.0 450 15
## 9 8 Pool Table 0.0 5.0 400 15Problem: 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?
-- Retrieve a list of only facility names and costs?
SELECT name, membercost
FROM cd.facilities;## name membercost
## 1 Tennis Court 1 5.0
## 2 Tennis Court 2 5.0
## 3 Badminton Court 0.0
## 4 Table Tennis 0.0
## 5 Massage Room 1 35.0
## 6 Massage Room 2 35.0
## 7 Squash Court 3.5
## 8 Snooker Table 0.0
## 9 Pool Table 0.0Problem: How can you produce a list of facilities that charge a fee to members?
-- Produce a list of facilities that charge a fee to members
SELECT name, membercost
FROM cd.facilities
WHERE membercost > 0;## name membercost
## 1 Tennis Court 1 5.0
## 2 Tennis Court 2 5.0
## 3 Massage Room 1 35.0
## 4 Massage Room 2 35.0
## 5 Squash Court 3.5Problem: 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.
-- 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, monthlymaintenance
FROM cd.facilities
WHERE membercost > 0 AND membercost < monthlymaintenance/50;## facid name membercost monthlymaintenance
## 1 4 Massage Room 1 35 3000
## 2 5 Massage Room 2 35 3000Problem: How can you produce a list of all facilities with the word ‘Tennis’ in their name?
-- Produce a list of all facilities with the word 'Tennis' in their name
SELECT name
FROM cd.facilities
WHERE name LIKE '%Tennis%';## name
## 1 Tennis Court 1
## 2 Tennis Court 2
## 3 Table TennisProblem: How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.
-- Retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.
SELECT facid, name
FROM cd.facilities
WHERE facid IN (1,5);## facid name
## 1 1 Tennis Court 2
## 2 5 Massage Room 2Problem: 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.
-- 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 *
FROM cd.members
WHERE joindate >= '2012-09-01';## NULLProblem: How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.
-- 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 surname ASC
LIMIT 10;## surname
## 1 Bader
## 2 Baker
## 3 Boothe
## 4 Butters
## 5 Coplin
## 6 Crumpet
## 7 Dare
## 8 Farrell
## 9 Genting
## 10 GUESTProblem: You’d like to get the signup date of your last member. How can you retrieve this information?
-- You'd like to get the signup date of your last member. How can you retrieve this information?
SELECT max(joindate)
FROM cd.members;## max
## 1 2012-09-26 18:08:45Problem: Produce a count of the number of facilities that have a cost to guests of 10 or more.
-- Produce a count of the number of facilities that have a cost to guests of 10 or more.
SELECT COUNT(*)
FROM cd.facilities
WHERE guestcost >= 10;## count
## 1 6Problem: 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.
-- 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) AS Total_Number_of_Slots
FROM cd.bookings
WHERE cd.bookings.starttime BETWEEN '2012-09-01' AND '2012-09-30'
GROUP by facid
ORDER BY SUM(slots);## facid total_number_of_slots
## 1 5 118
## 2 3 410
## 3 7 412
## 4 8 453
## 5 6 522
## 6 2 546
## 7 0 567
## 8 1 567
## 9 4 628Problem: 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.
-- 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) AS Total_Number_of_Slots
FROM cd.bookings
GROUP BY facid
HAVING SUM(slots) > 1000
ORDER BY facid;## facid total_number_of_slots
## 1 0 1320
## 2 1 1278
## 3 2 1209
## 4 4 1404
## 5 6 1104Problem: 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.
-- 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 starttime, name
FROM cd.bookings
INNER JOIN cd.facilities ON cd.facilities.facid = cd.bookings.facid
WHERE name LIKE '%Tennis Court%' AND starttime BETWEEN '2012-09-21 00:00:00' AND '2012-09-21 23:59:59'
ORDER BY starttime;## starttime name
## 1 2012-09-21 08:00:00 Tennis Court 1
## 2 2012-09-21 08:00:00 Tennis Court 2
## 3 2012-09-21 09:30:00 Tennis Court 1
## 4 2012-09-21 10:00:00 Tennis Court 2
## 5 2012-09-21 11:30:00 Tennis Court 2
## 6 2012-09-21 12:00:00 Tennis Court 1
## 7 2012-09-21 13:30:00 Tennis Court 1
## 8 2012-09-21 14:00:00 Tennis Court 2
## 9 2012-09-21 15:30:00 Tennis Court 1
## 10 2012-09-21 16:00:00 Tennis Court 2
## 11 2012-09-21 17:00:00 Tennis Court 1
## 12 2012-09-21 18:00:00 Tennis Court 2Problem: How can you produce a list of the start times for bookings by members named ‘David Farrell’?
-- Produce a list of the start times for bookings by members named 'David Farrell'?
SELECT starttime
FROM cd.bookings
INNER JOIN cd.members ON cd.members.memid = cd.bookings.memid
WHERE surname LIKE '%Farrell%' AND firstname LIKE '%David%'
ORDER BY starttime;## starttime
## 1 2012-09-18 09:00:00
## 2 2012-09-18 13:30:00
## 3 2012-09-18 17:30:00
## 4 2012-09-18 20:00:00
## 5 2012-09-19 09:30:00
## 6 2012-09-19 12:00:00
## 7 2012-09-19 15:00:00
## 8 2012-09-20 11:30:00
## 9 2012-09-20 14:00:00
## 10 2012-09-20 15:30:00
## 11 2012-09-21 10:30:00
## 12 2012-09-21 14:00:00
## 13 2012-09-22 08:30:00
## 14 2012-09-22 17:00:00
## 15 2012-09-23 08:30:00
## 16 2012-09-23 17:30:00
## 17 2012-09-23 19:00:00
## 18 2012-09-24 08:00:00
## 19 2012-09-24 12:30:00
## 20 2012-09-24 16:30:00
## 21 2012-09-25 15:30:00
## 22 2012-09-25 17:00:00
## 23 2012-09-26 13:00:00
## 24 2012-09-26 17:00:00
## 25 2012-09-27 08:00:00
## 26 2012-09-28 09:30:00
## 27 2012-09-28 11:30:00
## 28 2012-09-28 13:00:00
## 29 2012-09-29 10:30:00
## 30 2012-09-29 13:30:00
## 31 2012-09-29 14:30:00
## 32 2012-09-29 16:00:00
## 33 2012-09-29 17:30:00
## 34 2012-09-30 14:30:00