18th Jan 2024 21 minutes read

18 SQL Questions for Beginners: Theory and Practice

Whether you're starting or refreshing your SQL skills, join us as we work through these 18 SQL practice questions for beginners.

SQL, or Structured Query Language, is a programming language used to define, retrieve, and manipulate data in relational databases. It provides an intuitive syntax of SQL statements and keywords that create, modify, and query relational databases.

This article focuses on reviewing and practicing the basics of SQL. We’ll start by reviewing the SELECT statement and its required and optional components for fetching data from a single table. Following that, we’ll delve into JOINs, which allow us to merge data from two or more tables. Finally, we’ll demonstrate how to aggregate and group data to perform more advanced analysis. This can help you review your SQL knowledge before an interview or a test – or simply refresh and consolidate your skills.

This article showcases SQL practice exercises from our interactive SQL Practice Set course. The course offers over 80 hands-on practice exercises that cover different SQL topics: single table queries, joins, aggregation and grouping, subqueries, and more. If you want to practice more on your own, we encourage you to check out our SQL Practice track.

All our SQL practice courses provide exercises based on real-world datasets, so you can practice SQL in realistic scenarios. The courses are grouped into different topics – e.g. single table queries, joins, aggregation and grouping, and subqueries – so you can choose what you want to practice.

Let’s get started.

SQL Practice for Beginners

The SQL practice exercises in this article cover the basics of querying data. We’ll review:

Single table queries – Querying data from a single table using the SELECT statement.
JOINs – Joining data from multiple tables using various JOINs.
Aggregating and grouping data – Putting data into groups based on defined columns and compiling statistics.

Single Table Queries

We’ll start by reviewing the basics of querying data from a single table and imposing custom conditions on data columns.

Question 1: Elements of an SQL Query

Question:

List all elements in an SQL query.

Answer:

The SELECT statement consists of the following components:

SELECT column_name(s) – Defines the data columns shown in the output.
FROM table_name – Defines the database table from which data is selected.
WHERE column_name = value – Filters the output data based on stated conditions (optional).
GROUP BY column_name(s) – Groups data based on distinct values (optional).If you’re using aggregate functions, you must use the GROUP BY clause.
HAVING – Filters data after it has been processed by GROUP BY (optional); you can use this to impose conditions on aggregate functions.
ORDER BY column_name [ASC | DESC] – Orders output data by a defined column in ascending or descending order (optional).

Both the SELECT and FROM clauses are easy to grasp, as SELECT lists data columns and FROM defines the data table. In the case of the WHERE clause, there are a variety of conditions you can impose on columns, which we’ll review in the next question.

You can read more about the basic query elements in our article Enumerate and Explain All the Basic Elements of an SQL Query.

Takeaways:

These are the elements of an SQL query in order of appearance: SELECT, FROM, WHERE, GROUP BY, ORDER BY, and HAVING.

Question 2: Filtering Data in an SQL Query

Question:

How do you filter data in an SQL query using custom conditions?

Answer:

To impose custom conditions on data columns, we use the WHERE clause. For example, if you want to select people older than 18, use the WHERE clause as follows:

SELECT name, age
FROM person
WHERE age > 18;

The WHERE clause conditions typically involve comparisons or logical operations and depend on the data type stored in the column.

Comparison operators used to compare values:
- Numeric data types: =, <> or !=, >, <, >=, <=
- Text/string data types: =, <> or !=, LIKE, IN, NOT LIKE, NOT IN
- Date and time data types: =, <> or !=, >, <, >=, <=, BETWEEN, NOT BETWEEN
- Boolean data types: =, <> or !=
Operators used to check for NULL values: IS NULL, IS NOT NULL
Logical operators used to combine multiple conditions: AND, OR, NOT

You can read more about filtering data in our articles How to Write a WHERE Clause in SQL and Using AND, OR, and NOT Operators in SQL.

Takeaways:

The WHERE clause is used to filter data by imposing conditions on data columns.

Data for Questions 3 – 6

