Why subqueries are needed in SQL - See It in Action

Understanding Why Subqueries Are Needed in SQL

📖 Scenario: You are managing a small bookstore database. You want to find out which books have a price higher than the average price of all books in the store.

🎯 Goal: Build an SQL query using a subquery to find books priced above the average book price.

📋 What You'll Learn

Create a table called books with columns id, title, and price

Insert exactly three books with these details: (1, 'Book A', 10), (2, 'Book B', 15), (3, 'Book C', 20)

Write a subquery to calculate the average price of all books

Use the subquery in the WHERE clause to select books with price greater than the average price

💡 Why This Matters

🌍 Real World

Subqueries help answer questions like 'Which items are above average?' or 'Which customers spent more than the average?' in real business databases.

💼 Career

Knowing how to write subqueries is essential for data analysts and database developers to perform complex data filtering and reporting.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns id (integer), title (text), and price (integer). Then insert these three rows exactly: (1, 'Book A', 10), (2, 'Book B', 15), and (3, 'Book C', 20).

SQL

-- Create the books table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Calculate the average price with a subquery

Write a SQL subquery that calculates the average price of all books in the books table. Assign this subquery to a variable or prepare it to be used in the next step.

SQL

CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books (id, title, price) VALUES (1, 'Book A', 10);
INSERT INTO books (id, title, price) VALUES (2, 'Book B', 15);
INSERT INTO books (id, title, price) VALUES (3, 'Book C', 20);

-- Write a subquery to calculate average price
-- Your code here

Hint

Use SELECT AVG(price) FROM books to get the average price.

3

Use the subquery in a WHERE clause

Write a SQL query that selects id, title, and price from books where the price is greater than the average price. Use the subquery (SELECT AVG(price) FROM books) inside the WHERE clause.

SQL

CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books (id, title, price) VALUES (1, 'Book A', 10);
INSERT INTO books (id, title, price) VALUES (2, 'Book B', 15);
INSERT INTO books (id, title, price) VALUES (3, 'Book C', 20);

-- Select books with price greater than average price using a subquery
-- Your code here

Hint

Use the subquery inside parentheses in the WHERE clause to compare prices.

4

Complete the query to find books priced above average

Ensure the final SQL query selects id, title, and price from books where the price is greater than the average price calculated by the subquery (SELECT AVG(price) FROM books). This completes the use of subqueries to filter data.

SQL

CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books (id, title, price) VALUES (1, 'Book A', 10);
INSERT INTO books (id, title, price) VALUES (2, 'Book B', 15);
INSERT INTO books (id, title, price) VALUES (3, 'Book C', 20);

-- Finalize the query to find books priced above average
-- Your code here

Hint

Make sure the query uses the subquery correctly in the WHERE clause.

Practice

1. Why do we use subqueries in SQL?
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'NY');

easy

A. To use the result of one query inside another for filtering or comparison

B. To speed up the database server automatically

C. To create new tables from existing ones

D. To change the database schema

Solution

1. Step 1: Understand the role of subqueries

   The subquery inside the IN clause fetches department IDs located in 'NY'.

2. Step 2: See how the main query uses subquery results

   The main query selects employees whose department_id matches those IDs from the subquery.

3. Final Answer:

   To use the result of one query inside another for filtering or comparison -> Option A

4. Quick Check:

   Subqueries help filter data using other query results [OK]

Hint: Subqueries let queries talk to each other inside SQL [OK]

Common Mistakes:

• Thinking subqueries speed up queries automatically
• Confusing subqueries with table creation
• Believing subqueries change database structure

2. Which of the following is the correct syntax for a subquery in SQL?

easy

A. SELECT name FROM employees WHERE id IN SELECT manager_id FROM departments WHERE id = 5;

B. SELECT name FROM employees WHERE id == (SELECT manager_id FROM departments WHERE id = 5);

C. SELECT name FROM employees WHERE id = SELECT manager_id FROM departments WHERE id = 5;

D. SELECT name FROM employees WHERE id = (SELECT manager_id FROM departments WHERE id = 5);

