PostgreSQL Exercises

Getting Started

Introduction to the dataset

The dataset for these exercises is for a newly created country club, with a set of members, facilities such as tennis courts, and booking history for those facilities. The Entity Relationship Diagram for this dataset is shown below:

Amongst other things, the club wants to understand how they can use their information to analyse facility usage/demand.

Dataset Design

The dataset is designed purely for supporting an interesting array of exercises, and the database schema is flawed in several aspects - please don't take it as an example of good design.

Members table

We'll start off with a look at the cd.members table:

CREATE TABLE cd.members (
  memid integer NOT NULL, 
  surname character varying(200) NOT NULL, 
  firstname character varying(200) NOT NULL, 
  address character varying(300) NOT NULL, 
  zipcode integer NOT NULL, 
  telephone character varying(20) NOT NULL, 
  recommendedby integer,
  joindate timestamp NOT NULL,
  CONSTRAINT members_pk PRIMARY KEY (memid),
  CONSTRAINT fk_members_recommendedby FOREIGN KEY (recommendedby)
    REFERENCES cd.members(memid) ON DELETE SET NULL
);

Each member has an ID memid (not guaranteed to be sequential), basic address information (surname, firstname, address, zipcode, telephone), a reference (recommendedby) to the member that recommended them (if any), and a timestamp for when they joined (joindate). The addresses in the dataset are entirely (and unrealistically) fabricated.

Facilities table

The cd.facilities table definition is as follows:

CREATE TABLE cd.facilities (
  facid integer NOT NULL,
  name character varying(100) NOT NULL,
  membercost numeric NOT NULL,
  guestcost numeric NOT NULL,
  initialoutlay numeric NOT NULL,
  monthlymaintenance numeric NOT NULL,
  CONSTRAINT facilities_pk PRIMARY KEY (facid)
);

The facilities table lists all the bookable facilities that the country club possesses. The club stores id/name information (facid, name), the cost to book both members and guests (membercost, guestcost), the initial cost to build the facility (initialoutlay), and estimated monthly upkeep costs (monthlymaintenance). They hope to use this information to track how financially worthwhile each facility is.

Bookings table

The cd.bookings table definition is as follows:

CREATE TABLE cd.bookings (
  bookid integer NOT NULL,
  facid integer NOT NULL,
  memid integer NOT NULL,
  starttime timestamp NOT NULL,
  slots integer NOT NULL,
  CONSTRAINT bookings_pk PRIMARY KEY (bookid),
  CONSTRAINT fk_bookings_facid FOREIGN KEY (facid) REFERENCES cd.facilities(facid),
  CONSTRAINT fk_bookings_memid FOREIGN KEY (memid) REFERENCES cd.members(memid)
);

The cd.bookings table is for tracking bookings of facilities. The table stores the facility id (facid), the member who made the booking (memid), the start of the booking (starttime), and how many half hour 'slots' the booking was made for (slots). This idiosyncratic design will make certain queries more difficult, but should provide you with some interesting challenges - as well as prepare you for the horror of working with some real-world databases.

I want to use my own Postgres system

No problem! Getting up and running isn't too hard. First, you'll need an install of PostgreSQL, which you can get from the official Postgres site. Once you have it started, download the PostgreSQL Exercises SQL.

Finally, run

psql -U <username> -f clubdata.sql -d postgres -x -q

to create the exercises database, the Postgres pgexercises user, the tables, and to load the data in. Note that you may find that the sort order of your results differs from those shown on the web site: that's probably because your Postgres is set up using a different locale to that used by PGExercises (which uses the C locale)

When you're running queries, you may find psql a little clunky. If so, I recommend trying out pgAdmin or the Eclipse database development tools.

Basic

Intro

This category deals with the basics of SQL. It covers SELECT and WHERE clauses, CASE expressions, UNIONs, and a few other odds and ends. If you're already educated in SQL you will probably find these exercises fairly easy. If not, you should find them a good point to start learning for the more difficult categories ahead!

If you struggle with these questions, I strongly recommend Learning SQL, by Alan Beaulieu, as a concise and well-written book on the subject. If you're interested in the fundamentals of database systems (as opposed to just how to use them), you should also investigate An Introduction to Database Systems by C.J. Date.

Retrieve everything from a table

Question

How can you retrieve all information from the cd.facilities table (the order of the result set does not matter)?

Expected Result

┌───────┬─────────────────┬────────────┬───────────┬───────────────┬────────────────────┐
│ facid │      name       │ membercost │ guestcost │ initialoutlay │ monthlymaintenance │
├───────┼─────────────────┼────────────┼───────────┼───────────────┼────────────────────┤
│     0 │ Tennis Court 1  │          5 │        25 │         10000 │                200 │
│     1 │ Tennis Court 2  │          5 │        25 │          8000 │                200 │
│     2 │ Badminton Court │          0 │      15.5 │          4000 │                 50 │
│     3 │ Table Tennis    │          0 │         5 │           320 │                 10 │
│     4 │ Massage Room 1  │         35 │        80 │          4000 │               3000 │
│     5 │ Massage Room 2  │         35 │        80 │          4000 │               3000 │
│     6 │ Squash Court    │        3.5 │      17.5 │          5000 │                 80 │
│     7 │ Snooker Table   │          0 │         5 │           450 │                 15 │
│     8 │ Pool Table      │          0 │         5 │           400 │                 15 │
└───────┴─────────────────┴────────────┴───────────┴───────────────┴────────────────────┘
(9 rows)

Hint

SELECT * can be used to retrieve all columns from a table.

My Answer

SELECT
  F.*
FROM
  cd.facilities F;

In general, it is best to explicitly name columns in the SELECT query instead of the * catch-all. This answer discusses this issue a bit more. Essentially, * is useful when testing things and debugging, but naming the columns is good for portability (if your table is updated in the future, * will result in grabbing everything from the updated table, intentional or not).

---

Discussion

SELECT
  *
FROM
  cd.facilities;

The SELECT statement is the basic starting block for queries that read information out of the database. A minimal SELECT statement is generally comprised of SELECT [some set of columns] FROM [some table or group of tables].

In this case, we want all of the information from the cd.facilities table. The FROM part is easy--we just need to specify the cd.facilities table. cd is the table's schema (i.e., a term used for a logical grouping of related information in the database).

Next, we need to specify that we want all of the columns. Conveniently, there's a shorthand for "all columns": *. We can use this instead of laboriously specifying all the column names.

---

Schema Reminder

Retrieve specific columns from a table

Question

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? Order does not matter.

Expected Result

┌─────────────────┬────────────┐
│      name       │ membercost │
├─────────────────┼────────────┤
│ Tennis Court 1  │          5 │
│ Tennis Court 2  │          5 │
│ Badminton Court │          0 │
│ Table Tennis    │          0 │
│ Massage Room 1  │         35 │
│ Massage Room 2  │         35 │
│ Squash Court    │        3.5 │
│ Snooker Table   │          0 │
│ Pool Table      │          0 │
└─────────────────┴────────────┘
(9 rows)

Hint

The SELECT statement allows you to specify which column names to retrieve.

My Answer

SELECT
  F.name,
  F.membercost
FROM
  cd.facilities F;

This is generally the way you want to go (i.e., specifying the column names as opposed to just supplying * to grab everything). It is safer and more performant.

---

Discussion

SELECT
  name,
  membercost
FROM
  cd.facilities;

For this question, we need to specify the columns that we want. We can do that with a simple comma-delimited list of column names specified to the SELECT statement. All the database does is look at the columns available in the FROM clause, and return the ones we asked for, as illustrated below.

Generally speaking, for non-throwaway queries it's considered desirable to specify the names of the columns you want in your queries rather than using *. This is because your application might not be able to cope if more columns get added into the table.

---

Schema Reminder

Control which rows are retrieved

Question

How can you produce a list of facilities that charge a fee to members?

Expected Result

┌───────┬────────────────┬────────────┬───────────┬───────────────┬────────────────────┐
│ facid │      name      │ membercost │ guestcost │ initialoutlay │ monthlymaintenance │
├───────┼────────────────┼────────────┼───────────┼───────────────┼────────────────────┤
│     0 │ Tennis Court 1 │          5 │        25 │         10000 │                200 │
│     1 │ Tennis Court 2 │          5 │        25 │          8000 │                200 │
│     4 │ Massage Room 1 │         35 │        80 │          4000 │               3000 │
│     5 │ Massage Room 2 │         35 │        80 │          4000 │               3000 │
│     6 │ Squash Court   │        3.5 │      17.5 │          5000 │                 80 │
└───────┴────────────────┴────────────┴───────────┴───────────────┴────────────────────┘
(5 rows)

Hint

The WHERE clause allows you to filter the rows that you want to retrieve.

My Answer

SELECT 
  F.*
FROM
  cd.facilities F
WHERE
  F.membercost > 0;

Discussion

SELECT
  *
FROM
  cd.facilities
WHERE
  membercost > 0;

The FROM clause is used to build up a set of candidate rows to read results from. In our examples so far, this set of rows has simply been the contents of a table. In the future we will explore joining tables, which allows us to create much more interesting candidates.

Once we've built up our set of candidate rows, the WHERE clause allows us to filter for the rows we're interested in, namely those with a membercost of more than zero in this case. As you will see in later exercises, WHERE clauses can have multiple components combined with boolean logic; it's possible, for instance, to search for facilities with a cost greater than 0 and less than 10. The filtering action of the WHERE clause on the facilities table is illustrated below:

---

Schema Reminder

Control which rows are retrieved - part 2

Question

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 facility id, facility name, member cost, and monthly maintenance of the facilities in question.

Expected Result

┌───────┬────────────────┬────────────┬────────────────────┐
│ facid │      name      │ membercost │ monthlymaintenance │
├───────┼────────────────┼────────────┼────────────────────┤
│     4 │ Massage Room 1 │         35 │               3000 │
│     5 │ Massage Room 2 │         35 │               3000 │
└───────┴────────────────┴────────────┴────────────────────┘
(2 rows)

Hint

The WHERE clause allows you to filter the rows that you want to retrieve.

My Answer

SELECT
  F.facid, 
  F.name, 
  F.membercost,
  F.monthlymaintenance
FROM
  cd.facilities F
WHERE
  F.membercost > 0 
  AND F.membercost < (F.monthlymaintenance * 1/50);

You have to be somewhat careful here due to how integer division works in PostgreSQL. Specifically, replacing D.monthlymaintenance * 1/50 above with 1/50 * F.monthlymaintenance will result in an empty result set. This is because 1/50 resolves to 0 since both are of type integer. But monthlymaintenance is of type numeric. Hence, we should aim to be clear with our implementation of "1/50"th--we can do something like monthlymaintenance/50.0 where 50.0 implicitly instructs Postgres to treat 50 as a numeric type.

The Postgres docs note that division for integral types truncates the result towards zero.

---

Discussion

SELECT
  facid,
  name,
  membercost,
  monthlymaintenance
FROM
  cd.facilities
WHERE
  membercost > 0
  AND (membercost < monthlymaintenance / 50.0);

The WHERE clause allows us to filter for the rows we're interested in, namely those with a membercost of more than zero AND a membercost of less than 1/50th of the monthly maintenance cost in this case. As you can see, the massage rooms are very expensive to run thanks to staffing costs!

When we want to test for two or more conditions, we use AND to combine them. We can, as you might expect, use OR to test whether either of a pair of conditions is true.

You might have noticed that this is our first query that combines a WHERE clause with selecting specific columns. You can see in the image below the effect of this: the intersection of the selected columns and the selected rows gives us the data to return. This may not seem too interesting now, but as we add in more complex operations like joins later, you'll see the simple elegance of this behaviour.

---

Schema Reminder

Basic string searches

Question

How can you produce a list of all facilities with the word 'Tennis' in their name?

Expected Result

┌───────┬────────────────┬────────────┬───────────┬───────────────┬────────────────────┐
│ facid │      name      │ membercost │ guestcost │ initialoutlay │ monthlymaintenance │
├───────┼────────────────┼────────────┼───────────┼───────────────┼────────────────────┤
│     0 │ Tennis Court 1 │          5 │        25 │         10000 │                200 │
│     1 │ Tennis Court 2 │          5 │        25 │          8000 │                200 │
│     3 │ Table Tennis   │          0 │         5 │           320 │                 10 │
└───────┴────────────────┴────────────┴───────────┴───────────────┴────────────────────┘
(3 rows)

Hint

Try looking up the SQL LIKE operator.

My Answer

SELECT
  F.*
FROM
  cd.facilities F
WHERE
  F.name LIKE '%Tennis%';

Discussion

SELECT
  *
FROM
  cd.facilities
WHERE
  name LIKE '%Tennis%';

SQL's LIKE operator provides simple pattern matching on strings. It's pretty much universally implemented, and is nice and simple to use - it just takes a string with the % character matching any string, and _ matching any single character. In this case, we're looking for names containing the word 'Tennis', so putting a % on either side fits the bill.

There's other ways to accomplish this task: Postgres supports regular expressions with the ~ operator, for example. Use whatever makes you feel comfortable, but do be aware that the LIKE operator is much more portable between systems.

Also be aware that Postgres, unlike MySQL or SQL Server for instance, is case-sensitive. Postgres has an ILIKE operator which can be used to make the pattern matching case-insensitive.

---

Schema Reminder

Matching against multiple possible values

Question

How can you retrieve the details of facilities with ID 1 and 5? Try to do it without using the OR operator.

Expected Result

┌───────┬────────────────┬────────────┬───────────┬───────────────┬────────────────────┐
│ facid │      name      │ membercost │ guestcost │ initialoutlay │ monthlymaintenance │
├───────┼────────────────┼────────────┼───────────┼───────────────┼────────────────────┤
│     1 │ Tennis Court 2 │          5 │        25 │          8000 │                200 │
│     5 │ Massage Room 2 │         35 │        80 │          4000 │               3000 │
└───────┴────────────────┴────────────┴───────────┴───────────────┴────────────────────┘
(2 rows)

Hint

Try looking up the SQL IN operator.

My Answer

SELECT
  F.*
FROM
  cd.facilities F
WHERE
  F.facid IN (1,5);

Discussion

SELECT
  *
FROM
  cd.facilities
WHERE
  facid IN (1,5);

The obvious answer to this question is to use a WHERE clause that looks like WHERE facid = 1 OR facid = 5. An alternative that is easier with large numbers of possible matches is the IN operator. The IN operator takes a list of possible values, and matches them against (in this case) the facid. If one of the values matches, the WHERE clause is true for that row, and the row is returned.

The IN operator is a good early demonstrator of the elegance of the relational model. The argument it takes is not just a list of values - it's actually a table with a single column. Since queries also return tables, if you create a query that returns a single column, then you can feed those results into an IN operator. To give a toy example:

SELECT
  *
FROM
  cd.facilities
WHERE
  facid IN (SELECT facid FROM cd.facilities);

This example is functionally equivalent to just selecting all the facilities, but shows you how to feed the results of one query into another. The inner query is called a subquery and we will learn more about those later.

---

Schema Reminder

Classify results into buckets

Question

How 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 of the facilities in question as well as a cost column that specifies whether the booking is 'cheap' or 'expensive' based on the parameters outlined above.

Expected Result

┌─────────────────┬───────────┐
│      name       │   cost    │
├─────────────────┼───────────┤
│ Tennis Court 1  │ expensive │
│ Tennis Court 2  │ expensive │
│ Badminton Court │ cheap     │
│ Table Tennis    │ cheap     │
│ Massage Room 1  │ expensive │
│ Massage Room 2  │ expensive │
│ Squash Court    │ cheap     │
│ Snooker Table   │ cheap     │
│ Pool Table      │ cheap     │
└─────────────────┴───────────┘
(9 rows)

Hint

Try looking up the SQL CASE statement as well as AS for column aliases.

My Answer

SELECT
  F.name,
  (CASE
    WHEN F.monthlymaintenance > 100 THEN 'expensive'
    ELSE 'cheap'
  END) AS cost
