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

Video: Putting up the first shelf

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, not published 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…

Quick check

1. What does a brand new table contain?

2. Which column name is allowed?

3. Which family of commands does CREATE TABLE belong to?