Views, Indexes & Transactions — SQL Questions with Answers

CREATE VIEW active_customers AS
SELECT * FROM customers WHERE active = 1;

Answer: A — A saved query that acts like a virtual table - it stores no data itself

CREATE VIEW names a query. Selecting from the view runs the underlying query. Views simplify complex logic and provide a stable interface even if base tables change.

VIEWVirtual table

Answer: A — They hide complexity and restrict access - users query a simple view instead of complex joins or sensitive columns

Views encapsulate complicated joins and can expose only certain columns/rows (security). Consumers get a clean, stable interface without knowing the underlying structure.

VIEWAbstraction

CREATE INDEX idx_email ON customers(email);

Answer: A — A data structure that speeds up row lookups on one or more columns

Like a book's index, it lets the database jump to matching rows instead of scanning every row. The trade-off is extra storage and slightly slower writes.

IndexPerformance

Answer: A — Columns used in WHERE, JOIN and ORDER BY - especially foreign keys and frequently searched fields

Index the columns your queries filter and join on. A column that is never in a WHERE/JOIN/ORDER BY gains nothing from an index and just slows writes.

Index strategy

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;

Answer: A — The query execution plan - how the database will run the query, including index use and join methods

EXPLAIN reveals whether a query uses indexes or does full scans, its join order, and estimated costs. It is the primary tool for diagnosing slow queries.

EXPLAINQuery plan

BEGIN;
UPDATE accounts SET bal = bal - 100 WHERE id = 1;
UPDATE accounts SET bal = bal + 100 WHERE id = 2;
COMMIT;

Answer: A — A group of statements executed as a single all-or-nothing unit

BEGIN starts it; COMMIT makes all changes permanent; ROLLBACK undoes them. Either every statement succeeds or none do - essential for operations like money transfers.

TransactionBEGIN/COMMIT

Answer: A — Undoes all changes made since the transaction began

If an error occurs mid-transaction, ROLLBACK reverts everything to the pre-transaction state, preserving consistency. COMMIT is the opposite - it finalises the changes.

ROLLBACKTransactions

Answer: A — So the debit and credit both succeed or both fail - never one without the other

If the debit succeeds but the credit fails without a transaction, money vanishes. Atomicity guarantees the pair is all-or-nothing - the textbook example of why transactions exist.

AtomicityTransactions

Answer: A — No - a regular view re-runs its query each time; only a MATERIALIZED view stores results

Regular views are purely a naming/abstraction convenience and add no caching. For stored results you need a materialized view (with periodic refresh).

VIEWMaterialized view

Answer: A — An index that also enforces uniqueness - no two rows may share the indexed value

A unique index doubles as a UNIQUE constraint and speeds lookups. Primary keys are implemented as unique indexes automatically.

Unique indexConstraints

Answer: A — Makes all changes in the transaction permanent and visible to others

After COMMIT the changes are durable (survive a crash) and other transactions can see them. Before COMMIT, the work is provisional and can be rolled back.

COMMITDurability

Answer: A — Queries that must read most rows anyway, or conditions that wrap the column in a function

If a query returns a large fraction of the table, a scan is cheaper than an index. And functions on the column (non-sargable conditions) prevent index use entirely.

Index limitsSargability

Answer: A — DROP INDEX idx_name;

DROP INDEX removes an index that is unused or hurting write performance. Some databases require the table name: DROP INDEX idx_name ON table.

DROP INDEX

Answer: A — A view whose results are STORED physically and refreshed periodically - fast reads, but data can be stale

Unlike a regular view (recomputed each query), a materialized view caches the result. It speeds up expensive aggregations at the cost of needing REFRESH to stay current.

Materialized viewCaching

Answer: A — Faster reads but slower writes (every INSERT/UPDATE/DELETE must maintain the index) and more storage

Indexes must be kept in sync on every write. Too many indexes hurt write-heavy tables. Index the columns you filter, join and sort on - not everything.

IndexTrade-offs

CREATE INDEX idx_ab ON t(a, b);

