Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

SQLite

About SQLite

Some features:

  • The database is just a single file.
  • It has an in-memory version for fast access to temporary data.
  • No need for external server process.
  • The client directly accesses the file containing the data.
  • There is a command-line tool called sqlite3 to access the data.
  • There are several GUI tools to view and manipulate data.
  • Most programming languages have a library to access an SQLite database.

Using SQLite in programming languages

Main features - where to use it

It is very fast for reading, but not so good for concurrent writing.

  • Embedded in every web browser
  • Embedded in many other applications.
  • Cars etc.

Install SQLite command-line tool

Debian/Ubuntu Linux:

$ sudo apt install sqlite3

Windows

  • Download sqlite-tools-win-x64-3530000.zip (or a later version) that includes the “Command-line tools for Windows x64,”

macOS

Version of SQLite

On Ubuntu 25.10:

$ sqlite3 --version
3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69aalt1 (64-bit)

Downloaded from web site of SQLite

$ ~/bin/sqlite3 --version
3.53.0 2026-04-09 11:41:38 4525003a53a7fc63ca75c59b22c79608659ca12f0131f52c18637f829977f20b (64-bit)

Some of the examples only work on the most recent version.

GUI for SQLite

TODO

There are number of desktop and web-based GUI tools to be used with an SQLite database.

DB Browser for SQLite

sudo apt install sqlitebrowser

Beekeeper Studio

DBeaver

Sqlime

SQLite Viewer

SQLite dialect

  • SQLite works according to the SQL92 standard.
  • There are some SQLite specific extensions.

Data types

  • Data types
  • Flexible typing - SQLite does not really care about the types you defined for the columns. You can even create columns without types.
  • STRICT tables - In special cases you can ask SQLite to enforce strict type-checking by setting the table to be STRICT.

Basic types

  • NULL
  • INTEGER
  • REAL
  • TEXT
  • BLOB

Conventions

SQL is case insensitive, but in order to make it easier to read some people follow the rule to always write every SQL command in capital letters and every user-defined values (e.g. names of the tables, columns etc.) in lower-case or maybe mixed case.

We’ll follow this convention in the examples.

SQLite interactive shell - .help and .quit

The command line tool has a number of internal commands starting with a dot. .help will show the list of the commands. .quit or .exit will close the shell.

Command Line Shell For SQLite

The commands can be given

  • interactively
  • sent through a pipe
  • redirected from a file
$ sqlite3
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> .help
...

sqlite> .quit

$

Help with SQLite CLI using a pipe

Pipe the command to the STDIN of sqlite3. Available on Linux and macOS and in the git-bash on Windows.

echo .help | sqlite3

Help with SQLite CLI using redirect

Redirect from an external file to the Standard Input (STDIN) of the sqlite3 command.

.help

$ sqlite3 < examples/help.sql

SQLite version

This is using the version I installed from SQLite on Ubuntu Linux.

Interactive

$ sqlite3
sqlite> .version
SQLite 3.53.0 2026-04-09 11:41:38 4525003a53a7fc63ca75c59b22c79608659ca12f0131f52c18637f829977f20b
zlib version 1.3.1
gcc-13.3.0 (64-bit)

sqlite> .quit

Piped to STDIN

$ echo .version | sqlite3
SQLite 3.53.0 2026-04-09 11:41:38 4525003a53a7fc63ca75c59b22c79608659ca12f0131f52c18637f829977f20b
zlib version 1.3.1
gcc-13.3.0 (64-bit)

Redirect from file

.version

$ sqlite3 < examples/version.sql
SQLite 3.53.0 2026-04-09 11:41:38 4525003a53a7fc63ca75c59b22c79608659ca12f0131f52c18637f829977f20b
zlib version 1.3.1
gcc-13.3.0 (64-bit)

Command line flag

$ sqlite3 --version
3.53.0 2026-04-09 11:41:38 4525003a53a7fc63ca75c59b22c79608659ca12f0131f52c18637f829977f20b (64-bit)

