PgExercises sample database

Download and install the PostgreSQL-compatible version of PgExercises on the YugabyteDB distributed SQL database. Work through 81 exercises to learn SQL or test your knowledge.

About the PgExercises sample database

The PgExercises sample database is based on the sample dataset used for the PostgreSQL Exercises tutorial website. The dataset is for a new country club, with a set of members, facilities, and booking history.

The PostgreSQL Exercises website includes 81 exercises designed to be used as a companion to the official PostgreSQL documentation. The exercises on the PgExercises site range from simple SELECT statements and WHERE clauses, through JOINs and CASE statements, then on to aggregations, window functions, and recursive queries.

For further details about the data, see the PostgresSQL Exercises’ Getting Started page.

The exercises database consists of three tables (for members, bookings, and facilities) and the table relationships as shown in the entity relationship diagram.

Install the PgExercises sample database

Follow the steps here to download and install the PgExercises sample database.

Before you begin

To use the PgExercises sample database, you must have installed and configured YugabyteDB. To get up and running quickly, see Quick Start.

1. Download the SQL scripts

You can download the PGExercise SQL scripts that is compatible with YugabyteDB from the sample directory of the YugabyteDB GitHub repository.

Here are the two files you’ll need.

• clubdata_ddl.sql — Creates the tables and other database objects
• clubdata_data.sql — Loads the sample data

2. Open the YSQL shell

To open the YSQL shell, run the ysqlsh command from the YugabyteDB root directory.

$ ./bin/ysqlsh

ysqlsh (11.2)
Type "help" for help.
yugabyte=#

3. Create the PgExercises database

To create the exercises database, run the following SQL CREATE DATABASE command.

CREATE DATABASE exercises;

Confirm that you have the exercises database by listing the databases on your cluster using the \l command.

yugabyte=# \l

Connect to the exercises database.

yugabyte=# \c exercises

You are now connected to database "exercises" as user "yugabyte".
exercises=#

4. Build the PgExercises tables and objects

To build the tables and database objects, run the \i command.

exercises=# \i share/clubdata_ddl.sql

You can verify that all three tables have been created by running the \d command.

exercises=# \d

5. Load the sample data

To load the exercises database with sample data, run the following command to execute commands in the file.

exercises=# \i share/clubdata_data.sql

You can verify that you have data to work with by running the following SELECT statement to pull data from the bookings table.

exercises=# SELECT * FROM bookings LIMIT 5;

Explore the PgExercises database

You are now ready to start working through the PostgreSQL Exercises exercises using YugabyteDB as the backend. The 81 exercises at the PostgreSQL Exercises website are broken into the following major sections.

• Simple SQL Queries
• JOINs and Subqueries
• Modifying Data
• Aggregation
• Working with Timestamps
• String Operations
• Recursive Queries

YugabyteDB returns the same results as expected based on the solutions on the PostgreSQL Exercises website, with the following exceptions.

• “Work out the start times of bookings for tennis courts”
  • The JOIN does not return the correct row numbers. See YugabyteDB GitHub issue #1827.
• “Find telephone numbers with parentheses”
  • YugabyteDB returns results with a sort order of strings different than in PostgreSQL due to hash partitioning in YugabyteDB.
• “Update a row based on the contents of another row”
  • YugabyteDB returns an error because using the FROM clause in UPDATE is not yet supported. See YugabyteDB GitHub issue #738.
• “Delete based on a subquery”
  • YugabyteDB returns an error. See YugabyteDB GitHub issue #1828.