5. Advanced SQL Flashcards
(11 cards)
GROUP BY:
Groups rows based on shared values in specified columns. Aggregates apply to each group.
Aggregate Functions:
(e.g., SUM, AVG, COUNT, MIN, MAX) compute a single value over a set of rows or a group. Using an aggregate without GROUP BY applies it to the entire result set (treated as one group).
HAVING:
Filters groups based on a condition, typically involving aggregates. Applied after GROUP BY; cannot use WHERE to filter groups.
Order of Evaluation (approximate):
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.
What are Subqueries?
A SELECT statement nested within another SQL statement. Can be used in SELECT, FROM, WHERE, or HAVING clauses. Often evaluated first. Many joins can be rewritten as subqueries, but not vice versa.
What are singleton Subqueries?
Return at most one row and one column. Used with comparison operators (=, <, >, etc.) in WHERE or HAVING.
What are correlated Subqueries?
The inner query’s WHERE clause refers to a table in the outer query. Executed once for each row of the outer query.
EXISTS:
Boolean operator taking a subquery; returns true if subquery returns any rows. Useful with correlated subqueries.
SOME/ALL:
Used with comparison operators and non-singleton subqueries (must return single column).
Common Table Expressions (CTEs) (WITH):
Reusable named subqueries defined using the WITH clause within a single SELECT statement. Improve readability and can simplify complex queries.
Window Functions (Analytic Functions):
Apply aggregate-like computations over a subset (“window”) of rows related to the current row. They do not reduce the number of rows in the result. Use the OVER clause. PARTITION BY divides rows into partitions (similar to GROUP BY but output includes all rows). ORDER BY sorts rows within each partition.