SQLite CLI - external commands .shell

To run any external (shell) command use .shell.

On Linux / macOS you have ls:

sqlite> .shell ls

On Windows you have dir:

sqlite> .shell dir

SQLite CLI - .schema and .tables

Using the .tables command we can see the list of tables and using the .schema command we can see the whole definition.

.schema
.tables

SQLite CLI - .mode

The .mode controls how results of SELECT statements are displayed.

Show the current mode

sqlite> .mode
.mode qbox --limits on --quote relaxed --sw auto --textjsonb on

List available modes

sqlite> .mode --list
available modes: ascii box c column count csv html insert jatom jobject json
  line list markdown off psql qbox quote split table tabs tcl batch tty

Set a mode:

sqlite> .mode column

SQLite in-memory or file-based

  • Using in-memory database
$ sqlite3
  • Using a persistent database file. If the file does not exist, this command will create it.
$ sqlite3 my.db

DDL - Data Definition Language

  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE

CREATE TABLE

CREATE TABLE person (
    id INTEGER,
    name TEXT
);

.schema


$ sqlite < examples/create-table.sql

CREATE TABLE person (
    id INTEGER,
    name TEXT
);

ALTER TABLE

Using ALTER TABLE we can modify the schema.

CREATE TABLE person (
    id INTEGER,
    name TEXT
);

.schema

ALTER TABLE person ADD COLUMN phone TEXT;

.schema

$ sqlite3 < examples/alter-table.sql

CREATE TABLE person (
    id INTEGER,
    name TEXT
);
CREATE TABLE person (
    id INTEGER,
    name TEXT
, phone TEXT);

DROP TABLE

DROP TABLE will remove the table and all its content. In this example we also used the .schema command to show what is the current schema and the .mode batch to make the SELECT statement show a simple line.

.mode batch
CREATE TABLE person (
    id INTEGER,
    name TEXT
);

SELECT '-------';
.schema

DROP TABLE person;

SELECT '-------';

.schema

SELECT '-------';


$ sqlite < examples/drop-table.sql

-------
CREATE TABLE person (
    id INTEGER,
    name TEXT
);
-------
-------

DML - Data Manipulation Language - Basic Operations

  • CREATE
  • INSERT
  • SELECT
  • UPDATE

CREATE - INSERT - SELECT

With CREATE we define the schema.

With INSERT we add a row.

With SELECT we can fetch data from the database.

CREATE TABLE person (
    id INTEGER,
    name TEXT,
    email TEXT
);

INSERT INTO person (id, name, email) VALUES (1, 'Joe', 'joe@example.com');
INSERT INTO person (id, name, email) VALUES (2, 'Jane', 'jane@example.com');

SELECT * FROM person;

In memory:

$ sqlite3 < create-insert-select.sql

1|Joe|joe@example.com
2|Jane|jane@example.com

In file:

$ sqlite3 demo.db < create-insert-select.sql

1|Joe|joe@example.com
2|Jane|jane@example.com

Then, if we don’t need it any more, we should remove the database file:

$ rm -f demo.db

SELECT only some of the columns

Instead of using a * to fetch all the columns, we can define the specific columns we would like to fetch.

CREATE TABLE person (
    id INTEGER,
    name TEXT,
    email TEXT
);

INSERT INTO person (id, name, email) VALUES (1, 'Joe', 'joe@example.com');
INSERT INTO person (id, name, email) VALUES (2, 'Jane', 'jane@example.com');
INSERT INTO person (id, name, email) VALUES (3, 'Mary', 'mary@example.com');
INSERT INTO person (id, name, email) VALUES (4, 'Peter', 'peter@example.com');

SELECT id, name FROM person;


$ sqlite3 < examples/select-some-columns.sql

1|Joe
2|Jane
3|Mary
4|Peter

SELECT only some of the rows - WHERE clause

We can also use the WHERE close to select which rows we would like to see.