Solution

1. Step 1: Check correct subquery syntax

   Subqueries must be enclosed in parentheses and use a single equals sign for comparison.

2. Step 2: Identify syntax errors in other options

   SELECT name FROM employees WHERE id == (SELECT manager_id FROM departments WHERE id = 5); uses '==' which is invalid in SQL; C misses parentheses; D misses parentheses around subquery.

3. Final Answer:

   SELECT name FROM employees WHERE id = (SELECT manager_id FROM departments WHERE id = 5); -> Option D

4. Quick Check:

   Subqueries need parentheses and single '=' [OK]

Hint: Subqueries always go inside parentheses with '=' or IN [OK]

Common Mistakes:

• Using '==' instead of '=' for comparison
• Forgetting parentheses around subqueries
• Using subqueries without proper syntax

3. What will be the output of this query?

SELECT name FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'Sales');

Assuming the departments table has one row with name 'Sales' and id 3, and employees table has:
id | name | department_id
1 | Alice | 3
2 | Bob | 2
3 | Carol | 3

medium

A. No rows returned

B. Bob only

C. Alice and Carol

D. Alice, Bob, and Carol

Solution

1. Step 1: Find department id for 'Sales'

   The subquery returns id = 3 for 'Sales' department.

2. Step 2: Select employees with department_id = 3

   Employees Alice and Carol have department_id 3, so they are selected.

3. Final Answer:

   Alice and Carol -> Option C

4. Quick Check:

   Subquery returns 3, employees with department_id 3 selected [OK]

Hint: Match subquery result with main query filter [OK]

Common Mistakes:

• Assuming subquery returns multiple rows causing error
• Selecting employees from wrong department
• Ignoring subquery result in main query

4. Identify the error in this SQL query:

SELECT name FROM employees WHERE department_id = SELECT id FROM departments WHERE location = 'LA';

medium

A. Missing parentheses around the subquery

B. Using '=' instead of 'IN' for multiple results

C. Wrong table name 'employees'

D. No error, query is correct

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses to be valid.

2. Step 2: Confirm other parts

   Using '=' is okay if subquery returns one value; table names are correct.

3. Final Answer:

   Missing parentheses around the subquery -> Option A

4. Quick Check:

   Subqueries need parentheses [OK]

Hint: Always put subqueries inside parentheses [OK]

Common Mistakes:

• Forgetting parentheses around subqueries
• Assuming '=' works for multiple rows
• Misreading table names as errors

5. You want to find all customers who placed orders with a total amount greater than the average order amount. Which query correctly uses a subquery to achieve this?

hard

A. SELECT customer_id FROM orders WHERE total_amount > AVG(total_amount);

B. SELECT customer_id FROM orders WHERE total_amount > (SELECT AVG(total_amount) FROM orders);

C. SELECT customer_id FROM orders WHERE total_amount IN (SELECT AVG(total_amount) FROM orders);

D. SELECT customer_id FROM orders WHERE total_amount = (SELECT total_amount FROM orders WHERE total_amount > AVG(total_amount));

Solution

1. Step 1: Understand the goal

   We want customers with orders greater than the average order amount.

2. Step 2: Check subquery usage

   SELECT customer_id FROM orders WHERE total_amount > (SELECT AVG(total_amount) FROM orders); correctly uses a subquery to calculate average and compares each order's total_amount to it.

3. Step 3: Identify errors in other options

   SELECT customer_id FROM orders WHERE total_amount > AVG(total_amount); misuses AVG without subquery; C uses IN incorrectly; D has wrong comparison logic.

4. Final Answer:

   SELECT customer_id FROM orders WHERE total_amount > (SELECT AVG(total_amount) FROM orders); -> Option B

5. Quick Check:

   Subquery calculates average, main query compares amounts [OK]

Hint: Use subquery to get average, compare in main query [OK]

Common Mistakes:

• Using aggregate functions without subqueries
• Misusing IN for single value comparisons
• Comparing with wrong operators or missing parentheses