Capstone: Data Transformation

Raw data is rarely ready to report. Roles are inconsistent — 'Engineer' and 'Senior Engineer' mixed together — budgets are stored in the wrong unit, and some rows hold placeholders that need real values before the stakeholder sees them. Transformation is the step between reading the data and presenting it: you clean, reshape, and enrich the rows so the report is accurate and readable.

This lesson covers the three tools that do most transformation work in SQL. UPDATE changes values in existing rows. CASE expresses conditional logic inside a query — categorising, flagging, or re-mapping values. INSERT ... SELECT copies query results into a new table, which is how you build summary tables and staging datasets. Together they turn the schema you designed into the exact shape the report requires.

Every transformation is a mutation, and mutations are risky because they change the source. In this course each exercise runs on a fresh copy of the database, so a mistake is harmless. In production there is no rewind, so we also teach the habit of previewing every change before you commit it.

flowchart LR
  R["Raw values"] --> C["CASE expression"]
  C --> K["Clean categories"]
  style C fill:#0e7490,color:#fff
  style K fill:#0e7490,color:#fff

CASE maps raw values into clean, consistent categories before they reach the report.

Conditional transformation with CASE

CASE is SQL's way of asking 'what if?' inside a query. It evaluates conditions in order and returns the result for the first one that matches. You can use it in a SELECT to create a computed column, in an UPDATE to reclassify rows, or in an ORDER BY to impose a custom sort order.

The structure has two forms. The searched form, which you will use most often, checks arbitrary conditions:

CASE
    WHEN salary >= 900000 THEN 'Lead'
    WHEN salary >= 700000 THEN 'Senior'
    ELSE 'Mid'
END

Order matters. SQLite evaluates the conditions top to bottom, so the most specific check must come first. If you test >= 700000 before >= 900000, every lead will be misclassified as Senior, because the first match wins and the rest are skipped. This ordering trap bites almost everyone once, and the only defence is to list your thresholds from highest to lowest.

Reclassify every employee by salary tier using UPDATE with a CASE expression.

UPDATE employees
SET role = CASE
    WHEN salary >= 900000 THEN 'Lead'
    WHEN salary >= 700000 THEN 'Senior'
    ELSE role
END;

SELECT name, salary, role FROM employees ORDER BY salary DESC;

Reshaping with INSERT ... SELECT

Sometimes the report needs a table that does not exist yet — a summary of headcount and average salary by department, or a list of high-budget projects with their owners. Rather than building that table by hand, row by row, you can derive it directly from a query.

INSERT INTO destination (cols) SELECT ... FROM source reads the source, applies whatever filters, joins, and aggregates you need, and writes the result into the destination table in one statement. It is the SQL equivalent of copy-paste, except the paste is computed fresh every time.

An even more direct variant is CREATE TABLE ... AS SELECT, which builds the destination table and populates it in a single pass. The new table inherits the column names and types from the query result, so you do not need to declare the schema separately. This is ideal for staging tables — intermediate datasets that exist only to feed the next step of the report.

flowchart TD
  S["source table"] --> T["SELECT filters & computes"]
  T --> I["INSERT INTO destination"]
  I --> D["new table populated"]
  style T fill:#0e7490,color:#fff
  style D fill:#0e7490,color:#fff

INSERT ... SELECT transforms and copies data from a source query into a destination table.

Create a summary table directly from a query, then inspect the result.

CREATE TABLE dept_summary AS
SELECT d.name, COUNT(e.id) AS headcount, AVG(e.salary) AS avg_salary
FROM departments d
JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

SELECT * FROM dept_summary ORDER BY avg_salary DESC;

Derived tables for intermediate results

A derived table is a subquery in the FROM clause, wrapped in parentheses and given an alias. It lets you compute an intermediate result — categorising every row, filtering a subset, or pre-aggregating — and then query that result as if it were a real table.

Derived tables shine when a report needs two stages of logic that cannot be expressed in one pass. First you classify every employee by salary tier in a derived table; then you group by that tier and count how many people landed in each bucket. Without the derived table, you would have to repeat the CASE expression in both SELECT and GROUP BY, which is tedious and error-prone.

The alias after the closing parenthesis is required — FROM (...) AS staged — because every table in a FROM clause must have a name. Choose aliases that describe the stage: categorized, filtered, or summarised.

