Subqueries & CTEs (66–78)
-
What is a subquery?
A query inside another query.
-
Types of subqueries?
Single-row, multi-row, correlated.
-
What is correlated subquery?
A subquery that depends on outer query.
-
What is CTE?
Common Table Expression is a temporary result set.
-
Why use CTE?
Improves readability and simplifies complex queries.
-
Difference between CTE and subquery?
CTE is reusable in same query; subquery is not.
-
Can CTE be recursive?
Yes.
-
Find 2nd highest salary using subquery.
SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp);
-
Find employees earning above average salary.
Use subquery with AVG(salary).
-
What is EXISTS?
Checks if subquery returns rows.
-
What is NOT EXISTS?
Checks if subquery returns no rows.
-
Difference between IN and EXISTS?
IN compares values; EXISTS checks row existence.
-
Which performs better: IN or EXISTS?
EXISTS is better for large datasets.
