DataBolt

All Lessons

Introduction to SQL

SQL Lesson 1: Your Sample Database

SQL Lesson 2: SELECT — Reading data

SQL Lesson 3: WHERE — Filtering rows

SQL Lesson 4: AND, OR, NOT

SQL Lesson 5: BETWEEN, IN, LIKE

SQL Lesson 6: NULL — The Mystery Value

SQL Lesson 7: ORDER BY

SQL Lesson 8: LIMIT & OFFSET

SQL Lesson 9: Aggregate Functions

SQL Lesson 10: GROUP BY

SQL Lesson 11: HAVING

SQL Lesson 12: INNER JOINs

SQL Lesson 13: LEFT JOINs

SQL Lesson 14: RIGHT JOINs

SQL Lesson 15: SELF JOINs

SQL Lesson 16: UNION JOINs

SQL Lesson 17: Joining Multiple Tables

SQL Lesson 18: Subqueries

SQL Lesson 19: CTEs (WITH)

SQL Lesson 20: CASE Statements

SQL Lesson 21: Window Functions

SQL Lesson 22: String Functions

SQL Lesson 23: Date & Time Functions

SQL Lesson 24: INSERT, UPDATE, DELETE

SQL Lesson 25: CREATE TABLE & DDL

SQL Lesson 26: Indexes & Performance

SQL Lesson 27: Transactions & ACID

SQL Lesson 28: SQL Execution Order

SQL Lesson 29: 50 Practice Problems

CHAPTER 19

Subqueries

Queries within queries — nesting for power.

IN THIS CHAPTER

Subquery in WHERE to filter on a computed value
Subquery with IN for list-based filtering
Subquery in FROM as a derived table
EXISTS — checking if a subquery returns any rows

🌟 Think of it this way: Subqueries are like asking two questions where the answer to the first feeds the second: 'What is the average salary?' → ₹70,125. 'Who earns more than ₹70,125?' Both steps happen in one SQL statement.

15.1 - Subquery in WHERE

Employees earning above the complany average

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

15.2 - Subquery in IN

Customers who ordered in January 2024

SELECT name FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders
  WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31'
);

15.3 - EXITS

EXISTS is faster than IN for large subqueries. It short-circuits as soon as one matching row is found:

SQL

SELECT c.name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

💡 Engineering Insight: EXISTS vs IN performance: EXISTS stops scanning as soon as it finds the first match. IN collects every matching ID first. On tables with millions of rows, EXISTS can be 10-100x faster. Use EXISTS when you only care 'does a matching row exist?' not 'how many?'

Included Tables in Exercise

Table: Orders

order_id	customer_id	order_date	total	status
1	1	2024-01-15	79999	completed
2	2	2024-01-20	3498	completed
3	1	2024-02-10	65000	completed
4	3	2024-02-14	499	pending
5	2	2024-03-01	248	completed
6	5	2024-03-15	2999	shipped
7	1	2024-04-05	149	completed

Table: Customers

customer_id	name	city	country
1	Ananya	Hyderabad	India
2	Rohan	Bangalore	India
3	Sam	Mumbai	India
4	Lisa	London	UK
5	Ravi	Delhi	India

Table: order_items

item_id	order_id	product_id	qty	unit_price
1	1	1	1	79999
2	2	3	1	2999
3	2	2	1	499
4	3	5	1	65000
5	4	2	1	499
6	5	4	2	99
7	5	6	1	50
8	6	3	1	2999

Table: products

product_id	name	category	price	stock
1	iPhone 15	Electronics	79999	50
2	SQL Book	Books	499	200
3	Headphones	Electronics	2999	75
4	Notebook	Stationery	99	500
5	Laptop	Electronics	65000	20
6	Pen Set	Stationery	149	0
7	Mouse	Electronics	599	25

Exercise 👇

Exercise:

Tasks

1.👉Get the name and price of products whose price is greater than the average price of all products.

2.Find customers who have not placed any orders.

3.Find products that have been ordered with quantity greater than 1.

4.Find customers who have orders using EXISTS.

5.Find customers whose total order amount is greater than 50000.

6.Find products whose price is greater than the average price of all products.

Stuck? Read this task's