20 SQL WHERE Practice Exercises for Beginners

In SQL, the WHERE clause filters data based on defined conditions. Read on to follow 20 WHERE practice exercises with beginner-friendly explanations and solutions.

This article will review some of our SQL WHERE exercises. We’ll start by explaining the basic syntax, then we’ll examine 20 beginner-friendly exercises that come from the SQL courses listed below. These exercises cover the main uses of the WHERE clause and are perfect for practicing your SQL skills. You’ll find more in these courses:

• SQL Practice: University
• SQL Practice: Store
• SQL Practice: Blog

Each course has separate sections for different SQL topics and uses a different database to let you practice SQL. Practice makes perfect and it is the best way to upgrade your SQL skills to a new level.

Without further ado, let’s get started.

SQL WHERE Clause Essentials

The SQL WHERE clause defines conditions for filtering data. In other words, it enables the retrieval of only the data that meets specific criteria. By including the WHERE clause in the SELECT, UPDATE, DELETE, or other SQL statement, the output dataset can be narrowed down to include only the relevant data.

Filtering data is essential for efficient data management and analysis. It enables extraction of only the necessary information from large datasets, reducing the volume of processed data and improving query performance.

The WHERE clause utilizes various tools to compare and filter data; these include comparison operators, pattern matching to compare values against specific patterns, and logical operators to combine multiple conditions.

In the SELECT statement, the WHERE clause comes after the SELECT and FROM clauses:

SELECT id, name, age
FROM persons
WHERE age > 18;

Quick review: The SELECT clause lists the columns to be presented in the output, and the FROM clause defines the table from which data is queried. The WHERE clause specifies the filter condition. Here, we select only the persons whose age is over 18. You can learn more about how to write the WHERE clause in SQL here.

The following sections  contain SQL WHERE practice exercises that cover the following applications:

• Filtering numerical values.
• Filtering text values.
• Filtering Boolean values.
• Filtering NULL values.
• Combining multiple conditions.

To get access to the practice environment, sample databases, and even more WHERE practice, I encourage you to sign up for the full course(s).

SQL WHERE Practice with Numerical Values

The exercises in this section come from the SQL Practice: University course. Here is the diagram of the university database used in the following exercises:

This is too short to be its own H2 heading. I recommend adding it to the WHERE Clause Essentials H2 if you don't want it to be part of the Numerical Exercises H2 heading.

Exercise 1: Display Information for a Specific Lecturer

Question: Display all data for the lecturer whose ID is equal to 8.

Solution:

SELECT *
FROM lecturer
WHERE id = 8;

Explanation: Since we want to display all the data for this lecturer, we select all columns from the lecturer table. This is accomplished with SELECT * FROM lecturer.

We are interested in data for the lecturer with an ID of 8. Therefore, we specify it as a condition in the WHERE clause: id = 8.

Exercise 2: Select Students Scoring 95 or Better

Question: Display the student ID, final grade, and letter grade for each course enrollment where the student’s final grade was 95 or higher.

Solution:

SELECT
  student_id,
  final_grade,
  course_letter_grade
FROM course_enrollment
WHERE final_grade >= 95;

Explanation: Here we want to display the student ID, final grade, and letter grade for each course enrollment. So we select only these columns from the course_enrollment table.

We want to output only those data rows where the final grade is equal to or greater than 95. To accomplish this, we define a condition in the WHERE clause as final_grade >= 95.

Exercise 3: Select All Students Except One

Question: Display all data for students with an ID different from 1.

Solution:

SELECT *
FROM student
WHERE id != 1;

Explanation: Again we want to select all data from the student table, so we use the asterisk (*) sign that stands for all available columns.

As we want to display data for all students except the one with the ID of 1, we define a condition in the WHERE clause that ID does not equal 1: id != 1.

Exercise 4: List All Courses Worth Less Than 3 ECTS

Question: Show all courses with less than 3 ECTS points. Display two columns: title and ects_points.

Solution:

SELECT
  title,
  ects_points
FROM course
WHERE ects_points < 3;

Explanation: We select course titles and course value in ECTS points from the course table. To display only courses worth less than 3 ECTS points, we define the WHERE clause condition as ects_points < 3.

SQL WHERE Exercises with Text Values

Exercises in this section come from the SQL Practice: Store course. Here is the diagram of the store database:

Exercise 5: Find a Product Category Description

Question: Find the description for the category with the name 'Alcohol'.

Solution:

SELECT description
FROM category
WHERE name = 'Alcohol';

Explanation: We want to display a description of a product category, so we select the description column from the category table. As we are interested in the description of the alcohol category, we define the condition in the WHERE clause as name = 'Alcohol'.

Note that string literals (‘Alcohol’) must be enclosed in single quotes (‘) in SQL.

