Antes de mostrar la solución a los ejercicios, se creó una base de datos usando Docker, luego, usando la herramienta DBeaver se crearon las tablas tal como lo indica la documentación de los ejercicios y añadieron los respectivos datos.
A continuación se presenta la solución a los ejercicios propuestos de PostgreSQL Exercises.
Iniciemos conectando la base de datos previamente creada con R, para esto necesitamos las siguientes librerías.
library(DBI)## Warning: package 'DBI' was built under R version 4.3.3library(RPostgreSQL)## Warning: package 'RPostgreSQL' was built under R version 4.3.3Ahora, realizamos la conexión con la base de datos.
con <- dbConnect(RPostgres::Postgres(),
dbname = "datos",
host = "localhost",
port = 5432,
user = "usuariosql",
password = "passwordsql")
dbListTables(conn = con)## [1] "members" "facilities" "bookings"El código anterior indica que la base de datos a la que nos conectamos se llama ‘datos’ y tiene 3 tablas estas son: ‘members’, ‘facilities’ y ‘bookings’.
Ahora, inicimiemos con las consultas:
How can you retrieve all the information from the facilities table?
dbGetQuery(con, "SELECT * FROM facilities");## facid name membercost guestcost initialoutlay monthlymaintenance
## 1 2 Badminton Court 0.0 15.5 4000 50
## 2 3 Table Tennis 0.0 5.0 320 10
## 3 4 Massage Room 1 35.0 80.0 4000 3000
## 4 5 Massage Room 2 35.0 80.0 4000 3000
## 5 6 Squash Court 3.5 17.5 5000 80
## 6 7 Snooker Table 0.0 5.0 450 15
## 7 8 Pool Table 0.0 5.0 400 15
## 8 9 Spa 20.0 30.0 100000 800
## 9 0 Tennis Court 1 6.0 30.0 10000 200
## 10 1 Tennis Court 2 6.6 33.0 10000 200You 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?
dbGetQuery(con, "SELECT name, membercost FROM facilities");## name membercost
## 1 Badminton Court 0.0
## 2 Table Tennis 0.0
## 3 Massage Room 1 35.0
## 4 Massage Room 2 35.0
## 5 Squash Court 3.5
## 6 Snooker Table 0.0
## 7 Pool Table 0.0
## 8 Spa 20.0
## 9 Tennis Court 1 6.0
## 10 Tennis Court 2 6.6How can you produce a list of facilities that charge a fee to members?
dbGetQuery(con, "SELECT * FROM facilities WHERE membercost > 0");## facid name membercost guestcost initialoutlay monthlymaintenance
## 1 4 Massage Room 1 35.0 80.0 4e+03 3000
## 2 5 Massage Room 2 35.0 80.0 4e+03 3000
## 3 6 Squash Court 3.5 17.5 5e+03 80
## 4 9 Spa 20.0 30.0 1e+05 800
## 5 0 Tennis Court 1 6.0 30.0 1e+04 200
## 6 1 Tennis Court 2 6.6 33.0 1e+04 200How 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.
dbGetQuery(con, "SELECT facid, name, membercost, monthlymaintenance
FROM facilities
WHERE
membercost > 0 and
(membercost < monthlymaintenance/50.0) ")## facid name membercost monthlymaintenance
## 1 4 Massage Room 1 35 3000
## 2 5 Massage Room 2 35 3000How can you produce a list of all facilities with the word ‘Tennis’ in their name?
dbGetQuery(con, "SELECT * FROM facilities WHERE name like '%Tennis%' ")## facid name membercost guestcost initialoutlay monthlymaintenance
## 1 3 Table Tennis 0.0 5 320 10
## 2 0 Tennis Court 1 6.0 30 10000 200
## 3 1 Tennis Court 2 6.6 33 10000 200How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.
dbGetQuery(con, "SELECT * FROM facilities
WHERE facid IN(1, 5) ")## facid name membercost guestcost initialoutlay monthlymaintenance
## 1 5 Massage Room 2 35.0 80 4000 3000
## 2 1 Tennis Court 2 6.6 33 10000 200How can you produce a list of facilities, with each labelled as ‘cheap’ or ‘expensive’ depending on if their monthly maintenance cost is more than $100? Return the name and monthly maintenance of the facilities in question.
dbGetQuery(con, "SELECT name,
CASE WHEN (monthlymaintenance > 100) THEN
'expensive'
ELSE
'cheap'
END AS cost
FROM facilities; ")## name cost
## 1 Badminton Court cheap
## 2 Table Tennis cheap
## 3 Massage Room 1 expensive
## 4 Massage Room 2 expensive
## 5 Squash Court cheap
## 6 Snooker Table cheap
## 7 Pool Table cheap
## 8 Spa expensive
## 9 Tennis Court 1 expensive
## 10 Tennis Court 2 expensiveHow 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.
dbGetQuery(con, "SELECT memid, surname, firstname, joindate FROM members
WHERE joindate > '2012-09-01' ")## memid surname firstname joindate
## 1 24 Sarwin Ramnaresh 2012-09-01 08:44:42
## 2 26 Jones Douglas 2012-09-02 18:43:05
## 3 27 Rumney Henrietta 2012-09-05 08:42:35
## 4 28 Farrell David 2012-09-15 08:22:05
## 5 29 Worthington-Smyth Henry 2012-09-17 12:27:15
## 6 30 Purview Millicent 2012-09-18 19:04:01
## 7 33 Tupperware Hyacinth 2012-09-18 19:32:05
## 8 35 Hunt John 2012-09-19 11:32:45
## 9 36 Crumpet Erica 2012-09-22 08:36:38
## 10 37 Smith Darren 2012-09-26 18:08:45How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.
dbGetQuery(con, "SELECT DISTINCT surname FROM members ORDER BY surname limit 10 ")## surname
## 1 Bader
## 2 Baker
## 3 Boothe
## 4 Butters
## 5 Coplin
## 6 Crumpet
## 7 Dare
## 8 Farrell
## 9 Genting
## 10 GUESTYou, for some reason, want a combined list of all surnames and all facility names. Yes, this is a contrived example :-). Produce that list!
dbGetQuery(con, "SELECT surname FROM members
UNION SELECT name FROM facilities; ")## surname
## 1 Hunt
## 2 Farrell
## 3 Tennis Court 2
## 4 Table Tennis
## 5 Dare
## 6 Rownam
## 7 GUEST
## 8 Badminton Court
## 9 Smith
## 10 Tupperware
## 11 Owen
## 12 Worthington-Smyth
## 13 Butters
## 14 Rumney
## 15 Tracy
## 16 Crumpet
## 17 Purview
## 18 Massage Room 2
## 19 Sarwin
## 20 Baker
## 21 Pool Table
## 22 Spa
## 23 Snooker Table
## 24 Jones
## 25 Coplin
## 26 Mackenzie
## 27 Boothe
## 28 Joplette
## 29 Stibbons
## 30 Tennis Court 1
## 31 Squash Court
## 32 Pinker
## 33 Genting
## 34 Bader
## 35 Massage Room 1You’d like to get the signup date of your last member. How can you retrieve this information?
dbGetQuery(con, "SELECT MAX(joindate) AS latest FROM members ")## latest
## 1 2012-09-26 18:08:45You’d like to get the first and last name of the last member(s) who signed up - not just the date. How can you do that?
dbGetQuery(con, "SELECT firstname, surname, joindate FROM members
WHERE joindate = (SELECT MAX(joindate) FROM members)")## firstname surname joindate
## 1 Darren Smith 2012-09-26 18:08:45How can you produce a list of the start times for bookings by members named ‘David Farrell’?
dbGetQuery(con, "SELECT b.starttime FROM bookings b
JOIN members m
ON m.memid = b.memid
WHERE m.firstname = 'David' AND m.surname = 'Farrell'")## starttime
## 1 2012-09-18 09:00:00
## 2 2012-09-18 17:30:00
## 3 2012-09-18 13:30:00
## 4 2012-09-18 20:00:00
## 5 2012-09-19 09:30:00
## 6 2012-09-19 15:00:00
## 7 2012-09-19 12:00:00
## 8 2012-09-20 15:30:00
## 9 2012-09-20 11:30:00
## 10 2012-09-20 14:00: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 16:30:00
## 20 2012-09-24 12: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 11:30:00
## 27 2012-09-28 09:30:00
## 28 2012-09-28 13:00:00
## 29 2012-09-29 16:00:00
## 30 2012-09-29 10:30:00
## 31 2012-09-29 13:30:00
## 32 2012-09-29 14:30:00
## 33 2012-09-29 17:30:00
## 34 2012-09-30 14:30:00How 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.
dbGetQuery(con, "SELECT b.starttime AS start, f.name AS name
FROM facilities f JOIN bookings b
ON f.facid = b.facid
WHERE
f.name IN ('Tennis Court 2','Tennis Court 1') AND
b.starttime >= '2012-09-21' AND
b.starttime < '2012-09-22'
ORDER BY b.starttime;
")## start 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 2How can you output a list of all members who have recommended another member? Ensure that there are no duplicates in the list, and that results are ordered by (surname, firstname).
dbGetQuery(con, "SELECT DISTINCT rec.firstname AS firstname, rec.surname AS surname
FROM members mem INNER JOIN members rec
ON rec.memid = mem.recommendedby
ORDER BY surname, firstname")## firstname surname
## 1 Florence Bader
## 2 Timothy Baker
## 3 Gerald Butters
## 4 Jemima Farrell
## 5 Matthew Genting
## 6 David Jones
## 7 Janice Joplette
## 8 Millicent Purview
## 9 Tim Rownam
## 10 Darren Smith
## 11 Tracy Smith
## 12 Ponder Stibbons
## 13 Burton TracyHow can you output a list of all members, including the individual who recommended them (if any)? Ensure that results are ordered by (surname, firstname).
dbGetQuery(con, "SELECT mem.firstname AS memfname, mem.surname AS memsname, rec.firstname AS recfname, rec.surname as recsname
FROM members mem
LEFT OUTER JOIN members rec ON rec.memid = mem.recommendedby
ORDER BY memsname, memfname; ")## memfname memsname recfname recsname
## 1 Florence Bader Ponder Stibbons
## 2 Anne Baker Ponder Stibbons
## 3 Timothy Baker Jemima Farrell
## 4 Tim Boothe Tim Rownam
## 5 Gerald Butters Darren Smith
## 6 Joan Coplin Timothy Baker
## 7 Erica Crumpet Tracy Smith
## 8 Nancy Dare Janice Joplette
## 9 David Farrell <NA> <NA>
## 10 Jemima Farrell <NA> <NA>
## 11 Matthew Genting Gerald Butters
## 12 GUEST GUEST <NA> <NA>
## 13 John Hunt Millicent Purview
## 14 David Jones Janice Joplette
## 15 Douglas Jones David Jones
## 16 Janice Joplette Darren Smith
## 17 Anna Mackenzie Darren Smith
## 18 Charles Owen Darren Smith
## 19 David Pinker Jemima Farrell
## 20 Millicent Purview Tracy Smith
## 21 Tim Rownam <NA> <NA>
## 22 Henrietta Rumney Matthew Genting
## 23 Ramnaresh Sarwin Florence Bader
## 24 Darren Smith <NA> <NA>
## 25 Darren Smith <NA> <NA>
## 26 Jack Smith Darren Smith
## 27 Tracy Smith <NA> <NA>
## 28 Ponder Stibbons Burton Tracy
## 29 Burton Tracy <NA> <NA>
## 30 Hyacinth Tupperware <NA> <NA>
## 31 Henry Worthington-Smyth Tracy SmithHow 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.
dbGetQuery(con, "SELECT DISTINCT mem.firstname || ' ' || mem.surname AS member, fac.name as facility
FROM members mem
INNER JOIN bookings b ON mem.memid = b.memid
INNER JOIN facilities fac ON b.facid = fac.facid
WHERE fac.name in ('Tennis Court 2','Tennis Court 1')
ORDER BY member, facility ")## member facility
## 1 Anne Baker Tennis Court 1
## 2 Anne Baker Tennis Court 2
## 3 Burton Tracy Tennis Court 1
## 4 Burton Tracy Tennis Court 2
## 5 Charles Owen Tennis Court 1
## 6 Charles Owen Tennis Court 2
## 7 Darren Smith Tennis Court 2
## 8 David Farrell Tennis Court 1
## 9 David Farrell Tennis Court 2
## 10 David Jones Tennis Court 1
## 11 David Jones Tennis Court 2
## 12 David Pinker Tennis Court 1
## 13 Douglas Jones Tennis Court 1
## 14 Erica Crumpet Tennis Court 1
## 15 Florence Bader Tennis Court 1
## 16 Florence Bader Tennis Court 2
## 17 Gerald Butters Tennis Court 1
## 18 Gerald Butters Tennis Court 2
## 19 GUEST GUEST Tennis Court 1
## 20 GUEST GUEST Tennis Court 2
## 21 Henrietta Rumney Tennis Court 2
## 22 Jack Smith Tennis Court 1
## 23 Jack Smith Tennis Court 2
## 24 Janice Joplette Tennis Court 1
## 25 Janice Joplette Tennis Court 2
## 26 Jemima Farrell Tennis Court 1
## 27 Jemima Farrell Tennis Court 2
## 28 Joan Coplin Tennis Court 1
## 29 John Hunt Tennis Court 1
## 30 John Hunt Tennis Court 2
## 31 Matthew Genting Tennis Court 1
## 32 Millicent Purview Tennis Court 2
## 33 Nancy Dare Tennis Court 1
## 34 Nancy Dare Tennis Court 2
## 35 Ponder Stibbons Tennis Court 1
## 36 Ponder Stibbons Tennis Court 2
## 37 Ramnaresh Sarwin Tennis Court 1
## 38 Ramnaresh Sarwin Tennis Court 2
## 39 Tim Boothe Tennis Court 1
## 40 Tim Boothe Tennis Court 2
## 41 Timothy Baker Tennis Court 1
## 42 Timothy Baker Tennis Court 2
## 43 Tim Rownam Tennis Court 1
## 44 Tim Rownam Tennis Court 2
## 45 Tracy Smith Tennis Court 1
## 46 Tracy Smith Tennis Court 2How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour ‘slot’), and the guest user is always ID 0. Include in your output the name of the facility, the name of the member formatted as a single column, and the cost. Order by descending cost, and do not use any subqueries.
dbGetQuery(con, "SELECT mem.firstname || ' ' || mem.surname as surname, fac.name as facility,
CASE
WHEN mem.memid = 0 THEN b.slots*fac.guestcost
ELSE b.slots*fac.membercost
END AS cost
FROM members mem
INNER JOIN bookings b ON mem.memid = b.memid
INNER JOIN facilities fac ON b.facid = fac.facid
WHERE b.starttime >= '2012-09-14' AND b.starttime <'2012-09-15' AND (
(mem.memid = 0 and b.slots*fac.guestcost > 30) OR
(mem.memid != 0 and b.slots*fac.membercost > 30)
)
ORDER BY cost DESC; ")## surname facility cost
## 1 GUEST GUEST Massage Room 2 320.0
## 2 GUEST GUEST Tennis Court 2 198.0
## 3 GUEST GUEST Massage Room 1 160.0
## 4 GUEST GUEST Massage Room 1 160.0
## 5 GUEST GUEST Massage Room 1 160.0
## 6 Jemima Farrell Massage Room 1 140.0
## 7 GUEST GUEST Tennis Court 2 99.0
## 8 GUEST GUEST Tennis Court 1 90.0
## 9 GUEST GUEST Tennis Court 1 90.0
## 10 Ponder Stibbons Massage Room 1 70.0
## 11 Jemima Farrell Massage Room 1 70.0
## 12 Jack Smith Massage Room 1 70.0
## 13 Burton Tracy Massage Room 1 70.0
## 14 Matthew Genting Massage Room 1 70.0
## 15 Florence Bader Massage Room 2 70.0
## 16 GUEST GUEST Squash Court 70.0
## 17 Tim Boothe Tennis Court 2 39.6
## 18 David Jones Tennis Court 2 39.6
## 19 GUEST GUEST Squash Court 35.0
## 20 GUEST GUEST Squash Court 35.0How can you output a list of all members, including the individual who recommended them (if any), without using any joins? Ensure that there are no duplicates in the list, and that each firstname + surname pairing is formatted as a column and ordered.
dbGetQuery(con, "SELECT DISTINCT mem.firstname || ' ' || mem.surname as surname,
(SELECT rec.firstname || ' ' || rec.surname as recommender
FROM members rec
WHERE rec.memid = mem.recommendedby
)
FROM members mem
ORDER BY surname; ")## surname recommender
## 1 Anna Mackenzie Darren Smith
## 2 Anne Baker Ponder Stibbons
## 3 Burton Tracy <NA>
## 4 Charles Owen Darren Smith
## 5 Darren Smith <NA>
## 6 David Farrell <NA>
## 7 David Jones Janice Joplette
## 8 David Pinker Jemima Farrell
## 9 Douglas Jones David Jones
## 10 Erica Crumpet Tracy Smith
## 11 Florence Bader Ponder Stibbons
## 12 Gerald Butters Darren Smith
## 13 GUEST GUEST <NA>
## 14 Henrietta Rumney Matthew Genting
## 15 Henry Worthington-Smyth Tracy Smith
## 16 Hyacinth Tupperware <NA>
## 17 Jack Smith Darren Smith
## 18 Janice Joplette Darren Smith
## 19 Jemima Farrell <NA>
## 20 Joan Coplin Timothy Baker
## 21 John Hunt Millicent Purview
## 22 Matthew Genting Gerald Butters
## 23 Millicent Purview Tracy Smith
## 24 Nancy Dare Janice Joplette
## 25 Ponder Stibbons Burton Tracy
## 26 Ramnaresh Sarwin Florence Bader
## 27 Tim Boothe Tim Rownam
## 28 Timothy Baker Jemima Farrell
## 29 Tim Rownam <NA>
## 30 Tracy Smith <NA>How can you produce a list of bookings on the day of 2012-09-14 which will cost the member (or guest) more than $30? Remember that guests have different costs to members (the listed costs are per half-hour ‘slot’), and the guest user is always ID 0. Include in your output the name of the facility, the name of the member formatted as a single column, and the cost. Order by descending cost.
dbGetQuery(con, "SELECT member, facility, cost FROM (
SELECT mem.firstname || ' ' || mem.surname AS member,fac.name AS facility,
CASE WHEN mem.memid = 0 THEN b.slots*fac.guestcost
ELSE b.slots*fac.membercost
END AS cost
FROM members mem
INNER JOIN bookings b ON mem.memid = b.memid
INNER JOIN facilities fac ON b.facid = fac.facid
WHERE b.starttime >= '2012-09-14' AND b.starttime <'2012-09-15') AS bookings
WHERE cost > 30
ORDER BY cost DESC; ")## member facility cost
## 1 GUEST GUEST Massage Room 2 320.0
## 2 GUEST GUEST Tennis Court 2 198.0
## 3 GUEST GUEST Massage Room 1 160.0
## 4 GUEST GUEST Massage Room 1 160.0
## 5 GUEST GUEST Massage Room 1 160.0
## 6 Jemima Farrell Massage Room 1 140.0
## 7 GUEST GUEST Tennis Court 2 99.0
## 8 GUEST GUEST Tennis Court 1 90.0
## 9 GUEST GUEST Tennis Court 1 90.0
## 10 Ponder Stibbons Massage Room 1 70.0
## 11 Jemima Farrell Massage Room 1 70.0
## 12 Jack Smith Massage Room 1 70.0
## 13 Burton Tracy Massage Room 1 70.0
## 14 Matthew Genting Massage Room 1 70.0
## 15 Florence Bader Massage Room 2 70.0
## 16 GUEST GUEST Squash Court 70.0
## 17 Tim Boothe Tennis Court 2 39.6
## 18 David Jones Tennis Court 2 39.6
## 19 GUEST GUEST Squash Court 35.0
## 20 GUEST GUEST Squash Court 35.0The club is adding a new facility - a spa. We need to add it into the facilities table. Use the following values:
facid: 9, Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.
dbGetQuery(con, "
UPDATE facilities
SET
name = 'Spa',
membercost = 20,
guestcost = 30,
initialoutlay = 100000,
monthlymaintenance = 800
WHERE
facid = 9;
")## Warning: Don't need to call dbFetch() for statements, only for queries## data frame with 0 columns and 0 rowsIn the previous exercise, you learned how to add a facility. Now you’re going to add multiple facilities in one command. Use the following values:
facid: 14, Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800. facid: 15, Name: ‘Squash Court 2’, membercost: 3.5, guestcost: 17.5, initialoutlay: 5000, monthlymaintenance: 80.
#dbGetQuery(con, "
#INSERT INTO facilities
# (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
#VALUES
# (15, 'Squash Court 2', 3.5, 17.5, 5000, 80);
# ")Let’s try adding the spa to the facilities table again. This time, though, we want to automatically generate the value for the next facid, rather than specifying it as a constant. Use the following values for everything else:
Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.
dbGetQuery(con, "
INSERT INTO facilities
(facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
SELECT (SELECT MAX(facid) FROM facilities)+1, 'Spa', 20, 30, 100000, 800;
")## Warning: Don't need to call dbFetch() for statements, only for queries## data frame with 0 columns and 0 rowsWe made a mistake when entering the data for the second tennis court. The initial outlay was 10000 rather than 8000: you need to alter the data to fix the error.
dbGetQuery(con, "UPDATE facilities SET initialoutlay = 10000
WHERE facid = 1;
")## Warning: Don't need to call dbFetch() for statements, only for queries## data frame with 0 columns and 0 rowsWe want to increase the price of the tennis courts for both members and guests. Update the costs to be 6 for members, and 30 for guests.
dbGetQuery(con, "UPDATE facilities SET
membercost = 6,
guestcost = 30
WHERE facid in (0,1);
")## Warning: Don't need to call dbFetch() for statements, only for queries## data frame with 0 columns and 0 rowsWe want to alter the price of the second tennis court so that it costs 10% more than the first one. Try to do this without using constant values for the prices, so that we can reuse the statement if we want to.
dbGetQuery(con, "UPDATE facilities fac SET
membercost = (SELECT membercost * 1.1 FROM facilities WHERE facid = 0),
guestcost = (SELECT guestcost * 1.1 FROM facilities WHERE facid = 0)
WHERE fac.facid = 1;
")## Warning: Don't need to call dbFetch() for statements, only for queries## data frame with 0 columns and 0 rowsAs part of a clearout of our database, we want to delete all bookings from the cd.bookings table. How can we accomplish this?
#dbGetQuery(con, "DELETE FROM bookings; ")Esta consulta realiza la eliminación de las filas de la tabla Bookings, sin embargo, se comentó la consulta puesto que necesitamos esa tabla para realizar las siguientes consultas.
We want to remove member 37, who has never made a booking, from our database. How can we achieve that?
dbGetQuery(con, "DELETE FROM members WHERE memid = 37; ")## Warning: Don't need to call dbFetch() for statements, only for queries## data frame with 0 columns and 0 rowsIn our previous exercises, we deleted a specific member who had never made a booking. How can we make that more general, to delete all members who have never made a booking?
dbGetQuery(con, "DELETE FROM members WHERE memid not in (SELECT memid FROM bookings); ")## Warning: Don't need to call dbFetch() for statements, only for queries## data frame with 0 columns and 0 rowsFor our first foray into aggregates, we’re going to stick to something simple. We want to know how many facilities exist - simply produce a total count.
dbGetQuery(con, "SELECT COUNT(*) FROM facilities; ")## count
## 1 11Produce a count of the number of facilities that have a cost to guests of 10 or more.
dbGetQuery(con, "SELECT COUNT(*) FROM facilities WHERE membercost >=10 ; ")## count
## 1 4Produce a count of the number of recommendations each member has made. Order by member ID.
dbGetQuery(con," select recommendedby, count(*)
from members
where recommendedby is not null
group by recommendedby
order by recommendedby; ")## recommendedby count
## 1 1 5
## 2 2 3
## 3 3 1
## 4 4 2
## 5 5 1
## 6 6 1
## 7 9 2
## 8 11 1
## 9 13 2
## 10 15 1
## 11 16 1
## 12 20 1
## 13 30 1Produce a list of the total number of slots booked per facility. For now, just produce an output table consisting of facility id and slots, sorted by facility id.
dbGetQuery(con," select facid, sum(slots) as Total_Slots
from bookings
group by facid
order by facid; ")## facid total_slots
## 1 0 1320
## 2 1 1278
## 3 2 1209
## 4 3 830
## 5 4 1404
## 6 5 228
## 7 6 1104
## 8 7 908
## 9 8 911List the total slots booked per facility in a given month.
dbGetQuery(con," select facid, sum(slots) as Total_Slots
from bookings
where
starttime >= '2012-09-01'
and starttime < '2012-10-01'
group by facid
order by sum(slots); ")## facid total_slots
## 1 5 122
## 2 3 422
## 3 7 426
## 4 8 471
## 5 6 540
## 6 2 570
## 7 1 588
## 8 0 591
## 9 4 648Produce a list of the total number of slots booked per facility per month in the year of 2012. Produce an output table consisting of facility id and slots, sorted by the id and month.
dbGetQuery(con," select facid, extract(month from starttime) as month, sum(slots) as Total_Slots
from bookings
where extract(year from starttime) = 2012
group by facid, month
order by facid, month; ")## facid month total_slots
## 1 0 7 270
## 2 0 8 459
## 3 0 9 591
## 4 1 7 207
## 5 1 8 483
## 6 1 9 588
## 7 2 7 180
## 8 2 8 459
## 9 2 9 570
## 10 3 7 104
## 11 3 8 304
## 12 3 9 422
## 13 4 7 264
## 14 4 8 492
## 15 4 9 648
## 16 5 7 24
## 17 5 8 82
## 18 5 9 122
## 19 6 7 164
## 20 6 8 400
## 21 6 9 540
## 22 7 7 156
## 23 7 8 326
## 24 7 9 426
## 25 8 7 117
## 26 8 8 322
## 27 8 9 471Find the total number of members (including guests) who have made at least one booking.
dbGetQuery(con," select count(distinct memid) from bookings ")## count
## 1 30Produce a list of facilities with more than 1000 slots booked. Produce an output table consisting of facility id and slots, sorted by facility id.
dbGetQuery(con," select facid, sum(slots) as Total_Slots
from bookings
group by facid
having sum(slots) > 1000
order by facid ")## facid total_slots
## 1 0 1320
## 2 1 1278
## 3 2 1209
## 4 4 1404
## 5 6 1104Produce a list of facilities along with their total revenue. The output table should consist of facility name and revenue, sorted by revenue. Remember that there’s a different cost for guests and members!
dbGetQuery(con," select facs.name, sum(slots * case
when memid = 0 then facs.guestcost
else facs.membercost
end) as revenue
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.name
order by revenue; ")## name revenue
## 1 Table Tennis 180.0
## 2 Snooker Table 240.0
## 3 Pool Table 270.0
## 4 Badminton Court 1906.5
## 5 Squash Court 13468.0
## 6 Massage Room 2 15810.0
## 7 Tennis Court 1 16632.0
## 8 Tennis Court 2 18889.2
## 9 Massage Room 1 72540.0Produce a list of facilities with a total revenue less than 1000. Produce an output table consisting of facility name and revenue, sorted by revenue. Remember that there’s a different cost for guests and members!
dbGetQuery(con," select name, revenue from (
select facs.name, sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end) as revenue
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.name
) as agg where revenue < 1000
order by revenue; ")## name revenue
## 1 Table Tennis 180
## 2 Snooker Table 240
## 3 Pool Table 270Output the facility id that has the highest number of slots booked. For bonus points, try a version without a LIMIT clause. This version will probably look messy!
dbGetQuery(con," select facid, sum(slots) as Total_Slots
from bookings
group by facid
order by sum(slots) desc
LIMIT 1; ")## facid total_slots
## 1 4 1404Produce a list of the total number of slots booked per facility per month in the year of 2012. In this version, include output rows containing totals for all months per facility, and a total for all months for all facilities. The output table should consist of facility id, month and slots, sorted by the id and month. When calculating the aggregated values for all months and all facids, return null values in the month and facid columns.
dbGetQuery(con," select facid, extract(month from starttime) as month, sum(slots) as slots
from bookings
where
starttime >= '2012-01-01'
and starttime < '2013-01-01'
group by rollup(facid, month)
order by facid, month; ")## facid month slots
## 1 0 7 270
## 2 0 8 459
## 3 0 9 591
## 4 0 NA 1320
## 5 1 7 207
## 6 1 8 483
## 7 1 9 588
## 8 1 NA 1278
## 9 2 7 180
## 10 2 8 459
## 11 2 9 570
## 12 2 NA 1209
## 13 3 7 104
## 14 3 8 304
## 15 3 9 422
## 16 3 NA 830
## 17 4 7 264
## 18 4 8 492
## 19 4 9 648
## 20 4 NA 1404
## 21 5 7 24
## 22 5 8 82
## 23 5 9 122
## 24 5 NA 228
## 25 6 7 164
## 26 6 8 400
## 27 6 9 540
## 28 6 NA 1104
## 29 7 7 156
## 30 7 8 326
## 31 7 9 426
## 32 7 NA 908
## 33 8 7 117
## 34 8 8 322
## 35 8 9 471
## 36 8 NA 910
## 37 NA NA 9191Produce a list of the total number of hours booked per facility, remembering that a slot lasts half an hour. The output table should consist of the facility id, name, and hours booked, sorted by facility id. Try formatting the hours to two decimal places.
dbGetQuery(con," select facs.facid, facs.name,
trim(to_char(sum(bks.slots)/2.0, '9999999999999999D99')) as Total_Hours
from bookings bks
inner join facilities facs
on facs.facid = bks.facid
group by facs.facid, facs.name
order by facs.facid; ")## facid name total_hours
## 1 0 Tennis Court 1 660.00
## 2 1 Tennis Court 2 639.00
## 3 2 Badminton Court 604.50
## 4 3 Table Tennis 415.00
## 5 4 Massage Room 1 702.00
## 6 5 Massage Room 2 114.00
## 7 6 Squash Court 552.00
## 8 7 Snooker Table 454.00
## 9 8 Pool Table 455.50Produce a list of each member name, id, and their first booking after September 1st 2012. Order by member ID.
dbGetQuery(con," select mems.surname, mems.firstname, mems.memid, min(bks.starttime) as starttime
from bookings bks
inner join members mems on
mems.memid = bks.memid
where starttime >= '2012-09-01'
group by mems.surname, mems.firstname, mems.memid
order by mems.memid; ")## surname firstname memid starttime
## 1 GUEST GUEST 0 2012-09-01 08:00:00
## 2 Smith Darren 1 2012-09-01 09:00:00
## 3 Smith Tracy 2 2012-09-01 11:30:00
## 4 Rownam Tim 3 2012-09-01 16:00:00
## 5 Joplette Janice 4 2012-09-01 15:00:00
## 6 Butters Gerald 5 2012-09-02 12:30:00
## 7 Tracy Burton 6 2012-09-01 15:00:00
## 8 Dare Nancy 7 2012-09-01 12:30:00
## 9 Boothe Tim 8 2012-09-01 08:30:00
## 10 Stibbons Ponder 9 2012-09-01 11:00:00
## 11 Owen Charles 10 2012-09-01 11:00:00
## 12 Jones David 11 2012-09-01 09:30:00
## 13 Baker Anne 12 2012-09-01 14:30:00
## 14 Farrell Jemima 13 2012-09-01 09:30:00
## 15 Smith Jack 14 2012-09-01 11:00:00
## 16 Bader Florence 15 2012-09-01 10:30:00
## 17 Baker Timothy 16 2012-09-01 15:00:00
## 18 Pinker David 17 2012-09-01 08:30:00
## 19 Genting Matthew 20 2012-09-01 18:00:00
## 20 Mackenzie Anna 21 2012-09-01 08:30:00
## 21 Coplin Joan 22 2012-09-02 11:30:00
## 22 Sarwin Ramnaresh 24 2012-09-04 11:00:00
## 23 Jones Douglas 26 2012-09-08 13:00:00
## 24 Rumney Henrietta 27 2012-09-16 13:30:00
## 25 Farrell David 28 2012-09-18 09:00:00
## 26 Worthington-Smyth Henry 29 2012-09-19 09:30:00
## 27 Purview Millicent 30 2012-09-19 11:30:00
## 28 Tupperware Hyacinth 33 2012-09-20 08:00:00
## 29 Hunt John 35 2012-09-23 14:00:00
## 30 Crumpet Erica 36 2012-09-27 11:30:00Produce a list of member names, with each row containing the total member count. Order by join date, and include guest members.
dbGetQuery(con," select count(*) over(), firstname, surname
from members
order by joindate ")## count firstname surname
## 1 30 GUEST GUEST
## 2 30 Darren Smith
## 3 30 Tracy Smith
## 4 30 Tim Rownam
## 5 30 Janice Joplette
## 6 30 Gerald Butters
## 7 30 Burton Tracy
## 8 30 Nancy Dare
## 9 30 Tim Boothe
## 10 30 Ponder Stibbons
## 11 30 Charles Owen
## 12 30 David Jones
## 13 30 Anne Baker
## 14 30 Jemima Farrell
## 15 30 Jack Smith
## 16 30 Florence Bader
## 17 30 Timothy Baker
## 18 30 David Pinker
## 19 30 Matthew Genting
## 20 30 Anna Mackenzie
## 21 30 Joan Coplin
## 22 30 Ramnaresh Sarwin
## 23 30 Douglas Jones
## 24 30 Henrietta Rumney
## 25 30 David Farrell
## 26 30 Henry Worthington-Smyth
## 27 30 Millicent Purview
## 28 30 Hyacinth Tupperware
## 29 30 John Hunt
## 30 30 Erica CrumpetProduce a monotonically increasing numbered list of members (including guests), ordered by their date of joining. Remember that member IDs are not guaranteed to be sequential.
dbGetQuery(con," select row_number() over(order by joindate), firstname, surname
from members
order by joindate ")## row_number firstname surname
## 1 1 GUEST GUEST
## 2 2 Darren Smith
## 3 3 Tracy Smith
## 4 4 Tim Rownam
## 5 5 Janice Joplette
## 6 6 Gerald Butters
## 7 7 Burton Tracy
## 8 8 Nancy Dare
## 9 9 Tim Boothe
## 10 10 Ponder Stibbons
## 11 11 Charles Owen
## 12 12 David Jones
## 13 13 Anne Baker
## 14 14 Jemima Farrell
## 15 15 Jack Smith
## 16 16 Florence Bader
## 17 17 Timothy Baker
## 18 18 David Pinker
## 19 19 Matthew Genting
## 20 20 Anna Mackenzie
## 21 21 Joan Coplin
## 22 22 Ramnaresh Sarwin
## 23 23 Douglas Jones
## 24 24 Henrietta Rumney
## 25 25 David Farrell
## 26 26 Henry Worthington-Smyth
## 27 27 Millicent Purview
## 28 28 Hyacinth Tupperware
## 29 29 John Hunt
## 30 30 Erica CrumpetOutput the facility id that has the highest number of slots booked. Ensure that in the event of a tie, all tieing results get output.
dbGetQuery(con," select facid, total from (
select facid, sum(slots) total, rank() over (order by sum(slots) desc) rank
from bookings
group by facid
) as ranked
where rank = 1 ")## facid total
## 1 4 1404Produce a list of members (including guests), along with the number of hours they’ve booked in facilities, rounded to the nearest ten hours. Rank them by this rounded figure, producing output of first name, surname, rounded hours, rank. Sort by rank, surname, and first name.
dbGetQuery(con," select firstname, surname,
((sum(bks.slots)+10)/20)*10 as hours,
rank() over (order by ((sum(bks.slots)+10)/20)*10 desc) as rank
from bookings bks
inner join members mems
on bks.memid = mems.memid
group by mems.memid
order by rank, surname, firstname; ")## firstname surname hours rank
## 1 GUEST GUEST 1200 1
## 2 Darren Smith 340 2
## 3 Tim Rownam 330 3
## 4 Tim Boothe 220 4
## 5 Tracy Smith 220 4
## 6 Gerald Butters 210 6
## 7 Burton Tracy 180 7
## 8 Charles Owen 170 8
## 9 Janice Joplette 160 9
## 10 Anne Baker 150 10
## 11 Timothy Baker 150 10
## 12 David Jones 150 10
## 13 Nancy Dare 130 13
## 14 Florence Bader 120 14
## 15 Anna Mackenzie 120 14
## 16 Ponder Stibbons 120 14
## 17 Jack Smith 110 17
## 18 Jemima Farrell 90 18
## 19 David Pinker 80 19
## 20 Ramnaresh Sarwin 80 19
## 21 Matthew Genting 70 21
## 22 Joan Coplin 50 22
## 23 David Farrell 30 23
## 24 Henry Worthington-Smyth 30 23
## 25 John Hunt 20 25
## 26 Douglas Jones 20 25
## 27 Millicent Purview 20 25
## 28 Henrietta Rumney 20 25
## 29 Erica Crumpet 10 29
## 30 Hyacinth Tupperware 10 29Produce a list of the top three revenue generating facilities (including ties). Output facility name and rank, sorted by rank and facility name.
dbGetQuery(con," select name, rank from (
select facs.name as name, rank() over (order by sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end) desc) as rank
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.name
) as subq
where rank <= 3
order by rank; ")## name rank
## 1 Massage Room 1 1
## 2 Tennis Court 2 2
## 3 Tennis Court 1 3Classify facilities into equally sized groups of high, average, and low based on their revenue. Order by classification and facility name.
dbGetQuery(con," select name, case when class=1 then 'high'
when class=2 then 'average'
else 'low'
end revenue
from (
select facs.name as name, ntile(3) over (order by sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end) desc) as class
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.name
) as subq
order by class, name; ")## name revenue
## 1 Massage Room 1 high
## 2 Tennis Court 1 high
## 3 Tennis Court 2 high
## 4 Badminton Court average
## 5 Massage Room 2 average
## 6 Squash Court average
## 7 Pool Table low
## 8 Snooker Table low
## 9 Table Tennis lowBased on the 3 complete months of data so far, calculate the amount of time each facility will take to repay its cost of ownership. Remember to take into account ongoing monthly maintenance. Output facility name and payback time in months, order by facility name. Don’t worry about differences in month lengths, we’re only looking for a rough value here!
dbGetQuery(con,"select facs.name as name,
facs.initialoutlay/((sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end)/3) - facs.monthlymaintenance) as months
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.facid
order by name; ")## name months
## 1 Badminton Court 6.8317677
## 2 Massage Room 1 0.1888574
## 3 Massage Room 2 1.7621145
## 4 Pool Table 5.3333333
## 5 Snooker Table 6.9230769
## 6 Squash Court 1.1339583
## 7 Table Tennis 6.4000000
## 8 Tennis Court 1 1.8712575
## 9 Tennis Court 2 1.6403123For each day in August 2012, calculate a rolling average of total revenue over the previous 15 days. Output should contain date and revenue columns, sorted by the date. Remember to account for the possibility of a day having zero revenue. This one’s a bit tough, so don’t be afraid to check out the hint!
dbGetQuery(con,"select dategen.date,
(select sum(case
when memid = 0 then slots * facs.guestcost
else slots * membercost
end) as rev
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
where bks.starttime > dategen.date - interval '14 days'
and bks.starttime < dategen.date + interval '1 day'
)/15 as revenue
from
(select cast(generate_series(timestamp '2012-08-01',
'2012-08-31','1 day') as date) as date
) as dategen
order by dategen.date; ")## date revenue
## 1 2012-08-01 1191.793
## 2 2012-08-02 1219.320
## 3 2012-08-03 1229.460
## 4 2012-08-04 1245.207
## 5 2012-08-05 1228.933
## 6 2012-08-06 1254.360
## 7 2012-08-07 1249.187
## 8 2012-08-08 1238.640
## 9 2012-08-09 1218.507
## 10 2012-08-10 1240.673
## 11 2012-08-11 1245.593
## 12 2012-08-12 1266.427
## 13 2012-08-13 1290.640
## 14 2012-08-14 1324.867
## 15 2012-08-15 1352.180
## 16 2012-08-16 1364.113
## 17 2012-08-17 1409.947
## 18 2012-08-18 1458.933
## 19 2012-08-19 1490.587
## 20 2012-08-20 1512.627
## 21 2012-08-21 1534.013
## 22 2012-08-22 1629.380
## 23 2012-08-23 1656.540
## 24 2012-08-24 1683.333
## 25 2012-08-25 1710.393
## 26 2012-08-26 1723.480
## 27 2012-08-27 1751.673
## 28 2012-08-28 1775.800
## 29 2012-08-29 1771.993
## 30 2012-08-30 1746.053
## 31 2012-08-31 1791.840Produce a timestamp for 1 a.m. on the 31st of August 2012.
dbGetQuery(con,"select timestamp '2012-08-31 01:00:00';")## timestamp
## 1 2012-08-31 01:00:00Find the result of subtracting the timestamp ‘2012-07-30 01:00:00’ from the timestamp ‘2012-08-31 01:00:00’
dbGetQuery(con,"select timestamp '2012-08-31 01:00:00' - timestamp '2012-07-30 01:00:00' as interval; ")## interval
## 1 32 daysProduce a list of all the dates in October 2012. They can be output as a timestamp (with time set to midnight) or a date.
dbGetQuery(con,"select generate_series(timestamp '2012-10-01', timestamp '2012-10-31', interval '1 day') as ts; ")## ts
## 1 2012-10-01
## 2 2012-10-02
## 3 2012-10-03
## 4 2012-10-04
## 5 2012-10-05
## 6 2012-10-06
## 7 2012-10-07
## 8 2012-10-08
## 9 2012-10-09
## 10 2012-10-10
## 11 2012-10-11
## 12 2012-10-12
## 13 2012-10-13
## 14 2012-10-14
## 15 2012-10-15
## 16 2012-10-16
## 17 2012-10-17
## 18 2012-10-18
## 19 2012-10-19
## 20 2012-10-20
## 21 2012-10-21
## 22 2012-10-22
## 23 2012-10-23
## 24 2012-10-24
## 25 2012-10-25
## 26 2012-10-26
## 27 2012-10-27
## 28 2012-10-28
## 29 2012-10-29
## 30 2012-10-30
## 31 2012-10-31Get the day of the month from the timestamp ‘2012-08-31’ as an integer.
dbGetQuery(con,"select extract(day from timestamp '2012-08-31'); ")## extract
## 1 31Work out the number of seconds between the timestamps ‘2012-08-31 01:00:00’ and ‘2012-09-02 00:00:00’
dbGetQuery(con,"select extract(epoch from (timestamp '2012-09-02 00:00:00' - '2012-08-31 01:00:00')); ")## extract
## 1 169200For each month of the year in 2012, output the number of days in that month. Format the output as an integer column containing the month of the year, and a second column containing an interval data type.
dbGetQuery(con,"select extract(month from cal.month) as month,
(cal.month + interval '1 month') - cal.month as length
from
(
select generate_series(timestamp '2012-01-01', timestamp '2012-12-01', interval '1 month') as month
) cal
order by month; ")## month length
## 1 1 31 days
## 2 2 29 days
## 3 3 31 days
## 4 4 30 days
## 5 5 31 days
## 6 6 30 days
## 7 7 31 days
## 8 8 31 days
## 9 9 30 days
## 10 10 31 days
## 11 11 30 days
## 12 12 31 daysFor any given timestamp, work out the number of days remaining in the month. The current day should count as a whole day, regardless of the time. Use ‘2012-02-11 01:00:00’ as an example timestamp for the purposes of making the answer. Format the output as a single interval value.
dbGetQuery(con,"select (date_trunc('month',ts.testts) + interval '1 month')
- date_trunc('day', ts.testts) as remaining
from (select timestamp '2012-02-11 01:00:00' as testts) ts ")## remaining
## 1 19 daysReturn a list of the start and end time of the last 10 bookings (ordered by the time at which they end, followed by the time at which they start) in the system.
dbGetQuery(con,"select starttime, starttime + slots*(interval '30 minutes') endtime
from bookings
order by endtime desc, starttime desc
limit 10")## starttime endtime
## 1 2013-01-01 15:30:00 2013-01-01 16:00:00
## 2 2012-09-30 19:30:00 2012-09-30 20:30:00
## 3 2012-09-30 19:00:00 2012-09-30 20:30:00
## 4 2012-09-30 19:30:00 2012-09-30 20:00:00
## 5 2012-09-30 19:00:00 2012-09-30 20:00:00
## 6 2012-09-30 19:00:00 2012-09-30 20:00:00
## 7 2012-09-30 18:30:00 2012-09-30 20:00:00
## 8 2012-09-30 18:30:00 2012-09-30 20:00:00
## 9 2012-09-30 19:00:00 2012-09-30 19:30:00
## 10 2012-09-30 18:30:00 2012-09-30 19:30:00Return a count of bookings for each month, sorted by month
dbGetQuery(con,"select date_trunc('month', starttime) as month, count(*)
from bookings
group by month
order by month ")## month count
## 1 2012-07-01 658
## 2 2012-08-01 1472
## 3 2012-09-01 1913
## 4 2013-01-01 1Work out the utilisation percentage for each facility by month, sorted by name and month, rounded to 1 decimal place. Opening time is 8am, closing time is 8.30pm. You can treat every month as a full month, regardless of if there were some dates the club was not open.
dbGetQuery(con,"select name, month,
round((100*slots)/
cast(
25*(cast((month + interval '1 month') as date)
- cast (month as date)) as numeric),1) as utilisation
from (
select facs.name as name, date_trunc('month', starttime) as month, sum(slots) as slots
from bookings bks
inner join facilities facs
on bks.facid = facs.facid
group by facs.facid, month
) as inn
order by name, month ")## name month utilisation
## 1 Badminton Court 2012-07-01 23.2
## 2 Badminton Court 2012-08-01 59.2
## 3 Badminton Court 2012-09-01 76.0
## 4 Massage Room 1 2012-07-01 34.1
## 5 Massage Room 1 2012-08-01 63.5
## 6 Massage Room 1 2012-09-01 86.4
## 7 Massage Room 2 2012-07-01 3.1
## 8 Massage Room 2 2012-08-01 10.6
## 9 Massage Room 2 2012-09-01 16.3
## 10 Pool Table 2012-07-01 15.1
## 11 Pool Table 2012-08-01 41.5
## 12 Pool Table 2012-09-01 62.8
## 13 Pool Table 2013-01-01 0.1
## 14 Snooker Table 2012-07-01 20.1
## 15 Snooker Table 2012-08-01 42.1
## 16 Snooker Table 2012-09-01 56.8
## 17 Squash Court 2012-07-01 21.2
## 18 Squash Court 2012-08-01 51.6
## 19 Squash Court 2012-09-01 72.0
## 20 Table Tennis 2012-07-01 13.4
## 21 Table Tennis 2012-08-01 39.2
## 22 Table Tennis 2012-09-01 56.3
## 23 Tennis Court 1 2012-07-01 34.8
## 24 Tennis Court 1 2012-08-01 59.2
## 25 Tennis Court 1 2012-09-01 78.8
## 26 Tennis Court 2 2012-07-01 26.7
## 27 Tennis Court 2 2012-08-01 62.3
## 28 Tennis Court 2 2012-09-01 78.4Output the names of all members, formatted as ‘Surname, Firstname’
dbGetQuery(con,"select surname || ', ' || firstname as name from members ")## name
## 1 GUEST, GUEST
## 2 Smith, Darren
## 3 Smith, Tracy
## 4 Rownam, Tim
## 5 Joplette, Janice
## 6 Butters, Gerald
## 7 Tracy, Burton
## 8 Dare, Nancy
## 9 Boothe, Tim
## 10 Stibbons, Ponder
## 11 Owen, Charles
## 12 Jones, David
## 13 Baker, Anne
## 14 Farrell, Jemima
## 15 Smith, Jack
## 16 Bader, Florence
## 17 Baker, Timothy
## 18 Pinker, David
## 19 Genting, Matthew
## 20 Mackenzie, Anna
## 21 Coplin, Joan
## 22 Sarwin, Ramnaresh
## 23 Jones, Douglas
## 24 Rumney, Henrietta
## 25 Farrell, David
## 26 Worthington-Smyth, Henry
## 27 Purview, Millicent
## 28 Tupperware, Hyacinth
## 29 Hunt, John
## 30 Crumpet, EricaFind all facilities whose name begins with ‘Tennis’. Retrieve all columns.
dbGetQuery(con,"select * from facilities where name like 'Tennis%';")## facid name membercost guestcost initialoutlay monthlymaintenance
## 1 0 Tennis Court 1 6.0 30 10000 200
## 2 1 Tennis Court 2 6.6 33 10000 200Perform a case-insensitive search to find all facilities whose name begins with ‘tennis’. Retrieve all columns.
dbGetQuery(con,"select * from facilities where upper(name) like 'TENNIS%'")## facid name membercost guestcost initialoutlay monthlymaintenance
## 1 0 Tennis Court 1 6.0 30 10000 200
## 2 1 Tennis Court 2 6.6 33 10000 200You’ve noticed that the club’s member table has telephone numbers with very inconsistent formatting. You’d like to find all the telephone numbers that contain parentheses, returning the member ID and telephone number sorted by member ID.
dbGetQuery(con,"select memid, telephone from members where telephone ~ '[()]';")## memid telephone
## 1 0 (000) 000-0000
## 2 3 (844) 693-0723
## 3 4 (833) 942-4710
## 4 5 (844) 078-4130
## 5 6 (822) 354-9973
## 6 7 (833) 776-4001
## 7 8 (811) 433-2547
## 8 9 (833) 160-3900
## 9 10 (855) 542-5251
## 10 11 (844) 536-8036
## 11 13 (855) 016-0163
## 12 14 (822) 163-3254
## 13 15 (833) 499-3527
## 14 20 (811) 972-1377
## 15 21 (822) 661-2898
## 16 22 (822) 499-2232
## 17 24 (822) 413-1470
## 18 27 (822) 989-8876
## 19 28 (855) 755-9876
## 20 29 (855) 894-3758
## 21 30 (855) 941-9786
## 22 33 (822) 665-5327
## 23 35 (899) 720-6978
## 24 36 (811) 732-4816The zip codes in our example dataset have had leading zeroes removed from them by virtue of being stored as a numeric type. Retrieve all zip codes from the members table, padding any zip codes less than 5 characters long with leading zeroes. Order by the new zip code.
dbGetQuery(con,"select lpad(cast(zipcode as char(5)),5,'0') zip from members order by zip ")## zip
## 1 00000
## 2 00234
## 3 00234
## 4 04321
## 5 04321
## 6 10383
## 7 11986
## 8 23423
## 9 28563
## 10 33862
## 11 34232
## 12 43532
## 13 43533
## 14 45678
## 15 52365
## 16 54333
## 17 56754
## 18 57392
## 19 58393
## 20 64577
## 21 65332
## 22 65464
## 23 68666
## 24 69302
## 25 75655
## 26 78533
## 27 80743
## 28 84923
## 29 87630
## 30 97676You’d like to produce a count of how many members you have whose surname starts with each letter of the alphabet. Sort by the letter, and don’t worry about printing out a letter if the count is 0.
dbGetQuery(con,"select substr (mems.surname,1,1) as letter, count(*) as count
from members mems
group by letter
order by letter ")## letter count
## 1 B 5
## 2 C 2
## 3 D 1
## 4 F 2
## 5 G 2
## 6 H 1
## 7 J 3
## 8 M 1
## 9 O 1
## 10 P 2
## 11 R 2
## 12 S 5
## 13 T 2
## 14 W 1The telephone numbers in the database are very inconsistently formatted. You’d like to print a list of member ids and numbers that have had ‘-’,‘(’,‘)’, and ’ ’ characters removed. Order by member id.
dbGetQuery(con,"select memid, translate(telephone, '-() ', '') as telephone
from members
order by memid; ")## memid telephone
## 1 0 0000000000
## 2 1 5555555555
## 3 2 5555555555
## 4 3 8446930723
## 5 4 8339424710
## 6 5 8440784130
## 7 6 8223549973
## 8 7 8337764001
## 9 8 8114332547
## 10 9 8331603900
## 11 10 8555425251
## 12 11 8445368036
## 13 12 8440765141
## 14 13 8550160163
## 15 14 8221633254
## 16 15 8334993527
## 17 16 8339410824
## 18 17 8114096734
## 19 20 8119721377
## 20 21 8226612898
## 21 22 8224992232
## 22 24 8224131470
## 23 26 8445368036
## 24 27 8229898876
## 25 28 8557559876
## 26 29 8558943758
## 27 30 8559419786
## 28 33 8226655327
## 29 35 8997206978
## 30 36 8117324816Find the upward recommendation chain for member ID 27: that is, the member who recommended them, and the member who recommended that member, and so on. Return member ID, first name, and surname. Order by descending member id.
dbGetQuery(con,"with recursive recommenders(recommender) as (
select recommendedby from members where memid = 27
union all
select mems.recommendedby
from recommenders recs
inner join members mems
on mems.memid = recs.recommender
)
select recs.recommender, mems.firstname, mems.surname
from recommenders recs
inner join members mems
on recs.recommender = mems.memid
order by memid desc ")## recommender firstname surname
## 1 20 Matthew Genting
## 2 5 Gerald Butters
## 3 1 Darren SmithFind the downward recommendation chain for member ID 1: that is, the members they recommended, the members those members recommended, and so on. Return member ID and name, and order by ascending member id.
dbGetQuery(con,"with recursive recommendeds(memid) as (
select memid from members where recommendedby = 1
union all
select mems.memid
from recommendeds recs
inner join members mems
on mems.recommendedby = recs.memid
)
select recs.memid, mems.firstname, mems.surname
from recommendeds recs
inner join members mems
on recs.memid = mems.memid
order by memid ")## memid firstname surname
## 1 4 Janice Joplette
## 2 5 Gerald Butters
## 3 7 Nancy Dare
## 4 10 Charles Owen
## 5 11 David Jones
## 6 14 Jack Smith
## 7 20 Matthew Genting
## 8 21 Anna Mackenzie
## 9 26 Douglas Jones
## 10 27 Henrietta RumneyProduce a CTE that can return the upward recommendation chain for any member. You should be able to select recommender from recommenders where member=x. Demonstrate it by getting the chains for members 12 and 22. Results table should have member and recommender, ordered by member ascending, recommender descending.
dbGetQuery(con,"with recursive recommenders(recommender, member) as (
select recommendedby, memid
from members
union all
select mems.recommendedby, recs.member
from recommenders recs
inner join members mems
on mems.memid = recs.recommender
)
select recs.member member, recs.recommender, mems.firstname, mems.surname
from recommenders recs
inner join members mems
on recs.recommender = mems.memid
where recs.member = 22 or recs.member = 12
order by recs.member asc, recs.recommender desc ")## member recommender firstname surname
## 1 12 9 Ponder Stibbons
## 2 12 6 Burton Tracy
## 3 22 16 Timothy Baker
## 4 22 13 Jemima Farrell