Subqueries, common table expressions, and window functions like RANK and ROW_NUMBER, each explained with a short example query.
35 cards · basic cards · AI-written, checked twice. Edit anything.
- What is a subquery in SQL?
- A query nested inside another query, used to return data that feeds into the outer query's WHERE, FROM, or SELECT clause.
- Which clause can a subquery NOT directly appear in?
- A subquery cannot directly appear in a GROUP BY clause.
- What is a correlated subquery?
- A subquery that references columns from the outer query, re-executing once per outer row.
- What does IN do when used with a subquery?
- Checks if a value matches any result returned by the subquery.
- What does EXISTS do in a subquery?
- Returns true if the subquery returns at least one row, false otherwise.
- What is the difference between IN and EXISTS?
- IN checks if a value is in a list; EXISTS only checks if a row exists (typically faster for large datasets).
- What do ALL and ANY do with subqueries?
- ALL checks if a condition holds for every row; ANY checks if it holds for at least one row.
- What is a scalar subquery?
- A subquery that returns exactly one row and one column, usable as a single value in the SELECT list or WHERE clause.
- What is a Common Table Expression (CTE)?
- A temporary named result set defined with a WITH clause, used to simplify complex queries or enable recursion.
- How do you define a CTE?
- Use WITH followed by a name, AS, and a SELECT statement in parentheses, then reference the name in the main query.
- What is a recursive CTE?
- A CTE that calls itself, typically containing an anchor (base case) and a recursive member joined by UNION ALL.
- What is a window function in SQL?
- A function that performs a calculation on a set of rows related to the current row, without collapsing results into a single row.
- What does ROW_NUMBER() do?
- Assigns a unique sequential number to each row within a partition, starting at 1.
- What does RANK() do?
- Assigns a rank to rows with ties receiving the same rank, then skipping the next rank number.
- What does DENSE_RANK() do?
- Assigns a rank to rows with ties receiving the same rank, but does not skip rank numbers.