flowchart LR
  S["source rows"] --> D["derived table<br/>(subquery)"]
  D --> O["outer query<br/>aggregates"]
  O --> R["final result"]
  style D fill:#0e7490,color:#fff
  style O fill:#0e7490,color:#fff

A derived table stages an intermediate result; the outer query aggregates on top of it.

Use a derived table to categorise employees, then aggregate by tier.

SELECT tier, COUNT(*) AS n, AVG(salary) AS avg_salary
FROM (
    SELECT name, salary,
        CASE
            WHEN salary > 800000 THEN 'high'
            WHEN salary > 600000 THEN 'mid'
            ELSE 'low'
        END AS tier
    FROM employees
) AS categorized
GROUP BY tier;

Enrichment: adding computed value

Transformation is not only about fixing what is broken. It is also about adding value that was implicit in the raw data. A salary becomes a tier. A hire date becomes a tenure band. A project budget becomes a cost-per-employee ratio when divided by headcount.

These computed columns are not stored in the source tables — they are produced on the fly by the query, or materialised into a staging table for performance. The skill is knowing which enrichment belongs in SQL and which belongs in the reporting tool. If the enrichment is reusable across multiple report sections, build it into a derived table or a staging table. If it is specific to one chart, compute it in the query that feeds that chart.

Exercise

Increase the budget of every active project by 10 percent. Use integer arithmetic: budget = budget + budget / 10.

UPDATE projects
-- set budget and filter by status
;

UPDATE projects SET budget = budget + budget / 10 WHERE status = 'active';

Exercise

Update every employee's role based on salary: if salary >= 900000 set to 'Lead', if salary >= 700000 set to 'Senior', otherwise keep the current role. Use a CASE expression.

UPDATE employees
SET role = CASE
    -- conditions here
END;

UPDATE employees SET role = CASE WHEN salary >= 900000 THEN 'Lead' WHEN salary >= 700000 THEN 'Senior' ELSE role END;

Exercise

This query tries to categorise projects by budget, but the CASE conditions are in the wrong order and the labels are mismatched. Fix it so > 1000000 is 'large', > 500000 is 'medium', and the rest is 'small'.

SELECT name,
    CASE
        WHEN budget > 1000000 THEN 'small'
        WHEN budget > 500000 THEN 'medium'
        ELSE 'large'
    END AS size
FROM projects;

SELECT name, CASE WHEN budget > 1000000 THEN 'large' WHEN budget > 500000 THEN 'medium' ELSE 'small' END AS size FROM projects;

Exercise

Create a summary table called high_earners containing the name and salary of every employee whose salary is greater than 700000. Use CREATE TABLE ... AS SELECT.

-- create high_earners from a SELECT on employees

CREATE TABLE high_earners AS SELECT name, salary FROM employees WHERE salary > 700000;

Exercise

Return each department's name and the number of employees in it who earn more than the company-wide average salary, as above_avg_count. Use a subquery to compute the average.

SELECT d.name, COUNT(e.id) AS above_avg_count
FROM departments d
JOIN employees e ON d.id = e.department_id
WHERE e.salary > /* compute company average here */
GROUP BY d.name;

SELECT d.name, COUNT(e.id) AS above_avg_count FROM departments d JOIN employees e ON d.id = e.department_id WHERE e.salary > (SELECT AVG(salary) FROM employees) GROUP BY d.name;

Recap

UPDATE changes existing rows; CASE adds conditional logic inside SELECT or SET.
Order matters in CASE: list conditions from most specific to least specific.
CREATE TABLE ... AS SELECT and INSERT ... SELECT build staging tables directly from query results.
A derived table in the FROM clause lets you stage intermediate results for multi-step logic.
Always write the WHERE first and preview with SELECT before running an UPDATE or DELETE.

You can now clean, reshape, and enrich data so it is ready to report. In the next lesson you will iterate on the report itself — testing accuracy, catching edge cases, and refining queries based on what the data reveals.

Capstone: Data Transformation

Conditional transformation with CASE

Reshaping with INSERT ... SELECT

Derived tables for intermediate results

Enrichment: adding computed value

Recap

Checkpoint quiz

Go deeper — technical resources