Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 33 ยท Practice and Review

SQLite Exercises with Solutions

Practice SQLite with 14 graded exercises on typing, STRICT tables, dates, queries, upsert, RETURNING, triggers, JSON and indexes. Run each one in your browser.

Reading about SQLite is not the same as writing it, and SQLite has enough quirks (flexible typing, text dates, JSON stored as text, upsert syntax) that only practice makes them stick. This page has 14 exercises that follow the whole course, from "what type did that value end up as?" to "did my query use the index?". Each exercise gives you a task, a live editor with a starter query and a hidden solution.

Every run starts from a fresh in-memory database, so you cannot break anything, even in the exercises that change or delete data. The exercises that use the bookstore sample database say so in the editor title. The rest create their own small tables.

How to practice

Look first Run the starter and read the columns.
Build in steps Add one clause at a time and re-run.
Compare results Your query may differ and still be right.
Peek last Open the solution after an honest try.

Exercises 1 to 14 get harder as you go.

1 / 2

The comment at the top of each editor tells you what the finished result looks like, so you can check your own work. If you get stuck on a topic, the lesson links in each group lead back to the explanation.

Types and strict tables

These practice Dynamic Typing and Type Affinity and Constraints and STRICT Tables.

1. Who ended up as what? (Beginner)

The table below has an INTEGER column, but the values inserted are a mixed bag. Show each value together with the storage class SQLite chose for it, using typeof(). Predict the answer for '9.0' before you run it.

SQL Exercise 1: Storage classes

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
SELECT v, typeof(v) AS type
FROM sample;

INTEGER affinity converts text that looks like a number, and '9.0' is exactly the whole number 9, so it is stored as an integer. 'ten' cannot be converted, so SQLite keeps it as text instead of complaining. 11.5 has a fractional part and stays a real number.

2. Make the database say no (Beginner)

Create a table temps with a required city (text) and a celsius reading (real) that must lie between -90 and 60. Make it a STRICT table so the types are enforced. Then insert three rows and show them with the storage class of celsius.

SQL Exercise 2: A strict table

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
CREATE TABLE temps (
  city    TEXT NOT NULL,
  celsius REAL CHECK (celsius BETWEEN -90 AND 60)
) STRICT;

INSERT INTO temps VALUES ('Oslo', -3.5), ('Cairo', 31), ('Lima', '19');

SELECT city, celsius, typeof(celsius) AS type FROM temps;

Even a strict table converts '19' to the real number 19.0 because the conversion loses nothing. A value such as 'warm' would fail with cannot store TEXT value in REAL column temps.celsius, and 100 would fail the CHECK.

Dates

These practice Dates, Times and Booleans.

3. Orders per month (Beginner)

In the bookstore database, count the orders and the total copies sold for each month. Show the month as 2024-02, oldest month first.

SQL Exercise 3: Orders per month ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
SELECT strftime('%Y-%m', ordered_on) AS month,
       count(*)      AS orders,
       sum(quantity) AS copies
FROM orders
GROUP BY month
ORDER BY month;

Dates are plain YYYY-MM-DD text, so strftime can cut them into any shape. SQLite lets you group by the alias month.

4. Days as a member (Intermediate)

For each customer, work out how many whole days passed between the day they joined and 2024-08-01. List the longest-standing member first. (Use a fixed date so the answer never changes.)

SQL Exercise 4: Days as a member ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
SELECT name,
       joined,
       CAST(julianday('2024-08-01') - julianday(joined) AS INTEGER) AS days_member
FROM customers
ORDER BY days_member DESC;

julianday() turns a date into a day count with a fractional part, so subtracting two of them gives days. The CAST drops the .0.

Querying

These practice Querying in SQLite, Built-in Functions, Joins, Subqueries and CTEs and Window Functions.

5. A shelf per author (Intermediate)

Show each author's name next to all of their book titles in one text cell, titles in alphabetical order and separated by a comma and a space. Sort the authors by name.

SQL Exercise 5: Titles per author ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
SELECT a.name,
       group_concat(b.title, ', ' ORDER BY b.title) AS titles
