1 Databases
2 Tables
3 Keys
4 Adding, Editing and Removing Data
5 Outputting a Table
6 Inputting a Table
7 Basic Querying
8 Filtering
9 Aggregating
10 Grouping and Pivoting
11 Join Tables
12 Statements Inside Statements

This is Part 3 in a series on SQLite:

Introduction
Worked Example
Cheat Sheet

Open SQLite in the terminal:

$ sqlite3

Exit out of SQLite:

.quit
# or
.exit

You can also use Ctrl+D

Remember, you can press the up arrow in the terminal to recall a previous command

1 Databases

Open a database, creating it if it doesn’t exist:
- If a different database is already open, this command closes it and opens the new one
- Use .open :memory: to create an in-memory database
```
.open database.db
```
Attach another database:
Allows access using a prefix, e.g. SELECT * FROM other.table_name;
```
ATTACH DATABASE 'database2.db' AS other;
```
Detach an attached database:
```
DETACH DATABASE other;
```
List open/attached databases:
```
.databases
```
Export current database to a file:
Also used to save an in-memory database to disk
```
.save database.db
```
Check schema of all tables:
```
.schema
```
…or for a specific attached database:
```
.schema other.table_name
```

2 Tables

Create a new table (with no column constraints):
- With one column:
```
CREATE TABLE table_name (column_1 data_type);
```
- With multiple columns:
```
CREATE TABLE table_name (
  column_1 data_type,
  column_2 data_type,
  column_3 data_type
);
```
  Common SQLite data types:
  INTEGER: whole number
  REAL: decimal number
  TEXT: string (use single or double quotes)
  DATE: date in 'YYYY-MM-DD' format
  BLOB: binary large object (e.g. images, files)
List all tables in all open databases:
```
.tables
```
Show the CREATE TABLE statement that was used:
```
.schema table_name
```
Show table structure (column names and types):
```
PRAGMA table_info(table_name);
```

Rename a table:

ALTER TABLE old_name RENAME TO new_name;

Delete (drop) a table:
```
DROP TABLE table_name;
```

Columns:

Add a column:

ALTER TABLE table_name ADD COLUMN new_column data_type;

Rename a column:

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Delete (drop) a column:
You cannot delete the last column in a table
```
ALTER TABLE table_name DROP COLUMN column_name;
```

Create a new table (with column constraints):
- Prevent NULL values from being added in a particular column:
```
CREATE TABLE table_name (
  column_1 data_type NOT NULL
);
```
- Define a default value to be added if no explicit value is given:
```
CREATE TABLE table_name (
  column_1 data_type DEFAULT 'default_value'
);
```
- Prevent duplicate values from being added in a particular column:
```
CREATE TABLE table_name (
  column_1 data_type UNIQUE
);
```
- Have the column’s values be able to uniquely identify the rows:
```
CREATE TABLE table_name (
  column_1 data_type PRIMARY KEY
);
```
  - There can be multiple UNIQUE columns in a table but no more than one PRIMARY KEY column
  - More info on the PRIMARY KEY constraint below:

3 Keys

Types of keys:
- Primary key: A column (or columns) that uniquely identifies each row in a table
  - Only one primary key per table
  - Cannot contain NULL values
- Composite key: A primary key made from two or more columns that together form a unique identifier
- Foreign key: A column that refers to the primary key in another table, creating a relationship between the two
- Candidate key: Any column or set of columns that could serve as a primary key
  - A table can have multiple candidate keys
  - One is chosen as the primary key; the others are called alternate keys
- Super key: Any combination of columns that uniquely identifies a row, possibly including extra columns
  - Candidate keys are minimal super keys (i.e. no redundant columns)
- Secondary key: A less common term, typically referring to non-unique columns used for filtering or indexing
  - Note: In relational databases, this term is rarely formalized

Create a table with a primary key:

CREATE TABLE table_name (
  id INTEGER PRIMARY KEY
);

