Imagine a library with a million books but no catalogue: to find a book you would have to check every shelf. A large database is no different. In this lesson we look at three important tools of real projects: indexes that speed up searches, views that give queries a name, and transactions that protect data from accidents.
Indexes
Without an index, for WHERE city = 'Bakı' the DBMS checks every row of the table (a full scan). An index keeps a column's values in sorted order and knows which rows each value is in — like the subject index at the back of a textbook. The DBMS can then jump straight to the rows it needs. EXPLAIN QUERY PLAN shows how SQLite is going to run a query.
EXPLAIN QUERY PLAN
SELECT first_name FROM students WHERE city = 'Bakı';Expected output
id | parent | notused | detail 2 | 0 | 216 | SCAN students
SCAN — the whole table is read. The detail column is what matters; the other numbers may differ between versions.CREATE INDEX idx_students_city ON students (city);
EXPLAIN QUERY PLAN
SELECT first_name FROM students WHERE city = 'Bakı';Expected output
id | parent | notused | detail 3 | 0 | 62 | SEARCH students USING INDEX idx_students_city (city=?)
SEARCH ... USING INDEX — only the needed rows are read.For PRIMARY KEY and UNIQUE columns the DBMS creates an index by itself. CREATE UNIQUE INDEX both speeds up searches and forbids duplicate values. Below, adding a second student with Aysel's email fails.
CREATE UNIQUE INDEX idx_students_email ON students (email);
INSERT INTO students (first_name, last_name, email)
VALUES ('Test', 'User', 'aysel@example.com');Expected output
Error: UNIQUE constraint failed: students.email
Views
ViewDefinition
A saved query with a name. You can SELECT from it like a table, but it doesn't store data of its own: it is recalculated from the underlying tables every time.
Writing a three-table JOIN again and again is tiring. Let's save it once as the view student_scores — after that the queries become very simple.
CREATE VIEW student_scores AS
SELECT s.first_name || ' ' || s.last_name AS student,
c.title AS course,
e.score
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN courses AS c ON c.id = e.course_id;
SELECT student, course, score
FROM student_scores
WHERE score >= 93
ORDER BY score DESC;Expected output
student | course | score Rəşad Kərimov | Python Basics | 99 Fidan Cəfərova | Algebra | 97 Leyla Hüseynova | Geometry | 95 Fidan Cəfərova | Mechanics | 94 Nigar Rzayeva | World History | 93
CREATE VIEW student_scores AS
SELECT s.first_name || ' ' || s.last_name AS student,
c.title AS course,
e.score
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN courses AS c ON c.id = e.course_id;
SELECT course, COUNT(*) AS students, MAX(score) AS best
FROM student_scores
GROUP BY course
ORDER BY best DESC
LIMIT 3;Expected output
course | students | best Python Basics | 3 | 99 Algebra | 4 | 97 Geometry | 3 | 95
WHERE, GROUP BY, ORDER BY and even JOIN.Transactions
Moving 30 manat from Aysel's account to Murad's takes two statements: subtract from one, add to the other. If the power goes out after the first one, the money vanishes! A transaction groups several statements into one indivisible unit of work: either all of them take effect (COMMIT) or none of them do (ROLLBACK).
CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
owner TEXT NOT NULL,
balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts (owner, balance) VALUES ('Aysel', 100), ('Murad', 50);
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 30 WHERE owner = 'Aysel';
UPDATE accounts SET balance = balance + 30 WHERE owner = 'Murad';
COMMIT;
SELECT owner, balance FROM accounts ORDER BY id;Expected output
owner | balance Aysel | 70 Murad | 80
COMMIT confirms both changes together. If we tried to take 150 manat from Aysel, the CHECK would reject the statement and the program would cancel the whole transfer with ROLLBACK.ROLLBACK undoes every change made since BEGIN. Below we accidentally delete all orders, but because we are inside a transaction, ROLLBACK brings every one of them back.
BEGIN TRANSACTION;
DELETE FROM orders; -- oops, no WHERE!
SELECT COUNT(*) AS inside FROM orders; -- 0 inside the transaction
ROLLBACK; -- undo everything since BEGIN
SELECT COUNT(*) AS after_rollback FROM orders;Expected output
after_rollback 12
| Letter | Property | Meaning |
|---|---|---|
| A | Atomicity | all or nothing |
| C | Consistency | after the transaction all rules (constraints) still hold |
| I | Isolation | transactions running at the same time don't interfere with each other |
| D | Durability | a committed change survives even a crash |
| DBMS | Start a transaction | Query plan |
|---|---|---|
| SQLite | BEGIN TRANSACTION | EXPLAIN QUERY PLAN |
| MySQL | START TRANSACTION | EXPLAIN |
| PostgreSQL | BEGIN | EXPLAIN |
| SQL Server | BEGIN TRANSACTION | execution plan |
COMMIT and ROLLBACK are the same everywhere.Exercise
Create a view city_stats that shows the number of students (students) in each city. Then select from this view the cities with at least 2 students, sorted by the number of students descending and then by city.
CREATE VIEW city_stats AS
-- the query that counts students per city
;
SELECT city, students
FROM city_stats
-- keep cities with at least 2 students and sortExpected output
city | students Bakı | 4 Gəncə | 2 Sumqayıt | 2
Exercise
Record a sale in one transaction: customer 5 buys 2 units of product 3 (Headphones) on 2025-08-01. Insert the order into orders, reduce the product's stock by 2 and COMMIT. Then show the name, stock and number of orders (orders) for Headphones.
BEGIN TRANSACTION;
-- INSERT the order
-- UPDATE the stock
COMMIT;
SELECT name,
stock,
(SELECT COUNT(*) FROM orders WHERE product_id = 3) AS orders
FROM products
WHERE id = 3;Expected output
name | stock | orders Headphones | 38 | 3
Remember
- An index speeds up searches but takes space and slows down writing.
CREATE UNIQUE INDEXadds speed and forbids duplicates.- A view (
CREATE VIEW) is a saved, named query used like a table. - A transaction:
BEGIN→ statements →COMMIT(confirm) orROLLBACK(undo). - ACID: atomicity, consistency, isolation, durability.