FROM
  cd.facilities F;

Discussion

SELECT
  name,
  CASE
    WHEN (monthlymaintenance > 100) THEN 'expensive'
    ELSE 'cheap'
  END AS cost
FROM
  cd.facilities;

This exercise contains a few new concepts. The first is the fact that we're doing computation in the area of the query between SELECT and FROM. Previously we've only used this to select columns that we want to return, but you can put anything in here that will produce a single result per returned row - including subqueries.

The second new concept is the CASE statement itself. CASE is effectively like if/switch statements in other languages, with a form as shown in the query. To add a 'middling' option, we would simply insert another WHEN ...THEN section.

Finally, there's the AS operator. This is simply used to label columns or expressions (i.e., it is a so-called column alias), to make them display more nicely or to make them easier to reference when used as part of a subquery.

---

Schema Reminder

Working with dates

Question

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.

Expected Result

┌───────┬───────────────────┬───────────┬─────────────────────┐
│ memid │      surname      │ firstname │      joindate       │
├───────┼───────────────────┼───────────┼─────────────────────┤
│    24 │ Sarwin            │ Ramnaresh │ 2012-09-01 08:44:42 │
│    26 │ Jones             │ Douglas   │ 2012-09-02 18:43:05 │
│    27 │ Rumney            │ Henrietta │ 2012-09-05 08:42:35 │
│    28 │ Farrell           │ David     │ 2012-09-15 08:22:05 │
│    29 │ Worthington-Smyth │ Henry     │ 2012-09-17 12:27:15 │
│    30 │ Purview           │ Millicent │ 2012-09-18 19:04:01 │
│    33 │ Tupperware        │ Hyacinth  │ 2012-09-18 19:32:05 │
│    35 │ Hunt              │ John      │ 2012-09-19 11:32:45 │
│    36 │ Crumpet           │ Erica     │ 2012-09-22 08:36:38 │
│    37 │ Smith             │ Darren    │ 2012-09-26 18:08:45 │
└───────┴───────────────────┴───────────┴─────────────────────┘
(10 rows)

Hint

Look up the SQL TIMESTAMP format, and remember that you can compare dates much like you would integer values.

My Answer

SELECT
  M.memid,
  M.surname,
  M.firstname,
  M.joindate
FROM
  cd.members M
WHERE
  M.joindate >= '2012-09-01';

Discussion

SELECT
  memid,
  surname,
  firstname,
  joindate
FROM
  cd.members
WHERE
  joindate >= '2012-09-01';

This is our first look at SQL timestamps. They're formatted in descending order of magnitude: YYYY-MM-DD HH:MM:SS.nnnnnn. We can compare them just like we might a unix timestamp, although getting the differences between dates is a little more involved (and powerful!). In this case, we've just specified the date portion of the timestamp. This gets automatically cast by Postgres into the full timestamp 2012-09-01 00:00:00.

---

Schema Reminder

Removing duplicates, and ordering results

Question

How can you produce an ordered list of the first 10 surnames in the members table? The list must not contain duplicates.

Expected Result

┌─────────┐
│ surname │
├─────────┤
│ Bader   │
│ Baker   │
│ Boothe  │
│ Butters │
│ Coplin  │
│ Crumpet │
│ Dare    │
│ Farrell │
│ GUEST   │
│ Genting │
└─────────┘
(10 rows)

Hint

Look up SELECT DISTINCT, ORDER BY, and LIMIT.

My Answer

SELECT DISTINCT
  M.surname
FROM
  cd.members M
ORDER BY
  M.surname
LIMIT 10;

Discussion

SELECT
  DISTINCT surname
FROM
  cd.members
ORDER BY
  surname
LIMIT
  10;

There's three new concepts here, but they're all pretty simple.

• Specifying DISTINCT after SELECT removes duplicate rows from the result set. Note that this applies to rows: if row A has multiple columns, row B is only equal to it if the values in all columns are the same. As a general rule, don't use DISTINCT in a willy-nilly fashion--it's not free to remove duplicates from large query result sets, so do it as-needed.
• Specifying ORDER BY (after the FROM and WHERE clauses, near the end of the query) allows results to be ordered by a column or set of columns (comma separated).
• The LIMIT keyword allows you to limit the number of results retrieved. This is useful for getting results a page at a time, and can be combined with the OFFSET keyword to get following pages. This is the same approach used by MySQL and is very convenient - you may, unfortunately, find that this process is a little more complicated in other DBs.

---

Schema Reminder

Combining results from multiple queries

Question

You, for some reason, want a combined list of all surnames and all facility names. Yes, this is a contrived example. Produce that list! The listing of surnames and names should have a column title of surname.

Expected Result

┌───────────────────┐
│      surname      │
├───────────────────┤
│ Hunt              │
│ Farrell           │
│ Tennis Court 2    │
│ Table Tennis      │
│ Dare              │
│ Rownam            │
│ GUEST             │
│ Badminton Court   │
│ Smith             │
│ Tupperware        │
│ Owen              │
│ Worthington-Smyth │
│ Butters           │
│ Rumney            │
│ Tracy             │
│ Crumpet           │
│ Purview           │
│ Massage Room 2    │
│ Sarwin            │
│ Baker             │
│ Pool Table        │
│ Snooker Table     │
│ Jones             │
│ Coplin            │
│ Mackenzie         │
│ Boothe            │
│ Joplette          │
│ Stibbons          │
│ Squash Court      │
│ Tennis Court 1    │
│ Pinker            │
│ Genting           │
│ Bader             │
│ Massage Room 1    │
└───────────────────┘
(34 rows)

Hint

Look up the SQL keyword UNION.

My Answer

SELECT M.surname FROM cd.members M
UNION
SELECT F.name FROM cd.facilities F;

Discussion

SELECT
  surname
FROM
  cd.members
UNION
SELECT
  name
FROM
  cd.facilities;

The UNION operator does what you might expect: combines the results of two SQL queries into a single table. The caveat is that both results from the two queries must have the same number of columns and compatible data types.

Additionally, the result set is not predictable in terms of what order records are returned in. To impose order, you can use ORDER BY after the second SELECT clause once everything has been unioned.

UNION removes duplicate rows, while UNION ALL does not. Use UNION ALL by default, unless you care about duplicate results.

---

Schema Reminder

Simple aggregation

Question

You'd like to get the signup date of your last member. How can you retrieve this information? Return the single result under a column labelled latest.

Expected Result

┌─────────────────────┐
│       latest        │
├─────────────────────┤
│ 2012-09-26 18:08:45 │
└─────────────────────┘
(1 row)

Hint

Look up the SQL aggregate function MAX.

My Answer

The normal approach might be something like

SELECT
  M.joindate AS latest
FROM
  cd.members M
ORDER BY
  M.joindate DESC
LIMIT 1;

but aggregate functions can work on not just numbers but dates as well:

SELECT 
  MAX(M.joindate) AS latest
FROM 
  cd.members M;

---

Discussion

SELECT
  MAX(joindate) AS latest
FROM
  cd.members;

This is our first foray into SQL's aggregate functions. They're used to extract information about whole groups of rows, and allow us to easily ask questions like:

• What's the most expensive facility to maintain on a monthly basis?
• Who has recommended the most new members?
• How much time has each member spent at our facilities?

The MAX aggregate function here is very simple: it receives all the possible values for joindate, and outputs the one that's biggest. There's a lot more power to aggregate functions, which you will come across in future exercises.

---

Schema Reminder

More aggregation

Question

You'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?

Expected Result

┌───────────┬─────────┬─────────────────────┐
│ firstname │ surname │      joindate       │
├───────────┼─────────┼─────────────────────┤
│ Darren    │ Smith   │ 2012-09-26 18:08:45 │
└───────────┴─────────┴─────────────────────┘
(1 row)

Hint

The LIMIT keyword may be helpful. You may also find a subquery to be quite useful in getting this done.

My Answer

Probably the most straightforward way to accomplish this is as follows:

SELECT
  M.firstname,
  M.surname,
  M.joindate
FROM
  cd.members M
ORDER BY
  M.joindate DESC
LIMIT 1;

But using a subquery adds some flair:

SELECT
  M1.firstname,
  M1.surname,
  M1.joindate
FROM
  cd.members M1
WHERE
  M1.joindate = (SELECT MAX(M2.joindate) FROM cd.members M2);

Note that the first solution, which uses LIMIT 1, is functionally different from the second solution. They do not do the same things. For example, suppose there are multiple people who signed up at the same time and that time happens to be the last time anyone signed up. Then it is technically true that several members were last in signing up.

The first solution would only return a single member whereas the subqeury solution would return all members.

---

Discussion

SELECT
  firstname,
  surname,
  joindate
FROM
  cd.members
WHERE
  joindate = (
    SELECT
      MAX(joindate)
    FROM
      cd.members
  );

In the suggested approach above, you use a subquery to find out what the most recent joindate is. This subquery returns a scalar table - that is, a table with a single column and a single row. Since we have just a single value, we can substitute the subquery anywhere we might put a single constant value. In this case, we use it to complete the WHERE clause of a query to find a given member.

You might hope that you'd be able to do something like below:

SELECT
  firstname,
  surname,
  MAX(joindate)
FROM
  cd.members;

Unfortunately, this doesn't work. The MAX function doesn't restrict rows like the WHERE clause does - it simply takes in a bunch of values and returns the biggest one. The database is then left wondering how to pair up a long list of names with the single joindate that's come out of the MAX function, and fails. Instead, you're left having to say 'find me the row(s) which have a joindate that's the same as the maximum joindate'.

As mentioned by the hint, there's other ways to get this job done - one example is below. In this approach, rather than explicitly finding out what the last joined date is, we simply order our members table in descending order of join date, and pick off the first one. Note that this approach does not cover the extremely unlikely eventuality of two people joining at the exact same time.

SELECT
  firstname,
  surname,
  joindate
FROM
  cd.members
ORDER BY
  joindate DESC
LIMIT
  1;

---

Schema Reminder

Joins and Subqueries

Intro

This category deals primarily with a foundational concept in relational database systems: joining. Joining allows you to combine related information from multiple tables to answer a question. This isn't just beneficial for ease of querying: a lack of join capability encourages denormalisation of data, which increases the complexity of keeping your data internally consistent.

This topic covers INNER, OUTER, and SELF joins, as well as spending a little time on subqueries (queries within queries). If you struggle with these questions, I strongly recommend Learning SQL, by Alan Beaulieu, as a concise and well-written book on the subject.

Retrieve the start times of members' bookings

Question

How can you produce a list of the start times for bookings by members named 'David Farrell'?

Expected Result

┌─────────────────────┐
│      starttime      │
├─────────────────────┤
│ 2012-09-18 09:00:00 │
│ 2012-09-18 13:30:00 │
│ 2012-09-18 17:30:00 │
│ 2012-09-18 20:00:00 │
│ 2012-09-19 09:30:00 │
│ 2012-09-19 12:00:00 │
│ 2012-09-19 15:00:00 │
│ 2012-09-20 11:30:00 │
│ 2012-09-20 14:00:00 │
│ 2012-09-20 15:30:00 │
│ 2012-09-21 10:30:00 │
│ 2012-09-21 14:00:00 │
│ 2012-09-22 08:30:00 │
│ 2012-09-22 17:00:00 │
│ 2012-09-23 08:30:00 │
│ 2012-09-23 17:30:00 │
│ 2012-09-23 19:00:00 │
│ 2012-09-24 08:00:00 │
│ 2012-09-24 12:30:00 │
│ 2012-09-24 16:30:00 │
│ 2012-09-25 15:30:00 │
│ 2012-09-25 17:00:00 │
│ 2012-09-26 13:00:00 │
│ 2012-09-26 17:00:00 │
│ 2012-09-27 08:00:00 │
│ 2012-09-28 09:30:00 │
│ 2012-09-28 11:30:00 │
│ 2012-09-28 13:00:00 │
│ 2012-09-29 10:30:00 │
│ 2012-09-29 13:30:00 │
│ 2012-09-29 14:30:00 │
│ 2012-09-29 16:00:00 │
│ 2012-09-29 17:30:00 │
│ 2012-09-30 14:30:00 │
└─────────────────────┘
(34 rows)

Hint

Take a look at the documentation for INNER JOIN.

My Answer

SELECT
  B.starttime
FROM
  cd.members M
  INNER JOIN cd.bookings B ON M.memid = B.memid
WHERE
  M.firstname = 'David' 
  AND M.surname = 'Farrell';

Discussion

SELECT
  bks.starttime
FROM
  cd.bookings bks
  INNER JOIN cd.members mems ON mems.memid = bks.memid
WHERE
  mems.firstname = 'David'
  AND mems.surname = 'Farrell';

The most commonly used kind of join is the INNER JOIN. What this does is combine two tables based on a join expression - in this case, for each member id in the members table, we're looking for matching values in the bookings table. Where we find a match, a row combining the values for each table is returned. Note that we've given each table an alias (bks and mems). This is used for two reasons: firstly, it's convenient, and secondly we might join to the same table several times, requiring us to distinguish between columns from each different time the table was joined in.

Let's ignore our SELECT and WHERE clauses for now, and focus on what the FROM statement produces. In all our previous examples, FROM has just been a simple table. What is it now? Another table! This time, it's produced as a composite of bookings and members. You can see a subset of the output of the join below:

For each member in the members table, the join has found all the matching member ids in the bookings table. For each match, it's then produced a row combining the row from the members table, and the row from the bookings table.

Obviously, this is too much information on its own, and any useful question will want to filter it down. In our query, we use the start of the SELECT clause to pick columns, and the WHERE clause to pick rows, as illustrated below:

That's all we need to find David's bookings! In general, I encourage you to remember that the output of the FROM clause is essentially one big table that you then filter information out of. This may sound inefficient - but don't worry, under the covers the DB will be behaving much more intelligently.

One final note: there's two different syntaxes for inner joins. I've shown you the one I prefer, that I find more consistent with other join types. You'll commonly see a different syntax, shown below:

SELECT
  bks.starttime
FROM
  cd.bookings bks,
  cd.members mems
WHERE
  mems.firstname = 'David'
  AND mems.surname = 'Farrell'
  AND mems.memid = bks.memid;

This is functionally exactly the same as the approved answer. If you feel more comfortable with this syntax, feel free to use it!

---

Schema Reminder

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 (ascending).

Expected Result

┌─────────────────────┬────────────────┐
│        start        │      name      │
├─────────────────────┼────────────────┤
│ 2012-09-21 08:00:00 │ Tennis Court 1 │
│ 2012-09-21 08:00:00 │ Tennis Court 2 │
│ 2012-09-21 09:30:00 │ Tennis Court 1 │
│ 2012-09-21 10:00:00 │ Tennis Court 2 │
│ 2012-09-21 11:30:00 │ Tennis Court 2 │
│ 2012-09-21 12:00:00 │ Tennis Court 1 │
│ 2012-09-21 13:30:00 │ Tennis Court 1 │
│ 2012-09-21 14:00:00 │ Tennis Court 2 │
│ 2012-09-21 15:30:00 │ Tennis Court 1 │
│ 2012-09-21 16:00:00 │ Tennis Court 2 │
│ 2012-09-21 17:00:00 │ Tennis Court 1 │
│ 2012-09-21 18:00:00 │ Tennis Court 2 │
└─────────────────────┴────────────────┘
(12 rows)

Hint

This is another INNER JOIN. You may also want to think about using the IN or LIKE operators to limit the results you get back.

My Answer

SELECT
  B.starttime AS start,
  F.name
FROM
  cd.facilities F
  INNER JOIN cd.bookings B ON F.facid = B.facid
WHERE
  F.name ILIKE '%tennis court%'
  AND starttime >= '2012-09-21'
  AND starttime < '2012-09-22'
ORDER BY
  starttime ASC;

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.

---

Schema Reminder

Produce a list of all members who have recommended another member

Question

How 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 and then firstname.

Expected Result

