Advanced SQL Cheat Sheet

1. Common Table Expressions (CTEs)

CTEs allow you to create temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

WITH RegionalSales AS (
    SELECT Region, SUM(Sales) AS TotalSales
    FROM Orders
    GROUP BY Region
)
SELECT * FROM RegionalSales WHERE TotalSales > 100000;

2. Window Functions

Perform calculations across a set of table rows that are somehow related to the current row.

SELECT EmployeeName, Department, Salary,
       RANK() OVER(PARTITION BY Department ORDER BY Salary DESC) as DeptRank
FROM Employees;

3. Advanced Joins (Self Join & Cross Join)

-- Self Join Example (Finding employees and their managers)
SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.ID;

-- Cross Join Example (All possible combinations)
SELECT Colors.ColorName, Sizes.SizeName
FROM Colors CROSS JOIN Sizes;

4. JSON Data Extraction

Modern SQL databases support extracting values from JSON columns.

SELECT id, data->>'$.customer.name' AS CustomerName
FROM Orders
WHERE JSON_EXTRACT(data, '$.status') = 'shipped';

5. Indexing Best Practices

• Composite Indexes: Order columns by cardinality (most unique first).
• Covering Indexes: Include all columns retrieved by the query to avoid table lookups.
• Avoid SELECT *: Only fetch columns you need to utilize indexes effectively.