CREATE TABLE person (
    id INTEGER,
    name TEXT,
    email TEXT
);

INSERT INTO person (id, name, email) VALUES (1, 'Joe', 'joe@example.com');
INSERT INTO person (id, name, email) VALUES (2, 'Jane', 'jane@example.com');
INSERT INTO person (id, name, email) VALUES (3, 'Mary', 'mary@example.com');
INSERT INTO person (id, name, email) VALUES (4, 'Peter', 'peter@example.com');

SELECT * FROM person WHERE id > 2;


$ sqlite < examples/select-some-rows.sql

3|Mary|mary@example.com
4|Peter|peter@example.com

AUTOINCREMENT

Keeping in mind to increase the id number can be annoying. SQL provides the AUTOINCREMENT option that can help us maintain that value. However in SQLite it is not necessary to use and it isn’t even recommended.

In any case let’s see an example:

CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT
);

INSERT INTO person (name, email) VALUES ('Joe', 'joe@example.com');
INSERT INTO person (name, email) VALUES ('Jane', 'jane@example.com');
INSERT INTO person (name, email) VALUES ('Mary', 'mary@example.com');
INSERT INTO person (name, email) VALUES ('Peter', 'peter@example.com');

SELECT * from person;

.schema


$ sqlite3 < examples/autoincrement.sql

1|Joe|joe@example.com
2|Jane|jane@example.com
3|Mary|mary@example.com
4|Peter|peter@example.com
CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email TEXT
);
CREATE TABLE sqlite_sequence(name,seq);

Invisible AUTOINCREMENT (ROWID)

If we have a column that is marked as INTEGER PRIMARY KEY, it will get incremented automatically. See the AUTOINCREMENT description for details.

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

INSERT INTO person (name, email) VALUES ('Joe', 'joe@example.com');
INSERT INTO person (name, email) VALUES ('Jane', 'jane@example.com');
INSERT INTO person (name, email) VALUES ('Mary', 'mary@example.com');
INSERT INTO person (name, email) VALUES ('Peter', 'peter@example.com');

SELECT * from person;

.schema


$ sqlite3 < examples/invisible-autoincrement.sql

1|Joe|joe@example.com
2|Jane|jane@example.com
3|Mary|mary@example.com
4|Peter|peter@example.com
CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
);

Missing value

When we insert data, some columns (besides the id) might be missing. SQLite will insert a NULL value.

CREATE TABLE qa (
    question TEXT,
    answer TEXT
);

INSERT INTO qa (question, answer) VALUES ('Language?', 'SQL');
INSERT INTO qa (question, answer) VALUES ('Database?', 'SQLite');

INSERT INTO qa (question) VALUES ('Meaning of life?');

SELECT * from qa;


$ sqlite3 < examples/missing-text-value.sql
Language?|SQL
Database?|SQLite
Meaning of life?|

This is hard to see in the above display, but in the interactive shell or if we use a real terminal it will look much better:

╭──────────────────┬────────╮
│     question     │ answer │
╞══════════════════╪════════╡
│ Language?        │ SQL    │
│ Database?        │ SQLite │
│ Meaning of life? │ NULL   │
╰──────────────────┴────────╯

Missing numerical value

CREATE TABLE qa (
    question TEXT,
    answer INTEGER
);

INSERT INTO qa (question, answer) VALUES ('2+2', 4);
INSERT INTO qa (question, answer) VALUES ('2-2', 0);

INSERT INTO qa (question) VALUES ('Meaning of life?');

SELECT * from qa;


$ sqlite3 < examples/missing-integer-value.sql
2+2|4
2-2|0
Meaning of life?|

╭──────────────────┬────────╮
│     question     │ answer │
╞══════════════════╪════════╡
│ 2+2              │      4 │
│ 2-2              │      0 │
│ Meaning of life? │ NULL   │
╰──────────────────┴────────╯

Field with DEFAULT value

We can set up a DEFAULT value to each field and if the INSERT does not set the value then this default value will be set. However, if the INSERT explicitly sets NULL then that will be the value.

