SQLite Tutorial

1. Getting Started

SQLite is a self-contained, serverless, zero-configuration SQL database engine. The entire database is a single file — bookshop.db (44 KB).

sqlite3 bookshop.db

Once inside the SQLite CLI, try these dot-commands:

.tables       -- list all tables
.schema books -- show CREATE statement for a table
.headers on   -- show column headers in query output
.mode column  -- format output as columns

2. Schema Overview

The bookshop database has 7 tables connected by foreign keys:

authors  ──┐
            │
publishers ─┼── books ─── order_items ── orders ── customers
            │       │
            └───────└── reviews ────────────────┘

authors

publishers

books

customers

orders & order_items

reviews

3. Basic Queries

SELECT & FROM

Retrieve rows and columns from a table.

-- All columns, all rows
SELECT * FROM books;

-- Specific columns
SELECT title, price FROM books;

-- Aliased columns
SELECT title, price * 1.1 AS price_with_tax FROM books;

WHERE

Filter rows with conditions.

-- Comparison
SELECT title, price FROM books WHERE price < 10;

-- Text match
SELECT * FROM authors WHERE nationality = 'British';

-- Multiple conditions
SELECT * FROM books
WHERE publication_year > 1950 AND price < 12;

ORDER BY

Sort results.

-- Ascending (default)
SELECT title, price FROM books ORDER BY price;

-- Descending
SELECT title, publication_year FROM books ORDER BY publication_year DESC;

-- Multiple sort keys
SELECT title, price, publication_year FROM books
ORDER BY price DESC, publication_year ASC;

LIMIT & OFFSET

Restrict the number of rows returned.

-- Top 3 most expensive books
SELECT title, price FROM books ORDER BY price DESC LIMIT 3;

-- Skip 2, return next 3 (pagination)
SELECT title, price FROM books ORDER BY price DESC LIMIT 3 OFFSET 2;

The Fellowship of the Ring    15.99
The Hobbit                    14.99
One Hundred Years of Solitude 13.99

4. Filtering

IN

SELECT title, author_id FROM books
WHERE author_id IN (1, 3, 4);

BETWEEN

SELECT title, publication_year FROM books
WHERE publication_year BETWEEN 1900 AND 1950;

LIKE

-- % matches any sequence, _ matches a single character
SELECT title FROM books WHERE title LIKE 'Harry%';
SELECT title FROM books WHERE title LIKE '%and%';

IS NULL

-- Check for missing data
SELECT * FROM authors WHERE birth_year IS NULL;

DISTINCT

SELECT DISTINCT nationality FROM authors;

British
American
Colombian

5. Aggregation

Aggregate functions combine multiple rows into a single result.

COUNT, SUM, AVG, MIN, MAX

SELECT COUNT(*)          AS total_books,
       ROUND(AVG(price), 2) AS avg_price,
       MIN(price)        AS cheapest,
       MAX(price)        AS priciest
FROM books;

GROUP BY

Group rows that share a value, then aggregate per group.

-- Number of books per author
SELECT author_id, COUNT(*) AS book_count
FROM books
GROUP BY author_id
ORDER BY book_count DESC;

-- Average rating per book (join needed for title)
SELECT b.title, ROUND(AVG(r.rating), 2) AS avg_rating
FROM books b
JOIN reviews r ON b.book_id = r.book_id
GROUP BY b.book_id
ORDER BY avg_rating DESC;

HAVING

Filter groups after aggregation (WHERE filters before grouping).

-- Authors with more than 1 book
SELECT author_id, COUNT(*) AS cnt
FROM books
GROUP BY author_id
HAVING cnt > 1;

author_id  cnt
1          2    (George Orwell)
3          2    (J.K. Rowling)
4          2    (J.R.R. Tolkien)
10         2    (Agatha Christie)

6. Joins

Joins combine rows from two or more tables based on a related column.

INNER JOIN

Returns rows with matching keys in both tables.

-- Books with their author names
SELECT b.title, a.name AS author
FROM books b
JOIN authors a ON b.author_id = a.author_id;

LEFT JOIN

Returns all rows from the left table, with NULLs where no match exists on the right.

-- All customers and their orders (NULL if no order)
SELECT c.name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.name;

Multi-Table Joins

-- Order details with customer, book, and author info
SELECT
  o.order_id,
  c.name              AS customer,
  b.title             AS book,
  oi.quantity,
  oi.unit_price,
  a.name              AS author
FROM orders o
JOIN customers c     ON o.customer_id  = c.customer_id
JOIN order_items oi  ON o.order_id     = oi.order_id
JOIN books b         ON oi.book_id     = b.book_id
JOIN authors a       ON b.author_id    = a.author_id
ORDER BY o.order_id;

7. Subqueries

A subquery is a SELECT nested inside another query.