┌───────────┬──────────┐
│ firstname │ surname  │
├───────────┼──────────┤
│ Florence  │ Bader    │
│ Timothy   │ Baker    │
│ Gerald    │ Butters  │
│ Jemima    │ Farrell  │
│ Matthew   │ Genting  │
│ David     │ Jones    │
│ Janice    │ Joplette │
│ Millicent │ Purview  │
│ Tim       │ Rownam   │
│ Darren    │ Smith    │
│ Tracy     │ Smith    │
│ Ponder    │ Stibbons │
│ Burton    │ Tracy    │
└───────────┴──────────┘
(13 rows)

Hint

You can use an INNER JOIN just like the previous exercise(s) or you can use a subquery.

My Answer

There are multiple ways of going about this. Perhaps the clearest way is the subquery way:

SELECT DISTINCT
  M1.firstname,
  M1.surname
FROM
  cd.members M1
WHERE
  M1.memid IN (SELECT DISTINCT M2.recommendedby FROM cd.members M2 WHERE M2.recommendedby IS NOT NULL)
ORDER BY
  M1.surname, M1.firstname;

The subquery

SELECT DISTINCT M2.recommendedby FROM cd.members M2 WHERE M2.recommendedby IS NOT NULL

retrieves all the distinct member IDs of those who have recommended people. This gives us the complete list of member IDs for the recommenders. Hence, to find the member info for the recommenders, we simply have to find the members whose IDs are in the list obtained by the subquery.

Another way of going about all of this is with a so-called "self-join" which is really just a joining technique that uses INNER JOIN to join a table with itself so as to compare rows within the same table.

In this case, a self-join is appropriate since the recommendedby field, if not empty, holds the member ID (memid) of another member whose information is in the same table (i.e., cd.members). The query

SELECT
  recommendee.firstname,
  recommendee.surname,
  recommendee.recommendedby,
  recommender.memid,
  recommender.firstname,
  recommender.surname
FROM
  cd.members recommendee 
    INNER JOIN cd.members recommender ON recommendee.recommendedby = recommender.memid
ORDER BY
  recommendee.surname, recommendee.firstname;

is quite informative in the result set it yields:

Specifically, for each person recommended (i.e., the recommendee) we see their recommender. Including the recommendedby and memid column values is helpful for illustrative purposes her to see exactly how the INNER JOIN is working.

For the purposes of this problem, we are asked to provide distinct recommender information which is now quite simple in light of the query and picture above:

SELECT DISTINCT
  recommender.firstname,
  recommender.surname
FROM
  cd.members recommendee
    INNER JOIN cd.members recommender ON recommendee.recommendedby = recommender.memid
ORDER BY
  surname, firstname;

---

Discussion

SELECT
  DISTINCT recs.firstname AS firstname,
  recs.surname AS surname
FROM
  cd.members mems
  INNER JOIN cd.members recs ON recs.memid = mems.recommendedby
ORDER BY
  surname,
  firstname;

Here's a concept that some people find confusing: you can join a table to itself! This is really useful if you have columns that reference data in the same table, like we do with recommendedby in cd.members.

If you're having trouble visualising this, remember that this works just the same as any other INNER JOIN. Our join takes each row in members that has a recommendedby value, and looks in members again for the row which has a matching member id. It then generates an output row combining the two members entries. This looks like the diagram below:

Note that while we might have two 'surname' columns in the output set, they can be distinguished by their table aliases. Once we've selected the columns that we want, we simply use DISTINCT to ensure that there are no duplicates.

---

Schema Reminder

Produce a list of all members, along with their recommender

Question

How can you output a list of all members, including the individual who recommended them (if any)? Ensure that results are ordered by (surname, firstname).

Expected Result

┌───────────┬───────────────────┬───────────┬──────────┐
│ memfname  │     memsname      │ recfname  │ recsname │
├───────────┼───────────────────┼───────────┼──────────┤
│ Florence  │ Bader             │ Ponder    │ Stibbons │
│ Anne      │ Baker             │ Ponder    │ Stibbons │
│ Timothy   │ Baker             │ Jemima    │ Farrell  │
│ Tim       │ Boothe            │ Tim       │ Rownam   │
│ Gerald    │ Butters           │ Darren    │ Smith    │
│ Joan      │ Coplin            │ Timothy   │ Baker    │
│ Erica     │ Crumpet           │ Tracy     │ Smith    │
│ Nancy     │ Dare              │ Janice    │ Joplette │
│ David     │ Farrell           │           │          │
│ Jemima    │ Farrell           │           │          │
│ GUEST     │ GUEST             │           │          │
│ Matthew   │ Genting           │ Gerald    │ Butters  │
│ John      │ Hunt              │ Millicent │ Purview  │
│ David     │ Jones             │ Janice    │ Joplette │
│ Douglas   │ Jones             │ David     │ Jones    │
│ Janice    │ Joplette          │ Darren    │ Smith    │
│ Anna      │ Mackenzie         │ Darren    │ Smith    │
│ Charles   │ Owen              │ Darren    │ Smith    │
│ David     │ Pinker            │ Jemima    │ Farrell  │
│ Millicent │ Purview           │ Tracy     │ Smith    │
│ Tim       │ Rownam            │           │          │
│ Henrietta │ Rumney            │ Matthew   │ Genting  │
│ Ramnaresh │ Sarwin            │ Florence  │ Bader    │
│ Darren    │ Smith             │           │          │
│ Darren    │ Smith             │           │          │
│ Jack      │ Smith             │ Darren    │ Smith    │
│ Tracy     │ Smith             │           │          │
│ Ponder    │ Stibbons          │ Burton    │ Tracy    │
│ Burton    │ Tracy             │           │          │
│ Hyacinth  │ Tupperware        │           │          │
│ Henry     │ Worthington-Smyth │ Tracy     │ Smith    │
└───────────┴───────────────────┴───────────┴──────────┘
(31 rows)

Hint

Try investigating the LEFT JOIN.

My Answer

Recall the query used for illustrative purposes in the previous exercise:

SELECT
  recommendee.firstname,
  recommendee.surname,
  recommendee.recommendedby,
  recommender.memid,
  recommender.firstname,
  recommender.surname
FROM
  cd.members recommendee 
    INNER JOIN cd.members recommender ON recommendee.recommendedby = recommender.memid
ORDER BY
  recommendee.surname, recommendee.firstname;

This gave us a very informative result set:

We can see each recommendee and their correspondent recommender. But we are now being asked to produce a list of all members regardless of whether or not they have been recommended. The way we can accomplish this is instead of using the recommendee table alias for an INNER JOIN as in the query above, we can now use an allmembers table alias for a LEFT JOIN:

SELECT
  allmembers.firstname,
  allmembers.surname,
  allmembers.recommendedby,
  recommender.memid,
  recommender.firstname,
  recommender.surname
FROM
  cd.members allmembers
    LEFT JOIN cd.members recommender ON allmembers.recommendedby = recommender.memid
ORDER BY
  allmembers.surname, allmembers.firstname;

We still get everything we got in the previous result set where we used recommendee for an INNER JOIN, but this time, using allmembers for a LEFT JOIN, we got all the members who had not actually been recommended as well:

Now all we need to do is reshape our query to be formatted so we get the same expected results as in the exercise query:

SELECT
  allmembers.firstname AS memfname,
  allmembers.surname AS memsname,
  recommender.firstname AS recfname,
  recommender.surname AS recsname
FROM
  cd.members allmembers
    LEFT JOIN cd.members recommender ON allmembers.recommendedby = recommender.memid
ORDER BY
  memsname, memfname;

Lastly, note how we can use column aliases as above to ORDER BY however we see fit and keep the syntax a bit briefer than it might be otherwise.

---

Discussion

SELECT
  mems.firstname AS memfname,
  mems.surname AS memsname,
  recs.firstname AS recfname,
  recs.surname AS recsname
FROM
  cd.members mems
  LEFT OUTER JOIN cd.members recs ON recs.memid = mems.recommendedby
ORDER BY
  memsname,
  memfname;

Let's introduce another new concept: the LEFT OUTER JOIN. These are best explained by the way in which they differ from inner joins. Inner joins take a left and a right table, and look for matching rows based on a join condition (ON). When the condition is satisfied, a joined row is produced. A LEFT OUTER JOIN operates similarly, except that if a given row on the left hand table doesn't match anything, it still produces an output row. That output row consists of the left hand table row, and a bunch of NULLS in place of the right hand table row.

This is useful in situations like this question, where we want to produce output with optional data. We want the names of all members, and the name of their recommender if that person exists. You can't express that properly with an inner join.

As you may have guessed, there's other outer joins too. The RIGHT OUTER JOIN is much like the LEFT OUTER JOIN, except that the left hand side of the expression is the one that contains the optional data. The rarely-used FULL OUTER JOIN treats both sides of the expression as optional.

---

Schema Reminder

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.

Expected Result

┌───────────────────┬────────────────┐
│      member       │    facility    │
├───────────────────┼────────────────┤
│ Anne Baker        │ Tennis Court 1 │
│ Anne Baker        │ Tennis Court 2 │
│ Burton Tracy      │ Tennis Court 1 │
│ Burton Tracy      │ Tennis Court 2 │
│ Charles Owen      │ Tennis Court 1 │
│ Charles Owen      │ Tennis Court 2 │
│ Darren Smith      │ Tennis Court 2 │
│ David Farrell     │ Tennis Court 1 │
│ David Farrell     │ Tennis Court 2 │
│ David Jones       │ Tennis Court 1 │
│ David Jones       │ Tennis Court 2 │
│ David Pinker      │ Tennis Court 1 │
│ Douglas Jones     │ Tennis Court 1 │
│ Erica Crumpet     │ Tennis Court 1 │
│ Florence Bader    │ Tennis Court 1 │
│ Florence Bader    │ Tennis Court 2 │
│ GUEST GUEST       │ Tennis Court 1 │
│ GUEST GUEST       │ Tennis Court 2 │
│ Gerald Butters    │ Tennis Court 1 │
│ Gerald Butters    │ Tennis Court 2 │
│ Henrietta Rumney  │ Tennis Court 2 │
│ Jack Smith        │ Tennis Court 1 │
│ Jack Smith        │ Tennis Court 2 │
│ Janice Joplette   │ Tennis Court 1 │
│ Janice Joplette   │ Tennis Court 2 │
│ Jemima Farrell    │ Tennis Court 1 │
│ Jemima Farrell    │ Tennis Court 2 │
│ Joan Coplin       │ Tennis Court 1 │
│ John Hunt         │ Tennis Court 1 │
│ John Hunt         │ Tennis Court 2 │
│ Matthew Genting   │ Tennis Court 1 │
│ Millicent Purview │ Tennis Court 2 │
│ Nancy Dare        │ Tennis Court 1 │
│ Nancy Dare        │ Tennis Court 2 │
│ Ponder Stibbons   │ Tennis Court 1 │
│ Ponder Stibbons   │ Tennis Court 2 │
│ Ramnaresh Sarwin  │ Tennis Court 1 │
│ Ramnaresh Sarwin  │ Tennis Court 2 │
│ Tim Boothe        │ Tennis Court 1 │
│ Tim Boothe        │ Tennis Court 2 │
│ Tim Rownam        │ Tennis Court 1 │
│ Tim Rownam        │ Tennis Court 2 │
│ Timothy Baker     │ Tennis Court 1 │
│ Timothy Baker     │ Tennis Court 2 │
│ Tracy Smith       │ Tennis Court 1 │
│ Tracy Smith       │ Tennis Court 2 │
└───────────────────┴────────────────┘
(46 rows)

Hint

This answer requires multiple joins. To concatenate strings you can use the || operator.

My Answer

SELECT DISTINCT
  M.firstname || ' ' || M.surname AS member,
  F.name AS facility
FROM
  cd.members M
  INNER JOIN cd.bookings B ON M.memid = B.memid
  INNER JOIN cd.facilities F ON B.facid = F.facid
WHERE
  F.name ILIKE '%tennis court%'
ORDER BY
  member, facility;

Discussion

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.

---

Schema Reminder

Produce a list of costly bookings

Question

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, and do not use any subqueries.

Expected Result

┌─────────────────┬────────────────┬──────┐
│     member      │    facility    │ cost │
├─────────────────┼────────────────┼──────┤
│ GUEST GUEST     │ Massage Room 2 │  320 │
│ GUEST GUEST     │ Massage Room 1 │  160 │
│ GUEST GUEST     │ Massage Room 1 │  160 │
│ GUEST GUEST     │ Massage Room 1 │  160 │
│ GUEST GUEST     │ Tennis Court 2 │  150 │
│ Jemima Farrell  │ Massage Room 1 │  140 │
│ GUEST GUEST     │ Tennis Court 1 │   75 │
│ GUEST GUEST     │ Tennis Court 2 │   75 │
│ GUEST GUEST     │ Tennis Court 1 │   75 │
│ Matthew Genting │ Massage Room 1 │   70 │
│ Florence Bader  │ Massage Room 2 │   70 │
│ GUEST GUEST     │ Squash Court   │ 70.0 │
│ Jemima Farrell  │ Massage Room 1 │   70 │
│ Ponder Stibbons │ Massage Room 1 │   70 │
│ Burton Tracy    │ Massage Room 1 │   70 │
│ Jack Smith      │ Massage Room 1 │   70 │
│ GUEST GUEST     │ Squash Court   │ 35.0 │
│ GUEST GUEST     │ Squash Court   │ 35.0 │
└─────────────────┴────────────────┴──────┘
(18 rows)

Hint

As before, this answer requires multiple joins. It's more complex WHERE logic than you're used to, and will require a CASE statement in the column selections!

My Answer

SELECT
  M.firstname || ' ' || M.surname AS member,
  F.name AS facility,
  CASE
    WHEN M.memid = 0 THEN B.slots * F.guestcost
    ELSE
      B.slots * F.membercost
  END AS cost
FROM
  cd.members M
  INNER JOIN cd.bookings B ON M.memid = B.memid
  INNER JOIN cd.facilities F ON B.facid = F.facid
WHERE
    B.starttime >= '2012-09-14'
    AND B.starttime < '2012-09-15'
    AND (
      (M.memid = 0 AND B.slots * F.guestcost > 30)
      OR
      (M.memid !=0 AND B.slots * F.membercost > 30)
    )
ORDER BY
  cost DESC;

Discussion

SELECT
  mems.firstname || ' ' || mems.surname AS member,
  facs.name AS facility,
  CASE
    WHEN mems.memid = 0 THEN bks.slots * facs.guestcost
    ELSE bks.slots * facs.membercost
  END AS cost
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
  bks.starttime >= '2012-09-14'
  AND bks.starttime < '2012-09-15'
  AND (
    (
      mems.memid = 0
      AND bks.slots * facs.guestcost > 30
    )
    OR (
      mems.memid != 0
      AND bks.slots * facs.membercost > 30
    )
  )
ORDER BY
  cost DESC;

This is a bit of a complicated one! While it's more complex logic than we've used previously, there's not an awful lot to remark upon. The WHERE clause restricts our output to sufficiently costly rows on 2012-09-14, remembering to distinguish between guests and members. We then use a CASE statement in the column selections to output the correct cost for the member or guest.

---

Schema Reminder

Produce a list of all members, along with their recommender, using no joins

Question

How 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.

Expected Result

