19 PostgreSQL Practice Exercises with Detailed Solutions

You’ve probably heard the saying “practice makes perfect”. Find out if this phrase is true by trying the comprehensive set of PostgreSQL practice exercises that you will find in this article!

To master any language, it is essential to practice it regularly so as not to lose the skill. This is as true for verbal communication as it is for programming. And SQL programming is no exception. Even the most seasoned PostgreSQL programmer needs to do PostgreSQL practice daily to perfect (and maintain) their skills.

Beginners in PostgreSQL should develop their skills through simple exercises that allow them to gradually incorporate new knowledge. In this article, you will find a series of PostgreSQL practice exercises that will help you quickly learn how to query PostgreSQL databases. You can find more SQL practice exercises for beginners in this article.

To work on these exercises, you will need a PostgreSQL server where you can create a database, add tables to it, and populate it with data. You will also need a client application that allows you to execute SQL commands against the PostgreSQL server. With these elements, you can create the example tables that will let you do all the PostgreSQL practice exercises throughout this article. If you need help, you can read this article on how to create your own SQL practice database.

There are many ways to practice SQL online. The one I recommend specifically for PostgreSQL users is our interactive SQL Practice Set in PostgreSQL. In it, you will find 88 exercises that move from simple to advanced concepts. The example tables used in this article are already created and populated; you can write and run all the query exercises you want and see how they work. The exercises in this article are taken directly from this course - if you find them useful, you can be sure that you will find the rest of the course useful, too. Don’t worry: you are allowed to make all kinds of mistakes - there’s nothing you can break and no one is going to get angry with you for running odd queries.

Finally, keep our free PostgreSQL cheat sheet handy as you work through this article. This quick guide summarizes key PostgreSQL commands and features.

Now, let’s start practicing PostgreSQL!

PostgreSQL Practice Dataset

The main table we will use for the PostgreSQL exercises in this article contains information about cats – their name, breed, age, etc. The table name is simply cat, and its structure is as follows:

• id: A numerical value that identifies each cat represented in the table.
• name: The cat’s name.
• breed
• coloration
• age
• Fav_toy: The cat’s favorite type of toy.

Here is a sample of the data:

Exercise 1: Getting to Know the Data

Exercise: Get all the information from the table cat.

Solution:

SELECT *
FROM cat;

Explanation: The SELECT * FROM cat  command asks PostgreSQL to fetch all the columns and all the rows of the cat table. The key to get all the columns is the SELECT *. The “*” works as a wildcard: when you don't know the column names, SELECT * will return all of them.

With the FROM cat clause, you are telling PostgreSQL that you want to get information from the cat table. By not setting any additional conditions, you will get all the rows. The semicolon at the end of the command is simply an indication that the command ends there.

Exercise 2: Selecting Specific Columns of a Table

Exercise: Select only the columns name and age from the table cat.

Solution:

SELECT name, age
FROM cat;

Explanation: In the previous exercise, you used the wildcard “*” to select all the columns from a table. In this exercise, you just want to see the name and age of each cat. Instead of using the wildcard "*", you just specify the column names name and age after the SELECT command. Separate columns with a comma.

Exercise 3: Gathering Information About Siamese Cats

Exercise: Get all information from the cat table about Siamese cats.

Solution:

SELECT *
FROM cat
WHERE breed = 'Siamese';

Explanation: To apply a filter on the selected rows, use the WHERE clause. This allows you to specify a condition that the rows must meet to be included in the result set. In this exercise, the WHERE condition asks the database to return information only for Siamese cats.

The condition specified in the WHERE clause is (in general) a logical condition that can be evaluated as true or false. In its simplest form, it indicates the name of one of the columns in the table (breed), then a comparison operator (=) and a value to compare against ('Siamese'). Note that all string literals must be enclosed in quotes.

Exercise 4: Finding the Youngest Cats

Exercise: Get rows from the table cat that correspond to individuals younger than 8 years old.

Solution:

SELECT *
FROM cat
WHERE age < 8;

