SQL from Zero › Module 2: DDL: building the structure
Your first table with CREATE TABLE
Put up the bookshop's first shelf: a table with named columns.
Lesson 8 of 35 · about 12 minutes
The lesson
Remember the five families of commands? Now you start building, with DDL. Your practice database is completely empty, like a new shop with bare walls. Let's put up the first shelf.
A table needs a name and a list of columns. Each column has a name and a type, which says what kind of value goes in it.
CREATE TABLE authors (
id INTEGER,
name TEXT,
country TEXT
);
Reading it out loud: "Create a table called authors, with three columns: id holds whole numbers, name holds text, and country holds text."
A few rules:
- Columns go inside round brackets
( ), separated by commas. There's no comma after the last one. - Names can't have spaces. Use
published_year, notpublished year. - The statement ends with a semicolon
;. - A new table is empty. It has columns but no rows yet. You'll add rows in Module 3.
To see the tables you've made, run SELECT name, sql FROM sqlite_master;. That's the database's own list of everything it contains.
Key ideas
- CREATE TABLE makes a new, empty table with the columns you list.
- Each column has a name and a type, separated by commas inside round brackets.
- A new table has structure but no rows until you add data.
Practise
Loading practice…