Create a table with a composite primary key:

CREATE TABLE table_name (
  col1 INTEGER,
  col2 TEXT,
  PRIMARY KEY (col1, col2)
);

Create a table with a foreign key:

CREATE TABLE table_name (
  othertable_id INTEGER,
  FOREIGN KEY (othertable_id) REFERENCES othertable(id)
);

Enforce a one-to-one relationship (foreign key must be unique):

CREATE TABLE table_name (
  othertable_id INTEGER UNIQUE,
  FOREIGN KEY (othertable_id) REFERENCES othertable(id)
);

Notes:

Foreign keys must be explicitly enabled in SQLite:
```
PRAGMA foreign_keys = ON;
```
To check if foreign keys are enabled:
```
PRAGMA foreign_keys;
```
To view foreign key constraints on a table:
```
PRAGMA foreign_key_list(table_name);
```

SQLite doesn’t enforce strict data types for keys, but it is best practice to use matching types between referenced keys.

4 Adding, Editing and Removing Data

Insert a new row:
- Insert a value into one column (other columns will be set to NULL):
```
INSERT INTO table_name (column_name)
VALUES (value);
```
  - Change the value used for nulls instead of NULL with:
```
.nullvalue value
```
- Insert values into multiple columns:
```
INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, value_2, value_3);
```
- Insert multiple rows in a single statement (SQLite 3.7.11+):
```
INSERT INTO table_name (column_1, column_2)
VALUES (value_1a, value_2a),
       (value_1b, value_2b),
       (value_1c, value_2c);
```
- Insert all values in order (omit column names):
  Only safe if you know the column order — not recommended for production code
```
INSERT INTO table_name
VALUES (value_1, value_2, value_3);
```
Replace a row (insert or update):
- Inserts a row or overwrites the existing one if the primary key already exists:
  Note: This deletes the existing row first, which may trigger ON DELETE constraints.
```
REPLACE INTO table_name (id, column_1, column_2)
VALUES (1, value_1, value_2);
```

Insert a row only if it doesn’t conflict with existing data (ignore conflict):

INSERT OR IGNORE INTO table_name (column_1, column_2)
VALUES (value_1, value_2);

Insert a row and update if a conflict occurs (UPSERT):
Requires SQLite 3.24.0+

INSERT INTO table_name (id, column_1)
VALUES (1, 'value')
ON CONFLICT(id) DO UPDATE SET column_1 = 'new_value';

Update existing data:

Change the value of a column for rows matching a condition:

UPDATE table_name
SET column_1 = new_value
WHERE column_2 = value;

Update multiple columns at once:

UPDATE table_name
SET column_1 = value_1,
    column_2 = value_2
WHERE id = some_id;

Cases:
Use CASE to return different values based on conditions in a column.

SELECT column_1,
  CASE
    WHEN column_2 = 5 THEN 'Great'
    WHEN column_2 >= 4 THEN 'Good'
    WHEN column_2 >= 3 THEN 'Average'
    WHEN column_2 >= 2 THEN 'Ok'
    ELSE 'Poor'
  END AS 'new_column_name'
FROM table_name;

Delete rows:
- Delete rows matching a condition:
```
DELETE FROM table_name
WHERE column_name = value;
```
- Delete all rows from a table (the table remains):
```
DELETE FROM table_name;
```

5 Outputting a Table

Display a table in the terminal:
- Use the SELECT query to select everything from a table as your output:
```
SELECT * FROM table_name;
```
- By default, output (in this case, everything from a table) is printed to the terminal using the current display mode.
- You can change the display format using the .mode command which sets the format for query results:
  - Column (default) – neat columns for terminal output:
```
.mode column
```
    - Adjust column width (only affects .mode column):
      Specify the width for each column (in order).
```
.width 20 30 15
```
  - List – each row printed vertically (one column per line):
```
.mode list
```
  - Line – similar to list, but formatted for readability:
```
.mode line
```
  - CSV – comma-separated values:
```
.mode csv
```
  - Tabs – tab-separated values (TSV):
```
.mode tabs
```
  - HTML – results wrapped in <table> tags:
```
.mode html
```
  - JSON – each row as a JSON object (SQLite 3.38.0+):
```
.mode json
```
  - Insert – generates INSERT statements:
```
.mode insert table_name
```
  - Quote – quoted CSV (useful for importing safely):
```
.mode quote
```
- Show column headers in output (on by default):
```
.headers on
```
- Turn off column headers:
```
.headers off
```
Control where output is sent:
- Send output to terminal (default):
  With this setting on, query results get displayed in the terminal
```
.output stdout
```
- Send output to a file:
  Don’t forget to reset .output back to stdout to see results in the terminal again.
```
.output filename.csv
SELECT * FROM table_name;
.output stdout
```

Outputting a table to a file:

Export as CSV:

-- Include the column headers
.headers on
-- Change the display mode
.mode csv
-- Redirect the output
.output table.csv
-- Select all rows and columns
SELECT * FROM table_name;
-- Redirect the output back to what it was
.output stdout
-- Change the display mode back to what it was
.mode column

Export as HTML:

.mode html
.output table.html
SELECT * FROM table_name;
.output stdout

Export as SQL insert statements:

.mode insert table_name
.output table.sql
SELECT * FROM table_name;
.output stdout

Alternatively, using .dump:

.mode insert table_name
.output table.sql
.dump
.output stdout

Notes:

All .mode and .output commands must be run before the SELECT statement to take effect.
You can see the current settings with .show

6 Inputting a Table

Import a table:
- From an SQL file:
  Runs SQL commands from a .sql file, typically containing CREATE TABLE and INSERT statements:
```
.read path_to_file.sql
```
- From a CSV file:
  Creates and populates a table from a CSV file:
  - The file must be plain CSV (no Excel formatting)
  - The first line is assumed to be column headers unless you specify otherwise
  - If the table doesn’t exist yet, it will be created automatically using the CSV header row
```
.mode csv
.import path_to_file.csv table_name
```

7 Basic Querying

Display one column:
```
SELECT column_name FROM table_name;
```

Display multiple columns:

SELECT column_1, column_2, column_3 FROM table_name;

Display all columns, but only some rows:
```
SELECT * FROM table_name
LIMIT 10;
```

Select a column and assign it an alias:

SELECT column_name AS 'alias' FROM table_name;

Remove duplicate values:

SELECT DISTINCT column_name FROM table_name;

Round values in a column (e.g. to 2 decimal places):
```
SELECT column_1, ROUND(column_2, 2) FROM table_name;
```

Sort a table:

Sort ascending (default):

SELECT * FROM table_name ORDER BY column_name;

Sort descending:

SELECT * FROM table_name ORDER BY column_name DESC;

8 Filtering

Filter out NULL values:

SELECT * FROM table_name
WHERE column_name IS NOT NULL;

Filter using comparison operators:
```
SELECT * FROM table_name
WHERE column_name > value;
```
Comparison operators:
=: equal to
!=: not equal to
>: greater than
<: less than
>=: greater than or equal to
<=: less than or equal to

Filter within a range using BETWEEN:

SELECT * FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;

Filter using multiple conditions:

SELECT * FROM table_name
WHERE column_1 > value AND column_2 < value;

SELECT * FROM table_name
WHERE column_1 BETWEEN value_1 AND value_2
  AND column_2 > value_3;

SELECT * FROM table_name
WHERE column_1 BETWEEN value_1 AND value_2
   OR column_2 > value_3;

Logical operators:
AND: both conditions must be true
OR: at least one condition must be true

Filter using pattern matching (LIKE):

SELECT * FROM table_name
WHERE column_name LIKE 'te_t';    -- Matches 'test', 'text', etc.

SELECT * FROM table_name
WHERE column_name LIKE '%substring%';    -- Matches any text containing 'substring'