Explanation: In the previous exercise, we saw how to filter query results by comparing a text column (breed) with a literal value ('Siamese'). This included only rows where the value in that column matches the specified string literal.

In this exercise, you also have to specify a condition in the WHERE clause. However, now you’re applying the condition to a numeric value and using the non-equality comparison operator < (less than).

To get a list of all the rows in the cat table for cats less than 8 years old, you must use the age column in the WHERE clause. You combine it with the comparison operator < and the numeric value 8.

In the same way, you can use different criteria to compare numerical values. To see cats older than 8, for example, just change the condition in the WHERE clause to age > 8.

Exercise 5: Looking for Young Siamese Cats

Exercise: Get rows that correspond to Siamese cats younger than 8 years old.

Solution:

SELECT *
FROM cat
WHERE breed = 'Siamese'
AND age < 8;

Explanation: You’ll often need to obtain rows that meet more than one condition. To achieve this, all conditions must be specified in the WHERE clause and joined by the AND conjunction. As a result of this query, PostgreSQL will return a set of rows for Siamese cats that are less than 8 years old.

In addition to the AND conjunction, there are other ways to combine conditions into logical statements that evaluate to true or false.

Exercise 6: Using Alternative Conditions (One or the Other)

Exercise: Get a list of Persian or Siamese cats from the cat table.

Solution:

SELECT id, name
FROM cat
WHERE breed = 'Siamese'
OR breed = 'Persian';

Explanation: Conditions combined by the logical OR operator return True when at least one of the expressions is True. In this exercise, we used the OR operator to get data from Siamese or Persian cats.

Exercise 7: Combining Different Logical Conditions

Exercise: Get a list of Siamese or Persian cats from the cat table that are younger than 5 years old or older than 10 years old.

Solution:

SELECT id, name
FROM cat
WHERE (breed = 'Siamese' OR breed = 'Persian')
AND (age < 5 OR age > 10);

Explanation: To get data from a table that meets a combination of alternative or mandatory conditions, you must combine the OR and AND logical operators. We do this with parentheses, so that PostgreSQL correctly understands the precedence of the conditions.

Care must be taken when OR and AND operators are placed in the same WHERE clause. PostgreSQL will combine the logical conditions in the order they appear unless parentheses are used. If parentheses are used (as in the example above), the result of what is in the parentheses is evaluated first; then, those results are combined with the operator that connects them.

In this exercise, first the cat’s breed is evaluated. If it meets either of the two WHERE conditions, the database will then evaluate the next set of conditions to see if the cat meets the age requirements. Only cats that meet one breed condition and one age condition are included in the result.

Exercise 8: Listing Cats of Different Breeds

Exercise: Get a list of Persian, Siamese, or Ragdoll cats – without using the OR operator.

Solution:

SELECT id, name
FROM cat
WHERE breed IN ('Persian', 'Siamese', 'Ragdoll');

Explanation: Sometimes it is necessary to filter the results of a query by a set of values. This could be achieved by specifying many conditions connected by OR operators, like this:

SELECT name
FROM cat
WHERE breed = 'Persian' OR breed = 'Siamese' OR breed = 'Ragdoll';

But to simplify a query with many enumerations, it is preferable to use the logical operator IN, which evaluates the membership of a value to a set. In this case, the value would be the column breed and the set would be a list of values ('Persian', 'Siamese', 'Ragdoll').

The IN operator makes complex queries easier to read and understand.

Exercise 9: Searching for Partial Matches

Exercise: Get all rows in the cat table corresponding to cats whose favorite toy is a ball of any color.

Solution:

SELECT *
FROM cat
WHERE fav_toy LIKE '%ball%';

Explanation: When you try to search for matches in columns containing descriptions, you often don't know exactly how the descriptions are stored. Sometimes you need to search for a particular word that may appear within a description. In those situations, the = comparison operator is of no use, since it only returns rows where the match is exact.

