Hands-on Practical: Filtering and Sorting Queries

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	email	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.

Exercise 1: Simple Filtering with WHERE

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

Exercise 2: Filtering with Multiple Conditions (AND)

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

Exercise 3: Filtering with IN

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

Exercise 4: Filtering with Pattern Matching (LIKE)

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

Exercise 5: Handling NULL Values

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	email
3	Clara	Rossi	[email protected]
6	Frank	Dubois	[email protected]

Exercise 6: Simple Sorting with ORDER BY

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

Exercise 7: Sorting in Descending Order

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

Exercise 8: Sorting by Multiple Columns

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

Exercise 9: Combining Filtering and Sorting

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.