lesson 59 Database engineering · advanced

DDL: create, alter & inspect a table

Create and alter a table, then inspect its structure.

the idea

DDL changes structure: CREATE, ALTER, DROP. DML changes rows. Use the disposable lab to try both safely. Keep all statements in one script, ending with SELECT; the next execution starts fresh.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

CREATE TABLE practice_people(id INTEGER PRIMARY KEY,name TEXT);
ALTER TABLE practice_people ADD COLUMN city TEXT;
SELECT name,type
FROM pragma_table_info('practice_people');

Watch for: Schema names and ALTER capabilities differ across engines.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Inspect the empty practice surface. DDL builds structure. Start from the lab items you already have, then create a separate practice table in the next step.

   step-1.sql Run

   sql›

   SELECT id, name, price
   FROM lab_items
   ORDER BY id;

2. 2

   Create and alter a table. CREATE TABLE defines columns; ALTER TABLE ADD COLUMN extends the contract. Inspect with a SELECT afterward.

   step-2.sql Run

   sql›

   CREATE TABLE practice_people(id INTEGER PRIMARY KEY,name TEXT);
   ALTER TABLE practice_people ADD COLUMN city TEXT;
   SELECT name,type
   FROM pragma_table_info('practice_people');