When you need to find partial matches, PostgreSQL lets you search for text fragments within varchar columns. The trick is to replace the = operator with LIKE, adding wildcards to stand for the parts of the text you do not know.

In the cat table, the fav_toy column stores “ball” as well as its color – e.g. “red ball” or “green ball”. With the LIKE operator, you can ask PostgreSQL to return the rows where fav_toy contains the word “ball”.

The % symbol placed on either side of the word “ball” acts as a wildcard, indicating that there can be any text surrounding “ball”. The resulting rows are those where the word “ball” appears as part of the text in the fav_toy column, regardless of what other text it contains.

Exercise 10: Finding NULL Values on Certain Columns

Exercise: Get all rows from the cat table corresponding to cats that don’t have a favorite toy. 

Solution:

SELECT name
FROM cat
WHERE fav_toy IS NULL;

Explanation: There are situations when you need to get the rows of a table for which there is no information in a certain column. In other words, you need to tell the database to fetch only the rows where a stated column has a NULL value. You cannot use comparison operators for this; in SQL, any comparison involving NULL values always yields the logical value False.

The only way to tell the database to fetch such rows is to specify that the column value IS NULL: fav_toy IS NULL.

Remember, the word NULL in SQL refers to an unknown value. It is not the same as the value 0 in a numeric column or a zero-length string in a varchar column. Any numeric, date, or string operation involving a NULL value also results in a NULL value.

Exercise 11: Avoiding Rows Without Data in Certain Columns

Exercise:  Get all rows from the cat table corresponding to cats that do have a favorite toy.

Solution:

SELECT name
FROM cat
WHERE fav_toy IS NOT NULL;

Explanation: We know how to get rows where a column has a NULL value. But what if you need the opposite – i.e. to get all rows with any value in that column? The IS NOT NULL condition does exactly the opposite of IS NULL: it returns all rows that have some data (any data) in the column fav_toy.

Exercise 12: Getting a List of Cats Sorted by Age

Exercise:  Select name and age from the cat table, making sure the results are ordered by age.

Solution:

SELECT name, age
FROM cat
ORDER BY age;

Explanation: When executing a SELECT query in an SQL database, it is impossible to predict in which order the results will appear – unless you specify an ordering criteria. If you want to get the results of a query in a particular order, it is necessary to specify the columns that will determine the ordering using an ORDER BY clause.

Exercise 13: Sorting Cats by Breed and Age

Exercise: Select the breed, name, and age of all rows from the table cat, getting the results sorted first by breed and then by age.

Solution:

SELECT breed, name, age
FROM cat
ORDER BY breed, age;

Explanation: You can use the ORDER BY clause to indicate one or more columns as sorting criteria. The query in this exercise sorts the results by breed; within each breed, the rows are sorted by age.

Exercise 14: Using Ascending and Descending Ordering

Exercise: Select the breed, name, and age of all cats, then sort the results first by breed in alphabetical order and then by age from oldest to youngest.

Solution:

SELECT breed, name, age
FROM cat
ORDER BY breed ASC, age DESC;

Explanation: The ORDER BY clause also lets you specify ascending or descending order for each of the sort columns. The word ASC indicates ascending  (A to Z, 1 to 10) order; DESC indicates descending (Z to A, 10 to 1) order. If the sort order is not specified, ascending order is assumed. For alphanumeric data, ascending order is the same as alphabetical order.

The query in this exercise sorts the rows alphabetically by breed (in ascending order) and then by age in descending order.

Exercise 15: Grouping and Aggregating Data

Exercise: Select the cat breeds from the cat table and the number of cats of each breed.

Solution:

SELECT breed, COUNT(*)
FROM cat
GROUP BY breed;

Explanation: The GROUP BY clause groups rows based on a column value (GROUP BY breed); one row is returned for each group value (e.g. the cats table would have one row for Siamese, one for Ragdoll, etc.)

To get the number of cats in each breed, we use the aggregate function COUNT(*), which returns the number of rows in each group.