┌─────────────────────────┬───────────────────┐
│         member          │    recommender    │
├─────────────────────────┼───────────────────┤
│ Anna Mackenzie          │ Darren Smith      │
│ Anne Baker              │ Ponder Stibbons   │
│ Burton Tracy            │                   │
│ Charles Owen            │ Darren Smith      │
│ Darren Smith            │                   │
│ David Farrell           │                   │
│ David Jones             │ Janice Joplette   │
│ David Pinker            │ Jemima Farrell    │
│ Douglas Jones           │ David Jones       │
│ Erica Crumpet           │ Tracy Smith       │
│ Florence Bader          │ Ponder Stibbons   │
│ GUEST GUEST             │                   │
│ Gerald Butters          │ Darren Smith      │
│ Henrietta Rumney        │ Matthew Genting   │
│ Henry Worthington-Smyth │ Tracy Smith       │
│ Hyacinth Tupperware     │                   │
│ Jack Smith              │ Darren Smith      │
│ Janice Joplette         │ Darren Smith      │
│ Jemima Farrell          │                   │
│ Joan Coplin             │ Timothy Baker     │
│ John Hunt               │ Millicent Purview │
│ Matthew Genting         │ Gerald Butters    │
│ Millicent Purview       │ Tracy Smith       │
│ Nancy Dare              │ Janice Joplette   │
│ Ponder Stibbons         │ Burton Tracy      │
│ Ramnaresh Sarwin        │ Florence Bader    │
│ Tim Boothe              │ Tim Rownam        │
│ Tim Rownam              │                   │
│ Timothy Baker           │ Jemima Farrell    │
│ Tracy Smith             │                   │
└─────────────────────────┴───────────────────┘
(30 rows)

Hint

Answering this question correctly requires the use of a subquery.

My Answer

SELECT DISTINCT
  M.firstname || ' ' || M.surname AS member,
  (SELECT firstname || ' ' || surname FROM cd.members WHERE memid = M.recommendedby) AS recommender
FROM
  cd.members M
ORDER BY
  member ASC;

Discussion

SELECT
  DISTINCT mems.firstname || ' ' || mems.surname AS member,
  (
    SELECT
      recs.firstname || ' ' || recs.surname AS recommender
    FROM
      cd.members recs
    WHERE
      recs.memid = mems.recommendedby
  )
FROM
  cd.members mems
ORDER BY
  member;

This exercise marks the introduction of subqueries. Subqueries are, as the name implies, queries within a query. They're commonly used with aggregates, to answer questions like 'get me all the details of the member who has spent the most hours on Tennis Court 1'.

In this case, we're simply using the subquery to emulate an outer join. For every value of member, the subquery is run once to find the name of the individual who recommended them (if any). A subquery that uses information from the outer query in this way (and thus has to be run for each row in the result set) is known as a correlated subquery.

---

Schema Reminder

Produce a list of costly bookings, using a subquery

Question

The Produce a list of costly bookings exercise contained some messy logic: we had to calculate the booking cost in both the WHERE clause and the CASE statement. Try to simplify this calculation using subqueries. For reference, the question was:

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.

Expected Result

┌─────────────────┬────────────────┬──────┐
│     member      │    facility    │ cost │
├─────────────────┼────────────────┼──────┤
│ GUEST GUEST     │ Massage Room 2 │  320 │
│ GUEST GUEST     │ Massage Room 1 │  160 │
│ GUEST GUEST     │ Massage Room 1 │  160 │
│ GUEST GUEST     │ Massage Room 1 │  160 │
│ GUEST GUEST     │ Tennis Court 2 │  150 │
│ Jemima Farrell  │ Massage Room 1 │  140 │
│ GUEST GUEST     │ Tennis Court 1 │   75 │
│ GUEST GUEST     │ Tennis Court 2 │   75 │
│ GUEST GUEST     │ Tennis Court 1 │   75 │
│ Matthew Genting │ Massage Room 1 │   70 │
│ Florence Bader  │ Massage Room 2 │   70 │
│ GUEST GUEST     │ Squash Court   │ 70.0 │
│ Jemima Farrell  │ Massage Room 1 │   70 │
│ Ponder Stibbons │ Massage Room 1 │   70 │
│ Burton Tracy    │ Massage Room 1 │   70 │
│ Jack Smith      │ Massage Room 1 │   70 │
│ GUEST GUEST     │ Squash Court   │ 35.0 │
│ GUEST GUEST     │ Squash Court   │ 35.0 │
└─────────────────┴────────────────┴──────┘
(18 rows)

Hint

Your answer will be similar to the referenced exercise. Use a subquery in the FROM clause to generate a result set that calculates the total cost of each booking. The outer query can then select the bookings it's interested in.

My Answer

This can be done without any joins whatsoever:

SELECT
  member,
  facility,
  cost
FROM
  (
    SELECT
      (SELECT M.firstname || ' ' || M.surname FROM cd.members M WHERE M.memid = B.memid) AS member,
      (SELECT F.name FROM cd.facilities F WHERE B.facid = F.facid) AS facility,
      CASE
        WHEN memid = 0 THEN slots * (SELECT guestcost FROM cd.facilities F WHERE F.facid = B.facid)
        ELSE
          slots * (SELECT membercost FROM cd.facilities F WHERE F.facid = B.facid)
      END AS cost
    FROM cd.bookings B
    WHERE starttime >= '2012-09-14' AND starttime < '2012-09-15'
  ) AS aggregate_table
WHERE
  cost > 30
ORDER BY
  cost DESC;

But from the above query we can tell that nearly always the condition of the subquery we are using is something similar to WHERE <table-alias-1>.id = <table-alias-2>.id, the exact kind of condition we would want to specify in what to join ON. It's silly not to take advantage of joins in this case; however, a subquery can still be very useful in terms of selecting cost without involving too much funky business in the last WHERE clause. Hence, our solution to this problem can be transformed from the rather unwieldy query

SELECT
  M.firstname || ' ' || M.surname AS member,
  F.name AS facility,
  CASE
    WHEN M.memid = 0 THEN B.slots * F.guestcost
    ELSE
      B.slots * F.membercost
  END AS cost
FROM
  cd.members M
  INNER JOIN cd.bookings B ON M.memid = B.memid
  INNER JOIN cd.facilities F ON B.facid = F.facid
WHERE
    B.starttime >= '2012-09-14'
    AND B.starttime < '2012-09-15'
    AND (
      (M.memid = 0 AND B.slots * F.guestcost > 30)
      OR
      (M.memid !=0 AND B.slots * F.membercost > 30)
    )
ORDER BY
  cost DESC;

where the SELECT clause is polluted with a CASE statement and the overly complicated WHERE clause contains semi-duplicated logic from the CASE statement, to the more easily understood query

SELECT
  member,
  facility,
  cost
FROM
  (
    SELECT
      M.firstname || ' ' || M.surname AS member,
      F.name AS facility,
      CASE
        WHEN M.memid = 0 THEN B.slots * F.guestcost
        ELSE
          B.slots * F.membercost
      END AS cost
    FROM
      cd.members M
      INNER JOIN cd.bookings B ON M.memid = B.memid
      INNER JOIN cd.facilities F ON B.facid = F.facid
    WHERE
      B.starttime >= '2012-09-14' AND B.starttime < '2012-09-15'
  ) AS desired_info
WHERE
  cost > 30
ORDER BY
  cost DESC;

One of the nice things about this cleaned up query is that the initial SELECT statement is cleaner as well as the WHERE clause. There's no issue with accessing the cost column to ORDER BY since we are essentially selecting from a temporary table created by a SELECT subquery in the FROM clause. Such a temporary table (i.e., a temporary table used as in SELECT ... FROM ( <temp-table-created-by-subquery> )) is often referred to as an inline view.

Discussion

SELECT
  member,
  facility,
  cost
FROM
  (
    SELECT
      mems.firstname || ' ' || mems.surname AS member,
      facs.name AS facility,
      CASE
        WHEN mems.memid = 0 THEN bks.slots * facs.guestcost
        ELSE bks.slots * facs.membercost
      END AS cost
    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
      bks.starttime >= '2012-09-14'
      AND bks.starttime < '2012-09-15'
  ) AS bookings
WHERE
  cost > 30
ORDER BY
  cost DESC;

This answer provides a mild simplification to the previous iteration: in the no-subquery version, we had to calculate the member or guest's cost in both the WHERE clause and the CASE statement. In our new version, we produce an inline query that calculates the total booking cost for us, allowing the outer query to simply select the bookings it's looking for. For reference, you may also see subqueries in the FROM clause referred to as inline views.

---

Schema Reminder

Modifying Data

Intro

Querying data is all well and good, but at some point you're probably going to want to put data into your database! This section deals with inserting, updating, and deleting information. Operations that alter your data like this are collectively known as Data Manipulation Language, or DML.

In previous sections, we returned to you the results of the query you've performed. Since modifications like the ones we're making in this section don't return any query results, we instead show you the updated content of the table you're supposed to be working on. You can compare this with the table shown in 'Expected Results' to see how you've done.

If you struggle with these questions, I strongly recommend Learning SQL, by Alan Beaulieu.

Insert some data into a table

Question

The 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.

Hint

INSERT can be used to insert data into a table.

My Answer

INSERT INTO
  cd.facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
VALUES
  (9, 'Spa', 20, 30, 100000, 800);

Discussion

INSERT INTO
  cd.facilities (
    facid,
    name,
    membercost,
    guestcost,
    initialoutlay,
    monthlymaintenance
  )
VALUES
  (9, 'Spa', 20, 30, 100000, 800);

INSERT INTO ... VALUES is the simplest way to insert data into a table. There's not a whole lot to discuss here: VALUES is used to construct a row of data, which the INSERT statement inserts into the table. It's a simple as that.

You can see that there's two sections in parentheses. The first is part of the INSERT statement, and specifies the columns that we're providing data for. The second is part of VALUES, and specifies the actual data we want to insert into each column.

If we're inserting data into every column of the table, as in this example, explicitly specifying the column names is optional. As long as you fill in data for all columns of the table, in the order they were defined when you created the table, you can do something like the following:

INSERT INTO
  cd.facilities
VALUES
  (9, 'Spa', 20, 30, 100000, 800);

Generally speaking, for SQL that's going to be reused I tend to prefer being explicit and specifying the column names.

---

Schema Reminder

Insert multiple rows of data into a table

Question

In 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: 9, Name: 'Spa', membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800.
• facid: 10, Name: 'Squash Court 2', membercost: 3.5, guestcost: 17.5, initialoutlay: 5000, monthlymaintenance: 80.

Hint

VALUES can be used to generate more than one row.

My Answer

INSERT INTO
  cd.facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
VALUES
  (9, 'Spa', 20, 30, 100000, 800),
  (10, 'Squash Court 2', 3.5, 17.5, 5000, 80);

Discussion

INSERT INTO
  cd.facilities (
    facid,
    name,
    membercost,
    guestcost,
    initialoutlay,
    monthlymaintenance
  )
VALUES
  (9, 'Spa', 20, 30, 100000, 800),
  (10, 'Squash Court 2', 3.5, 17.5, 5000, 80);

VALUES can be used to generate more than one row to insert into a table, as seen in this example. Hopefully it's clear what's going on here: the output of VALUES is a table, and that table is copied into cd.facilities, the table specified in the INSERT command.

While you'll most commonly see VALUES when inserting data, Postgres allows you to use VALUES wherever you might use a SELECT. This makes sense: the output of both commands is a table, it's just that VALUES is a bit more ergonomic when working with constant data.

Similarly, it's possible to use SELECT wherever you see a VALUES. This means that you can INSERT the results of a SELECT. For example:

INSERT INTO cd.facilities 
  (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
  SELECT 9, 'Spa', 20, 30, 100000, 800
  UNION ALL
    SELECT 10, 'Squash Court 2', 3.5, 17.5, 5000, 80;

In later exercises you'll see us using INSERT ... SELECT to generate data to insert based on the information already in the database.

---

Schema Reminder

Insert calculated data into a table

Question

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.

Hint

You can calculate data to insert using subqueries.

My Answer

INSERT INTO
  cd.facilities (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
VALUES
  ((SELECT MAX(facid) + 1 FROM cd.facilities), 'Spa', 20, 30, 100000, 800);

Discussion

INSERT INTO cd.facilities
  (facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
  SELECT (SELECT MAX(facid) FROM cd.facilities)+1, 'Spa', 20, 30, 100000, 800; 

In the previous exercises we used VALUES to insert constant data into the facilities table. Here, though, we have a new requirement: a dynamically generated ID. This gives us a real quality of life improvement, as we don't have to manually work out what the current largest ID is: the SQL command does it for us.

Since the VALUES clause is only used to supply constant data, we need to replace it with a query instead. The SELECT statement is fairly simple: there's an inner subquery that works out the next facid based on the largest current id, and the rest is just constant data. The output of the statement is a row that we insert into the facilities table.

While this works fine in our simple example, it's not how you would generally implement an incrementing ID in the real world. Postgres provides SERIAL types that are auto-filled with the next ID when you insert a row. As well as saving us effort, these types are also safer: unlike the answer given in this exercise, there's no need to worry about concurrent operations generating the same ID.

---

Schema Reminder

Update some existing data

Question

We 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.

Hint

You can alter existing data using the UPDATE statement.

My Answer

UPDATE
  cd.facilities
SET
  initialoutlay = 10000
WHERE
  name = 'Tennis Court 2';

Discussion

UPDATE cd.facilities
  SET initialoutlay = 10000
  WHERE facid = 1;   

The UPDATE statement is used to alter existing data. If you're familiar with SELECT queries, it's pretty easy to read: the WHERE clause works in exactly the same fashion, allowing us to filter the set of rows we want to work with. These rows are then modified according to the specifications of the SET clause: in this case, setting the initial outlay.

The WHERE clause is extremely important. It's easy to get it wrong or even omit it, with disastrous results. Consider the following command:

UPDATE cd.facilities
  SET initialoutlay = 10000;

There's no WHERE clause to filter for the rows we're interested in. The result of this is that the update runs on every row in the table! This is rarely what we want to happen.

---

Schema Reminder

Update multiple rows and columns at the same time

Question

We 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.

Hint

The SET clause can update multiple columns.

My Answer

UPDATE
  cd.facilities
SET
  membercost = 6,
  guestcost = 30
WHERE
  name ILIKE '%tennis court%';

Discussion

UPDATE cd.facilities
  SET
    membercost = 6,
    guestcost = 30
  WHERE facid IN (0,1);

The SET clause accepts a comma separated list of values that you want to update.

---

Schema Reminder

Update a row based on the contents of another row

Question

We 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.

Hint

Take a look at UPDATE FROM in the PostgreSQL documentation.

My Answer

Here is the largely straightforward way:

UPDATE
  cd.facilities
SET
  membercost = (SELECT membercost * 1.1 FROM cd.facilities WHERE name = 'Tennis Court 1'),
  guestcost = (SELECT guestcost * 1.1 FROM cd.facilities WHERE name = 'Tennis Court 1')
WHERE
  name = 'Tennis Court 2';

But this is a clearly cumbersome approach, especially if, say, we wanted to update much more than just membercost and guestcost for Tennis Court 2 where the things being updated depend on Tennis Court 1 as they do above. Postgres provides a nonstandard extension to SQL called UPDATE...FROM that addresses this issue and can be used like so:

UPDATE cd.facilities F
  SET
    membercost = facCosts.membercost * 1.1,
    guestcost = facCosts.guestcost * 1.1
  FROM
    (SELECT membercost, guestcost FROM cd.facilities WHERE name = 'Tennis Court 1') facCosts
WHERE
  F.name = 'Tennis Court 2';

Discussion

UPDATE cd.facilities facs
  SET
    membercost = (SELECT membercost * 1.1 FROM cd.facilities WHERE facid = 0),
    guestcost = (SELECT guestcost * 1.1 FROM cd.facilities WHERE facid = 0)
  WHERE facs.facid = 1;   

Updating columns based on calculated data is not too intrinsically difficult: we can do so pretty easily using subqueries. You can see this approach in our selected answer.

As the number of columns we want to update increases, standard SQL can start to get pretty awkward: you don't want to be specifying a separate subquery for each of 15 different column updates. Postgres provides a nonstandard extension to SQL called UPDATE...FROM that addresses this: it allows you to supply a FROM clause to generate values for use in the SET clause. Example below:

UPDATE cd.facilities facs
  SET
    membercost = facs2.membercost * 1.1,
    guestcost = facs2.guestcost * 1.1
  FROM (SELECT * FROM cd.facilities WHERE facid = 0) facs2
  WHERE facs.facid = 1;

---

Schema Reminder

Delete all bookings

Question

As part of a clearout of our database, we want to delete all bookings from the cd.bookings table. How can we accomplish this?

Hint

Take a look at the DELETE or TRUNCATE statement in the PostgreSQL docs.

My Answer

TRUNCATE TABLE cd.bookings;

Discussion

DELETE FROM cd.bookings;

The DELETE statement does what it says on the tin: deletes rows from the table. Here, we show the command in its simplest form, with no qualifiers. In this case, it deletes everything from the table. Obviously, you should be careful with your deletes and make sure they're always limited - we'll see how to do that in the next exercise.

An alternative to unqualified DELETEs is the following:

TRUNCATE cd.bookings;

TRUNCATE also deletes everything in the table, but does so using a quicker underlying mechanism. It's not perfectly safe in all circumstances, though, so use judiciously. When in doubt, use DELETE.

---

Schema Reminder

Delete a member from the cd.members table

Question

We want to remove member 37, who has never made a booking, from our database. How can we achieve that?

Hint

Take a look at the DELETE statement in the PostgreSQL docs.

My Answer

DELETE FROM
  cd.members
WHERE
  memid = 37;

Discussion

DELETE FROM
  cd.members
WHERE
  memid = 37;

This exercise is a small increment on our previous one. Instead of deleting all bookings, this time we want to be a bit more targeted, and delete a single member that has never made a booking. To do this, we simply have to add a WHERE clause to our command, specifying the member we want to delete. You can see the parallels with SELECT and UPDATE statements here.

There's one interesting wrinkle here. Try this command out, but substituting in member id 0 instead. This member has made many bookings, and you'll find that the delete fails with an error about a foreign key constraint violation. This is an important concept in relational databases, so let's explore a little further.

Foreign keys are a mechanism for defining relationships between columns of different tables. In our case we use them to specify that the memid column of the bookings table is related to the memid column of the members table. The relationship (or 'constraint') specifies that for a given booking, the member specified in the booking must exist in the members table. It's useful to have this guarantee enforced by the database: it means that code using the database can rely on the presence of the member. It's hard (even impossible) to enforce this at higher levels: concurrent operations can interfere and leave your database in a broken state.

PostgreSQL supports various different kinds of constraints that allow you to enforce structure upon your data. For more information on constraints, check out the PostgreSQL documentation on foreign keys.

---

Schema Reminder

Delete based on a subquery

Question

In 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?

Hint

You can delete based on the output of a subquery.

My Answer

DELETE FROM
  cd.members
WHERE
  memid NOT IN (SELECT DISTINCT memid FROM cd.bookings);

Discussion

DELETE FROM
  cd.members
WHERE
  memid NOT IN (
    SELECT
      memid
    FROM
      cd.bookings
  );

We can use subqueries to determine whether a row should be deleted or not. There's a couple of standard ways to do this. In our featured answer, the subquery produces a list of all the different member ids in the cd.bookings table. If a row in the table isn't in the list generated by the subquery, it gets deleted.

An alternative is to use a correlated subquery. Where our previous example runs a large subquery once, the correlated approach instead specifies a smaller subquery to run against every row.

DELETE FROM
  cd.members mems
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      cd.bookings
    WHERE
      memid = mems.memid
  );

The two different forms can have different performance characteristics. Under the hood, your database engine is free to transform your query to execute it in a correlated or uncorrelated fashion, though, so things can be a little hard to predict.

---

Schema Reminder

Aggregates

Intro

Aggregation is one of those capabilities that really makes you appreciate the power of relational database systems. It allows you to move beyond merely persisting your data, into the realm of asking truly interesting questions that can be used to inform decision making. This category covers aggregation at length, making use of standard grouping as well as more recent window functions.

If you struggle with these questions, I strongly recommend Learning SQL, by Alan Beaulieu and SQL Cookbook by Anthony Molinaro. In fact, get the latter anyway - it'll take you beyond anything you find on this site, and on multiple different database systems to boot.

Count the number of facilities

Question

For 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.

Expected Result

┌───────┐
│ count │
├───────┤
│     9 │
└───────┘
(1 row)

Hint

Try investigating the SQL COUNT function.

My Answer

SELECT
  COUNT(*) AS count
FROM
  cd.facilities;

Discussion

SELECT
  COUNT(*)
FROM
  cd.facilities;

Aggregation starts out pretty simply! The SQL above selects everything from our cd.facilities table, and then counts the number of rows in the result set. The COUNT function has a variety of uses:

• COUNT(*) simply returns the number of rows
• COUNT(address) counts the number of non-null addresses in the result set.
• Finally, COUNT(DISTINCT address) counts the number of different addresses in the result set.

The basic idea of an aggregate function is that it takes in a column of data, performs some function upon it, and outputs a scalar (single) value. There are a bunch more aggregation functions, including MAX, MIN, SUM, and AVG. These all do pretty much what you'd expect from their names.

One aspect of aggregate functions that people often find confusing is in queries like the below:

SELECT
  facid,
  count(*)
FROM
  cd.facilities;

Try it out, and you'll find that it doesn't work. This is because COUNT(*) wants to collapse the cd.facilities table into a single value - unfortunately, it can't do that, because there's a lot of different facid's in cd.facilities - Postgres doesn't know which facid to pair the count with.

Instead, if you wanted a query that returns all the facid's along with a count on each row, you can break the aggregation out into a subquery as below:

SELECT
  facid,
  (
    SELECT
      COUNT(*)
    FROM
      cd.facilities
  )
FROM
  cd.facilities;

When we have a subquery that returns a scalar value like this, Postgres knows to simply repeat the value for every row in cd.facilities.

---

Schema Reminder

Count the number of expensive facilities

Question

Produce a count of the number of facilities that have a cost to guests of 10 or more.

Expected Result

┌───────┐
│ count │
├───────┤
│     6 │
└───────┘
(1 row)

Hint

You'll need to add a WHERE clause to the answer of the previous question.

My Answer

SELECT
  COUNT(*)
FROM
  cd.facilities
WHERE
  guestcost >= 10;

Discussion

SELECT
  COUNT(*)
FROM
  cd.facilities
WHERE
  guestcost >= 10;

This one is only a simple modification to the previous question: we need to weed out the inexpensive facilities. This is easy to do using a WHERE clause. Our aggregation can now only see the expensive facilities.

---

Schema Reminder

Count the number of recommendations each member makes

Question

Produce a count of the number of recommendations each member has made. Order by member ID.

Expected Result

┌───────────────┬───────┐
│ recommendedby │ count │
├───────────────┼───────┤
│             1 │     5 │
│             2 │     3 │
│             3 │     1 │
│             4 │     2 │
│             5 │     1 │
│             6 │     1 │
│             9 │     2 │
│            11 │     1 │
│            13 │     2 │
│            15 │     1 │
│            16 │     1 │
│            20 │     1 │
│            30 │     1 │
└───────────────┴───────┘
(13 rows)

Hint

Try investigating GROUP BY with your count this time. Don't forget to filter out null recommenders! See IS NOT NULL documentation as well.

My Answer

You could do this the hard way with an unnecessary INNER JOIN:

SELECT
  recommendees.recommendedby AS recommendedby,
  COUNT(recommendees.recommendedby) AS count
FROM
  cd.members recommenders
  INNER JOIN cd.members recommendees ON recommenders.memid = recommendees.recommendedby
GROUP BY
  recommendees.recommendedby
ORDER BY
  recommendedby;

Or you could do it in a much simpler way:

SELECT
  recommendedby,
  COUNT(*)
FROM
  cd.members
WHERE
  recommendedby IS NOT NULL
GROUP BY
  recommendedby
ORDER BY
  recommendedby;

Notice that the inner join is what makes the first solution work (since joining on recommenders.memid = recommendees.recommendedby effectively filters out all the rows where recommendedby is NULL).

Discussion

SELECT
  recommendedby,
  COUNT(*)
FROM
  cd.members
WHERE
  recommendedby IS NOT NULL
GROUP BY
  recommendedby
ORDER BY
  recommendedby;

Previously, we've seen that aggregation functions are applied to a column of values, and convert them into an aggregated scalar value. This is useful, but we often find that we don't want just a single aggregated result: for example, instead of knowing the total amount of money the club has made this month, I might want to know how much money each different facility has made, or which times of day were most lucrative.

In order to support this kind of behaviour, SQL has the GROUP BY construct. What this does is batch the data together into groups, and run the aggregation function separately for each group. When you specify a GROUP BY, the database produces an aggregated value for each distinct value in the supplied columns. In this case, we're saying 'for each distinct value of recommendedby, get me the number of times that value appears'.

---

Schema Reminder

List the total slots booked per facility

Question

Produce 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.

Expected Result

┌───────┬──────┐
│ facid │ sum  │
├───────┼──────┤
│     0 │ 1320 │
│     1 │ 1278 │
│     2 │ 1209 │
│     3 │  830 │
│     4 │ 1404 │
│     5 │  228 │
│     6 │ 1104 │
│     7 │  908 │
│     8 │  911 │
└───────┴──────┘
(9 rows)

Hint

For this one you'll need to check out the SUM aggregate function.

My Answer

SELECT
  facid,
  SUM(slots)
FROM
  cd.bookings
GROUP BY
  facid
ORDER BY
  facid;

Discussion

SELECT
  facid,
  SUM(slots) AS "Total Slots"
FROM
  cd.bookings
GROUP BY
  facid
ORDER BY
  facid;

Other than the fact that we've introduced the SUM aggregate function, there's not a great deal to say about this exercise. For each distinct facility id, the SUM function adds together everything in the slots column.

---

Schema Reminder

List the total slots booked per facility in a given month

Question

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.

Expected Result

┌───────┬─────────────┐
│ facid │ Total Slots │
├───────┼─────────────┤
│     5 │         122 │
│     3 │         422 │
│     7 │         426 │
│     8 │         471 │
│     6 │         540 │
│     2 │         570 │
│     1 │         588 │
│     0 │         591 │
│     4 │         648 │
└───────┴─────────────┘
(9 rows)

Hint

You can restrict the data that goes into your aggregate functions using the WHERE clause. Remember that the WHERE clause applies to individual rows directly before GROUP BY in the order of execution for a query. As stated below in the answers and discussion, a good way of thinking about WHERE in the context of aggregates is that the WHERE clause is used to restrict the data we aggregate over.

My Answer

SELECT
  facid,
  SUM(slots) AS "Total Slots"
FROM
  cd.bookings
WHERE
  starttime >= '2012-09-01' AND starttime < '2012-10-01'
GROUP BY
  facid
ORDER BY
  "Total Slots" ASC;

Discussion

SELECT
  facid,
  SUM(slots) AS "Total Slots"
FROM
  cd.bookings
WHERE
  starttime >= '2012-09-01'
  AND starttime < '2012-10-01'
GROUP BY
  facid
ORDER BY
  SUM(slots);

This is only a minor alteration of our previous example. Remember that aggregation happens after the WHERE clause is evaluated: we thus use the WHERE to restrict the data we aggregate over, and our aggregation only sees data from a single month.

---

Schema Reminder

List the total slots booked per facility per month

Question

Produce 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.

Expected Result

┌───────┬───────┬─────────────┐
│ facid │ month │ Total Slots │
├───────┼───────┼─────────────┤
│     0 │     7 │         270 │
│     0 │     8 │         459 │
│     0 │     9 │         591 │
│     1 │     7 │         207 │
│     1 │     8 │         483 │
│     1 │     9 │         588 │
│     2 │     7 │         180 │
│     2 │     8 │         459 │
│     2 │     9 │         570 │
│     3 │     7 │         104 │
│     3 │     8 │         304 │
│     3 │     9 │         422 │
│     4 │     7 │         264 │
│     4 │     8 │         492 │
│     4 │     9 │         648 │
│     5 │     7 │          24 │
│     5 │     8 │          82 │
│     5 │     9 │         122 │
│     6 │     7 │         164 │
│     6 │     8 │         400 │
│     6 │     9 │         540 │
│     7 │     7 │         156 │
│     7 │     8 │         326 │
│     7 │     9 │         426 │
│     8 │     7 │         117 │
│     8 │     8 │         322 │
│     8 │     9 │         471 │
└───────┴───────┴─────────────┘
(27 rows)

Hint

Take a look at the EXTRACT function.

My Answer

SELECT
  facid,
  EXTRACT(MONTH FROM starttime) AS month,
  SUM(slots) AS "Total Slots"
FROM
  cd.bookings
WHERE
  EXTRACT(YEAR FROM starttime) = 2012
GROUP BY
  EXTRACT(MONTH FROM starttime), facid
ORDER BY
  facid, month;

Discussion

SELECT facid, EXTRACT(MONTH FROM starttime) AS month, SUM(slots) AS "Total Slots"
    FROM cd.bookings
    WHERE EXTRACT(YEAR FROM starttime) = 2012
    GROUP BY facid, month
ORDER BY facid, month;

The main piece of new functionality in this question is the EXTRACT function. EXTRACT allows you to get individual components of a timestamp, like day, month, year, etc. We group by the output of this function to provide per-month values. An alternative, if we needed to distinguish between the same month in different years, is to make use of the DATE_TRUNC function, which truncates a date to a given granularity. It's also worth noting that this is the first time we've truly made use of the ability to group by more than one column.

One thing worth considering with this answer: the use of the EXTRACT function in the WHERE clause has the potential to cause severe issues with performance on larger tables. If the timestamp column has a regular index on it, Postgres will not understand that it can use the index to speed up the query and will instead have to scan through the whole table. You've got a couple of options here:

• Consider creating an expression-based index on the timestamp column. With appropriately specified indexes Postgres can use indexes to speed up WHERE clauses containing function calls.
• Alter the query to be a little more verbose, but use more standard comparisons, for example:

SELECT facid, extract(MONTH FROM starttime) AS month, SUM(slots) AS "Total Slots"
    FROM cd.bookings
    WHERE
        starttime >= '2012-01-01'
        AND starttime < '2013-01-01'
    GROUP BY facid, month
ORDER BY facid, month;

Postgres is able to use an index using these standard comparisons without any additional assistance.

---

Schema Reminder

Find the count of members who have made at least one booking

Question

Find the total number of members (including guests) who have made at least one booking.

Expected Result

┌───────┐
│ count │
├───────┤
│    30 │
└───────┘
(1 row)

Hint

Take a look at COUNT DISTINCT.

My Answer

SELECT
  COUNT(DISTINCT memid) AS count
FROM
  cd.bookings;

Discussion

SELECT
  COUNT(DISTINCT memid)
FROM
  cd.bookings;

Your first instinct may be to go for a subquery here. Something like the below:

SELECT COUNT(*) FROM
    (SELECT DISTINCT memid FROM cd.bookings) AS mems;

This does work perfectly well, but we can simplify a touch with the help of a little extra knowledge in the form of COUNT DISTINCT. This does what you might expect, counting the distinct values in the passed column.

---

Schema Reminder

List facilities with more than 1000 slots booked

Question

Produce a list of facilities with more than 1000 slots booked. Produce an output table consisting of facility id and slots, sorted by facility id.

Expected Result

┌───────┬─────────────┐
│ facid │ Total Slots │
├───────┼─────────────┤
│     0 │        1320 │
│     1 │        1278 │
│     2 │        1209 │
│     4 │        1404 │
│     6 │        1104 │
└───────┴─────────────┘
(5 rows)

Hint

Try investigating the HAVING clause.

My Answer

SELECT
  facid,
  SUM(slots) AS "Total Slots"
FROM
 cd.bookings
GROUP BY
  facid
HAVING
  SUM(slots) > 1000
ORDER BY
  facid;

Discussion

SELECT
  facid,
  SUM(slots) AS "Total Slots"
FROM
  cd.bookings
GROUP BY
  facid
HAVING
  SUM(slots) > 1000
ORDER BY
  facid;

It turns out that there's actually an SQL keyword designed to help with the filtering of output from aggregate functions. This keyword is HAVING.

The behaviour of HAVING is easily confused with that of WHERE. The best way to think about it is that in the context of a query with an aggregate function, WHERE is used to filter what data gets input into the aggregate function, while HAVING is used to filter the data once it is output from the function. Try experimenting to explore this difference!

---

Schema Reminder

Find the total revenue of each facility

Question

Produce 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!

Expected Result

┌─────────────────┬─────────┐
│      name       │ revenue │
├─────────────────┼─────────┤
│ Table Tennis    │     180 │
│ Snooker Table   │     240 │
│ Pool Table      │     270 │
│ Badminton Court │  1906.5 │
│ Squash Court    │ 13468.0 │
│ Tennis Court 1  │   13860 │
│ Tennis Court 2  │   14310 │
│ Massage Room 2  │   15810 │
│ Massage Room 1  │   72540 │
└─────────────────┴─────────┘
(9 rows)

Hint

Remember the CASE statement!

My Answer

This seems like a somewhat difficult problem on the surface, but starting with a small, simple query should clarify things:

SELECT
  F.name, 
  SUM(slots)
FROM
  cd.bookings B
  INNER JOIN cd.facilities F ON B.facid = F.facid
GROUP BY
  F.name;

This query gives us the following result set:

We are very close to getting exactly what we need--we have everything grouped by facility name and we have the total number of slots for each facility. The obvious critical piece of information that we are missing is what we are being asked to report, namely the total revenue of each facility.

How do we find this? It seems like we should be able to just multiply slots by some number to give us the revenue and that's basically what the correct approach is--the only problem is that not every slot costs the same amount. If the person who booked the slot is a guest (indicated internally by the guest having memid = 0), then each slot will cost guestcost amount; otherwise, when memid != 0, each slot will cost membercost amount.

Hence, an appropriate solution is to multiply the slots number by guestcost or membercost depending on whether or not memid = 0 or memid != 0, respectively, and then sum up all those values where the values are grouped by facility name:

SELECT
  F.name,
  SUM(B.slots *
  CASE
    WHEN B.memid = 0 THEN F.guestcost
    ELSE
      F.membercost
  END) AS revenue
FROM
  cd.bookings B
  INNER JOIN cd.facilities F ON B.facid = F.facid
GROUP BY
  F.name
ORDER BY
  revenue ASC;

This gives us the desired result set:

This result set may make even more sense when we look at a breakdown of everything by whether or not the person making the booking for a facility was a guest or a member:

SELECT
  F.name, SUM(B.slots) AS slots_booked, 'Member' AS role, F.membercost AS cost_per_slot
FROM
  cd.bookings B INNER JOIN cd.facilities F ON B.facid = F.facid
WHERE
  B.memid != 0
GROUP BY
  F.name, F.membercost
UNION ALL
SELECT
  F.name, SUM(B.slots) AS slots_booked, 'GUEST' AS role, F.guestcost AS cost_per_slot
FROM
  cd.bookings B INNER JOIN cd.facilities F ON B.facid = F.facid
WHERE
  B.memid = 0
GROUP BY
  F.name, F.guestcost
ORDER BY
  role, name;

This query gives us the following result set:

Finally, as usual, there's more than one way to do something in SQL--we could use an inline view to give us what we want (this general approach may be more favorable, albeit less aparent, than the prior approach given the ease with which you can then use WHERE among other things since what you are selecting is not a column alias):

SELECT
  name,
  revenue
FROM
  (
    SELECT
      F.name,
      SUM(B.slots *
        CASE
          WHEN B.memid = 0 THEN F.guestcost
          ELSE
            F.membercost
        END) AS revenue
    FROM
      cd.bookings B
      INNER JOIN cd.facilities F ON B.facid= F.facid
    GROUP BY
      F.name
  ) AS agg
ORDER BY
  revenue ASC;

Discussion

SELECT
  facs.name,
  SUM(
    slots * CASE
      WHEN memid = 0 THEN facs.guestcost
      ELSE facs.membercost
    END
  ) AS revenue
FROM
  cd.bookings bks
  INNER JOIN cd.facilities facs ON bks.facid = facs.facid
GROUP BY
  facs.name
ORDER BY
  revenue;

The only real complexity in this query is that guests (member ID 0) have a different cost to everyone else. We use a case statement to produce the cost for each session, and then sum each of those sessions, grouped by facility.

---

Schema Reminder

Find facilities with a total revenue less than 1000

Question

Produce 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!

Expected Result

┌───────────────┬─────────┐
│     name      │ revenue │
├───────────────┼─────────┤
│ Table Tennis  │     180 │
│ Snooker Table │     240 │
│ Pool Table    │     270 │
└───────────────┴─────────┘
(3 rows)

Hint

You may find HAVING difficult to use here. Try a subquery instead. You'll probably also need a CASE statement.

My Answer

The work we did in the previous exercise where we used an inline view will help a great deal here (not using an inline view makes things much more difficult):

SELECT
  name,
  revenue
FROM
  (
    SELECT
      F.name,
      SUM(B.slots *
        CASE
          WHEN B.memid = 0 THEN F.guestcost
          ELSE
            F.membercost
        END) AS revenue
    FROM
      cd.bookings B
      INNER JOIN cd.facilities F ON B.facid= F.facid
    GROUP BY
      F.name
  ) AS agg
WHERE
  revenue < 1000
ORDER BY
  revenue ASC;

Discussion

SELECT
  name,
  revenue
FROM
  (
    SELECT
      facs.name,
      SUM(
        CASE
          WHEN memid = 0 THEN slots * facs.guestcost
          ELSE slots * membercost
        END
      ) AS revenue
    FROM
      cd.bookings bks
      INNER JOIN cd.facilities facs ON bks.facid = facs.facid
    GROUP BY
      facs.name
  ) AS agg
WHERE
  revenue < 1000
ORDER BY
  revenue;

You may well have tried to use the HAVING keyword we introduced in an earlier exercise, producing something like below:

SELECT
  facs.name,
  SUM(
    CASE
      WHEN memid = 0 THEN slots * facs.guestcost
      ELSE slots * membercost
    END
  ) AS revenue
FROM
  cd.bookings bks
  INNER JOIN cd.facilities facs ON bks.facid = facs.facid
GROUP BY
  facs.name
HAVING
  revenue < 1000
ORDER BY
  revenue;

Unfortunately, this doesn't work! You'll get an error along the lines of ERROR: column "revenue" does not exist. Postgres, unlike some other RDBMSs like SQL Server and MySQL, doesn't support putting column names in the HAVING clause. This means that for this query to work, you'd have to produce something like below:

SELECT
  facs.name,
  SUM(
    CASE
      WHEN memid = 0 THEN slots * facs.guestcost
      ELSE slots * membercost
    END
  ) AS revenue
FROM
  cd.bookings bks
  INNER JOIN cd.facilities facs ON bks.facid = facs.facid
GROUP BY
  facs.name
HAVING
  SUM(
    CASE
      WHEN memid = 0 THEN slots * facs.guestcost
      ELSE slots * membercost
    END
  ) < 1000
ORDER BY
  revenue;

Having to repeat significant calculation code like this is messy, so our anointed solution instead just wraps the main query body as a subquery, and selects from it using a WHERE clause. In general, I recommend using HAVING for simple queries, as it increases clarity. Otherwise, this subquery approach is often easier to use.

---

Schema Reminder

Output the facility id that has the highest number of slots booked

Question

Output 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!

Expected Result

┌───────┬─────────────┐
│ facid │ Total Slots │
├───────┼─────────────┤
│     4 │        1404 │
└───────┴─────────────┘
(1 row)

Hint

Consider the use of the LIMIT keyword combined with ORDER BY. For the LIMIT-less version, you'll probably want to investigate the HAVING keyword. Be aware that the latter version is difficult!

My Answer

There are many ways to approach this problem. Let's take a look at a few of them.

Simple INNER JOIN:

Arguably the simplest solution is as follows:

SELECT
  F.facid,
  SUM(B.slots) AS "Total Slots"
FROM
  cd.bookings B
  INNER JOIN cd.facilities F ON B.facid = F.facid
GROUP BY
  F.facid
ORDER BY
  SUM(B.slots) DESC
LIMIT 1;

Simple INNER JOIN with an inline view:

We could also use an inline view to clean up the SELECT clause, but this seems like more trouble than it's worth:

SELECT
  facid,
  "Total Slots"
FROM
  (
    SELECT
      F.facid,
      SUM(B.slots) AS "Total Slots"
    FROM
      cd.bookings B
      INNER JOIN cd.facilities F ON B.facid = F.facid
    GROUP BY
      F.facid
    ORDER BY
       SUM(B.slots) DESC
    LIMIT 1
  ) X;

The biggest problem with both solutions above can be seen by asking a simple question: What happens in the case of a tie? We just lose the data which is never ideal and sometimes unacceptable. We can write up another solution using HAVING that results in a query that is rather unsightly:

No INNER JOIN but messy use of HAVING:

SELECT
  facid,
  SUM(slots) AS "Total Slots"
FROM
  cd.bookings
GROUP BY
  facid
HAVING
  SUM(slots) = (
    SELECT
      MAX("Total Slots")
    FROM
      (
        SELECT
          SUM(slots) AS "Total Slots"
        FROM
          cd.bookings
        GROUP BY
          facid
      ) AS agg
  );

A non-recursive Common Table Expression (CTE):

We can also approach this problem using a PostgreSQL Common Table Expression (CTE) (see the official docs as well) which is meant to simplify complex queries (it is essentially an inline view).

A CTE is a temporary result set (i.e., temporary in the sense that a CTE only exists during the execution of a query) which you can reference within another SQL statement including SELECT, INSERT, UPDATE, or DELETE. You can create a CTE by using the following syntax:

WITH cte_name [(column_list)] AS (
  CTE_query_definition 
)
statement;

Some points worth noting about this syntax:

• First, specify the name of the CTE, cte_name, followed by an optional column_list.
• Second, inside the body of the WITH clause, specify a query that returns a result set. If you do not explicitly specify the column_list after the CTE name, the select list of the CTE_query_definition will become the column list of the CTE.
• Third, use the CTE like a table or view in the statement which can be a SELECT, INSERT, UPDATE, or DELETE.

CTEs are typically used to simplify complex joins and subqueries in PostgreSQL. There are also recursive CTEs which can be even more powerful and have the following syntax:

WITH RECURSIVE cte_name AS (
    CTE_query_definition -- non-recursive term
    UNION [ALL]
    CTE_query definion  -- recursive term
) SELECT * FROM cte_name;

Returning to the problem at hand, we see that using a CTE where the statement is a SELECT might be helpful here (a recursive CTE is not needed here; recursive CTEs will be looked at later):

WITH slotSums AS (
  SELECT
    facid,
    SUM(slots) AS "Total Slots"
  FROM
    cd.bookings
  GROUP BY
    facid
)
SELECT
  facid,
  "Total Slots"
FROM
  slotSums
WHERE
  "Total Slots" = (
    SELECT
      MAX("Total Slots")
    FROM
      slotSums
  );

This is a very clean solution in terms of thought process (and the query really is not all that bad in terms of how messy it is).

Using the DENSE_RANK() window function:

Lastly, this is a great opportunity to use the DENSE_RANK() window function, where DENSE_RANK() is used to assign a rank to each row within a partition of a result set, with no gaps in ranking values (thus overcoming the issue we encountered earlier of possibly not returing every facility with the maximum number of bookings in the case of a tie):

SELECT
  facid,
  "Total Slots"
FROM
  (
    SELECT
      B.facid,
      SUM(B.slots) AS "Total Slots",
      DENSE_RANK() OVER (
        ORDER BY SUM(B.slots) DESC
      ) slotsRank
    FROM
      cd.bookings B
    GROUP BY
      B.facid
  ) X
WHERE
  slotsRank = 1;

This is probably the clearest and cleanest solution for this problem.

Discussion

SELECT
  facid,
  SUM(slots) AS "Total Slots"
FROM
  cd.bookings
GROUP BY
  facid
ORDER BY
  SUM(slots) DESC
LIMIT
  1;

Let's start off with what's arguably the simplest way to do this: produce a list of facility IDs and the total number of slots used, order by the total number of slots used, and pick only the top result.

It's worth realising, though, that this method has a significant weakness. In the event of a tie, we will still only get one result! To get all the relevant results, we might try using the MAX aggregate function, something like below:

SELECT
  facid,
  MAX(totalslots)
FROM
  (
    SELECT
      facid,
      SUM(slots) AS totalslots
    FROM
      cd.bookings
    GROUP BY
      facid
  ) AS sub
GROUP BY
  facid;

The intent of this query is to get the highest totalslots value and its associated facid(s). Unfortunately, this just won't work! In the event of multiple facids having the same number of slots booked, it would be ambiguous which facid should be paired up with the single (or scalar) value coming out of the MAX function. This means that Postgres will tell you that facid ought to be in a GROUP BY section, which won't produce the results we're looking for.

Let's take a first stab at a working query:

SELECT
  facid,
  SUM(slots) AS totalslots
FROM
  cd.bookings
GROUP BY
  facid
HAVING
  SUM(slots) = (
    SELECT
      MAX(sum2.totalslots)
    FROM
      (
        SELECT
          SUM(slots) AS totalslots
        FROM
          cd.bookings
        GROUP BY
          facid
      ) AS sum2
  );

The query produces a list of facility IDs and number of slots used, and then uses a HAVING clause that works out the maximum Total Slots value. We're essentially saying: 'produce a list of facids and their number of slots booked, and filter out all the ones that doen't have a number of slots booked equal to the maximum.'

Useful as HAVING is, however, our query is pretty ugly. To improve on that, let's introduce another new concept: Common Table Expressions (CTEs). CTEs can be thought of as allowing you to define a database view inline in your query. It's really helpful in situations like this, where you're having to repeat yourself a lot.

CTEs are declared in the form WITH CTE Name as (SQL-Expression). You can see our query redefined to use a CTE below:

WITH sum AS (
  SELECT
    facid,
    SUM(slots) AS totalslots
  FROM
    cd.bookings
  GROUP BY
    facid
)
SELECT
  facid,
  totalslots
FROM
  sum
WHERE
  totalslots = (
    SELECT
      MAX(totalslots)
    FROM
      sum
  );

You can see that we've factored out our repeated selections from cd.bookings into a single CTE, and made the query a lot simpler to read in the process!

BUT WAIT. There's more. It's also possible to complete this problem using Window Functions. We'll leave these until later, but even better solutions to problems like these are available.

That's a lot of information for a single exercise. Don't worry too much if you don't get it all right now - we'll reuse these concepts in later exercises.

---

Schema Reminder

List the total slots booked per facility per month, part 2

Question

Produce 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 facid, 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.

Expected Result

┌───────┬───────┬───────┐
│ facid │ month │ slots │
├───────┼───────┼───────┤
│     0 │     7 │   270 │
│     0 │     8 │   459 │
│     0 │     9 │   591 │
│     0 │       │  1320 │
│     1 │     7 │   207 │
│     1 │     8 │   483 │
│     1 │     9 │   588 │
│     1 │       │  1278 │
│     2 │     7 │   180 │
│     2 │     8 │   459 │
│     2 │     9 │   570 │
│     2 │       │  1209 │
│     3 │     7 │   104 │
│     3 │     8 │   304 │
│     3 │     9 │   422 │
│     3 │       │   830 │
│     4 │     7 │   264 │
│     4 │     8 │   492 │
│     4 │     9 │   648 │
│     4 │       │  1404 │
│     5 │     7 │    24 │
│     5 │     8 │    82 │
│     5 │     9 │   122 │
│     5 │       │   228 │
│     6 │     7 │   164 │
│     6 │     8 │   400 │
│     6 │     9 │   540 │
│     6 │       │  1104 │
│     7 │     7 │   156 │
│     7 │     8 │   326 │
│     7 │     9 │   426 │
│     7 │       │   908 │
│     8 │     7 │   117 │
│     8 │     8 │   322 │
│     8 │     9 │   471 │
│     8 │       │   910 │
│       │       │  9191 │
└───────┴───────┴───────┘
(37 rows)

Hint

Look up Postgres' ROLLUP operator.

My Answer

SELECT
  B.facid,
  EXTRACT(MONTH FROM B.starttime) AS month,
  SUM(B.slots) AS slots
FROM
  cd.bookings B
WHERE
  B.starttime >= '2012-01-01' AND B.starttime < '2013-01-01'
GROUP BY
  ROLLUP(B.facid, EXTRACT(MONTH FROM B.starttime))
ORDER BY
  B.facid ASC, month ASC;

Discussion

SELECT
  facid,
  EXTRACT(
    MONTH
    FROM
      starttime
  ) AS month,
  SUM(slots) AS slots
FROM
  cd.bookings
WHERE
  starttime >= '2012-01-01'
  AND starttime < '2013-01-01'
GROUP BY
  ROLLUP(facid, month)
ORDER BY
  facid,
  month;

When we are doing data analysis, we sometimes want to perform multiple levels of aggregation to allow ourselves to 'zoom' in and out to different depths. In this case, we might be looking at each facility's overall usage, but then want to dive in to see how they've performed on a per-month basis. Using the SQL we know so far, it's quite cumbersome to produce a single query that does what we want - we effectively have to resort to concatenating multiple queries using UNION ALL:

SELECT facid, EXTRACT(MONTH FROM starttime) AS month, SUM(slots) AS slots
  FROM cd.bookings
  WHERE
    starttime >= '2012-01-01'
    AND starttime < '2013-01-01'
  GROUP BY facid, month
UNION ALL
SELECT facid, null, SUM(slots) AS slots
  FROM cd.bookings
  WHERE
    starttime >= '2012-01-01'
    AND starttime < '2013-01-01'
  GROUP BY facid
UNION ALL
SELECT null, null, SUM(slots) AS slots
  FROM cd.bookings
  WHERE
    starttime >= '2012-01-01'
    AND starttime < '2013-01-01'
ORDER BY facid, month;

As you can see, each subquery performs a different level of aggregation, and we just combine the results. We can clean this up a lot by factoring out commonalities using a CTE:

WITH bookings AS (
    SELECT facid, EXTRACT(MONTH FROM starttime) AS month, slots
    FROM cd.bookings
    WHERE
        starttime >= '2012-01-01'
        AND starttime < '2013-01-01'
)
SELECT facid, month, SUM(slots) FROM bookings GROUP BY facid, month
UNION ALL
SELECT facid, null, SUM(slots) FROM bookings GROUP BY facid
UNION ALL
SELECT null, null, SUM(slots) FROM bookings
ORDER BY facid, month;

This version is not excessively hard on the eyes, but it becomes cumbersome as the number of aggregation columns increases. Fortunately, PostgreSQL 9.5 introduced support for the ROLLUP operator, which we've used to simplify our accepted answer.

ROLLUP produces a hierarchy of aggregations in the order passed into it: for example, ROLLUP(facid, month) outputs aggregations on (facid, month), (facid), and (). If we wanted an aggregation of all facilities for a month (instead of all months for a facility) we'd have to reverse the order, using ROLLUP(month, facid). Alternatively, if we instead want all possible permutations of the columns we pass in, we can use CUBE rather than ROLLUP. This will produce (facid, month), (month), (facid), and ().

ROLLUP and CUBE are special cases of GROUPING SETS. GROUPING SETS allow you to specify the exact aggregation permutations you want: you could, for example, ask for just (facid, month) and (facid), skipping the top-level aggregation.

---

Schema Reminder

List the total hours booked per named facilit

Question

Produce 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.

Expected Result

┌───────┬─────────────────┬────────────┐
│ facid │      name       │ totalslots │
├───────┼─────────────────┼────────────┤
│     0 │ Tennis Court 1  │ 660.00     │
│     1 │ Tennis Court 2  │ 639.00     │
│     2 │ Badminton Court │ 604.50     │
│     3 │ Table Tennis    │ 415.00     │
│     4 │ Massage Room 1  │ 702.00     │
│     5 │ Massage Room 2  │ 114.00     │
│     6 │ Squash Court    │ 552.00     │
│     7 │ Snooker Table   │ 454.00     │
│     8 │ Pool Table      │ 455.50     │
└───────┴─────────────────┴────────────┘
(9 rows)

Hint

Remember that in Postgres, dividing two integers together causes an integer division. Here you want a floating point division. For formatting the hours, take a look at the to_char function, remembering to trim any leftover whitespace.

My Answer

• Divide by a float to ensure you retain decimal places (Postgres uses integer division otherwise)
• In general, group by all columns you do not have an aggregate on
• With TO_CHAR, it seems there is no better way than to simply have a ton of 9s prepending the format and then simply TRIM the whitespace off.

SELECT
  F.facid,
  F.name,
  TRIM(TO_CHAR((SUM(B.slots) * 30) / 60.0, '999999999999999D99')) AS totalslots
FROM cd.bookings B
  INNER JOIN cd.facilities F ON B.facid = F.facid
GROUP BY
  F.facid, F.name
ORDER BY
  F.facid;

Discussion

SELECT facs.facid, facs.name,
    TRIM(TO_CHAR(SUM(bks.slots)/2.0, '9999999999999999D99')) AS "Total Hours"
    FROM cd.bookings bks
    INNER JOIN cd.facilities facs
        ON facs.facid = bks.facid
    GROUP BY facs.facid, facs.name
ORDER BY facs.facid;

There's a few little pieces of interest in this question. Firstly, you can see that our aggregation works just fine when we join to another table on a 1:1 basis. Also note that we group by both facs.facid and facs.name. This might seem odd: after all, since facid is the primary key of the facilities table, each facid has exactly one name, and grouping by both fields is the same as grouping by facid alone. In fact, you'll find that if you remove facs.name from the GROUP BY clause, the query works just fine: Postgres works out that this 1:1 mapping exists, and doesn't insist that we group by both columns.

Unfortunately, depending on which database system we use, validation might not be so smart, and may not realise that the mapping is strictly 1:1. That being the case, if there were multiple names for each facid and we hadn't grouped by name, the DBMS would have to choose between multiple (equally valid) choices for the name. Since this is invalid, the database system will insist that we group by both fields. In general, I recommend grouping by all columns you don't have an aggregate function on: this will ensure better cross-platform compatibility.

Next up is the division. Those of you familiar with MySQL may be aware that integer divisions are automatically cast to floats. Postgres is a little more traditional in this respect, and expects you to tell it if you want a floating point division. You can do that easily in this case by dividing by 2.0 rather than 2.

Finally, let's take a look at formatting. The TO_CHAR function converts values to character strings. It takes a formatting string, which we specify as (up to) lots of numbers before the decimal place, decimal place, and two numbers after the decimal place. The output of this function can be prepended with a space, which is why we include the outer TRIM function.

---

Schema Reminder

List each member's first booking after September 1st 2012

Question

Produce a list of each member name, id, and their first booking after September 1st 2012. Order by member ID.

Expected Result

┌───────────────────┬───────────┬───────┬─────────────────────┐
│      surname      │ firstname │ memid │      starttime      │
├───────────────────┼───────────┼───────┼─────────────────────┤
│ GUEST             │ GUEST     │     0 │ 2012-09-01 08:00:00 │
│ Smith             │ Darren    │     1 │ 2012-09-01 09:00:00 │
│ Smith             │ Tracy     │     2 │ 2012-09-01 11:30:00 │
│ Rownam            │ Tim       │     3 │ 2012-09-01 16:00:00 │
│ Joplette          │ Janice    │     4 │ 2012-09-01 15:00:00 │
│ Butters           │ Gerald    │     5 │ 2012-09-02 12:30:00 │
│ Tracy             │ Burton    │     6 │ 2012-09-01 15:00:00 │
│ Dare              │ Nancy     │     7 │ 2012-09-01 12:30:00 │
│ Boothe            │ Tim       │     8 │ 2012-09-01 08:30:00 │
│ Stibbons          │ Ponder    │     9 │ 2012-09-01 11:00:00 │
│ Owen              │ Charles   │    10 │ 2012-09-01 11:00:00 │
│ Jones             │ David     │    11 │ 2012-09-01 09:30:00 │
│ Baker             │ Anne      │    12 │ 2012-09-01 14:30:00 │
│ Farrell           │ Jemima    │    13 │ 2012-09-01 09:30:00 │
│ Smith             │ Jack      │    14 │ 2012-09-01 11:00:00 │
│ Bader             │ Florence  │    15 │ 2012-09-01 10:30:00 │
│ Baker             │ Timothy   │    16 │ 2012-09-01 15:00:00 │
│ Pinker            │ David     │    17 │ 2012-09-01 08:30:00 │
│ Genting           │ Matthew   │    20 │ 2012-09-01 18:00:00 │
│ Mackenzie         │ Anna      │    21 │ 2012-09-01 08:30:00 │
│ Coplin            │ Joan      │    22 │ 2012-09-02 11:30:00 │
│ Sarwin            │ Ramnaresh │    24 │ 2012-09-04 11:00:00 │
│ Jones             │ Douglas   │    26 │ 2012-09-08 13:00:00 │
│ Rumney            │ Henrietta │    27 │ 2012-09-16 13:30:00 │
│ Farrell           │ David     │    28 │ 2012-09-18 09:00:00 │
│ Worthington-Smyth │ Henry     │    29 │ 2012-09-19 09:30:00 │
│ Purview           │ Millicent │    30 │ 2012-09-19 11:30:00 │
│ Tupperware        │ Hyacinth  │    33 │ 2012-09-20 08:00:00 │
│ Hunt              │ John      │    35 │ 2012-09-23 14:00:00 │
│ Crumpet           │ Erica     │    36 │ 2012-09-27 11:30:00 │
└───────────────────┴───────────┴───────┴─────────────────────┘
(30 rows)

Hint

Take a look at the MIN aggregate function.

My Answer

SELECT
  M.surname,
  M.firstname,
  M.memid,
  MIN(B.starttime) AS starttime
FROM
  cd.bookings B
  INNER JOIN cd.members M ON B.memid = M.memid
WHERE
  B.starttime >= '2012-09-01'
GROUP BY
  M.surname, M.firstname, M.memid
ORDER BY
  M.memid;

Discussion

SELECT mems.surname, mems.firstname, mems.memid, MIN(bks.starttime) AS starttime
    FROM cd.bookings bks
    INNER JOIN cd.members mems ON
        mems.memid = bks.memid
    WHERE starttime >= '2012-09-01'
    GROUP BY mems.surname, mems.firstname, mems.memid
ORDER BY mems.memid;

This answer demonstrates the use of aggregate functions on dates. MIN works exactly as you'd expect, pulling out the lowest possible date in the result set. To make this work, we need to ensure that the result set only contains dates from September onwards. We do this using the WHERE clause.

You might typically use a query like this to find a customer's next booking. You can use this by replacing the date '2012-09-01' with the function now().

---

Schema Reminder

Produce a list of member names, with each row containing the total member count

Question

Produce a list of member names, with each row containing the total member count. Order by join date, and include guest members.

Expected Result

┌───────┬───────────┬───────────────────┐
│ count │ firstname │      surname      │
├───────┼───────────┼───────────────────┤
│    31 │ GUEST     │ GUEST             │
│    31 │ Darren    │ Smith             │
│    31 │ Tracy     │ Smith             │
│    31 │ Tim       │ Rownam            │
│    31 │ Janice    │ Joplette          │
│    31 │ Gerald    │ Butters           │
│    31 │ Burton    │ Tracy             │
│    31 │ Nancy     │ Dare              │
│    31 │ Tim       │ Boothe            │
│    31 │ Ponder    │ Stibbons          │
│    31 │ Charles   │ Owen              │
│    31 │ David     │ Jones             │
│    31 │ Anne      │ Baker             │
│    31 │ Jemima    │ Farrell           │
│    31 │ Jack      │ Smith             │
│    31 │ Florence  │ Bader             │
│    31 │ Timothy   │ Baker             │
│    31 │ David     │ Pinker            │
│    31 │ Matthew   │ Genting           │
│    31 │ Anna      │ Mackenzie         │
│    31 │ Joan      │ Coplin            │
│    31 │ Ramnaresh │ Sarwin            │
│    31 │ Douglas   │ Jones             │
│    31 │ Henrietta │ Rumney            │
│    31 │ David     │ Farrell           │
│    31 │ Henry     │ Worthington-Smyth │
│    31 │ Millicent │ Purview           │
│    31 │ Hyacinth  │ Tupperware        │
│    31 │ John      │ Hunt              │
│    31 │ Erica     │ Crumpet           │
│    31 │ Darren    │ Smith             │
└───────┴───────────┴───────────────────┘
(31 rows)

Hint

Read up on the COUNT window function.

My Answer

Here is the standard approach given what we have looked at so far concerning subqueries and the like:

SELECT
  (SELECT COUNT(*) FROM cd.members) AS count,
  firstname,
  surname
FROM
  cd.members
ORDER BY
  joindate;

But we can do something similar with window functions, where there are a number of different dedicated window functions (e.g., DENSE_RANK) but AVG(), MIN(), MAX(), SUM(), and COUNT() can also be used as window functions:

SELECT
  COUNT(*) OVER(), firstname, surname
FROM
  cd.members
ORDER BY
  joindate;

Discussion

SELECT COUNT(*) OVER(), firstname, surname
    FROM cd.members
ORDER BY joindate;

Using the knowledge we've built up so far, the most obvious answer to this is below. We use a subquery because otherwise SQL will require us to group by firstname and surname, producing a different result to what we're looking for.

SELECT (SELECT COUNT(*) FROM cd.members) AS count, firstname, surname
    FROM cd.members
ORDER BY joindate;

There's nothing at all wrong with this answer, but we've chosen a different approach to introduce a new concept called window functions. Window functions provide enormously powerful capabilities, in a form often more convenient than the standard aggregation functions. While this exercise is only a toy, we'll be working on more complicated examples in the near future.

Window functions operate on the result set of your (sub-)query, after the WHERE clause and all standard aggregation. They operate on a window of data. By default this is unrestricted: the entire result set, but it can be restricted to provide more useful results. For example, suppose instead of wanting the count of all members, we want the count of all members who joined in the same month as that member:

SELECT COUNT(*) OVER(PARTITION BY DATE_TRUNC('month',joindate)),
    firstname, surname
    FROM cd.members
ORDER BY joindate;

In this example, we partition the data by month. For each row the window function operates over, the window is any rows that have a joindate in the same month. The window function thus produces a count of the number of members who joined in that month.

You can go further. Imagine if, instead of the total number of members who joined that month, you want to know what number joinee they were that month. You can do this by adding in an ORDER BY to the window function:

SELECT COUNT(*) OVER(PARTITION BY DATE_TRUNC('month',joindate) ORDER BY joindate),
    firstname, surname
    FROM cd.members
ORDER BY joindate;

The ORDER BY changes the window again. Instead of the window for each row being the entire partition, the window goes from the start of the partition to the current row, and not beyond. Thus, for the first member who joins in a given month, the count is 1. For the second, the count is 2, and so on.

One final thing that's worth mentioning about window functions: you can have multiple unrelated ones in the same query. Try out the query below for an example - you'll see the numbers for the members going in opposite directions! This flexibility can lead to more concise, readable, and maintainable queries.

SELECT COUNT(*) OVER(PARTITION BY DATE_TRUNC('month',joindate) ORDER BY joindate ASC), 
    COUNT(*) OVER(PARTITION BY DATE_TRUNC('month',joindate) ORDER BY joindate DESC), 
    firstname, surname
    FROM cd.members
ORDER BY joindate;

Window functions are extraordinarily powerful, and they will change the way you write and think about SQL. Make good use of them!

---

Schema Reminder

Produce a numbered list of members

Question

Produce 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.

Expected Result

┌────────────┬───────────┬───────────────────┐
│ row_number │ firstname │      surname      │
├────────────┼───────────┼───────────────────┤
│          1 │ GUEST     │ GUEST             │
│          2 │ Darren    │ Smith             │
│          3 │ Tracy     │ Smith             │
│          4 │ Tim       │ Rownam            │
│          5 │ Janice    │ Joplette          │
│          6 │ Gerald    │ Butters           │
│          7 │ Burton    │ Tracy             │
│          8 │ Nancy     │ Dare              │
│          9 │ Tim       │ Boothe            │
│         10 │ Ponder    │ Stibbons          │
│         11 │ Charles   │ Owen              │
│         12 │ David     │ Jones             │
│         13 │ Anne      │ Baker             │
│         14 │ Jemima    │ Farrell           │
│         15 │ Jack      │ Smith             │
│         16 │ Florence  │ Bader             │
│         17 │ Timothy   │ Baker             │
│         18 │ David     │ Pinker            │
│         19 │ Matthew   │ Genting           │
│         20 │ Anna      │ Mackenzie         │
│         21 │ Joan      │ Coplin            │
│         22 │ Ramnaresh │ Sarwin            │
│         23 │ Douglas   │ Jones             │
│         24 │ Henrietta │ Rumney            │
│         25 │ David     │ Farrell           │
│         26 │ Henry     │ Worthington-Smyth │
│         27 │ Millicent │ Purview           │
│         28 │ Hyacinth  │ Tupperware        │
│         29 │ John      │ Hunt              │
│         30 │ Erica     │ Crumpet           │
│         31 │ Darren    │ Smith             │
└────────────┴───────────┴───────────────────┘
(31 rows)

Hint

Read up on the ROW_NUMBER window function.

My Answer

SELECT
  ROW_NUMBER() OVER(ORDER BY joindate) AS row_number,
  firstname,
  surname
FROM
  cd.members;

Discussion

SELECT ROW_NUMBER() OVER(ORDER BY joindate), firstname, surname
    FROM cd.members
ORDER BY joindate;

This exercise is a simple bit of window function practise! You could just as easily use count(*) over(order by joindate) here, so don't worry if you used that instead.

In this query, we don't define a partition, meaning that the partition is the entire dataset. Since we define an order for the window function, for any given row the window is: start of the dataset -> current row.

---

Schema Reminder

Output the facility id that has the highest number of slots booked, again

Question

Output the facility id that has the highest number of slots booked. Ensure that in the event of a tie, all tieing results get output.

Expected Result

┌───────┬─────────────┐
│ facid │ Total Slots │
├───────┼─────────────┤
│     4 │        1404 │
└───────┴─────────────┘
(1 row)

Hint

This one's a little bit tough. You'll need the RANK window function, and it's worth noting that it's possible to use an aggregate function inside the ORDER BY clause of a window function.

My Answer

SELECT
  facid,
  "Total Slots"
FROM
  (
    SELECT
      B.facid,
      SUM(B.slots) AS "Total Slots",
      DENSE_RANK() OVER (
        ORDER BY SUM(B.slots) DESC
      ) slotsRank
    FROM
      cd.bookings B
    GROUP BY
      B.facid
  ) X
WHERE
  slotsRank = 1;

Discussion

SELECT facid, total FROM (
    SELECT facid, SUM(slots) total, RANK() OVER (ORDER BY SUM(slots) DESC) rank
            FROM cd.bookings
        GROUP BY facid
    ) AS ranked
    WHERE rank = 1;

You may recall that this is a problem we've already solved in an earlier exercise. We came up with an answer something like below, which we then cut down using CTEs:

SELECT facid, SUM(slots) AS totalslots
    FROM cd.bookings
    GROUP BY facid
    HAVING SUM(slots) = (SELECT max(sum2.totalslots) FROM
        (SELECT SUM(slots) AS totalslots
        FROM cd.bookings
        GROUP BY facid
        ) AS sum2);

Once we've cleaned it up, this solution is perfectly adequate. Explaining how the query works makes it seem a little odd, though - 'find the number of slots booked by the best facility. Calculate the total slots booked for each facility, and return only the rows where the slots booked are the same as for the best'. Wouldn't it be nicer to be able to say 'calculate the number of slots booked for each facility, rank them, and pick out any at rank 1'?

Fortunately, window functions allow us to do this - although it's fair to say that doing so is not trivial to the untrained eye. The first key piece of information is the existence of the RANK function. This ranks values based on the ORDER BY that is passed to it. If there's a tie for (say) second place), the next gets ranked at position 4. So, what we need to do is get the number of slots for each facility, rank them, and pick off the ones at the top rank. A first pass at this might look something like the below:

SELECT facid, total FROM (
    SELECT facid, total, RANK() OVER (ORDER BY total DESC) rank FROM (
        SELECT facid, SUM(slots) total
            FROM cd.bookings
            GROUP BY facid
        ) AS sumslots
    ) AS ranked
WHERE rank = 1;

The inner query calculates the total slots booked, the middle one ranks them, and the outer one creams off the top ranked. We can actually tidy this up a little: recall that window function get applied pretty late in the select function, after aggregation. That being the case, we can move the aggregation into the ORDER BY part of the function, as shown in the approved answer.

While the window function approach isn't massively simpler in terms of lines of code, it arguably makes more semantic sense.

---

Schema Reminder

Rank members by (rounded) hours used

Question

Produce 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.

Expected Result

┌───────────┬───────────────────┬───────┬──────┐
│ firstname │      surname      │ hours │ rank │
├───────────┼───────────────────┼───────┼──────┤
│ GUEST     │ GUEST             │  1200 │    1 │
│ Darren    │ Smith             │   340 │    2 │
│ Tim       │ Rownam            │   330 │    3 │
│ Tim       │ Boothe            │   220 │    4 │
│ Tracy     │ Smith             │   220 │    4 │
│ Gerald    │ Butters           │   210 │    6 │
│ Burton    │ Tracy             │   180 │    7 │
│ Charles   │ Owen              │   170 │    8 │
│ Janice    │ Joplette          │   160 │    9 │
│ Anne      │ Baker             │   150 │   10 │
│ Timothy   │ Baker             │   150 │   10 │
│ David     │ Jones             │   150 │   10 │
│ Nancy     │ Dare              │   130 │   13 │
│ Florence  │ Bader             │   120 │   14 │
│ Anna      │ Mackenzie         │   120 │   14 │
│ Ponder    │ Stibbons          │   120 │   14 │
│ Jack      │ Smith             │   110 │   17 │
│ Jemima    │ Farrell           │    90 │   18 │
│ David     │ Pinker            │    80 │   19 │
│ Ramnaresh │ Sarwin            │    80 │   19 │
│ Matthew   │ Genting           │    70 │   21 │
│ Joan      │ Coplin            │    50 │   22 │
│ David     │ Farrell           │    30 │   23 │
│ Henry     │ Worthington-Smyth │    30 │   23 │
│ John      │ Hunt              │    20 │   25 │
│ Douglas   │ Jones             │    20 │   25 │
│ Millicent │ Purview           │    20 │   25 │
│ Henrietta │ Rumney            │    20 │   25 │
│ Erica     │ Crumpet           │    10 │   29 │
│ Hyacinth  │ Tupperware        │    10 │   29 │
└───────────┴───────────────────┴───────┴──────┘
(30 rows)

Hint

You'll need the RANK window function again. You can use integer arithmetic to accomplish rounding.

My Answer

It is helpful to utilize an inline view here to make the initial SELECT statement clearer (you cannot ORDER BY hours unless you have an inline view).

SELECT
  firstname,
  surname,
  hours,
  RANK() OVER(ORDER BY hours DESC) AS rank
FROM
  (
    SELECT
      M.firstname,
      M.surname,
      ROUND(SUM(B.slots) / 2, -1) AS hours
    FROM
      cd.members M
      INNER JOIN cd.bookings B ON M.memid = B.memid
    GROUP BY
      M.firstname, M.surname
    ORDER BY
      Hours DESC
  ) X
ORDER BY
  rank, surname, firstname;

Discussion

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 cd.bookings bks
    INNER JOIN cd.members mems
        ON bks.memid = mems.memid
    GROUP BY mems.memid
ORDER BY rank, surname, firstname;

This answer isn't a great stretch over our previous exercise, although it does illustrate the function of RANK better. You can see that some of the clubgoers have an equal rounded number of hours booked in, and their rank is the same. If position 2 is shared between two members, the next one along gets position 4. There's a different function, DENSE_RANK, that would assign that member position 3 instead.

It's worth noting the technique we use to do rounding here. Adding 5, dividing by 10, and multiplying by 10 has the effect (thanks to integer arithmetic cutting off fractions) of rounding a number to the nearest 10. In our case, because slots are half an hour, we need to add 10, divide by 20, and multiply by 10. One could certainly make the argument that we should do the slots -> hours conversion independently of the rounding, which would increase clarity.

Talking of clarity, this rounding malarky is starting to introduce a noticeable amount of code repetition. At this point it's a judgement call, but you may wish to factor it out using a subquery as below:

SELECT firstname, surname, hours, RANK() OVER (ORDER BY hours DESC) FROM
    (SELECT firstname, surname,
        ((SUM(bks.slots)+10)/20)*10 AS hours

        FROM cd.bookings bks
        INNER JOIN cd.members mems
            ON bks.memid = mems.memid
        GROUP BY mems.memid
    ) AS subq
ORDER BY rank, surname, firstname;

---

Schema Reminder

Find the top three revenue generating facilities

Question

Produce a list of the top three revenue generating facilities (including ties). Output facility name and rank, sorted by rank and facility name.

Expected Result

┌────────────────┬──────┐
│      name      │ rank │
├────────────────┼──────┤
│ Massage Room 1 │    1 │
│ Massage Room 2 │    2 │
│ Tennis Court 2 │    3 │
└────────────────┴──────┘
(3 rows)

Hint

Yet another question based on the RANK window function! Remember the relative complexity of calculating the revenue of a facility, since you need to count for the different costs for the GUEST user..

My Answer

SELECT
  name,
  rank
FROM
  (
    SELECT
      name,
      DENSE_RANK() OVER(ORDER BY total_revenue DESC) AS rank
    FROM
      (
        SELECT
          F.name,
          SUM(B.slots *
            CASE
              WHEN B.memid = 0 THEN F.guestcost
              ELSE
                F.membercost
            END
          ) AS total_revenue
        FROM
          cd.bookings B
          INNER JOIN cd.facilities F ON B.facid = F.facid
        GROUP BY
          F.name
      ) X
  ) Y
WHERE
  rank <= 3;

Discussion

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 cd.bookings bks
        INNER JOIN cd.facilities facs
            ON bks.facid = facs.facid
        GROUP BY facs.name
    ) AS subq
    WHERE rank <= 3
