AI Hints
Homeโ€บSQL Tutorialโ€บCreating and Changing Data
SQL ยท Lesson 70 of 126

Creating Tables

CREATE TABLE.

โฑ 2 min readโ–ถ Video at 1:38:51๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Until now, you've only read data. Select never changes anything, so you couldn't break a thing. Now we'll start creating and changing data. Don't worry, every example runs on a fresh copy of the shop database. Helen wants to start running events in her shop, like author talks and story hours. She needs a new table.

Creating Tables
From the video: Creating Tables
  • So far: only reading
  • Now: creating and changing
  • A new table for events

CREATE TABLE

Create table, then the name, then the columns in brackets. Each column has a name and a type. Integer for whole numbers, text for words and dates. Primary key means each event gets a unique ID, filled in automatically. Not null means a title is required. And default thirty means, if you don't give the seats, it's thirty. Run it. The list of tables now includes events.

query.sqlsql
CREATE TABLE events (
    event_id   INTEGER PRIMARY KEY,
    title      TEXT NOT NULL,
    event_date TEXT,
    seats      INTEGER DEFAULT 30
);
SELECT name FROM sqlite_master
WHERE type = 'table';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    name     โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ customers   โ”‚
โ”‚ books       โ”‚
โ”‚ orders      โ”‚
โ”‚ order_items โ”‚
โ”‚ staff       โ”‚
โ”‚ events      โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Common SQLite types

SQLite keeps types simple. Integer for whole numbers. Real for decimals, like prices. Text for words, and also for dates, written as year, month, day. Other databases have more types, like varchar, date, and decimal, but the idea is the same.

INTEGERwhole numbers
REALdecimals like 19.99
TEXTwords, and dates
OthersVARCHAR, DATE, DECIMAL

Summary

Create table defines a table's columns, types, and rules. Next, putting rows into it.

Key takeaways
  • CREATE TABLE name (...)
  • Column name + type
  • PRIMARY KEY, NOT NULL, DEFAULT
Next up: Adding Rows โ€” INSERT INTO