Advanced SQL Topics

Advanced SQL Topics — Explore advanced SQL concepts: window functions, recursive queries, pivoting, and query optimization. The guide walks through Window Functions Deep Dive, Pivoting Data, Query Performance Tuning, Full-Text Search. Window functions use OVER() to define a frame of rows for calculation. PARTITION BY divides the result set into groups. ORDER BY within the window controls row ordering. ROWS/RANGE BETWEEN defines the frame boundaries (preceding/following rows). Window functions are evaluated after JOIN, WHERE, GROUP BY, and HAVING. Pivot transforms rows into columns. Use CASE with aggregation for manual pivoting: SELECT department, SUM(CASE WHEN year=2024 THEN amount END) AS y2024 ... GROUP BY department. SQL Server has PIVOT operator. Oracle has PIVOT/UNPIVOT. PostgreSQL uses crosstab from the tablefunc extension. The guide is organized into 4 sections that build on each other, each pairing a prose explanation with real SQL you can run as-is. Related guides: SQL Introduction, SELECT Statement, WHERE Clause & Operators, SQL JOINs, GROUP BY & Aggregation.