Exercise 6: List All Customers Whose Email Matches the Pattern

Question: Display all data for customers whose emails end with ‘.com’.

Solution:

SELECT *
FROM customer
WHERE contact_email LIKE '%.com';

Explanation: Here we select all customers from the customer table.

We want to list only the customers whose email address ends with ‘.com’. To accomplish this, we are going to use pattern matching. In the WHERE clause, we define a condition as contact_email LIKE '%.com'. In SQL pattern matching, the % sign stands for any sequence of characters.

The LIKE operator is used to compare its left operand (usually the column value) with its right operand (the pattern to be matched). If the value matches the pattern, the condition passes; otherwise, it fails.

You can learn more about how to use LIKE in SQL here.

Exercise 7: Find Employees Whose Surname Matches the Pattern

Question: You don't remember the employee's last name exactly. Was it Mixer, Fixer, Bixer, or something else? Display data for all employees whose last name starts with one letter and is followed by ‘ixer’.

Solution:

SELECT *
FROM employee
WHERE last_name LIKE '_ixer';

Explanation: We select all columns from the employee table.

As we want to find employees whose surname ends with ‘ixer’, we again use pattern matching. In the WHERE clause, we define the condition as last_name LIKE '_ixer'. The _ sign stands for one character.

Exercise 8: List All Products Where the Name Contains a Dash

Question: Show all data for products where the name contains a dash (-).

Solution:

SELECT *
FROM product
WHERE product_name LIKE '%-%';

Explanation: We select all columns from the product table. In the WHERE clause, we define the condition as product_name LIKE '%-%'. The % sign stands for any sequence or number of characters.

WHERE Practice Exercises with Boolean Values

Exercises in this section come from the SQL Practice: University course. Here is the diagram of the university database:

Exercise 9: List All Courses That End with a Final Exam

Question: Display all data for courses that have final exams (column has_exam). Note that the has_exam column stores Boolean values (true or false).

Solution:

SELECT *
FROM course
WHERE has_exam IS TRUE;

Explanation: We select all columns from the course table.

As we want to list only the courses that end with a final exam, we define the WHERE condition as has_exam IS TRUE.

Exercise 10: List All Courses That Don’t Have a Final Project

Question: Show all the data for courses that don't have a final project (column has_project). Note that the has_project column stores Boolean values (true or false).

Solution:

SELECT *
FROM course
WHERE has_project IS FALSE;

Explanation: Again we select all columns from the course table. This time, we want to list all courses that do not have a final project. We define the WHERE clause condition as has_project IS FALSE.

Exercise 11: Select All Students Who Passed Their Finals

Question: Display the IDs of all students who've passed their final exam. Include the passed column in your results. Note that the passed column stores Boolean values (true or false).

Solution:

SELECT
  student_id,
  passed
FROM course_enrollment
WHERE passed IS TRUE;

Explanation: Here we want to display the column that contains the student IDs and the column that contains information on whether the student passed their finals. We select these columns from the course_enrollment table.

In the WHERE clause, we define the condition passed IS TRUE to filter only those students who passed their finals.

Exercise 12: Select Students That Failed Their Finals

Question: Display the IDs and final grades of all students who failed their final exam. Remember, the passed column stores Boolean values (true or false).

Solution:

SELECT
  student_id,
  final_grade
FROM course_enrollment
WHERE passed IS FALSE;

Explanation: Here we want to display the columns that contain the student IDs and the final grade. We select these columns from the course_enrollment table. In the WHERE clause, we define the condition passed IS FALSE to filter only those students who failed their finals.

WHERE Practice with NULL values

Exercises in this section come from the SQL Practice: Store course. Here is the  diagram of the store database:

Exercise 13: Select Categories Without Descriptions

Question: Display all categories that have no description.

Solution:

SELECT *
FROM category
WHERE description IS NULL;

Explanation: We select all columns from the category table, which stores product categories.

As we want to list only the categories that do not have any description, we must provide the WHERE condition description IS NULL; NULL means there is no value in that column.

You may wonder why we use IS NULL instead of = NULL. It is because NULL stands for no value; we cannot check whether the value is equal to NULL because there is no value for us to compare. IS NULL lets us check whether a value is (or is not) there.

Exercise 14: Find All Customers with a Company

Question: Display all data for customers whose company name isn't NULL.

Solution:

SELECT *
FROM customer
WHERE company_name IS NOT NULL;

Explanation: We select all columns from the customer table. As we want to list all customers that are associated with a company, we define the WHERE clause condition as company_name IS NOT NULL. This is pretty self-explanatory, as it’s what we’d say in plain English.

Exercise 15: Find Purchases with an Unknown Address

Question: Display the purchase ID, customer ID, and employee ID for purchases with an unknown shipping address.

Solution:

SELECT
  purchase_id,
  customer_id,
  employee_id
FROM purchase
WHERE ship_address IS NULL;

Explanation: We want to display the purchase ID, customer ID, and employee ID, so we select these columns from the purchase table.

Next, we want to list only the purchases with an unknown shipping address, which is equivalent to the value being NULL. So the WHERE clause condition is ship_address IS NULL.

Exercise 16: List All Parent Categories

Question: Display the names of all parent categories, which are categories that do not have any parent category.

Solution:

SELECT name
FROM category
WHERE parent_category_id IS NULL;

Explanation: We want to display the category names, so we select the name column from the category table.

To select only the parent categories, we define the WHERE condition as parent_category_id IS NULL. This follows the instruction that parent categories are the ones with no assigned parent category.

Using WHERE with Multiple Filtering Conditions

Exercises in this section come from the SQL Practice: Blog course. Here is the database diagram:

Exercise 17: Select All Articles from March 2020

Question: Display the URLs and the titles of all articles (the url and title columns) whose publication date is in March 2020.

Solution:

SELECT
  url,
  title
FROM article
WHERE publication_date >= '2020-03-01' 
AND publication_date < '2020-04-01';

Explanation: We want to display URLs and titles of the articles, so we select the appropriate columns from the article table.

As we are interested only in the articles from March 2020, we defined the WHERE clause condition to ensure that every selected article was published on or after March 1, 2020^t and before April 1, 2020. We use the AND logical operator to join these conditions: publication_date >= '2020-03-01' AND publication_date < '2020-04-01'.

Dates are usually stored in special data types like DATE, DATETIME, or TIMESTAMP. To compare a date column with a specific date value, we use standard comparison operators like =, <, >, <=, >=, or BETWEEN. The date format in SQL is typically YYYY-MM-DD, which stands for year, month, and day. Date part values are separated by hyphens.

Note that you have to enclose date comparison literals in single quotes (publication_date >= '2020-03-01').

Exercise 18: Find June Traffic to a Given Article

Question: Find traffic information for article '/blog/puppy-training-techniques/' in June 2020. Display the visit_date and the number of views on that day.

Solution:

SELECT 
  visit_date,
  views
FROM article_traffic
WHERE url = '/blog/puppy-training-techniques/'
AND visit_date >= '2020-06-01' 
AND visit_date < '2020-07-01';

Explanation: We select the date of the visit and the number of views per day from the article_traffic table.

We want to display traffic to a specific article. Therefore, we filter this article using its URL: url = '/blog/puppy-training-techniques/'. Note the single quotes around the comparison value.

As we are interested in June 2020 traffic, we impose two more WHERE conditions on the visit_date column. These will return only traffic information from June: visit_date >= '2020-06-01' AND visit_date < '2020-07-01'.

This time, the WHERE clause comprises three conditions – all combined using AND logical operators.

Exercise 19: List Articles For an Author and Category

Question: Return the URL of all articles by Lewis Goodwin  (author ID = 16) from either category 3 ('Dog Health') or category 6 ('Dog Gift Ideas').

Solution:

SELECT url
FROM article
WHERE author_id = 16
AND (article_category_id = 3 OR article_category_id = 6);

Explanation: We want to display the URL of specific articles from the article table.

First, we want to list articles written by the author with an ID of 16. This is the first WHERE clause condition: author_id = 16.

We also want to list only articles of either category 3 or category 6. We need to create a composite condition with the OR logical operator and enclose it in parenthesis: (article_category_id = 3 OR article_category_id = 6).

Finally, we combine these two conditions with the AND logical operator.

Exercise 20: Show Specific Article Traffic Data

Question: Return all article traffic data on Dec. 1, 2020 where the number of views is between 50 and 100, inclusive (i.e. 50 <= views <= 100).

Solution:

SELECT *
FROM article_traffic
WHERE views <= 100
AND views >= 50
AND visit_date = '2020-12-01';

Explanation: We select all columns from the article_traffic table.

We want to see traffic data for articles with between 50 and 100 views, so we define the combined WHERE clause condition as views <= 100 AND views >= 50.

As we are interested in data from December 1, 2020, we define another WHERE clause condition: visit_date = '2020-12-01'.

Query On with SQL WHERE Practice!

This article presented all the basics of the SQL WHERE clause. The practice exercises covered filtering numerical, text, Boolean, and NULL values, and combining multiple conditions for more complex filtering options. You can find more articles with SQL practice exercises here.

• SQL Practice: University
• SQL Practice: Store
• SQL Practice: Blog

All these courses are available in our SQL Practice track, where you’ll find even more SQL practice. For the ultimate practice option, our All Forever SQL Package gives you lifetime access to all our current and future courses with a single purchase.

Practice makes perfect! Happy SQL journey!