FROM authors a
JOIN books b ON b.author_id = a.id
GROUP BY a.id
ORDER BY a.name;

group_concat takes the separator as its second argument. Since SQLite 3.44 it also accepts an ORDER BY inside the call, which makes the order of the pieces dependable.

6. The priciest book of each genre (Intermediate)

For every genre, show only its most expensive book (genre, title, price), ordered by genre.

SQL Exercise 6: Top book per genre ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
WITH ranked AS (
  SELECT genre, title, price,
         row_number() OVER (PARTITION BY genre ORDER BY price DESC) AS rn
  FROM books
)
SELECT genre, title, price
FROM ranked
WHERE rn = 1
ORDER BY genre;

A window function cannot appear in WHERE, so rank inside a CTE (or subquery) and filter outside it. Use rank() instead of row_number() if ties should all be kept.

7. The biggest spender (Intermediate)

Which customer spent the most money in total (quantity times book price, rounded to two decimals)? Show the customer's name and the amount.

SQL Exercise 7: Biggest spender ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
SELECT c.name,
       round(sum(o.quantity * b.price), 2) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN books b ON b.id = o.book_id
GROUP BY c.id
ORDER BY spent DESC
LIMIT 1;

Join the three tables, multiply inside sum(), group per customer and keep the top row. Rounding at the end avoids tiny floating-point tails such as 43.95000000000001.

8. A week of dates (Advanced)

Use a recursive CTE to produce the seven days from 2024-03-04 to 2024-03-10, and label each one weekday, Sat or Sun.

SQL Exercise 8: A calendar week

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
WITH RECURSIVE week(d) AS (
  SELECT '2024-03-04'
  UNION ALL
  SELECT date(d, '+1 day') FROM week WHERE d < '2024-03-10'
)
SELECT d,
       CASE strftime('%w', d)
         WHEN '0' THEN 'Sun'
         WHEN '6' THEN 'Sat'
         ELSE 'weekday'
       END AS kind
FROM week;

The recursive part adds one day to the previous row until the WHERE condition stops it. strftime('%w', ...) returns the weekday as text, 0 for Sunday through 6 for Saturday, so compare it with text values.

Changing data

These practice INSERT, UPDATE and DELETE and UPSERT and RETURNING.

9. A page counter (Intermediate)

Record page views. Each call must create the row with hits = 1 if the page is new, or add one to the existing counter. Run the call for /home three times and for /about once, then list the counters. Use a single statement shape for every call.

SQL Exercise 9: Counter with upsert

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
INSERT INTO page_views (page, hits) VALUES ('/home', 1)
  ON CONFLICT (page) DO UPDATE SET hits = hits + 1;
INSERT INTO page_views (page, hits) VALUES ('/home', 1)
  ON CONFLICT (page) DO UPDATE SET hits = hits + 1;
INSERT INTO page_views (page, hits) VALUES ('/about', 1)
  ON CONFLICT (page) DO UPDATE SET hits = hits + 1;
INSERT INTO page_views (page, hits) VALUES ('/home', 1)
  ON CONFLICT (page) DO UPDATE SET hits = hits + 1;

The first /home call inserts the row. The next two collide on the primary key, so DO UPDATE runs and hits = hits + 1 uses the stored value. Using INSERT OR REPLACE here would reset the counter every time.

10. Price rise with UPDATE FROM (Intermediate)

Raise the price of every book by authors from Sweden by 10 percent, rounded to two decimals, using the author's country from the authors table. Then show that author's books.

SQL Exercise 10: UPDATE FROM ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
UPDATE books
SET price = round(price * 1.1, 2)
FROM authors
WHERE books.author_id = authors.id
  AND authors.country = 'Sweden';

SELECT title, price FROM books WHERE author_id = 2;

UPDATE ... FROM (SQLite 3.33 and later) lets the WHERE clause use columns of another table. Only books is changed. Qualify the columns so SQLite knows which table you mean.

11. Delete and report (Intermediate)

