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 7 of 33 · about 12 minutes
Video: Putting up the first shelfThe narrated video for this lesson is coming soon. The lesson below covers the same ideas.
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…