CREATE TABLE qa (
    question TEXT,
    answer TEXT DEFAULT 42
);

INSERT INTO qa (question, answer) VALUES ('Language?', 'SQL');
INSERT INTO qa (question, answer) VALUES ('Database?', 'SQLite');
INSERT INTO qa (question) VALUES ('Meaning of life?');

INSERT INTO qa (question, answer) VALUES ('What is void?', NULL);

SELECT * from qa;


$ sqlite3 < examples/default-value.sql

Language?|SQL
Database?|SQLite
Meaning of life?|42
What is void?|

╭──────────────────┬────────╮
│     question     │ answer │
╞══════════════════╪════════╡
│ Language?        │ SQL    │
│ Database?        │ SQLite │
│ Meaning of life? │ '42'   │
│ What is void?    │ NULL   │
╰──────────────────┴────────╯

NOT NULL constraint

Here we have two tables. In the restricted table we set one of the fields to be NOT NULL.

CREATE TABLE plain (
    id INTEGER,
    name TEXT
);

INSERT INTO plain (id, name) VALUES (1, 'Joe');
INSERT INTO plain (name) VALUES ('Jane');

SELECT * from plain;


CREATE TABLE restricted (
    id INTEGER NOT NULL,
    name TEXT
);

INSERT INTO restricted (id, name) VALUES (1, 'Joe');
INSERT INTO restricted (name) VALUES ('Jane');
INSERT INTO restricted (id, name) VALUES (NULL, 'Mary');

SELECT * from restricted;



$ sqlite3 < examples/not-null.sql

1|Joe
|Jane
Error near line 18: NOT NULL constraint failed: restricted.id
Error near line 19: NOT NULL constraint failed: restricted.id
1|Joe

UPDATE

With UPDATE we can change one or more fields in one or more rows.

.mode batch

CREATE TABLE people (
    name TEXT,
    grade INTEGER
);

INSERT INTO people (name, grade) VALUES ('Joe', 40);
INSERT INTO people (name, grade) VALUES ('Jane', 60);
SELECT * from people;
SELECT '-------';

-- SELECT 'Setting the grade of Joe to be 44';
UPDATE people SET grade = 44;
SELECT * from people;
SELECT '-------';

UPDATE people SET grade = 100 WHERE name = 'Joe';
SELECT * from people;
SELECT '-------';


$ sqlite < examples/update.sql

Joe|40
Jane|60
-------
Joe|44
Jane|44
-------
Joe|100
Jane|44
-------

SELECT without table

$ echo "SELECT 2 + 3" | sqlite3
$ echo "SELECT "Hello World'" | sqlite3

SELECT date and time

Date And Time Functions

SELECT "date", date();
SELECT "time", time();
SELECT "datetime", datetime();
SELECT "julianday",julianday();
SELECT "unixepoch", unixepoch();
SELECT "strftime", strftime();
SELECT "strftime with param", strftime("%Y-%m-%d %H:%M:%S");
--SELECT timediff("%Y");

$ sqlite3 < date-and-time.sql

date|2026-04-28
time|09:40:04
datetime|2026-04-28 09:40:04
julianday|2461158.90283096
unixepoch|1777369204
strftime|
strftime with param|2026-04-28 09:40:04

Import data from CSV

Import iris.csv

$ sqlite3

sqlite> .import --csv  -v iris.csv iris
CREATE TABLE "main"."iris"(
"Id" TEXT, "SepalLengthCm" TEXT, "SepalWidthCm" TEXT, "PetalLengthCm" TEXT,
 "PetalWidthCm" TEXT, "Species" TEXT)

Added 150 rows with 0 errors using 151 lines of input

sqlite> .schema
CREATE TABLE IF NOT EXISTS "iris"(
"Id" TEXT, "SepalLengthCm" TEXT, "SepalWidthCm" TEXT, "PetalLengthCm" TEXT,
 "PetalWidthCm" TEXT, "Species" TEXT);

