Life of a Table & DDL Cheat Sheet

Follow one table from CREATE to DROP, and keep a one-page DDL cheat sheet plus best practices.

Let's put the whole chapter together. Every table has a life: it is born with CREATE, grows and changes with ALTER, and finally dies with DROP — all using DDL.

Definition

The life cycle of a table is the sequence of DDL operations applied to it: CREATE (an empty table is born) → ALTER (its structure changes, as many times as needed) → DROP (it is deleted for good).

The four stages

Stage	Command	What happens
1	`CREATE TABLE players`	Empty table is born
2	`ALTER TABLE players ADD jersey_no INT`	A new column appears
3	`ALTER TABLE players DROP COLUMN jersey_no`	The column is removed
4	`DROP TABLE players`	The table is deleted

Example — the whole life in one script

sql

-- 1. CREATE
CREATE TABLE players_new (
    id   INT         NOT NULL,
    name VARCHAR(50) NOT NULL,
    team VARCHAR(10),
    CONSTRAINT pk_players_new PRIMARY KEY (id)
);
INSERT INTO players_new VALUES (1, 'Virat Kohli', 'RCB'), (4, 'MS Dhoni', 'CSK');

-- 2. ALTER: add a column
ALTER TABLE players_new ADD jersey_no INT;
UPDATE players_new SET jersey_no = 18 WHERE id = 1;
UPDATE players_new SET jersey_no = 7  WHERE id = 4;
SELECT * FROM players_new;

id	name	team	jersey_no
1	Virat Kohli	RCB	18
4	MS Dhoni	CSK	7

sql

-- 3. ALTER: drop the column
ALTER TABLE players_new DROP COLUMN jersey_no;
SELECT * FROM players_new;

id	name	team
1	Virat Kohli	RCB
4	MS Dhoni	CSK

sql

-- 4. DROP
DROP TABLE players_new;   -- the table and both rows are gone

DDL cheat sheet

Statement	What it does
`CREATE TABLE t (col type, ...)`	Build a new table
`ALTER TABLE t ADD col type`	Add a column
`ALTER TABLE t ALTER COLUMN col new_type`	Change a data type (SQL Server)
`ALTER TABLE t DROP COLUMN col`	Remove a column
`ALTER TABLE t ADD CONSTRAINT name ...`	Add a rule to an existing table
`DROP TABLE t`	Delete the table + data
`PRIMARY KEY` \| `NOT NULL` \| `DEFAULT`	Rules for columns
`UNIQUE` \| `CHECK` \| `FOREIGN KEY`	More rules for columns

Best practices for DDL

Every table gets a PRIMARY KEY. Without one, you can't reliably identify, update or delete a single row.
Pick precise data types and sizes. DATE for dates, DECIMAL for exact numbers, sensible VARCHAR lengths.
Use NOT NULL for required columns and DEFAULT for sensible starting values.
Name things clearly and consistently — snake_case, no spaces, no reserved words, constraint prefixes like pk_, fk_, uq_, ck_.
Save your DDL as scripts (.sql files in Git) so the database can be rebuilt.
Test DDL on a development copy first, never directly on production.
Back up before ALTER/DROP on important tables — these changes are hard or impossible to undo.
Use IF EXISTS in clean-up scripts so they can be re-run safely.

Common mistakes (whole chapter)

❌ Using DROP when you meant DELETE — losing the table structure.
❌ ADD COLUMN in SQL Server (it's just ADD) / DROP jersey_no (it's DROP COLUMN).
❌ Text columns for numbers and dates.
❌ Forgetting the comma between column definitions, or adding one after the last.
❌ Running DDL against the wrong database.

Interview tip

Q: Walk me through creating, modifying and removing a table. A: CREATE TABLE with columns, types and constraints creates an empty table; ALTER TABLE … ADD / DROP COLUMN / ALTER COLUMN changes its structure while keeping its data; DROP TABLE deletes the structure and all data permanently. All three are DDL commands.

Key points to remember

A table's life cycle: CREATE (born empty) → ALTER (structure changes) → DROP (deleted).
CREATE TABLE t (col type, ...) builds; ALTER TABLE t ADD / DROP COLUMN / ALTER COLUMN changes; DROP TABLE t deletes.
PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE, CHECK and FOREIGN KEY are the rules (constraints) for columns.
Give every table a primary key and choose precise data types.
Name tables, columns and constraints clearly and consistently.
Test DDL on a copy and back up before ALTER/DROP on important tables.

Try it yourself

The life of a table: CREATE → ALTER → ALTER → DROP

-- 1. CREATE: empty table is born
CREATE TABLE players_new (
    id   INT         NOT NULL,
    name VARCHAR(50) NOT NULL,
    team VARCHAR(10),
    CONSTRAINT pk_players_new PRIMARY KEY (id)
);

