These exercises provide hands-on experience applying SQL for data filtering and sorting. You will use clauses like WHERE and ORDER BY to answer specific data questions, developing confidence in these essential data manipulation tools.
We'll work with a set of example tables representing customers, products, and their orders. Imagine you have access to a database containing these tables. For reference, here's a simplified view of their structure and some sample data we'll use for our queries.
Customers Table:
| customer_id | first_name | last_name | city | country | phone | |
|---|---|---|---|---|---|---|
| 1 | Anna | Svensson | Stockholm | Sweden | [email protected] | 555-1234 |
| 2 | Ben | Carter | London | UK | [email protected] | 555-5678 |
| 3 | Clara | Rossi | Rome | Italy | [email protected] | NULL |
| 4 | David | Miller | New York | USA | [email protected] | 555-8765 |
| 5 | Eva | Fischer | Berlin | Germany | [email protected] | 555-4321 |
| 6 | Frank | Dubois | Paris | France | [email protected] | NULL |
| 7 | Grace | Kim | London | UK | [email protected] | 555-1122 |
Products Table:
| product_id | product_name | category | price |
|---|---|---|---|
| 101 | Laptop | Electronics | 1200 |
| 102 | Keyboard | Electronics | 75 |
| 103 | Mouse | Electronics | 25 |
| 104 | Web Development Book | Books | 45 |
| 105 | SQL Cookbook | Books | 55 |
| 106 | Coffee Mug | Home | 15 |
| 107 | Desk Lamp | Home | 35 |
Orders Table:
| order_id | customer_id | product_id | order_date | quantity | total_amount |
|---|---|---|---|---|---|
| 501 | 2 | 102 | 2023-10-26 | 1 | 75 |
| 502 | 4 | 104 | 2023-10-27 | 2 | 90 |
| 503 | 1 | 106 | 2023-10-27 | 1 | 15 |
| 504 | 2 | 103 | 2023-10-28 | 1 | 25 |
| 505 | 5 | 101 | 2023-10-28 | 1 | 1200 |
| 506 | 3 | 107 | 2023-10-29 | 1 | 35 |
| 507 | 7 | 105 | 2023-10-29 | 1 | 55 |
Try running these queries in your own SQL environment if you have one set up. If not, follow along and study the logic.
Goal: Find all customers who live in London.
Concept: Use the WHERE clause with the equality operator (=) to filter based on the city column.
SELECT
customer_id,
first_name,
last_name,
city
FROM Customers
WHERE
city = 'London';
Explanation: This query selects specific columns from the Customers table. The WHERE city = 'London' part filters the rows, returning only those where the value in the city column is exactly 'London'. Note that text values like 'London' need to be enclosed in single quotes.
Expected Result:
| customer_id | first_name | last_name | city |
|---|---|---|---|
| 2 | Ben | Carter | London |
| 7 | Grace | Kim | London |
Goal: Find all products in the 'Electronics' category that cost more than $50.
Concept: Combine two conditions using the AND operator in the WHERE clause. Both conditions must be true for a row to be included.
SELECT
product_id,
product_name,
category,
price
FROM Products
WHERE
category = 'Electronics' AND price > 50;
Explanation: The WHERE clause filters for rows where the category is 'Electronics' and the price is greater than 50.
Expected Result:
| product_id | product_name | category | price |
|---|---|---|---|
| 101 | Laptop | Electronics | 1200 |
| 102 | Keyboard | Electronics | 75 |
Goal: Find all orders for products with product_id 102, 104, or 106.
Concept: Use the IN operator to efficiently check if a column's value matches any value in a provided list.
SELECT
order_id,
product_id,
order_date,
total_amount
FROM Orders
WHERE
product_id IN (102, 104, 106);
Explanation: The WHERE product_id IN (102, 104, 106) clause selects rows where the product_id is either 102, 104, or 106. This is often more concise than using multiple OR conditions (e.g., WHERE product_id = 102 OR product_id = 104 OR product_id = 106).
Expected Result:
| order_id | product_id | order_date | total_amount |
|---|---|---|---|
| 501 | 102 | 2023-10-26 | 75 |
| 502 | 104 | 2023-10-27 | 90 |
| 503 | 106 | 2023-10-27 | 15 |
Goal: Find all customers whose first name starts with the letter 'A'.
Concept: Use the LIKE operator with the wildcard character % to match patterns in text data. % matches any sequence of zero or more characters.
SELECT
customer_id,
first_name,
last_name
FROM Customers
WHERE
first_name LIKE 'A%';
Explanation: WHERE first_name LIKE 'A%' filters for rows where the first_name starts with 'A'. The % allows any characters to follow the initial 'A'.
Expected Result:
| customer_id | first_name | last_name |
|---|---|---|
| 1 | Anna | Svensson |
Goal: Find all customers who do not have a phone number listed (i.e., the phone column is NULL).
Concept: Use the IS NULL condition to specifically check for NULL values. Remember, you cannot use = NULL.
SELECT
customer_id,
first_name,
last_name,
email
FROM Customers
WHERE
phone IS NULL;
Explanation: The WHERE phone IS NULL clause correctly identifies rows where the phone column contains a NULL value.
Expected Result:
| customer_id | first_name | last_name | |
|---|---|---|---|
| 3 | Clara | Rossi | [email protected] |
| 6 | Frank | Dubois | [email protected] |
Goal: List all products sorted by price, from the lowest price to the highest.
Concept: Use the ORDER BY clause. By default, ORDER BY sorts in ascending order (ASC).
SELECT
product_id,
product_name,
price
FROM Products
ORDER BY
price; -- ASC is implied
Explanation: The ORDER BY price clause sorts the entire result set based on the values in the price column. Since ASC (ascending) is the default, the lowest priced items appear first.
Expected Result:
| product_id | product_name | price |
|---|---|---|
| 106 | Coffee Mug | 15 |
| 103 | Mouse | 25 |
| 107 | Desk Lamp | 35 |
| 104 | Web Development Book | 45 |
| 105 | SQL Cookbook | 55 |
| 102 | Keyboard | 75 |
| 101 | Laptop | 1200 |
Goal: List all orders sorted by the order date, showing the most recent orders first.
Concept: Use the ORDER BY clause with the DESC keyword for descending order.
SELECT
order_id,
customer_id,
order_date,
total_amount
FROM Orders
ORDER BY
order_date DESC;
Explanation: ORDER BY order_date DESC sorts the results based on the order_date column, with the latest dates appearing first.
Expected Result:
| order_id | customer_id | order_date | total_amount |
|---|---|---|---|
| 506 | 3 | 2023-10-29 | 35 |
| 507 | 7 | 2023-10-29 | 55 |
| 504 | 2 | 2023-10-28 | 25 |
| 505 | 5 | 2023-10-28 | 1200 |
| 502 | 4 | 2023-10-27 | 90 |
| 503 | 1 | 2023-10-27 | 15 |
| 501 | 2 | 2023-10-26 | 75 |
Goal: List all customers, sorted first by their country (alphabetically) and then by their last name (alphabetically) within each country.
Concept: Specify multiple columns in the ORDER BY clause, separated by commas. The sorting happens sequentially based on the order of columns listed.
SELECT
customer_id,
first_name,
last_name,
country,
city
FROM Customers
ORDER BY
country ASC, -- Sort by country first (A-Z)
last_name ASC; -- Then sort by last name within each country (A-Z)
Explanation: The database first sorts all rows based on the country. Then, for rows that have the same country value (like 'UK'), it applies the second sorting rule based on last_name. ASC is optional but included here for clarity.
Expected Result:
| customer_id | first_name | last_name | country | city |
|---|---|---|---|---|
| 6 | Frank | Dubois | France | Paris |
| 5 | Eva | Fischer | Germany | Berlin |
| 3 | Clara | Rossi | Italy | Rome |
| 1 | Anna | Svensson | Sweden | Stockholm |
| 2 | Ben | Carter | UK | London |
| 7 | Grace | Kim | UK | London |
| 4 | David | Miller | USA | New York |
Goal: Find all orders with a total_amount greater than $50, and list them from the smallest amount to the largest.
Concept: Use both WHERE and ORDER BY clauses in the same query. The WHERE clause is applied first to filter the rows, and then the ORDER BY clause sorts the filtered results.
SELECT
order_id,
order_date,
total_amount
FROM Orders
WHERE
total_amount > 50
ORDER BY
total_amount ASC; -- or just 'total_amount'
Explanation: First, the query filters the Orders table to include only rows where total_amount is greater than 50. Then, these filtered results are sorted by total_amount in ascending order.
Expected Result:
| order_id | order_date | total_amount |
|---|---|---|
| 507 | 2023-10-29 | 55 |
| 501 | 2023-10-26 | 75 |
| 502 | 2023-10-27 | 90 |
| 505 | 2023-10-28 | 1200 |
These exercises cover the core functionalities of filtering data with various WHERE conditions and sorting the output using ORDER BY. By working through them, you should gain a much better practical understanding of how to refine your SQL queries to get exactly the information you need, presented in the order you want. As you proceed, remember that the WHERE clause filters before sorting occurs with ORDER BY.
Was this section helpful?
© 2026 ApX Machine LearningAI Ethics & Transparency•