Delete every book that is out of stock and get the deleted titles back from the same statement, without a separate SELECT.

SQL Exercise 11: DELETE with RETURNING ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
DELETE FROM books
WHERE stock = 0
RETURNING title;

RETURNING works on DELETE too and shows the rows that were removed. Remember that it is a data-changing statement even though it prints rows.

Triggers and JSON

These practice Views and Triggers and Generated Columns and JSON.

12. An automatic price log (Advanced)

Write a trigger on products that records the product id, old price and new price in price_log whenever a price actually changes (not when an update leaves it the same). The three updates below should leave exactly two log rows.

SQL Exercise 12: Audit trigger

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
CREATE TRIGGER log_price
AFTER UPDATE OF price ON products
WHEN OLD.price <> NEW.price
BEGIN
  INSERT INTO price_log VALUES (NEW.id, OLD.price, NEW.price);
END;

Create the trigger before the updates. The WHEN clause skips the second update, which wrote the same price. Inside the body, OLD and NEW are the row before and after the change.

13. Mining JSON (Advanced)

The events table stores each event as JSON text. Do two things: sum the amount of all buy events per user, then count how often each tag appears across all events.

SQL Exercise 13: JSON events

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
-- part 1
SELECT payload ->> '$.user' AS usr,
       sum(payload ->> '$.amount') AS total
FROM events
WHERE payload ->> '$.action' = 'buy'
GROUP BY usr
ORDER BY usr;

-- part 2
SELECT j.value AS tag, count(*) AS uses
FROM events e, json_each(e.payload, '$.tags') AS j
GROUP BY j.value
ORDER BY uses DESC, tag;

The ->> operator returns SQL values (text or numbers), which is what you want for grouping and summing. json_each is a table-valued function: it turns the tags array of each row into one row per element.

Indexes

This practices Indexes and EXPLAIN QUERY PLAN and the Optimizer.

14. Fix the scan (Advanced)

The table has 1,000 rows. First show the query plan for the lookup by email and note that SQLite scans the whole table. Then add an index and show the plan again: it should now say SEARCH ... USING INDEX.

SQL Exercise 14: Index and plan

Press Run (or Ctrl + Enter) to see the result here.

Show solution
SQL
CREATE INDEX idx_t_email ON t (email);

EXPLAIN QUERY PLAN
SELECT * FROM t WHERE email = '[email protected]';

Without the index SQLite reads every row (SCAN t). With it, SQLite jumps straight to the match (SEARCH t USING INDEX idx_t_email (email=?)). A plan shows only the detail text that matters; the other columns are internal numbers that change between versions.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A column declared INTEGER receives the text 'ten'. In a normal (non-STRICT) table, what happens?

    Affinity only converts values that look like numbers. Anything else is kept as it is, unless the table is STRICT.
  2. 2. Why does exercise 6 rank rows in a CTE instead of filtering row_number() = 1 directly in WHERE?

    WHERE is applied before window functions are computed, so you compute the rank in an inner query and filter in the outer one.
  3. 3. What is the main danger of INSERT OR REPLACE for the page counter?

    REPLACE is delete-then-insert, so omitted or recomputed columns do not build on the stored value.
  4. 4. In EXPLAIN QUERY PLAN output, what does SCAN t tell you?

    SCAN means a full pass over the table, while SEARCH means SQLite used an index or the rowid to jump to matching rows.

Summary

  • Affinity converts only lossless, number-looking text; STRICT tables enforce the declared type, and CHECK adds your own rules.
  • Dates are text: strftime, date and julianday slice and compare them.
  • Filter window-function results from an outer query; group_concat can take an inner ORDER BY on modern SQLite.
  • ON CONFLICT DO UPDATE builds on the stored row, RETURNING reports what a statement changed, and UPDATE ... FROM joins while updating.
  • Triggers with a WHEN clause log only real changes; ->> and json_each pull values out of JSON text.
  • EXPLAIN QUERY PLAN shows SCAN before and SEARCH ... USING INDEX after a useful index.

Next: SQLite Interview Questions, the concepts behind all of this in short answers.