CREATE TABLE and data types

The full text of this chapter, to read or revisit. To practise, do the exercises.

β–Ά Do the exercises

Exercise 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?

Options
β€’ TEXT, storing dates as 'YYYY-MM-DD'
β€’ DATE
β€’ INTEGER, storing the day of the month
β€’ REAL

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.

Items to match
β€’ A number of badges
β€’ A weight in kilograms, with decimals
β€’ A city name
β€’ A date
Definitions
β€’ INTEGER
β€’ REAL
β€’ TEXT, free text
β€’ TEXT, 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?

Options
β€’ Nothing, and no error: the existing table is kept as it is
β€’ It replaces the table with the new definition
β€’ An error: the table already exists
β€’ It adds the missing columns to the existing table

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.

Starter code
-- Write your statements below, and finish with a SELECT to check the result
Back to the course