12  SQL Essentials

12.1 Select and filter records

SELECT
    city,
    year,
    value
FROM analysis.indicators
WHERE year >= 2024
ORDER BY value DESC;

12.2 Aggregate data

SELECT
    year,
    COUNT(*) AS records,
    AVG(value) AS average_value
FROM analysis.indicators
GROUP BY year
ORDER BY year;

12.3 Create a category

SELECT
    city,
    value,
    CASE
        WHEN value >= 80 THEN 'high'
        WHEN value >= 50 THEN 'medium'
        ELSE 'low'
    END AS category
FROM analysis.indicators;

12.4 Things to remember

• Single quotes represent text values.
• Double quotes preserve case-sensitive identifiers in PostgreSQL.
• Use aliases that describe the result.
• Test destructive statements inside a transaction.