sqlite> SELECT COUNT(*) FROM iris;
150

sqlite> SELECT AVG(SepalLengthCm) FROM iris;
5.84333333333333

sqlite> SELECT AVG(PetalLengthCm) FROM iris;
3.75866666666667

sqlite> SELECT AVG(PetalLengthCm), Species FROM iris GROUP BY Species;
1.464|Iris-setosa
4.26|Iris-versicolor
5.552|Iris-virginica

sqlite> DROP TABLE iris;

Load schema and Import iris.csv

CREATE TABLE iris (
    Id            INTEGER,
    SepalLengthCm REAL,
    SepalWidthCm  REAL,
    PetalLengthCm REAL,
    PetalWidthCm  REAL,
    Species       TEXT
);

sqlite> .schema

sqlite> .read iris.sql

sqlite> .import --csv --skip 1 -v iris.csv iris

Added 150 rows with 0 errors using 151 lines of input

sqlite> .schema
CREATE TABLE iris (
    Id            INTEGER,
    SepalLengthCm REAL,
    SepalWidthCm  REAL,
    PetalLengthCm REAL,
    PetalWidthCm  REAL,
    Species       TEXT
);

Import planets.csv

Planet name,Distance (AU),Mass
Mercury,0.4,0.055
Venus,0.7,0.815
Earth,1,1
Mars,1.5,0.107
Ceres,2.77,0.00015
Jupiter,5.2,318
Saturn,9.5,95
Uranus,19.6,14
Neptune,30,17
Pluto,39,0.00218
Charon,39,0.000254

sqlite> .import --csv planets.csv planets
sqlite> .schema
CREATE TABLE IF NOT EXISTS "planets"(
"Planet name" TEXT, "Distance (AU)" TEXT, "Mass" TEXT);
sqlite> select "Distance (AU)" from planets;

Flexible typing

The Advantages Of Flexible Typing

CREATE TABLE flexible_data (
    id INTEGER,
    name TEXT,
    height REAL
);

INSERT INTO flexible_data (id, name, height) VALUES (1, "foo", 1.8);
INSERT INTO flexible_data (id, name, height) VALUES ("id", 23, "tall");

SELECT * from flexible_data;

Database normalization

Database normalization

  • First Normal Form (1NF) - every column should store only one value.
  • Second Normal Form (2NF) - the same value should not be repeated in the column.
  • Third Normal Form (3NF)
  • BCNF

Flat table

CREATE TABLE person (
    name TEXT,
    subject TEXT,
    grade INTEGER
);

INSERT INTO person (name, subject, grade) VALUES
    ('Jane',  'Chemistry', 62),
    ('Jane',  'Physics', 70),
    ('Jane',  'Math', 23),
    ('Joe',   'Chemistry', 45),
    ('Joe',   'Physics', 85),
    ('Joe',   'Math', 77),
    ('Peter', 'Chemistry', 35),
    ('Peter', 'Physics', 63),
    ('Mary',  'Chemistry', 95),
    ('Dean', 'Physics', 100),
    ('Dana', 'Math', 97),
    ('George', 'Math', 97),
    ('George', 'Chemistry', 45);


SELECT * FROM person;

Two tables


-- enforce foreignk key integrity
PRAGMA foreign_keys = ON;

