CREATE TABLE and INSERT

Create tables with types and constraints, insert rows, and how SQLite stores types.

Runs in Zerethon IDE · SQLite 3 Official SQL documentation ↗

main.sql

CREATE TABLE books (
    id INTEGER PRIMARY KEY,   -- filled in automatically
    title TEXT NOT NULL,
    year INTEGER,
    price REAL
);

INSERT INTO books (title, year, price) VALUES ('SICP', 1985, 45.5), ('K&R', 1978, 39.0);
INSERT INTO books (title) VALUES ('Untitled draft');

SELECT id, title, year, price, typeof(price) AS type FROM books;

Result

id	title	year	price	type
1	SICP	1985	45.5	real
2	K&R	1978	39	real
3	Untitled draft	NULL	NULL	null

INTEGER PRIMARY KEY gets the next number when you leave it out.
A column left out of an INSERT is NULL (or its DEFAULT), unless it is NOT NULL.
SQLite types are flexible: a column's type is a preference, so 'abc' can be stored in an INTEGER column unless the table is declared STRICT.
Put a table you use in many queries in a setup/ file, so every run creates it first.

CREATE TABLE and INSERT

Related topics

Official references