ORDER BY rank;

This question doesn't introduce any new concepts, and is just intended to give you the opportunity to practise what you already know. We use the CASE statement to calculate the revenue for each slot, and aggregate that on a per-facility basis using SUM. We then use the RANK window function to produce a ranking, wrap it all up in a subquery, and extract everything with a rank less than or equal to 3.

---

Schema Reminder

Classify facilities by value

Question

Classify facilities into equally sized groups of high, average, and low based on their revenue. Order by classification and facility name.

Expected Result

┌─────────────────┬─────────┐
│      name       │ revenue │
├─────────────────┼─────────┤
│ Massage Room 1  │ high    │
│ Massage Room 2  │ high    │
│ Tennis Court 2  │ high    │
│ Badminton Court │ average │
│ Squash Court    │ average │
│ Tennis Court 1  │ average │
│ Pool Table      │ low     │
│ Snooker Table   │ low     │
│ Table Tennis    │ low     │
└─────────────────┴─────────┘
(9 rows)

Hint

Investigate the NTILE window function.

My Answer

SELECT
  name,
  CASE
    WHEN bucketNum = 1 THEN 'high'
    WHEN bucketNum = 2 THEN 'average'
    ELSE
      'low'
  END AS revenue
FROM
  (
    SELECT
      F.name,
      NTILE(3) OVER(ORDER BY SUM(B.slots *
        CASE
          WHEN B.memid = 0 THEN F.guestcost
          ELSE
            F.membercost
        END) DESC) bucketNum
    FROM
      cd.bookings B
      INNER JOIN cd.facilities F ON B.facid = F.facid
    GROUP BY
      F.name
    ORDER BY
      bucketNum, F.name
  ) X;

