Chapter 13 · 8 levels

Creating and changing tables

Build your own tables: CREATE TABLE with types, INSERT, CREATE TABLE AS SELECT, UPDATE, DELETE, ALTER TABLE, and an analyst workflow from request to validated answer.

Start

Analysts spend most of their time reading data, but sooner or later you build tables too: a staging copy to clean before a report, a summary table a dashboard reads every morning, an import table for a supplier’s price list. That needs the other half of SQL: CREATE TABLE, INSERT, UPDATE, DELETE and ALTER TABLE.

Here every change is safe. Each time you press Run, your code runs inside a transaction that is rolled back afterwards, so the store tables are back to normal on the next run and your own tables disappear. That is why every query in this chapter creates what it needs first. The habits still matter for real databases: work on a copy, run the WHERE as a SELECT before you change anything, and check the result with a query. The chapter ends with the full analyst workflow, from a colleague’s question to a validated, published table.

Levels

  1. Set up a supplier import table

    from Tyler Morgan, Junior Data Engineer

    create table, insert, data types, primary key, null

    Code

  2. Save a summary as a table

    from Brian Mitchell, Finance Analyst

    create table as, staging table, ctas, aggregation

    Code

  3. Plan a price rise with UPDATE

    from Jessica Turner, Merchandising Manager

    update, where, staging table, create table as

    Code

  4. Remove bad lines with DELETE

    from Kevin Brooks, Operations Manager

    delete, truncate, drop table, staging table

    Code

  5. Add columns with ALTER TABLE

    from Megan Collins, Head of Sales

    alter table, add column, default, update from

    Code

  6. Empty it, clean it or drop it?

    from Tyler Morgan, Junior Data Engineer

    delete, truncate, drop table, data types

    Quiz

  7. From question to checked answer

    from Rachel Adams, Customer Support Lead

    analyst workflow, cte, grain, validation, return rate

    Code

  8. Boss: import, clean and publish

    from Jessica Turner, Merchandising Manager

    create table, insert select, update, delete, create table as, validation, analyst workflow

    Boss · 3 steps

Cookie settings

Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.