Practice

SQL exercises

Make a change, predict what it will do, and compare the result.

1
SQL SELECT
Read rows from a table.
Adapt the lesson example. Change one value or element, predict the result, and explain why it changes.
Review lesson →Open compiler
Show starting example
SQL
```
SELECT name, score
FROM students
WHERE score >= 80
ORDER BY score DESC;
```
2
SQL Aggregates and GROUP BY
Summarize many rows into one row per group.
Show only the items whose total quantity is more than 5.
Reveal answer and reasoning
Add HAVING SUM(qty) > 5 after GROUP BY item. Only pen|15|2 remains.
Review lesson →Open compiler
Show starting example
SQL
```
CREATE TABLE sales (item TEXT, qty INTEGER, price REAL);
INSERT INTO sales VALUES ('pen', 10, 1.5), ('pen', 5, 1.5), ('book', 2, 12.0);

SELECT item, SUM(qty) AS total_qty, COUNT(*) AS orders
FROM sales
GROUP BY item
ORDER BY item;
```

SQL Joins

Combine rows from two tables that share a key.

List every author even if they have no books.

Reveal answer and reasoning

Start from authors and use LEFT JOIN books ON books.author_id = authors.id. Authors without books get NULL in the title column.

Review lesson →Open compiler

Show starting example

SQL

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER);
INSERT INTO authors VALUES (1, 'Ada'), (2, 'Grace');
INSERT INTO books VALUES (1, 'Notes', 1), (2, 'Compilers', 2), (3, 'Drafts', 1);

SELECT authors.name, books.title
FROM books
JOIN authors ON authors.id = books.author_id
ORDER BY authors.name, books.title;

SQL INSERT, UPDATE, and DELETE

Add, change, and remove rows, then check the result.

Mark every task as not done again.

Reveal answer and reasoning

UPDATE tasks SET done = 0; with no WHERE, the change applies to all rows.

Review lesson →Open compiler

Show starting example

SQL

CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT, done INTEGER DEFAULT 0);
INSERT INTO tasks (title) VALUES ('write'), ('test'), ('ship');
UPDATE tasks SET done = 1 WHERE title = 'write';
DELETE FROM tasks WHERE title = 'ship';

SELECT id, title, done FROM tasks ORDER BY id;

SQL: constraints keep the data valid

Use PRIMARY KEY, NOT NULL, UNIQUE, CHECK, and REFERENCES so the database itself rejects bad rows.

What would the count of employees be with PRAGMA foreign_keys = ON at the start?

Reveal answer and reasoning

One. The employee with department 99 would be rejected with a foreign key error, so only Ada would remain.

Review lesson →Open compiler

Show starting example

SQL