Discussion

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 cd.bookings bks
        INNER JOIN cd.facilities facs
            ON bks.facid = facs.facid
        GROUP BY facs.name
    ) AS subq
ORDER BY class, name;

This exercise should mostly use familiar concepts, although we do introduce the NTILE window function. NTILE groups values into a passed-in number of groups, as evenly as possible. It outputs a number from 1->number of groups. We then use a CASE statement to turn that number into a label!

---

Schema Reminder

Calculate the payback time for each facility

Question

Based 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!

Expected Result

┌─────────────────┬────────────────────────┐
│      name       │         months         │
├─────────────────┼────────────────────────┤
│ Badminton Court │     6.8317677198975235 │
│ Massage Room 1  │ 0.18885741265344664778 │
│ Massage Room 2  │     1.7621145374449339 │
│ Pool Table      │     5.3333333333333333 │
│ Snooker Table   │     6.9230769230769231 │
│ Squash Court    │     1.1339582703356516 │
│ Table Tennis    │     6.4000000000000000 │
│ Tennis Court 1  │     2.2624434389140271 │
│ Tennis Court 2  │     1.7505470459518600 │
└─────────────────┴────────────────────────┘
(9 rows)

Hint

There's no need to use window functions to solve this problem. Hard-code the number of months for an easy time, calculate them for a tougher one.