INSERT INTO players_new (id, name, team)
VALUES (1, 'Virat Kohli', 'RCB'), (4, 'MS Dhoni', 'CSK');

-- 2. ALTER: a new column appears
ALTER TABLE players_new ADD jersey_no INT;
UPDATE players_new SET jersey_no = 18 WHERE id = 1;
UPDATE players_new SET jersey_no = 7  WHERE id = 4;

SELECT *
FROM players_new;

-- 3. ALTER: the column is removed
ALTER TABLE players_new DROP COLUMN jersey_no;

SELECT *
FROM players_new;

-- 4. DROP: the table is deleted
DROP TABLE players_new;

-- 1. CREATE: empty table is born
CREATE TABLE players_new (
    id   INT         NOT NULL,
    name VARCHAR(50) NOT NULL,
    team VARCHAR(10),
    CONSTRAINT pk_players_new PRIMARY KEY (id)
);

INSERT INTO players_new (id, name, team)
VALUES (1, 'Virat Kohli', 'RCB'), (4, 'MS Dhoni', 'CSK');

-- 2. ALTER: a new column appears
ALTER TABLE players_new ADD jersey_no INT;
UPDATE players_new SET jersey_no = 18 WHERE id = 1;
UPDATE players_new SET jersey_no = 7  WHERE id = 4;

SELECT *
FROM players_new;

-- 3. ALTER: the column is removed
ALTER TABLE players_new DROP COLUMN jersey_no;

SELECT *
FROM players_new;

-- 4. DROP: the table is deleted
DROP TABLE players_new;

Runs on a fresh IPL practice database in your browser · edit the query and press Ctrl + Enter

A complete, well-designed table

CREATE TABLE match_results (
    match_id    INT          NOT NULL,
    match_date  DATE         NOT NULL,
    winner      VARCHAR(10)  NOT NULL,
    margin_runs INT          DEFAULT 0,
    is_final    INT          NOT NULL DEFAULT 0,
    CONSTRAINT pk_match_results PRIMARY KEY (match_id),
    CONSTRAINT ck_match_results_margin CHECK (margin_runs >= 0),
    CONSTRAINT fk_match_results_winner FOREIGN KEY (winner) REFERENCES teams (team)
);

INSERT INTO match_results (match_id, match_date, winner, margin_runs)
VALUES
    (1, '2025-03-22', 'RCB', 7),
    (2, '2025-03-23', 'MI',  0);

SELECT *
FROM match_results;

CREATE TABLE match_results (
    match_id    INT          NOT NULL,
    match_date  DATE         NOT NULL,
    winner      VARCHAR(10)  NOT NULL,
    margin_runs INT          DEFAULT 0,
    is_final    INT          NOT NULL DEFAULT 0,
    CONSTRAINT pk_match_results PRIMARY KEY (match_id),
    CONSTRAINT ck_match_results_margin CHECK (margin_runs >= 0),
    CONSTRAINT fk_match_results_winner FOREIGN KEY (winner) REFERENCES teams (team)
);

INSERT INTO match_results (match_id, match_date, winner, margin_runs)
VALUES
    (1, '2025-03-22', 'RCB', 7),
    (2, '2025-03-23', 'MI',  0);

SELECT *
FROM match_results;

This example runs a small setup step first — click to see it

PRAGMA foreign_keys = ON;

Runs on a fresh IPL practice database in your browser · edit the query and press Ctrl + Enter

Practice: run your own table's life cycle

-- 🏏 Your turn: create a coaches table (coach_id, name, team),
-- ALTER it to add an experience_years INT column, fill it, look at it, then DROP it.
CREATE TABLE coaches (
    coach_id INT         NOT NULL,
    name     VARCHAR(50) NOT NULL,
    team     VARCHAR(10),
    CONSTRAINT pk_coaches PRIMARY KEY (coach_id)
);

INSERT INTO coaches (coach_id, name, team)
VALUES (1, 'Stephen Fleming', 'CSK');

SELECT *
FROM coaches;

-- 🏏 Your turn: create a coaches table (coach_id, name, team),
-- ALTER it to add an experience_years INT column, fill it, look at it, then DROP it.
CREATE TABLE coaches (
    coach_id INT         NOT NULL,
    name     VARCHAR(50) NOT NULL,
    team     VARCHAR(10),
    CONSTRAINT pk_coaches PRIMARY KEY (coach_id)
);

INSERT INTO coaches (coach_id, name, team)
VALUES (1, 'Stephen Fleming', 'CSK');

SELECT *
FROM coaches;

Runs on a fresh IPL practice database in your browser · edit the query and press Ctrl + Enter

Revision Flashcards

1 / 4

Tap a card to flip it and see the answer.