CREATE TABLE dept (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE emp (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  salary INTEGER NOT NULL CHECK (salary > 0),
  dept_id INTEGER NOT NULL REFERENCES dept(id)
);

INSERT INTO dept VALUES (1, 'Eng');
INSERT OR IGNORE INTO dept VALUES (2, 'Eng');
INSERT OR IGNORE INTO emp VALUES (1, 'Ada', 120, 1);
INSERT OR IGNORE INTO emp VALUES (2, 'Bo', -5, 1);
INSERT OR IGNORE INTO emp VALUES (3, 'Cy', 90, 99);

SELECT (SELECT COUNT(*) FROM dept) AS departments, (SELECT COUNT(*) FROM emp) AS employees;
PRAGMA foreign_key_check;

SQL: transactions with COMMIT and ROLLBACK

Move money between two accounts so that both updates happen or neither does.

What would the balances be if the transfer subtracted 120 from Ada inside a transaction?

Reveal answer and reasoning

The CHECK (balance >= 0) would fail on the first UPDATE, the statement would stop, and a rollback would leave 100 and 50.

Review lesson →Open compiler

Show starting example

SQL

CREATE TABLE account (id INTEGER PRIMARY KEY, owner TEXT, balance INTEGER CHECK (balance >= 0));
INSERT INTO account VALUES (1, 'Ada', 100), (2, 'Bo', 50);

BEGIN;
UPDATE account SET balance = balance - 30 WHERE id = 1;
UPDATE account SET balance = balance + 30 WHERE id = 2;
ROLLBACK;
SELECT owner, balance FROM account ORDER BY id;

BEGIN;
UPDATE account SET balance = balance - 30 WHERE id = 1;
UPDATE account SET balance = balance + 30 WHERE id = 2;
COMMIT;
SELECT owner, balance FROM account ORDER BY id;

SQL: normalization removes repeated facts

Split a table that repeats a customer's city on every row into two tables and see why one update is enough.

Which fact would you move out of order_item if each item also stored its price from a product list?

Reveal answer and reasoning

The price belongs with the product, so it should live in a product table keyed by item and be joined in when needed.

Review lesson →Open compiler

Show starting example

SQL

CREATE TABLE orders_flat (order_id INTEGER, customer TEXT, city TEXT, item TEXT);
INSERT INTO orders_flat VALUES (1, 'Ada', 'London', 'pen'), (1, 'Ada', 'London', 'book'), (2, 'Bo', 'Paris', 'pen');

CREATE TABLE customer (name TEXT PRIMARY KEY, city TEXT);
INSERT INTO customer SELECT DISTINCT customer, city FROM orders_flat;
CREATE TABLE order_item (order_id INTEGER, customer TEXT REFERENCES customer(name), item TEXT);
INSERT INTO order_item SELECT order_id, customer, item FROM orders_flat;

UPDATE customer SET city = 'Oxford' WHERE name = 'Ada';

SELECT i.order_id, c.name, c.city, i.item
FROM order_item i JOIN customer c ON c.name = i.customer
ORDER BY i.order_id, i.item;

SELECT (SELECT COUNT(*) FROM orders_flat WHERE customer = 'Ada') AS flat_copies,
       (SELECT COUNT(*) FROM customer WHERE name = 'Ada') AS normalized_copies;

SQL: subqueries and common table expressions

Compare each row with a group average, and name an intermediate result with WITH.

Who is above the average of the whole company?

Reveal answer and reasoning

The company average is (120 + 90 + 100 + 80 + 140) / 5 = 106, so Ada (120) and Ed (140) are above it.

Review lesson →Open compiler

Show starting example

SQL

CREATE TABLE emp (name TEXT, dept TEXT, salary INTEGER);
INSERT INTO emp VALUES ('Ada', 'Eng', 120), ('Bo', 'Ops', 90), ('Cy', 'Eng', 100), ('Di', 'Ops', 80), ('Ed', 'Eng', 140);

WITH dept_avg AS (
  SELECT dept, AVG(salary) AS avg_salary FROM emp GROUP BY dept
)
SELECT e.name, e.dept, e.salary
FROM emp e
JOIN dept_avg d ON d.dept = e.dept
WHERE e.salary > d.avg_salary
ORDER BY e.name;

SELECT name FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

9
SQL: window functions for running totals and ranks
Compute a running total and a rank within each group without collapsing the rows.
How would you get each region's total on every row instead of a running total?
Reveal answer and reasoning
Drop the ORDER BY inside OVER: SUM(amount) OVER (PARTITION BY region) is the same total on every row of the region.
Review lesson →Open compiler
Show starting example
SQL
```
CREATE TABLE sales (day INTEGER, region TEXT, amount INTEGER);
INSERT INTO sales VALUES (1, 'N', 10), (2, 'N', 20), (3, 'N', 5), (1, 'S', 7), (2, 'S', 7), (3, 'S', 30);

SELECT day, region, amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY day) AS running_total,
       RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM sales
ORDER BY region, day;
```

SQL: views and triggers

Name a reusable query as a view and record price changes automatically with a trigger.

What would price_log contain after a second update that sets the pen's price to 6?

Reveal answer and reasoning

A second row, 1|5|6, because the trigger fires for every update of the price column.

Review lesson →Open compiler

Show starting example

SQL

CREATE TABLE product (id INTEGER PRIMARY KEY, name TEXT, price INTEGER);
CREATE TABLE price_log (product_id INTEGER, old_price INTEGER, new_price INTEGER);

CREATE TRIGGER log_price AFTER UPDATE OF price ON product
BEGIN
  INSERT INTO price_log VALUES (OLD.id, OLD.price, NEW.price);
END;

CREATE VIEW cheap_products AS SELECT name, price FROM product WHERE price < 20;

INSERT INTO product VALUES (1, 'pen', 5), (2, 'book', 25);
UPDATE product SET price = 15 WHERE id = 2;

SELECT * FROM price_log;
SELECT * FROM cheap_products ORDER BY name;

SQL: a unique index and the system catalog

Enforce uniqueness with an index and look up the index in the database's own catalog.

What would changes() return if the insert had used a new email address?

Reveal answer and reasoning

1, because one row would have been inserted.

Review lesson →Open compiler

Show starting example

SQL

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, name TEXT);
INSERT INTO users VALUES (1, 'a@x.com', 'Ada'), (2, 'b@x.com', 'Bo');

CREATE UNIQUE INDEX idx_users_email ON users(email);
INSERT OR IGNORE INTO users VALUES (3, 'a@x.com', 'Impostor');

SELECT changes();
SELECT COUNT(*) FROM users;
SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = 'users';

SQL SELECT

SQL Aggregates and GROUP BY

SQL Joins

SQL INSERT, UPDATE, and DELETE

SQL: constraints keep the data valid

SQL: transactions with COMMIT and ROLLBACK

SQL: normalization removes repeated facts

SQL: subqueries and common table expressions

SQL: window functions for running totals and ranks

SQL: views and triggers

SQL: a unique index and the system catalog