My Answer

SELECT
  F.name,
  F.initialoutlay / (SUM(B.slots *
    CASE
      WHEN B.memid = 0 THEN F.guestcost
      ELSE
        F.membercost
    END
  ) / 3.0 - F.monthlymaintenance) AS months
FROM
  cd.bookings B
  INNER JOIN cd.facilities F ON B.facid = F.facid
GROUP BY
  F.name, F.monthlymaintenance, F.initialoutlay
ORDER BY
  F.name;

Discussion

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 cd.bookings bks
    INNER JOIN cd.facilities facs
        ON bks.facid = facs.facid
    GROUP BY facs.facid
ORDER BY name;

In contrast to all our recent exercises, there's no need to use window functions to solve this problem: it's just a bit of maths involving monthly revenue, initial outlay, and monthly maintenance. Again, for production code you might want to clarify what's going on a little here using a subquery (although since we've hard-coded the number of months, putting this into production is unlikely!). A tidied-up version might look like:

SELECT name, 
    initialoutlay / (monthlyrevenue - monthlymaintenance) AS repaytime 
    FROM 
        (SELECT facs.name AS name, 
            facs.initialoutlay AS initialoutlay,
            facs.monthlymaintenance AS monthlymaintenance,
            SUM(CASE
                WHEN memid = 0 THEN slots * facs.guestcost
                ELSE slots * membercost
            END)/3 AS monthlyrevenue
        FROM cd.bookings bks
        INNER JOIN cd.facilities facs
            ON bks.facid = facs.facid
        GROUP BY facs.facid
    ) AS subq
ORDER BY name;

But, I hear you ask, what would an automatic version of this look like? One that didn't need to have a hard-coded number of months in it? That's a little more complicated, and involves some date arithmetic. I've factored that out into a CTE to make it a little more clear.

WITH monthdata AS (
    SELECT mincompletemonth,
        maxcompletemonth,
        (EXTRACT(YEAR FROM maxcompletemonth)*12) +
            EXTRACT(MONTH FROM maxcompletemonth) -
            (EXTRACT(YEAR FROM mincompletemonth)*12) -
            EXTRACT(MONTH FROM mincompletemonth) AS nummonths 
    FROM (
        SELECT DATE_TRUNC('month', 
                (SELECT MAX(starttime) FROM cd.bookings)) AS maxcompletemonth,
            DATE_TRUNC('month', 
                (SELECT MIN(starttime) FROM cd.bookings)) AS mincompletemonth
    ) AS subq
)