CREATE TABLE subject (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE person (
    name TEXT,
    subject_id INTEGER REFERENCES subject(id),
    grade INTEGER
);

INSERT INTO subject (id, name) VALUES
    (1, 'Chemistry'),
    (2, 'Physics'),
    (3, 'Math');

INSERT INTO person (name, subject_id, grade) VALUES
    ('Jane', 1, 62),
    ('Jane', 2, 70),
    ('Jane', 3, 23),
    ('Joe', 1, 45),
    ('Joe', 2, 85),
    ('Joe', 3, 77),
    ('Peter', 1, 35),
    ('Peter', 2, 63),
    ('Mary', 1, 95),
    ('Dean', 2, 100),
    ('Dana', 3, 97),
    ('Gerorge', 3, 97),
    ('Gerorge', 1, 45);

SELECT * FROM subject;
SELECT * FROM person;

SELECT person.name, subject.name AS subject, person.grade
  FROM person, subject
  WHERE person.subject_id = subject.id;

SELECT person.name, subject.name AS subject, person.grade
  FROM person JOIN subject
  WHERE person.subject_id = subject.id;

SELECT person.name, subject.name AS subject, person.grade
  FROM person INNER JOIN subject
  WHERE person.subject_id = subject.id;

FOREIGN KEY

-- turn on FOREIGN KEY checking
PRAGMA foreign_keys = ON;

CREATE TABLE people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT

);

CREATE TABLE groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    manager INTEGER,
    FOREIGN KEY(manager) REFERENCES people(id)
);

INSERT INTO people (name) VALUES ('Natalie Portman ');
INSERT INTO people (name) VALUES ('Gal Gadot');
INSERT INTO people (name) VALUES ('Cole Lawrence');
INSERT INTO people (name) VALUES ('Lior Raz');
INSERT INTO people (name) VALUES ('Erick Tryzelaar');
INSERT INTO people (name) VALUES ('Ernest Kissiedu');
SELECT * FROM people;
SELECT "-----";

INSERT INTO groups (name, manager) VALUES ('Rust London', (SELECT id FROM people WHERE name = 'Ernest Kissiedu'));
INSERT INTO groups (name, manager) VALUES ('Rust NYC', (SELECT id FROM people WHERE name = 'Cole Lawrence'));
INSERT INTO groups (name, manager) VALUES ('Rust Bay Area', (SELECT id FROM people WHERE name = 'Erick Tryzelaar'));
INSERT INTO groups (name, manager) VALUES ('Other Group', 42);
SELECT * FROM people;
SELECT "";
SELECT * FROM groups;
SELECT "-----";

DELETE FROM people WHERE name = 'Cole Lawrence';
SELECT * FROM people;
SELECT "";
SELECT * FROM groups;
SELECT "-----";

PRAGMA

PRAGMA

Family

PRAGMA foreign_keys = ON;

CREATE TABLE people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    father INTEGER,
    mother INTEGER,
    FOREIGN KEY (father) REFERENCES people(id),
    FOREIGN KEY (mother) REFERENCES people(id)
);

INSERT INTO people (name) VALUES
    ('Abraham'),
    ('Hagar'),
    ('Sarah'),
    ('Keturah'),
    ('Ishmael'),
    ('Isaac'),
    ('Rebecca'),
    ('Jacob');

UPDATE people SET
    father = (SELECT id FROM people WHERE name = 'Abraham'),
    mother =(SELECT id FROM people WHERE name = 'Sarah')
    WHERE name = 'Isaac';

UPDATE people SET
    father = (SELECT id FROM people WHERE name = 'Abraham'),
    mother =(SELECT id FROM people WHERE name = 'Hagar')
    WHERE name = 'Ishmael';

UPDATE people SET
    father = (SELECT id FROM people WHERE name = 'Isaac'),
    mother =(SELECT id FROM people WHERE name = 'Rebecca')
    WHERE name = 'Jacob';

UPDATE people SET father = 32;

SELECT child.name AS Name, father.name AS Father, mother.name AS Mother
    FROM people child, people father, people mother
    WHERE father.id = child.father AND mother.id = child.mother
--    ORDER BY child.id;
    ORDER BY child.name;