Answer: A — An index on multiple columns; it helps queries filtering on a LEFT-prefix of those columns (leftmost-prefix rule)

An index on (a, b) helps WHERE a = ? and WHERE a = ? AND b = ?, but not WHERE b = ? alone. Put the most selective/most-filtered column first.

Composite indexLeftmost prefix

Answer: A — An index that contains ALL columns a query needs, so the database answers it from the index alone

If the index already holds the selected columns, the database skips reading the table (an 'index-only scan'), which is very fast. Adding INCLUDE columns can create covering indexes.

Covering indexIndex-only scan

Answer: A — Reading every row - fine for small tables, but slow on large ones when an index could have been used

Seeing 'Seq Scan' or 'full scan' on a large filtered query in EXPLAIN usually signals a missing index or a non-sargable condition (a function wrapped around the column).

Table scanEXPLAIN

Answer: A — Atomicity (all-or-nothing), Consistency (valid state), Isolation (no interference), Durability (survives crashes)

ACID is the contract of a reliable transaction. It is why relational databases are trusted for banking and orders - a committed transaction is complete, correct and permanent.

ACIDTransactions

Answer: A — A marker within a transaction you can roll back TO, without undoing the whole transaction

SAVEPOINT sp1 lets you ROLLBACK TO sp1, undoing only the work after that point. Useful in long transactions to recover from a partial failure without discarding everything.

SAVEPOINTTransactions

Answer: A — Interference between concurrent transactions - dirty reads, non-repeatable reads and phantom reads

When many transactions run at once, they can see each other's uncommitted or changing data. Isolation levels control which anomalies are allowed, trading consistency against concurrency.

IsolationConcurrency

Answer: A — Reading data another transaction has changed but NOT yet committed - which may be rolled back

Dirty reads happen at the READ UNCOMMITTED isolation level. If the other transaction rolls back, you acted on data that never really existed. Higher isolation levels prevent this.

Dirty readIsolation levels

Answer: A — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE

Each level prevents more anomalies but reduces concurrency. SERIALIZABLE behaves as if transactions ran one at a time (safest, slowest); READ COMMITTED is a common default.

Isolation levels

Answer: A — Two transactions each waiting for a lock the other holds - neither can proceed; the database aborts one

Deadlocks arise from circular lock dependencies. Databases detect them and kill one transaction (a deadlock victim), which the application should retry. Consistent lock ordering reduces them.

DeadlockLocks

Answer: A — Sometimes - simple single-table views are updatable; views with joins, aggregates or DISTINCT usually are not

If the database can unambiguously map the change back to base-table rows, the view is updatable. Complex views need INSTEAD OF triggers to be writable.

Updatable viewsINSTEAD OF triggers

Answer: A — Every INSERT/UPDATE/DELETE must update all relevant indexes, adding overhead to each write

Indexes are a read optimisation paid for with write cost and storage. On a table with heavy inserts, keep only the indexes that meaningfully help your queries.

Index overheadWrite performance

Answer: A — The indexed column is compared directly, not wrapped in a function - so the index can be used

WHERE created_at >= '2026-01-01' is sargable; WHERE YEAR(created_at) = 2026 is not, because the function must run on every row. Keep the column bare and transform the constant instead.

SargabilityIndexes

Answer: A — Pessimistic locks rows upfront to prevent conflicts; optimistic checks for conflicts at commit (e.g. a version column)

Pessimistic (SELECT ... FOR UPDATE) suits high-contention writes. Optimistic (compare a version/timestamp on update) suits low contention and avoids holding locks - common in web apps.

LockingConcurrency

Answer: A — How many distinct values a column has - high selectivity (many distinct values) makes an index effective

An index on a boolean (2 values) is rarely useful because each value matches half the table. An index on email (near-unique) is highly selective and very effective.

SelectivityIndex effectiveness

Answer: A — Once a transaction commits, its changes survive crashes and power loss (written to durable storage)

Durability is typically achieved via a write-ahead log: changes are logged to disk before COMMIT returns, so recovery can replay them after a crash. A committed transaction is never lost.

DurabilityWrite-ahead log