Hackforge Academy

Explanation

A subquery is a query written inside another SQL query. It is used to fetch intermediate data that is used by the main query.

Code:

SELECT * FROM employees WHERE 
salary > (SELECT AVG(salary) 
FROM employees);

Explanation

Subqueries can be used inside WHERE clauses to filter records based on another query result.

Code:

SELECT name FROM students WHERE 
marks > (SELECT AVG(marks) 
FROM students);

Explanation

A subquery can return a single value, multiple values, or even an entire table depending on usage.

Code:

SELECT * FROM products WHERE 
category_id IN 
(SELECT id FROM categories WHERE 
status = 'active');

Explanation

Subqueries are commonly used for comparisons, filtering, existence checks, and nested calculations.

Code:

SELECT * FROM orders WHERE 
EXISTS (SELECT 1 FROM customers 
WHERE customers.id =
 orders.customer_id);

Explanation

A correlated subquery depends on the outer query and executes once for each row of the outer query.

Code:

SELECT e1.name FROM employees e1 WHERE 
salary > (SELECT AVG(salary)
 FROM employees e2 
WHERE e1.department_id = e2.department_id);