-- SELECT child.name AS Name,
--        IFNULL(father.name, 'NULL') AS Father,
--        IFNULL(mother.name, 'NULL') AS Mother
--     FROM people AS child
--     LEFT JOIN people AS father ON father.id = child.father
--     LEFT JOIN people AS mother ON mother.id = child.mother
--     ORDER BY child.id;
--
-- CREATE VIEW family AS
--   SELECT child.name AS Name,
--          IFNULL(father.name, 'NULL') AS Father,
--          IFNULL(mother.name, 'NULL') AS Mother
--       FROM people AS child
--       LEFT JOIN people AS father ON father.id = child.father
--       LEFT JOIN people AS mother ON mother.id = child.mother
--       ORDER BY child.id;
--
-- SELECT * FROM family;
--
-- INSERT INTO people (name) VALUES ('Gabor');
-- SELECT * FROM family;

INDEX and EXPLAIN

.timer on
.import --csv -v examples/big-data.csv pairs
SELECT COUNT(*) FROM pairs;
SELECT * from pairs WHERE name = 'aaaaaaaz';
EXPLAIN SELECT * from pairs WHERE name = 'aaaaaaaz';

CREATE INDEX names ON pairs (name);

SELECT * from pairs WHERE name = 'aaaaaaaz';
EXPLAIN SELECT * from pairs WHERE name = 'aaaaaaaz';


perl generat.pl

$ sqlite3 < examples/bug-data.sql

STRICT Tables

If you really want, you can defined a table to be STRICT, but you need to do that on a per-table basis and the data types you can use are limited.

CREATE TABLE strict_data (
    id INTEGER,
    name TEXT,
    height REAL
) STRICT;

INSERT INTO strict_data (id, name, height) VALUES (1, "foo", 1.8);
INSERT INTO strict_data (id, name, height) VALUES ("2", "bar", "2.1");

INSERT INTO strict_data (id, name, height) VALUES ("three", "Jane", 1.3);
-- Runtime error near line 10: cannot store TEXT value in INTEGER column strict_data.id (19)

SELECT * from strict_data;

Default time

