BizTechLab — Ideas, Innovation, ImpactBizTechLab — Ideas, Innovation, Impact
Back to the IndexNext: Putting Data In, Taking Data Out

DDL: CREATE, ALTER, DROP

1.Building the Shop, Table by Table

M

In this chapter

We'll turn Act 1's paper design into three real database tables — and meet the three commands (CREATE, ALTER, DROP) that build and reshape a table's structure.

12–15 min

The Problem in Real Life

Mike wants a straight answer: how much did the shop actually sell today? Sarah has the design from the checkpoint — three tables, sketched out, every key and constraint decided. A design on paper has never rung up a single sale.

Sarah opens the SQL editor for the first time. Deciding what a table needs was Act 1's work. Actually telling the database to build it, one table at a time, is a different skill — starting from a completely blank screen.

S

Give me a second... I'm teaching it to answer that.

Sarah

A Design on Paper vs. a Real Table

A design isn't a database

Every decision from Act 1 exists only on paper until it's written as real SQL.

New needs show up after the fact

Nobody designs the perfect schema on the first try — real needs surface only once the shop starts using it.

Structure changes carry real risk

The wrong command against the wrong table can permanently destroy real data.

Nothing to query yet

Three empty tables can't answer a single one of Mike's real questions.

What Is DDL, Really?

Everything decided across Act 1 — entities, keys, constraints, data types — was a design. None of it exists inside an actual database yet. The part of SQL that turns a design into something real is called DDL (Data Definition Language): the commands that create, change, and remove a table's structure itself, never the data stored inside it.

DDL is exactly three commands, and GreenMart's new schema is about to need all of them.

CREATE builds a brand-new table, with every column, key, and constraint in place from the moment it exists.

Creating GreenMart's Schema
CREATE TABLE Customers (
  CustomerID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL,
  Phone TEXT,
  BalanceDue NUMERIC NOT NULL DEFAULT 0
);


CREATE TABLE Products (
  ProductID INTEGER PRIMARY KEY,
  Name TEXT NOT NULL UNIQUE,
  Price NUMERIC NOT NULL CHECK (Price > 0),
  InStock INTEGER NOT NULL DEFAULT 0
);


CREATE TABLE Sales (
  SaleID INTEGER PRIMARY KEY,
  CustomerID INTEGER NOT NULL REFERENCES Customers(CustomerID),
  ProductID INTEGER NOT NULL REFERENCES Products(ProductID),
  SaleDate DATE NOT NULL
);

This is exactly the schema the checkpoint asked for, written as real SQL for the first time — three CREATE TABLE statements, one per entity, with every primary key, foreign key, constraint, and data type from Act 1 built directly into the definition.

ALTER changes a table that already exists, like adding a column nobody thought of the first time.

Adding a Column Nobody Thought Of
ALTER TABLE Customers
ADD COLUMN Email TEXT;

A week in, Mike wants to email receipts. Nobody designed an Email column, because nobody needed one yet. ALTER TABLE adds it to the table that already exists — without losing a single row already in it.

DROP permanently deletes a table's structure, and every row inside it, with no undo.

A Command With No Undo
DROP TABLE Products;

This isn't "empty the table." The table itself — its structure, its constraints, and every row inside it — is gone completely. There's no confirmation dialog, and no way back. GreenMart should never run this by accident.

Notice what stayed constant across all three commands: the schema decided back in Act 1 never changed. CREATE built exactly what the checkpoint designed. ALTER extended it without touching what already worked. Even DROP — the one command that destroys — only does exactly what it's told, nothing more.

GreenMart finally has three real tables. They're just empty. Nothing has been sold, restocked, or paid for inside them yet — that's the next chapter's job.

Key Takeaway

DDL builds and reshapes a table's structure — CREATE, ALTER, DROP. It never touches the data inside; that's a completely different set of commands, coming next.

Why This Matters

Every constraint, key, and data type decided across all of Act 1 only becomes real the moment it's written as CREATE TABLE syntax — this chapter is where seven chapters of design finally take effect. ALTER TABLE will come up constantly for the rest of GreenMart's story, every time a real business need reveals a column nobody thought to include upfront.

GreenMart has three real, empty tables now. The next chapter is where Sarah actually starts putting Mike's sales, customers, and products into them.

Next
Schema Design Checkpoint