CREATE TABLE and data types
The full text of this chapter, to read or revisit. To practise, do the exercises.
βΆ Do the exercisesExercise 1 β Your first table
So far every table was already there. CREATE TABLE builds a new one: the table name, then one line per column with its name and its type.
Statements that define the structure of the database (CREATE, ALTER, DROP) are called DDL, Data Definition Language.
The league needs a gyms table:
CREATE TABLE gyms (
gym_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT,
type TEXT
);
INSERT INTO gyms (name, city, type)
VALUES ('Tide Gym', 'Sunset Valley', 'water'),
('Ember Gym', 'Azure City', 'fire');
SELECT * FROM gyms;The table is created empty; the INSERT fills it with two rows. gym_id INTEGER PRIMARY KEY is the automatic id you have been using all along β NOT NULL and PRIMARY KEY are constraints, rules attached to a column. More on them in the next chapter.
To see which tables exist, ask the database's own catalog:
CREATE TABLE gyms (
gym_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT,
type TEXT
);
SELECT name FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
ORDER BY name;Already there?
CREATE TABLE gyms β¦ fails if gyms already exists. CREATE TABLE IF NOT EXISTS gyms β¦ does nothing in that case, without an error β handy in scripts you run more than once.
Exercise 2 β Data types
Each column gets a type: INTEGER for whole numbers, REAL for decimals, TEXT for anything written. There is no date type in SQLite: dates are TEXT in the 'YYYY-MM-DD' form, which sorts correctly. Yes/no values are INTEGER 0 or 1.
CREATE TABLE battles (
battle_id INTEGER PRIMARY KEY,
battle_date TEXT,
duration_min REAL,
is_official INTEGER
);
INSERT INTO battles (battle_date, duration_min, is_official)
VALUES ('2023-02-01', 12.5, 1),
('2023-02-08', 7.25, 0);
SELECT * FROM battles ORDER BY battle_date;typeof() tells you what is really stored in a value:
SELECT name, typeof(name) AS name_type,
level, typeof(level) AS level_type
FROM codemon
LIMIT 3;SQLite is lenient, others are not
SQLite will accept 'abc' in an INTEGER column and simply store the text. Most other databases refuse. Choose your types as if they were strictly enforced β your data stays clean and portable.
Exercise 3 β A column for dates
Which type should a capture_date column have in SQLite?
Hint : No date type: text in a sortable format.
Exercise 4 β Build the gyms table
Complete the statement so that it creates the gyms table with an automatic id, then shows the two inserted gyms.
Hint : The INSERT below tells you the table's name; an automatic id is a PRIMARY KEY.
Exercise 5 β Automatic ids
An INTEGER PRIMARY KEY column numbers rows for you: skip it in the INSERT and the database picks the next number.
After a DELETE, that number can be reused:
CREATE TABLE gyms (gym_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO gyms (name) VALUES ('Tide Gym'), ('Ember Gym'), ('Boulder Gym');
DELETE FROM gyms WHERE gym_id = 3;
INSERT INTO gyms (name) VALUES ('Spark Gym');
SELECT * FROM gyms;Spark Gym got the id 3 that Boulder Gym had. Add AUTOINCREMENT and an id is never given twice β the right choice when ids end up in badges, logs or URLs:
CREATE TABLE gyms (gym_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL);
INSERT INTO gyms (name) VALUES ('Tide Gym'), ('Ember Gym'), ('Boulder Gym');
DELETE FROM gyms WHERE gym_id = 3;
INSERT INTO gyms (name) VALUES ('Spark Gym');
SELECT * FROM gyms;Exercise 6 β Pick the type
Match each piece of information with the type of its column.
INTEGERREALTEXT, free textTEXT, as 'YYYY-MM-DD'Exercise 7 β A table from a query
CREATE TABLE β¦ AS SELECT β¦ creates a table and fills it with the result of a query, columns and rows alike:
CREATE TABLE fire_codemon AS
SELECT name, level
FROM codemon
WHERE type = 'fire';
SELECT * FROM fire_codemon;Useful for a snapshot or a working copy. The new table is independent: changing codemon afterwards does not change fire_codemon.
Types only, no constraints
A table created with AS SELECT gets its column types from the query but no primary key and no other constraint. For a real, long-lived table, write the full CREATE TABLE and INSERT β¦ SELECT into it.
Exercise 8 β Second time around
gyms already exists. What does CREATE TABLE IF NOT EXISTS gyms (gym_id INTEGER PRIMARY KEY, name TEXT); do?
Hint : IF NOT EXISTS turns the error into a no-op.
Exercise 9 β The gyms table
Professor Pi wants the gyms in the database at last.
Create gyms with gym_id (automatic id), name (text, required), city (text) and type (text). Insert Tide Gym (Sunset Valley, water), Ember Gym (Azure City, fire) and Boulder Gym (Stone Ridge, rock), then show every column of every gym, sorted by gym_id.
-- Write your statements below, and finish with a SELECT to check the result