When using the GROUP BY clause, it’s important to note that columns in the SELECT list that aren’t used in an aggregate function must also appear in the column list of the GROUP BY clause.

If you feel you need more practice to fully understand grouping in SQL, try these 10 GROUP BY practice exercises.

Exercise 16: Getting the Age of the Youngest and Oldest Cat in Each Breed

Exercise: Select a list of the cat breeds from the cat table, together with the maximum and minimum age for each breed.

Solution:

SELECT breed, MIN(age), MAX(age)
FROM cat
GROUP BY breed;

Explanation: Grouping allows us to get summarized data. Besides getting the number of elements in each group (as we did in the previous exercise), we can use functions to calculate totals, averages, and maximum/minimum values for each group.

In this exercise, the MIN() and MAX() functions are used to get the maximum and minimum ages for each breed.

Exercise 17: Grouping by Multiple Columns

Exercise: Select a list of the cat breeds and different colorations from the cat table, counting the number of cats of each combination of breed and coloration.

Solution:

SELECT 
  breed, 
  coloration, 
  COUNT(*)
FROM cat
GROUP BY breed, coloration;

Explanation: You may want to group data by more than one column, creating a hierarchy of several nested grouping criteria. This can be done by detailing all the grouping fields in the GROUP BY clause.

The order in which these fields are written will determine how the grouping hierarchy will look. In this exercise, we get the data grouped by breed and then by coloration within each breed. And the COUNT(*) function shows how many cats are in the table for each combination of breed and coloration.

Exercise 18: Setting Conditions on Aggregated Data

Exercise: Get a list of the breeds that are represented by more than one record in the cat table.

Solution:

SELECT breed, COUNT(*)
FROM cat
GROUP BY breed
HAVING COUNT(*) > 1;

Explanation: We’ve seen that WHERE allows you to set conditions on rows returned by the query. But you may need to filter the grouped rows by setting conditions on the results of aggregate functions.

To do that, set the condition that the result of the COUNT(*) function must be greater than 1. But you cannot specify that in the WHERE clause; it’s computed on individual rows before GROUP BY is processed by the database. Therefore, you must use the HAVING clause, as we did in this exercise.

Exercise 19: Obtaining Related Data from Different Tables

Exercise: Select the breed origin of each cat in the cat table by obtaining it from the related breed_origin table. Here’s a snapshot of the data in the breed_origin table:

Solution:

SELECT cat.name, cat.breed, breed_origin.origin
FROM cat JOIN breed_origin
ON cat.breed = breed_origin.breed;

Explanation: If you need a query that returns the breed origin for each of the cats, you must join the cat table with the breed_origin table. To do that, you have to specify both tables in the FROM clause – separated by the word JOIN.

The joining condition is specified after the table names and preceded by the word ON. It is necessary to set this condition so that the database knows how to properly match the rows. In this case, the condition is that the breed column of cat is equal to the breed column of breed_origin.

Since there is more than one table in the FROM clause, it’s best to put the table name before the column name, separating them with a period (e.g. cat.breed). This avoids ambiguity for both the database and the reader. It is not mandatory to do this for all columns, but it is essential for columns that share the same name in both tables.

In the example above, the column breed is in both cat and breed_origin, so it is mandatory to prefix the table name every time you refer to either column.

Once you get a grasp on how to join tables in SQL, you can strengthen your knowledge by completing these 12 JOIN practice exercises.

Ready for Advanced PostgreSQL Practice Exercises?

What did you think of this set of PostgreSQL practice exercises for beginners? Remember that you can continue practicing with the 88 exercises in the SQL Practice Set in PostgreSQL interactive course, which is only one of the 65 hands-on SQL courses for beginners and experts that you can find in LearnSQL.com.

If you found the suggested PostgreSQL exercises very easy, maybe you are ready to start learning advanced PostgreSQL. Keep in mind that any effort you put into learning PostgreSQL will have been well invested, as this dialect of the SQL language is growing rapidly and will open the door to many interesting job opportunities. Keep practicing and learning, and you will keep your SQL skills fresh.