Subqueries within the WHERE clause in SQL empower analytics engineers to perform sophisticated data filtering based on dynamically computed values. By embedding queries within WHERE conditions, you can easily compare individual records against aggregated or summarized data, identifying outliers, benchmarks, or specific conditions efficiently.
In this tutorial, “SQL Exercises: Subqueries In WHERE,” you’ll learn how to use subqueries to perform advanced comparisons and filtering tasks—such as finding above-average sales, identifying unique customer behaviors, and isolating records based on aggregated thresholds. Mastering WHERE-clause subqueries significantly enhances your ability to derive precise insights, streamline your data analysis workflows, and make data-driven decisions effectively.
🚀 Jump Right to Exercise Tasks: SQL Exercises – Subqueries In WHERE
Filtering with Subqueries for Above-Average Values
Imagine you’re managing a sales database. You want to quickly identify orders that surpass the overall average sales amount. A subquery in the WHERE clause efficiently filters these records based on dynamically calculated averages.
Practical Example
SELECT order_id, sale_amount
FROM sales
WHERE sale_amount > (SELECT AVG(sale_amount) FROM sales);Example Solution Explained:
This query retrieves orders whose sales amounts exceed the average of all sales, immediately highlighting above-average performances.
Example Output:
order_id | sale_amount
---------|------------
101 | 1200.00
104 | 1100.50Key Takeaways:
- Easily identify records exceeding average metrics.
- Subqueries in WHERE facilitate dynamic filtering.
Identifying Records with Threshold Conditions
Suppose you need to identify customers who have placed unusually large orders. Using a WHERE subquery, you can quickly pinpoint customers whose order amounts cross a certain threshold, such as orders exceeding $1000.
Practical Example
SELECT customer_name, email
FROM customers
WHERE customer_id IN (
SELECT customer_id FROM orders WHERE total_amount > 1000
);Example Solution Explained:
This query identifies customers who have made high-value purchases, enabling targeted outreach and analysis of high-value customer segments.
Example Output:
customer_name | email
--------------|------------------
Emily Smith | emily@domain.com
James Brown | james@domain.comKey Takeaways:
- Use subqueries to filter records by specific criteria.
- Ideal for isolating important customer behaviors.
Filtering Based on Comparative Conditions
Imagine you’re examining product performance within each category. You want to find products priced lower than their category’s average price, quickly identifying bargains or potential pricing issues.
Practical Example
SELECT product_name, unit_price
FROM products p
WHERE unit_price < (
SELECT AVG(unit_price) FROM products WHERE category_id = p.category_id
);Example Solution Explained:
This query selects products priced below their category average, identifying opportunities for promotions or price adjustments.
Example Output:
product_name | unit_price
-------------|-----------
Desk Lamp | 15.00
Basic Chair | 25.00Key Takeaways:
- Compare records against category averages using subqueries.
- Valuable for precise comparative analysis.
What You’ll Gain from Completing This Exercise
Mastering subqueries in the WHERE clause enhances your analytical capabilities, enabling sophisticated, context-aware filtering and rapid identification of insights.
Earn XP, Unlock Rewards, and Track Progress!
Log in to earn XP, unlock exciting rewards, and automatically track your progress as you enhance your SQL and analytics skills!
Schema Information
| Column Name | Data Type | Description |
|---|---|---|
| customer_id | INTEGER | Unique identifier for each customer |
| first_name | TEXT | Customer's first name |
| last_name | TEXT | Customer's last name |
| TEXT | Customer's email address | |
| city | TEXT | City of residence |
| join_date | DATE | Date the customer joined |
| phone | TEXT | Customer's phone number |
| Column Name | Data Type | Description |
|---|---|---|
| city_id | INTEGER | Unique identifier for each city |
| city_name | TEXT | Name of the city |
| state | TEXT | State where the city is located |
| Column Name | Data Type | Description |
|---|---|---|
| order_id | INTEGER | Unique identifier for each order |
| customer_id | INTEGER | ID of the customer who placed the order |
| employee_id | INTEGER | ID of the employee who handled the order |
| order_date | DATE | Date when the order was placed |
| status | TEXT | Order status (e.g., Shipped, Cancelled, Pending) |
| total_amount | REAL | Total amount for the order |
| Column Name | Data Type | Description |
|---|---|---|
| order_id | INTEGER | Associated order identifier |
| product_id | INTEGER | Identifier for the product |
| quantity | INTEGER | Quantity of the product in the order |
| unit_price | REAL | Price per unit of the product |
| Column Name | Data Type | Description |
|---|---|---|
| product_id | INTEGER | Unique identifier for each product |
| product_name | TEXT | Name of the product |
| category_id | INTEGER | Category to which the product belongs |
| unit_price | REAL | Price per unit of the product |
| stock_quantity | INTEGER | Quantity of items available in stock |
| creation_date | DATE | Date the product was added to the system |
| Column Name | Data Type | Description |
|---|---|---|
| supplier_id | INTEGER | Unique identifier for each supplier |
| supplier_name | TEXT | Name of the supplier |
| city | TEXT | City where the supplier is located |
| products_supplied | TEXT | List of product IDs supplied (e.g., [90,91,33,121]) |
| Column Name | Data Type | Description |
|---|---|---|
| movement_id | INTEGER | Unique identifier for each movement record |
| product_id | INTEGER | Product being moved |
| change_quantity | INTEGER | Change in quantity (+/-) |
| movement_date | DATE | Date of the inventory movement |
| reason | TEXT | Explanation (Sale, Restock, Adjustment, etc.) |
| Column Name | Data Type | Description |
|---|---|---|
| employee_id | INTEGER | Unique identifier for each employee |
| first_name | TEXT | Employee's first name |
| last_name | TEXT | Employee's last name |
| department_id | INTEGER | Identifier of the department |
| hire_date | DATE | Date the employee was hired |
| salary | INTEGER | Employee's salary |
| Column Name | Data Type | Description |
|---|---|---|
| department_id | INTEGER | Unique identifier for each department |
| department_name | TEXT | Name of the department |
| location | TEXT | Location of the department |
| Column Name | Data Type | Description |
|---|---|---|
| shipping_id | INTEGER | Unique identifier for each shipping record |
| order_id | INTEGER | Order to which the shipping address belongs |
| address | TEXT | Street address for shipping |
| city | TEXT | Shipping city |
| state | TEXT | Shipping state |
| postal_code | INTEGER | Shipping postal/ZIP code |
| Column Name | Data Type | Description |
|---|---|---|
| category_id | INTEGER | Unique identifier for each category |
| category_name | TEXT | Name of the category |
