Indexes, views and transactions

Speed up searches with indexes, give complex queries a name with views and make changes all-or-nothing with transactions.

  • Advanced
  • 18 min
  • 10 questions
  • Lesson 13 of 28

In this lesson you will learn

  • Explain what an index is for and create one with CREATE INDEX
  • Create a view with CREATE VIEW and use it like a table
  • Control transactions with BEGIN, COMMIT, ROLLBACK and understand ACID

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.

SQL
EXPLAIN QUERY PLAN
SELECT first_name FROM students WHERE city = 'Bakı';
Expected output
id | parent | notused | detail
2 | 0 | 216 | SCAN students
No index: SCAN — the whole table is read. The detail column is what matters; the other numbers may differ between versions.
SQL
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=?)
With the index: 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.

SQL
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

Sign up free to keep reading

The rest of the lesson, the quiz and your notes open with an Educora account. Signing up takes a minute, and your progress is saved on all your devices.

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.

SQL
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
SQL
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
A view works like an ordinary table: 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).

SQL
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.

SQL
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
LetterPropertyMeaning
AAtomicityall or nothing
CConsistencyafter the transaction all rules (constraints) still hold
IIsolationtransactions running at the same time don't interfere with each other
DDurabilitya committed change survives even a crash
ACID — the four properties of reliable transactions.
DBMSStart a transactionQuery plan
SQLiteBEGIN TRANSACTIONEXPLAIN QUERY PLAN
MySQLSTART TRANSACTIONEXPLAIN
PostgreSQLBEGINEXPLAIN
SQL ServerBEGIN TRANSACTIONexecution 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.

Exercise · SQL
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 sort
Expected 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.

Exercise · SQL
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 INDEX adds speed and forbids duplicates.
  • A view (CREATE VIEW) is a saved, named query used like a table.
  • A transaction: BEGIN → statements → COMMIT (confirm) or ROLLBACK (undo).
  • ACID: atomicity, consistency, isolation, durability.
Spotted a mistake or something inaccurate in this lesson?

Check yourself

10 questions. Every correct answer earns XP.

Question 1 / 10

Question 1 / 10. What is an index for?