Subquery in WHERE

-- Books by authors born before 1900
SELECT title FROM books
WHERE author_id IN (
  SELECT author_id FROM authors
  WHERE birth_year < 1900
);

Subquery in SELECT

-- Each book with its average rating
SELECT b.title,
  (SELECT ROUND(AVG(r.rating), 2)
   FROM reviews r
   WHERE r.book_id = b.book_id) AS avg_rating
FROM books b;

EXISTS

-- Customers who have placed at least one order
SELECT name, email
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

8. Writing Data

INSERT

-- Add a new author
INSERT INTO authors (name, birth_year, nationality)
VALUES ('Neil Gaiman', 1960, 'British');

-- Add a new book
INSERT INTO books (title, isbn, publication_year, price, author_id, publisher_id)
VALUES ('American Gods', '978-0380789030', 2001, 14.99, 11, 1);

UPDATE

-- Update a price
UPDATE books SET price = 12.99
WHERE title = 'Animal Farm';

DELETE

-- Remove a review
DELETE FROM reviews WHERE review_id = 3;

INSERT OR REPLACE

SQLite-specific: insert a row or replace it if a UNIQUE constraint is violated.

INSERT OR REPLACE INTO books (book_id, title, price)
VALUES (1, '1984 (Special Edition)', 14.99);

INSERT OR IGNORE

INSERT OR IGNORE INTO authors (name, birth_year, nationality)
VALUES ('Neil Gaiman', 1960, 'British');  -- skipped, already exists

9. SQLite Gems

Date & Time Functions

-- Today's date
SELECT DATE('now');

-- Orders placed in 2024
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Customers who joined in the last 90 days (relative)
SELECT name, joined_date
FROM customers
WHERE joined_date >= DATE('now', '-90 days');

String Functions

-- Uppercase and length
SELECT UPPER(title), LENGTH(title) FROM books;

-- Substring
SELECT SUBSTR(title, 1, 10) AS short_title FROM books;

Views

A view is a saved SELECT query that acts like a virtual table.

CREATE VIEW book_details AS
SELECT
  b.title, b.price, b.publication_year,
  a.name  AS author,
  p.name  AS publisher
FROM books b
JOIN authors a    ON b.author_id    = a.author_id
JOIN publishers p ON b.publisher_id = p.publisher_id;

-- Query the view like a table
SELECT * FROM book_details WHERE price > 12;

Indexes

Indexes speed up lookups on frequently-queried columns.

CREATE INDEX idx_books_author ON books(author_id);
CREATE INDEX idx_reviews_book ON reviews(book_id);

AUTOINCREMENT

SQLite automatically assigns rowid values for INTEGER PRIMARY KEY columns. Adding AUTOINCREMENT guarantees never-reused IDs (at a small performance cost).

Transactions

Group multiple writes into an atomic unit — all succeed or all roll back.

BEGIN TRANSACTION;

UPDATE books SET price = price * 1.1;
INSERT INTO authors (name) VALUES ('New Author');

-- COMMIT;     -- save changes
-- ROLLBACK;   -- discard changes

10. Example Queries

📋 Top-rated books (4+ stars, at least 1 review)

SELECT b.title,
       ROUND(AVG(r.rating), 2) AS avg_rating,
       COUNT(r.review_id)      AS review_count
FROM books b
JOIN reviews r ON b.book_id = r.book_id
GROUP BY b.book_id
HAVING avg_rating >= 4
ORDER BY avg_rating DESC;

📋 Customer lifetime value (total spent)

SELECT c.name,
       COUNT(DISTINCT o.order_id) AS orders,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS total_spent
FROM customers c
JOIN orders o       ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id    = oi.order_id
GROUP BY c.customer_id
ORDER BY total_spent DESC;

📋 Books that have never been ordered

SELECT b.title, b.price
FROM books b
LEFT JOIN order_items oi ON b.book_id = oi.book_id
WHERE oi.book_id IS NULL;

📋 Author popularity (total copies sold)

SELECT a.name,
       SUM(oi.quantity) AS copies_sold
FROM authors a
JOIN books b       ON a.author_id = b.author_id
JOIN order_items oi ON b.book_id  = oi.book_id
GROUP BY a.author_id
ORDER BY copies_sold DESC;

📋 Monthly sales in 2024

SELECT STRFTIME('%Y-%m', o.order_date) AS month,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY month
ORDER BY month;

📋 Customers who reviewed a book they bought

SELECT DISTINCT c.name, b.title
FROM customers c
JOIN reviews r      ON c.customer_id = r.customer_id
JOIN books b        ON r.book_id     = b.book_id
JOIN order_items oi ON b.book_id     = oi.book_id
JOIN orders o       ON oi.order_id   = o.order_id
WHERE o.customer_id = c.customer_id;

→ Take the Quiz ←