Query, model, and reason about relational data.
3 topics
Retrieve data from tables. SELECT columns, filter with WHERE (=, !=, <, >, BETWEEN, IN, LIKE, IS NULL), sort with ORDER BY (ASC/DESC), limit results with LIMIT/OFFSET. Combine conditions with AND/OR/NOT. Use aliases for readability.
2 resources
Combine rows from multiple tables. INNER JOIN (matching rows in both), LEFT JOIN (all from left + matching right), RIGHT JOIN, FULL OUTER JOIN (all from both), CROSS JOIN (cartesian product). Join on foreign keys. Multiple joins in one query. Self-joins for hierarchical data.
Summarize data: COUNT, SUM, AVG, MIN, MAX with GROUP BY. Filter groups with HAVING (vs WHERE which filters rows). Multiple grouping columns. NULL handling in aggregates. DISTINCT for unique values. Combine with JOINs for powerful analytics.
2 topics
Subqueries: nested SELECT in WHERE, FROM, or SELECT clauses. CTEs (WITH clause): named temporary result sets for readable complex queries. Recursive CTEs for hierarchical data (org charts, category trees). CTEs can reference each other. Prefer CTEs over deeply nested subqueries.
Perform calculations across related rows without collapsing results. ROW_NUMBER (sequential numbering), RANK/DENSE_RANK (ranking with ties), LAG/LEAD (access previous/next rows), SUM/AVG OVER (running totals, moving averages), FIRST_VALUE/LAST_VALUE, NTILE (divide into buckets). PARTITION BY defines groups, ORDER BY defines sequence within partitions.