DDL Create Alter Drop

Why this matters

As a Data Analyst, you often need tables you can trust and evolve. DDL (Data Definition Language) helps you:

• Create clean staging or sandbox tables for analysis.
• Alter table structure when requirements change (rename columns, add fields, add constraints).
• Drop temporary or obsolete tables to keep the workspace tidy and fast.

Concept explained simply

DDL is how you define and change database objects such as tables. The three essentials:

• CREATE: make a new table (its name, columns, types, and constraints).
• ALTER: change an existing table (add/rename/drop columns, modify data types, add constraints).
• DROP: remove a table entirely.

Mental model

• Think of CREATE as drafting a blueprint and building the frame.
• ALTER is remodeling: add a new room (column), widen a door (increase column length), put rules on usage (constraints).
• DROP is demolition: remove the building when it’s no longer useful.

Core syntax you will use often

-- Create only if the table does not exist
CREATE TABLE IF NOT EXISTS analytics_sandbox.products (
  product_id     INT PRIMARY KEY,
  product_name   VARCHAR(100) NOT NULL,
  category       VARCHAR(50),
  price          DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT price_nonneg CHECK (price >= 0)
);

-- Add a column with a default
ALTER TABLE analytics_sandbox.products
  ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

-- Rename a column
-- PostgreSQL: 
ALTER TABLE analytics_sandbox.products RENAME COLUMN product_name TO name;

-- Change data type (widen size)
ALTER TABLE analytics_sandbox.products
  ALTER COLUMN category TYPE VARCHAR(80);

-- Add a UNIQUE constraint
ALTER TABLE analytics_sandbox.products
  ADD CONSTRAINT uq_products_name UNIQUE (name);

-- Safely remove a table if it exists
DROP TABLE IF EXISTS analytics_sandbox.products_backup;

-- If dependencies exist, some systems support CASCADE
-- Use carefully: it can drop related objects
-- DROP TABLE IF EXISTS analytics_sandbox.products CASCADE;

Worked examples

CREATE TABLE IF NOT EXISTS analytics_sandbox.products (
  product_id   INT PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  category     VARCHAR(50),
  price        DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT price_nonneg CHECK (price >= 0)
);

ALTER TABLE analytics_sandbox.products
  ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

ALTER TABLE analytics_sandbox.products
  ALTER COLUMN category TYPE VARCHAR(80);

DROP TABLE IF EXISTS analytics_sandbox.tmp_sales_agg;

Safety first

• Work in a sandbox or dev environment. Never experiment on production tables.
• Prefer widening over narrowing column types.
• Use IF NOT EXISTS and DROP ... IF EXISTS to avoid breaking runs.
• Before DROP, confirm you target the correct schema and table name.
• Consider transactions if your system supports them: if an ALTER fails, you can roll back.

Exercises

Tip: Everyone can do the exercises. Saved progress is available for logged-in users.

• Before you start: pick or create a sandbox schema you can safely change.
• Mirror of Exercises ex1–ex3 below. Try first, then open the solution.

Exercise ex1 — Create a table with constraints

Create a table analytics_sandbox.customers with:

• customer_id INT primary key
• email VARCHAR(120) not null and unique
• sign_up_date DATE not null
• status VARCHAR(20) default 'active'

Exercise ex2 — Alter the table safely

Alter analytics_sandbox.customers:

• Add last_login TIMESTAMP with default NULL.
• Rename status to account_status.
• Widen account_status to VARCHAR(30).

Exercise ex3 — Drop a temp table

Drop analytics_sandbox.tmp_customers_stage if it exists. Ensure your command does not error if it’s already gone.

Common mistakes and self-check

• Forgetting the schema: running against the default schema by accident. Self-check: fully-qualify names (e.g., analytics_sandbox.table).
• Narrowing types prematurely: e.g., VARCHAR(20) for emails. Self-check: sample actual data lengths before narrowing.
• Dropping the wrong table: ambiguous names. Self-check: run SELECT * FROM information_schema.tables (or your DB’s catalog) to confirm target.
• Skipping constraints: missing UNIQUE/NOT NULL leads to dirty data. Self-check: list required rules and translate them into constraints.

Practical projects

• Project 1: Build a sandbox schema. Create orders, order_items, and customers tables with primary/foreign keys and basic checks.
• Project 2: Model evolution. Start with a minimal products table. As requirements arise (tags, active flag, longer names), apply ALTER TABLE changes and keep a simple change log.
• Project 3: Cleanup job. Identify obsolete temp tables older than 30 days and write safe DROP TABLE IF EXISTS scripts to tidy up.

Who this is for

• Data Analysts who need to create and evolve reliable tables.
• BI Analysts preparing clean structures for dashboards.
• Anyone moving from ad-hoc SELECTs to managed analytical datasets.

Prerequisites

• Basic SELECT skills and comfort with schemas/databases.
• Access to a safe environment (sandbox or dev database).
• Basic understanding of common types: INT, VARCHAR, DATE/TIMESTAMP, DECIMAL.

Learning path

• This: DDL Create/Alter/Drop (table structures).
• Next: Constraints and Indexes (PK, FK, UNIQUE, CHECK, performance basics).
• Then: DML Insert/Update/Delete and CTAS (populate and reshape data).
• Later: Views and Materialized Views (share curated datasets safely).

Next steps

• Apply DDL to one real dataset you use weekly. Evolve its table safely.
• Create a short checklist you run before any ALTER or DROP.
• Optionally, keep a text file of DDL changes (a simple change log) to track evolution.

Quick Test (save progress when logged in)

Everyone can take the quick test; logged-in users will have progress saved.

Mini challenge

Your stakeholder wants to add product tags and ensure emails are unique in customers. Draft the minimal set of DDL statements to:

• Add a tags VARCHAR(200) column to analytics_sandbox.products (nullable is fine).
• Ensure analytics_sandbox.customers.email is unique (add a constraint if missing).
• Safely remove analytics_sandbox.tmp_sales_agg_old if it still exists.

Aim for safe patterns (IF EXISTS/IF NOT EXISTS) and future-proof types.