Skip to main content

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​

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

Retrieve specific columns from a table​

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.

Control which rows are retrieved​

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

Control which rows are retrieved - part 2​

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.

Basic string searches​

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

Matching against multiple possible values​

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

Classify results into buckets​

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.

Working with dates​

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.

Removing duplicates, and ordering results​

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

Combining results from multiple queries​

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.

Simple aggregation​

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.

More aggregation​

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?

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​

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

Work out the start times of bookings for tennis courts​

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

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.

Produce a list of all members, along with their recommender​

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

Produce a list of all members who have used a tennis court​

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.

Produce a list of costly bookings​

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.

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

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.

Produce a list of costly bookings, using a subquery​

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.

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​

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.

Insert multiple rows of data into a table​

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.

Insert calculated data into a table​

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.

Update some existing data​

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.

Update multiple rows and columns at the same time​

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.

Update a row based on the contents of another row​

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.

Delete all bookings​

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

Delete a member from the cd.members table​

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

Delete based on a subquery​

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?

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​

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.

Count the number of expensive facilities​

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

Count the number of recommendations each member makes​

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

List the total slots booked per facility​

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.

List the total slots booked per facility in a given month​

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.

List the total slots booked per facility per month​

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.

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

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

List facilities with more than 1000 slots booked​

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.

Find the total revenue of each facility​

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!

Find facilities with a total revenue less than 1000​

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!

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

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!

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

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.

List the total hours booked per named facilit​

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.

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

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

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

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

Produce a numbered list of members​

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.

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

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.

Rank members by (rounded) hours used​

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.

Find the top three revenue generating facilities​

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

Classify facilities by value​

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

Calculate the payback time for each facility​

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!

Calculate a rolling average of total revenue​

For each day in August 2012, calculate a rolling average of total revenue over the previous 15 days. Output should contain date and revenue columns, sorted by the date. Remember to account for the possibility of a day having zero revenue. This one's a bit tough, so don't be afraid to check out the hint!

Date​

Intro​

Dates/Times in SQL are a complex topic, deserving of a category of their own. They're also fantastically powerful, making it easier to work with variable-length concepts like 'months' than many programming languages.

Before getting started on this category, it's probably worth taking a look over the PostgreSQL docs page on date/time functions. You might also want to complete the aggregate functions category, since we'll use some of those capabilities in this section.

Produce a timestamp for 1 a.m. on the 31st of August 2012​

Produce a timestamp for 1 a.m. on the 31st of August 2012.

Subtract timestamps from each other​

Find the result of subtracting the timestamp '2012-07-30 01:00:00' from the timestamp '2012-08-31 01:00:00'.

Generate a list of all the dates in October 2012​

Produce a list of all the dates in October 2012. They can be output as a timestamp (with time set to midnight) or a date.

Get the day of the month from a timestamp​

Get the day of the month from the timestamp '2012-08-31' as an integer.

Work out the number of seconds between timestamps​

Work out the number of seconds between the timestamps '2012-08-31 01:00:00' and '2012-09-02 00:00:00'.

Work out the number of days in each month of 2012​

For each month of the year in 2012, output the number of days in that month. Format the output as an integer column containing the month of the year, and a second column containing an interval data type.

Work out the number of days remaining in the month​

For any given timestamp, work out the number of days remaining in the month. The current day should count as a whole day, regardless of the time. Use '2012-02-11 01:00:00' as an example timestamp for the purposes of making the answer. Format the output as a single interval value.

Work out the end time of bookings​

Return a list of the start and end time of the last 10 bookings (ordered by the time at which they end, followed by the time at which they start) in the system.

Return a count of bookings for each month​

Return a count of bookings for each month, sorted by month.

Work out the utilisation percentage for each facility by month​

Work out the utilisation percentage for each facility by month, sorted by name and month, rounded to 1 decimal place. Opening time is 8am, closing time is 8.30pm. You can treat every month as a full month, regardless of if there were some dates the club was not open.

String​

Intro​

String operations in most RDBMSs are, arguably, needlessly painful. Fortunately, Postgres is better than most in this regard, providing strong regular expression support. This section covers basic string manipulation, use of the LIKE operator, and use of regular expressions. I also make an effort to show you some alternative approaches that work reliably in most RDBMSs. Be sure to check out Postgres' string function docs page if you're not confident about these exercises.

Anthony Molinaro's SQL Cookbook provides some excellent documentation of (difficult) cross-DBMS compliant SQL string manipulation. I'd strongly recommend his book.

Format the names of members​

Output the names of all members, formatted as 'Surname, Firstname'

Find facilities by a name prefix​

Find all facilities whose name begins with 'Tennis'. Retrieve all columns.

Perform a case-insensitive search to find all facilities whose name begins with 'tennis'. Retrieve all columns.

Find telephone numbers with parentheses​

You've noticed that the club's member table has telephone numbers with very inconsistent formatting. You'd like to find all the telephone numbers that contain parentheses, returning the member ID and telephone number sorted by member ID.

Pad zip codes with leading zeroes​

The zip codes in our example dataset have had leading zeroes removed from them by virtue of being stored as a numeric type. Retrieve all zip codes from the members table, padding any zip codes less than 5 characters long with leading zeroes. Order by the new zip code.

Count the number of members whose surname starts with each letter of the alphabet​

You'd like to produce a count of how many members you have whose surname starts with each letter of the alphabet. Sort by the letter, and don't worry about printing out a letter if the count is 0.

Clean up telephone numbers​

The telephone numbers in the database are very inconsistently formatted. You'd like to print a list of member ids and numbers that have had '-', '(',')', and ' ' characters removed. Order by member id.

Recursive​

Intro​

Common Table Expressions allow us to, effectively, create our own temporary tables for the duration of a query - they're largely a convenience to help us make more readable SQL. Using the WITH RECURSIVE modifier, however, it's possible for us to create recursive queries. This is enormously advantageous for working with tree and graph-structured data - imagine retrieving all of the relations of a graph node to a given depth, for example.

This category shows you some basic recursive queries that are possible using our dataset.

Find the upward recommendation chain for member ID 27​

Find the upward recommendation chain for member ID 27: that is, the member who recommended them, and the member who recommended that member, and so on. Return member ID, first name, and surname. Order by descending member id.

Find the downward recommendation chain for member ID 1​

Find the downward recommendation chain for member ID 1: that is, the members they recommended, the members those members recommended, and so on. Return member ID and name, and order by ascending member id.

Produce a CTE that can return the upward recommendation chain for any member​

Produce a CTE that can return the upward recommendation chain for any member. You should be able to select recommender from recommenders where member=x. Demonstrate it by getting the chains for members 12 and 22. Results table should have member and recommender, ordered by member ascending, recommender descending.

Custom​

Produce a CTE that can return the downward recommendation chain for any member​

Produce a CTE that can return the downward recommendation chain for any member. You should be able to select recommender from recommenders where member=x. Demonstrate it by getting the chains for members 12 and 22. Results table should have member and recommender, ordered by member ascending, recommender descending.