18 SQL Questions for Beginners: Theory and Practice

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 !=, >, =,
  • Boolean data types: =, <> or !=

  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:

  • What Is a NULL in SQL?
  • How to Use Comparison Operators with NULLs in SQL
  • How to Find Records with NULL in a Column
  • How to Filter Rows Without NULL in a Column

  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.

  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.

  

  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.

  

  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 .

  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.

  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:

  • 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:

  • Basic SQL Practice: A Store
  • Basic SQL Practice: University
  • Basic SQL Practice: Blog & Traffic Data
  • Basic SQL Practice: Run Track Through Queries!

  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!