CREATE TABLE login (
    uid INTEGER NOT NULL,
    logged_in DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO login (uid) VALUES (1);
INSERT INTO login (uid, logged_in) VALUES (2, '2026-04-29 08:06:36');

SELECT * from login;

Aggregate: COUNT, AVG, MAX, MIN

Aggregare functions

CREATE TABLE person (
    name TEXT,
    subject TEXT,
    grade INTEGER
);

INSERT INTO person (name, subject, grade) VALUES
    ('Jane',  'Chemistry', 62),
    ('Jane',  'Physics', 70),
    ('Jane',  'Math', 23),
    ('Joe',   'Chemistry', 45),
    ('Joe',   'Physics', 85),
    ('Joe',   'Math', 77),
    ('Peter', 'Chemistry', 35),
    ('Peter', 'Physics', 63),
    ('Mary',  'Chemistry', 95),
    ('Dean', 'Physics', 100),
    ('Dana', 'Math', 97),
    ('George', 'Math', 97),
    ('George', 'Chemistry', 45);


SELECT * FROM person;


SELECT COUNT(*) FROM person;
SELECT "Total", COUNT(*) FROM person;
SELECT "Physycs", COUNT(*) FROM person WHERE subject == "Physics";
SELECT "Failed", COUNT(*) FROM person WHERE grade < 60;

SELECT "Maximum all", MAX(grade) FROM person;
SELECT "Maximum Chemistry", MAX(grade) FROM person WHERE subject == "Chemistry";

SELECT "Average all", AVG(grade) FROM person;
SELECT "Average Chemistry", AVG(grade) FROM person WHERE subject == "Chemistry";
SELECT "Average Physics", AVG(grade) FROM person WHERE subject == "Physics";

$ cat grades.sql  aggregate.sql | sqlite3

SQLite Transaction - in a bank

  • Setup Bank accounts with some initial money.
  • Move some money from one account to another - two separate steps. Worked.
  • Move some money from one account to another - two separate steps - stop in the middle. Failed.
  • Move some money from one account to another - Transaction - stop in the middle. Worked. (money stayed where it was)
  • Remove bank
CREATE TABLE bank (
    name TEXT PRIMARY KEY,
    balance INTEGER NOT NULL
);

INSERT INTO bank (name, balance) VALUES ("Jane", 0);
INSERT INTO bank (name, balance) VALUES ("Mary", 1000);
INSERT INTO bank (name, balance) VALUES ("Ann", 1000);



SELECT * FROM bank;
SELECT "Total", SUM(balance) FROM bank;
SELECT "-----";

UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Mary") - 100 WHERE name = "Mary";
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Jane") + 100 WHERE name = "Jane";


UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Mary") - 100 WHERE name = "Mary";
.exit
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Jane") + 100 WHERE name = "Jane";


BEGIN TRANSACTION;
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Mary") - 100 WHERE name = "Mary";
.exit
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Jane") + 100 WHERE name = "Jane";
COMMIT;

sqlite3 bank.db < setup_bank.sql
sqlite3 bank.db < show.sql

sqlite3 bank.db < transfer.sql
sqlite3 bank.db < show.sql

sqlite3 bank.db < without_transaction.sql
sqlite3 bank.db < show.sql

sqlite3 bank.db < with_transaction.sql
sqlite3 bank.db < show.sql

rm -f bank.db

Jane|0
Mary|1000
Ann|1000
Total|2000
-----
Jane|100
Mary|900
Ann|1000
Total|2000
-----
Jane|100
Mary|800
Ann|1000
Total|1900
-----
Jane|100
Mary|800
Ann|1000
Total|1900
-----
  • TODO: loading a large CSV file into the database and running queries.
  • TODO: creating a multi-tabe database, dumping it and then loading it and running queries against it.
  • TODO: FOREIGN KEY - cascading deletition?

DISTINCT

.import --csv examples/iris.csv iris
SELECT COUNT(species) FROM iris;
SELECT COUNT(DISTINCT species) FROM iris;


$ sqlite3 < handle-iris.sql

LIMIT

.import --csv examples/iris.csv iris
SELECT * FROM iris LIMIT 3;


sqlite3 < limit-iris.sql

Increment value

CREATE TABLE people (
    name TEXT,
    grade INTEGER
);

INSERT INTO people (name, grade) VALUES ('Joe', 40);
INSERT INTO people (name, grade) VALUES ('Jane', 60);
SELECT * from people;
SELECT "-------";

SELECT "Incrementing the grade of Joe by 1";
UPDATE people SET grade = (SELECT grade FROM people WHERE name = "Joe") + 1 WHERE name = "Joe";
SELECT * from people;
SELECT "";

ALTER TABLE after adding data

CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

INSERT INTO person (name) VALUES ('Joe');
INSERT INTO person (name) VALUES ('Jane');

SELECT * FROM person;


ALTER TABLE person ADD COLUMN phone TEXT;

SELECT * FROM person;

INSERT INTO person (name, phone) VALUES ('Marcus', '1234565');

SELECT * FROM person;

ALTER TABLE not NULL

What if we would like to add a column that should be NOT NULL?

We cannot add a column that is NOT NULL as it would immediately violate the integriry of the table.

We can first add the column. Then update the existing rows, then change the column definition.

CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

INSERT INTO person (name) VALUES ('Joe');
INSERT INTO person (name) VALUES ('Jane');

SELECT * FROM person;

-- ALTER TABLE person ADD COLUMN phone TEXT NOT NULL;
-- Error near line 11: Cannot add a NOT NULL column with default value NULL

ALTER TABLE person ADD COLUMN phone TEXT;
UPDATE person SET phone = '123' WHERE name = 'Joe';
UPDATE person SET phone = '567' WHERE name = 'Jane';
SELECT * FROM person;

ALTER TABLE person ALTER phone SET NOT NULL;

-- INSERT INTO person (name) VALUES ('Mary');
-- Error near line 21: NOT NULL constraint failed: person.phone

INSERT INTO person (name, phone) VALUES ('Mary', '890');
SELECT * FROM person;

SQLite in Python

Python comes with the sqlite3 module that wraps (embeds) the C library implementing SQLite.

See SQLite in Python pages