Filter and sort together:

SELECT * FROM table_name
WHERE column_1 > value
ORDER BY column_2 DESC;

9 Aggregating

Count rows:

SELECT COUNT(*) FROM table_name;

SELECT COUNT(*) FROM table_name
WHERE column_name = value;

Sum values:

SELECT SUM(column_name) FROM table_name;

SELECT SUM(column_1) FROM table_name
WHERE column_2 = value;

Get the maximum value:

SELECT MAX(column_name) FROM table_name;

SELECT MAX(column_1) FROM table_name
WHERE column_2 = value;

Get the minimum value:

SELECT MIN(column_name) FROM table_name;

SELECT MIN(column_1) FROM table_name
WHERE column_2 = value;

Get the average value:

SELECT AVG(column_name) FROM table_name;

SELECT AVG(column_1) FROM table_name
WHERE column_2 = value;

10 Grouping and Pivoting

Group and show all columns:

SELECT * FROM table_name
GROUP BY column_name;

Show average per group and sort by group:

SELECT column_1, AVG(column_2) FROM table_name
GROUP BY column_1
ORDER BY column_1;

Use positional references in GROUP BY or ORDER BY:
Here, 1 refers to the first column in the SELECT clause, 2 to the second, etc.
```
SELECT column_1, AVG(column_2) FROM table_name
GROUP BY 1
ORDER BY 1;
```

Filter before grouping using WHERE:

SELECT column_1, AVG(column_2) FROM table_name
WHERE column_3 > value
GROUP BY column_1
ORDER BY column_1;

Filter after grouping using HAVING:

SELECT column_1, COUNT(column_2) FROM table_name
GROUP BY column_1
HAVING COUNT(column_2) > value;

Note: HAVING is used after aggregation (e.g. to filter groups), while WHERE is used before aggregation (to filter rows).

11 Join Tables

Join via an inner join:

Join two tables and select all columns:

SELECT * FROM table_1
JOIN table_2 ON table_1.column_1 = table_2.column_1;

Join two tables and select one column from each:

SELECT table_1.column_2, table_2.column_3 FROM table_1
JOIN table_2 ON table_1.column_1 = table_2.column_1;

Join two tables and filter results:

SELECT * FROM table_1
JOIN table_2 ON table_1.column_1 = table_2.column_1
WHERE table_1.column_2 = value;

Join via a left join:
Returns all rows from the left table and matching rows from the right (or NULL if no match)
```
SELECT * FROM table_1
LEFT JOIN table_2 ON table_1.column_1 = table_2.column_1;
```
Join via a cross join (Cartesian product):
Every row in table_1 is paired with every row in table_2
- Select all columns:
```
SELECT * FROM table_1
CROSS JOIN table_2;
```
- Select specific columns from both tables:
```
SELECT table_1.column_1, table_2.column_2 FROM table_1
CROSS JOIN table_2;
```
Append rows from one table to another (UNION):
- Combine two tables with the same structure into one:
```
SELECT * FROM table_1
UNION
SELECT * FROM table_2;
```
  Note: UNION removes duplicates. Use UNION ALL to include all rows, even if duplicated.

12 Statements Inside Statements

Use WITH to define a subquery that can be reused later in the main query: This is also known as a Common Table Expression (CTE), useful for breaking complex queries into logical steps.

WITH previous_results AS (
   SELECT column_1 AS 'alias' FROM table_1
   UNION
   SELECT * FROM table_2
)
SELECT * FROM previous_results
JOIN table_3 ON table_3.column_2 = previous_query.alias;

You can also nest subqueries directly inside SELECT, FROM, or WHERE clauses:

SELECT *
FROM table_1
WHERE column_1 IN (
  SELECT column_1 FROM table_2
  WHERE column_2 > value
);

Use subqueries for filtering, aggregating, or creating reusable logic within queries.

⇦ Back

SQLite:Cheat Sheet