In the exercises 3 – 6, we’ll use the cat table. It has the following columns:

id – The id of a given cat.
name – The cat’s name.
breed – The cat’s breed (e.g. Siamese, British shorthair, etc.).
coloration – The cat’s coloration (e.g. calico, tabby, etc.).
age – The cat’s age.
sex – The cat’s sex.
fav_toy – The cat’s favorite toy.

Question 3: Select Cats of a Given Age and Breed

Question:

Select the ID and name for every Ragdoll cat that is either 1) younger than five years old, or 2) older than ten years old.

Answer:

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

Explanation:

As the instruction says, we select the id and name columns from the cat table.

Next, we use the WHERE clause to impose conditions:

On the age column:

We want to select cats that are younger than 5 (age < 5) or older than 10 (age > 10), so we use the OR keyword and enclose both conditions in parentheses.

Why do we need parenthesis? Well, we want to impose this composite condition on the age column. What if we do not include parenthesis? The parenthesis will be implicitly imposed on the last two conditions, like this: age < 5 OR (age > 10 AND breed = 'Ragdoll'). This will cause an incorrect result.

On the breed column:

We want to select cats of the Ragdoll breed; therefore, we simply define the condition as breed = 'Ragdoll'. Note that text values in SQL are enclosed in single quotes (').

This exercise demonstrates a composite condition that uses logical operators (AND, OR) and mathematical comparison operators (<, >, =).

Question 4: List Cats Whose Favorite Toy Is a Ball

Question:

Select all data for cats whose breed starts with 'R', whose favorite toy starts with 'ball', and whose coloration ends with an 'm'.

Answer:

SELECT *
FROM cat
WHERE breed LIKE 'R%'
  AND fav_toy LIKE 'ball%'
  AND coloration LIKE '%m';

Explanation:

Here, we select all data columns (*) from the cat table.

We want to impose conditions on the literal values of the breed, colorations, and fav_toy columns. To do that, we’ll use pattern matching; in SQL, % is a wildcard character that stands for any sequence of characters.

The breed column value should start with an 'R'. Therefore, we use a pattern that indicates a value starting with 'R' and followed by any number of characters (defined by %). If we want to impose such a condition on a literal value, we must use the LIKE keyword: breed LIKE 'R%'.

Similarly, we want the favorite toy name to start with 'ball'; therefore, the condition is fav_toy LIKE 'ball%'.

And it’s the same again for the coloration column. We want the literal value to end with an 'm', so the % character goes in front: coloration LIKE '%m'.

You can read more about using the LIKE operator in our articles What Do the Operators LIKE and NOT LIKE Do? and How to Use LIKE in SQL.

Question 5: Find the Most Bored Cat

Question:

Select the names of all male cats that don't have a favorite toy – that is, the value of the field fav_toy is NULL.

Answer:

SELECT name
FROM cat
WHERE sex = 'M'
  AND fav_toy IS NULL;

Explanation:

As the instruction says, we select the name column from the cat table.

We want to select only male cats; therefore, we define a condition on the sex column as sex = 'M'. You need to be familiar with the data stored in the cat table to define this condition – i.e. to know that the sex column stores the value ‘F’ for female cats and ‘M’ for male cats.

As we’re looking for the most bored cat, we need to define a condition that says the fav_toy column must have no value, or be NULL. We do this with fav_toy IS NULL.

Working with NULLs is quite complicated in SQL. For more details, we recommend the articles:

Question 6: Select Cats that Love Teaser Toys

Question:

Select the ID, name, breed, and coloration of all cats that:

Are females.
Like teaser toys,
Are not of the Persian or Siamese breeds.

Answer:

SELECT 
  id,
  name,
  breed,
  coloration
FROM cat
WHERE sex = 'F'
  AND fav_toy = 'teaser'
  AND (breed NOT LIKE 'Persian' AND breed NOT LIKE 'Siamese');

Explanation:

In this exercise, we select the id, name, breed, and coloration columns from the cat table. Then we impose the following conditions:

On the sex column:
We want to select female cats; hence, the condition is sex = 'F'.
On the fav_toy column:
We want to find cats that like teaser toys, so the condition is fav_toy = 'teaser'.
On the breed column:
We want to select any breed except for Persian and Siamese. To do that, we use the NOT LIKE keyword and enclose the entire composite condition in parenthesis (breed NOT LIKE 'Persian' AND breed NOT LIKE 'Siamese').

Great work! You’ve completed the section on selecting data from a single table with various filter conditions. Let’s move on to working with multiple tables.

Data from Multiple Tables: SQL JOINs

Now you know how to select data from a single table. But what if we want to select data from two or more tables? We need to join these tables based on common column values. This is where JOIN operations come into play.

Question 7: The Role of JOIN

Question:

What does JOIN do in SQL?

Answer:

The JOIN clause is used to combine data from two or more tables.

You can use as many JOINs as you need. Below, we use two JOINs to combine data from three tables:

SELECT t1.column, t2.column, t3.column
FROM table1 AS t1
JOIN table2 AS t2 ON t1.column = t2.column
JOIN table3 AS t3 ON t1.column = t3.column
...

When joining tables, it’s best to use alias names for each table (here, t1, t2, and t3). These alias names are used to refer to columns from each table.

To find out more about SQL JOINs, see our articles SQL INNER JOIN Explained in Simple Words and How to Join Two Tables in SQL.

Takeaway:

JOINs are used to combine data from multiple tables.

Question 8: Types of JOINs

Question:

List all types of JOINs available in SQL and briefly describe each one.

Answer:

There are four types of JOINs: [INNER] JOIN, RIGHT JOIN, LEFT JOIN, and FULL [OUTER] JOIN. Each one provides different results.

A JOIN, also known as an INNER JOIN, is the most common type of join. It returns only the matching records from two or more tables.

A LEFT JOIN returns all the records from the left (first) table and the matching records from the right (second) table. If there are no matches in the right table, null values are included in the result set.

Read What Is a LEFT JOIN in SQL? for more details.

A RIGHT JOIN returns all the records from the right (second) table and the matching records from the left (first) table. If there are no matches in the left table, null values are included in the result set.

A FULL JOIN, also known as a FULL OUTER JOIN, returns all the records from both the left and right tables. It includes matching records from both tables and uses null values for non-matching records.

Read this article to learn more about FULL JOINs.

In summary, LEFT JOIN and RIGHT JOIN focus on one table as the primary source of data, while a FULL JOIN combines all the records from both tables. The choice of which JOIN to use depends on the specific data retrieval needs and the relationship between the tables involved.

To read more about different JOIN types, we recommend our articles SQL JOINs and SQL JOIN Types Explained. Our SQL JOIN Cheat Sheet summarizes the syntax of different types of JOINs.

Takeaways:

JOIN types include [INNER] JOIN, LEFT JOIN, RIGHT JOIN, and FULL [OUTER] JOIN.

Data for Questions 9 – 12

In exercises 9 – 12, we’ll use the Museum dataset that consists of three tables.

The artists table contains the following columns:

id – The database ID for a given artist.
name – The artist’s name.
birth_year – The year the artist was born.
death_year – The year the artist
artistic_field – That artist’s primary field (e.g. watercolor painting, sculpture, oil painting).

The museum table contains the following columns:

id – The ID of a given museum.
name – The museum’s name.
country – The country where the museum is located.

The piece_of_art table contains the following columns:

id – The ID of a given piece of art.
name – The piece’s name.
artist_id – The ID of the artist who created this piece.
museum_id – The ID of the museum that has this piece in its collection.

Question 9: Find Artists born after 1800 and the Art They Created

Question:

For each artist who was born after the year 1800 and lived for more than 50 years, show their name and the name of the pieces of art they created. Rename the columns as artist_name and piece_name, respectively.

Answer:

SELECT
  a.name AS artist_name,
  poa.name AS piece_name
FROM artist a
JOIN piece_of_art poa
  ON a.id = poa.artist_id
WHERE death_year - birth_year > 50
  AND birth_year > 1800;

Explanation:

We select artist names (aliased as artist_name) along with pieces of art they created (aliased as piece_name). Therefore, we must join the artist table (aliased as a) with the piece_of_art table (aliased as poa) on their common column that stores artist IDs (ON a.id = poa.artist_id).

We want to consider only artists who lived for more than 50 years. To define this condition, we’ll use the birth_year and death_year columns from the artist table as follows:

death_year - birth_year > 50

Also, we want to list artists born after 1800: birth_year > 1800.

Check out this article about joining two tables in SQL to learn more.

Question 10: Select All Pieces of Art and Their Location

Question:

Select the names of all pieces of art together with the names of the museums that house them and the countries in which these museums are located. Also show lost pieces of art (those without an associated museum).

Answer:

SELECT
  poa.name,
  m.name,
  m.country
FROM piece_of_art poa
LEFT JOIN museum m
  ON poa.museum_id = m.id;

Explanation:

As we want to select the names of art pieces and the names and countries of museums, we must join the piece_of_art table (aliased as poa) with the museum table (aliased as m) on the museum ID column (ON poa.museum_id = m.id).

We need to show all pieces of art, including the ones that are lost. Note that the lost pieces of art do not have any museum assigned. Therefore, we require a specific type of JOIN that selects all data from the piece_of_art table, regardless of whether it has any matching records in the museum table:

FROM piece_of_art poa LEFT JOIN museum m

This LEFT JOIN ensures that we select all rows from the left table (here, piece_of_art).

Check out this article on LEFT JOIN to learn more.

Question 11: List All Pieces of Art

Question:

Show the names of all pieces of art together with the names of their creators and the names of the museums that house these pieces of art. Omit lost works and pieces of art with an unknown artist. Name the columns piece_of_art_name, artist_name, and museum_name.

Answer:

SELECT
  a.name AS artist_name,
  m.name AS museum_name,
  poa.name AS piece_of_art_name
FROM museum m
JOIN piece_of_art poa
  ON m.id = poa.museum_id
JOIN artist a
  ON a.id = poa.artist_id;

Explanation:

Here we select names of artists from the artist table, names of museums from the museum table, and names of art pieces from the piece_of_art table. Hence, we must join all three tables on their common columns:

We join the museum table with the piece_of_art table on museum ID values.
We join the artist table with the piece_of_art table on artist ID values.

Once we’ve joined all three tables, we can select the output values.

Note that we want to omit art pieces that do not have any museum or any artist assigned. Therefore, we use the standard JOIN (or INNER JOIN) that joins data from tables only when there is a match in the column on which the JOIN is performed.

Follow this article on how to join 3 or more tables to learn more.

Question 12: List pieces of art created by unknown artists

Question:

Check whether any pieces were created by unknown artists. Show the names of these pieces together with the names of the museums that house them.

Answer:

SELECT
  poa.name,
  m.name
FROM piece_of_art poa
JOIN museum m
  ON poa.museum_id = m.id
WHERE poa.artist_id IS NULL;

Explanation:

We want to show the names of ‘unknown artist’ pieces along with the names of museums where the pieces are located. Hence, we join the piece_of_art table (aliased as poa) with the museum table (aliased as m) on the museum ID column (ON poa.museum_id = m.id).

As we’re looking for art pieces created by unknown artists, we include the following condition in the WHERE clause: poa.artist_id IS NULL.

Grouping and Aggregating Data

Aggregation and grouping are techniques used to organize data into groups based on defined criteria and perform calculations on the groups.

Question 13: Aggregate Functions and the Role of GROUP BY

Question:

List the available aggregate functions and explain the role of the GROUP BY clause.

Answer:

Aggregation involves applying mathematical operations to a set of values in a column. The more commonly used aggregate functions include SUM(), AVG(), COUNT(), MAX(), and MIN().

For example, imagine a table that stores monthly sales values:

year	month	sales
2022	11	5
2022	12	4
2023	1	3
2023	2	6
2023	3	6
2023	4	4
2023	5	5

You can use the SUM() aggregate function to get the total sales, like this:

SELECT SUM(sales) AS total_sales
FROM sales_table;

The output is as follows:

total_sales
33

When we’re aggregating data, we also often segment data into groups based on distinct values in the column that is used to group data.

Grouping involves creating groups of data based on values in column(s) given as arguments to the GROUP BY clause.

For example, imagine you want to select sales per year. To do this, you have to group data by the year, like this:

SELECT year, SUM(sales) AS year_sales
FROM sales_table
GROUP BY year;

The output is as follows:

year	year_sales
2022	9
2023	24

If the column on which we group data has five distinct values, data will be grouped into five groups.

We recommend this article if you want to learn more about the GROUP BY clause.

Takeaways:

Aggregation is about performing calculations on a set of values and grouping is about organizing data into groups based on specific criteria.

Question 14: WHERE vs. HAVING

Question:

What is the difference between WHERE and HAVING?

Answer:

Both WHERE and HAVING are used to filter data by imposing certain conditions.

The difference is that WHERE is used to impose conditions on data columns (as you’ve seen in the Single Table Queries section) and HAVING is used to impose conditions on aggregate functions (as you’ll see in this section).

Read this article on WHERE vs. HAVING to learn more about the differences between these two clauses.

Takeaways:

WHERE imposes conditions on columns. HAVING imposes conditions on aggregate functions.

Data for Questions 15 – 18

In exercises 15 – 18, we’ll use the games table. It consists of the following columns:

id – The ID of a given game.
title – The game’s name (e.g. ‘Super Mario Bros’).
company – The name of the company that makes this game (e.g. ‘Nintendo’).
type – The type of game (e.g. ‘arcade’).
production_year – The year when the game was created.
system – The system for which a game was released (e.g. ‘NES’).
production_cost – The cost of producing the game.
revenue – The revenue generated by the game.
rating – The rating given to this game.

Question 15: Calculate the Average Production Cost of Good Games

Question:

Show the average production cost of games that were produced between 2010 and 2015 and were rated higher than 7.

Answer:

SELECT 
  AVG(production_cost)
FROM games
WHERE production_year BETWEEN 2010 AND 2015
  AND rating > 7;

Explanation:

To select the average production cost of games, we use the AVG() aggregate function on the production_cost column. This function takes all values present in the production_cost column and calculates the average.

As we are interested in games produced between 2010 and 2015, we must include this condition in the WHERE clause: production_year BETWEEN 2010 AND 2015. That sounds just like plain English!

Also, we want to include only games with a rating higher than 7, so we add another condition in the WHERE clause: AND rating > 7.

Check out this article on the AVG() function to see more examples.

Question 16: Provide Game Production Statistics by Year

Question:

For each year:

Display the year (production_year).
Count the number of games released in this year (name this count).
Show the average cost of production (as avg_cost) for these games.
Show the average revenue (as avg_revenue) of these games.

Answer:

SELECT
  production_year,
  COUNT(*) AS count,
  AVG(production_cost) AS avg_cost,
  AVG(revenue) AS avg_revenue
FROM games
GROUP BY production_year;

Explanation:

We want to display different statistics per year; therefore, we need to GROUP BY production_year.

As we select from the games table, we use the COUNT() aggregate function to count games released per year. We use * as an argument because we want to count all rows (not values of a specific column). We alias it AS count.

Next, we want to display the average cost of production: AVG(production_cost). We alias it AS avg_cost.

Finally, we show the average revenue: AVG(revenue). We alias it AS avg_revenue.

Question 17: Calculate the Gross Profit per Company

Question:

For all companies present in the games table, show their name and their gross profit over all years. To simplify this problem, assume that the gross profit is equal to revenue minus the production cost of all games; name this column gross_profit_sum. Order the results so the company with the highest gross profit is first.

Answer:

SELECT
  company,
  SUM(revenue - production_cost) AS gross_profit_sum
FROM games
GROUP BY 1
ORDER BY 2 DESC;

Explanation:

We select the company column from the games table. For each company, we sum the gross profit values (revenue - production_cost) produced by each game created by this company.

As we want to see the sum of gross profit per company, we must GROUP BY company. However, in this case, we use a different syntax: GROUP BY 1, which means that we want to GROUP BY the 1^st column listed in SELECT.

Finally, we order the output in descending order based on the gross profit values per company.

Question 18: Identify Good Games

Question:

We're interested in good games produced between 2000 and 2009. A good game has a rating higher than 6 and was profitable. For each company, show:

The company name.
The total revenue from good games produced between 2000 and 2009 (as the revenue_sum column).
The number of good games the company produced in this period (as the number_of_games column).

Important: Only show companies with good-game revenue over 4,000,000.

Answer:

SELECT
  company,
  COUNT(company) AS number_of_games,
  SUM(revenue) AS revenue_sum
FROM games
WHERE production_year BETWEEN 2000 AND 2009
  AND rating > 6
  AND revenue - production_cost > 0
GROUP BY company
HAVING SUM(revenue) > 4000000;

Explanation:

This one is a bit trickier, as we need to create a query that uses WHERE, HAVING, aggregate functions, and grouping.

Let’s analyze the instructions step by step and translate it into SQL code.

WHERE-related instructions:

games produced between 2000 and 2009 results in this condition being added to the WHERE clause:
WHERE production_year BETWEEN 2000 AND 2009
games rated higher than 6 results in this condition being added to the WHERE clause:
AND rating > 6
games that were profitable results in this condition being added to the WHERE clause:
AND revenue - production_cost > 0
Remember, a profitable game means that the revenue is higher than the cost of production.

SELECT-related instructions:

show company name results in this column being added to the SELECT statement:
SELECT company
show its total revenue (as revenue_sum) results in this column being added to the SELECT statement:
SUM(revenue) AS revenue_sum
show the number of good games (number_of_games) results in this column being added to the SELECT statement:
COUNT(company) AS number_of_games

GROUP BY- and HAVING-related instructions:

for each company means that we calculate the statistics (COUNT() and SUM()) on a company So, we must group the data by company:
GROUP BY company
show companies with good-game revenue over 4,000,000 result in this condition being added to the HAVING clause:
HAVING SUM(revenue) > 4000000

That’s how we dissected the instructions and translated them into SQL code.

Get More Basic SQL Practice

This article covered the basics of SQL queries, including how to filter data, join multiple tables, order and sort output, and aggregate and group data.

Have you enjoyed the SQL practice exercises so far? All these exercises come from our SQL Practice Set course. For more SQL exercises, check out these LearnSQL.com practice courses:

You can buy each of these courses individually, or you can purchase our All Forever SQL package. It covers all 70+ SQL courses offered on our platform, including these practice courses, and all new courses we’ll add in the future.

And remember, practice makes perfect. Good luck on your SQL journey!

Tags:

SQL Practice for Beginners

Single Table Queries

Question 1: Elements of an SQL Query

Question 2: Filtering Data in an SQL Query

Data for Questions 3 – 6

Question 3: Select Cats of a Given Age and Breed

Question 4: List Cats Whose Favorite Toy Is a Ball

Question 5: Find the Most Bored Cat

Question 6: Select Cats that Love Teaser Toys

Data from Multiple Tables: SQL JOINs

Question 7: The Role of JOIN

Question 8: Types of JOINs

Data for Questions 9 – 12

Question 9: Find Artists born after 1800 and the Art They Created

Question 10: Select All Pieces of Art and Their Location

Question 11: List All Pieces of Art

Question 12: List pieces of art created by unknown artists

Grouping and Aggregating Data

Question 13: Aggregate Functions and the Role of GROUP BY

Question 14: WHERE vs. HAVING

Data for Questions 15 – 18

Question 15: Calculate the Average Production Cost of Good Games

Question 16: Provide Game Production Statistics by Year

Question 17: Calculate the Gross Profit per Company

Question 18: Identify Good Games

Get More Basic SQL Practice

You may also like