Skip to content

DBMS and SQL

~14 min

In short

Database questions mostly test four things: joins and aggregations written without help, transactions and isolation, what an index buys and costs, and normalisation. Run the practice queries in a real database, not only on paper.

Checklist

  • [ ] What is a primary key, a foreign key and a candidate key?
  • [ ] Explain ACID with an example.
  • [ ] What is a transaction? What do COMMIT and ROLLBACK do?
  • [ ] Name the four isolation levels and the anomalies each prevents.
  • [ ] What is a dirty read, a non-repeatable read and a phantom read?
  • [ ] Explain 1NF, 2NF and 3NF in one sentence each.
  • [ ] Why would you denormalise a table?
  • [ ] What is an index, and what does it cost on writes?
  • [ ] Why do most relational databases use B-tree indexes?
  • [ ] What is the leftmost-prefix rule for a composite index?
  • [ ] What is the difference between WHERE and HAVING?
  • [ ] What do inner, left, right, full and cross joins return?
  • [ ] How do you find rows in one table that have no match in another?
  • [ ] What is a correlated subquery?
  • [ ] Why does = NULL not find NULL values?
  • [ ] What is a self join? Give a real use.
  • [ ] What does EXPLAIN show you?
  • [ ] What is the difference between DELETE, TRUNCATE and DROP?
  • [ ] Name a trade-off between SQL and NoSQL databases.

Key ideas

ACID. Atomicity: a transaction completes entirely or not at all. Consistency: it moves the data from one valid state to another under the database's rules. Isolation: concurrent transactions behave as if they ran one at a time, to the degree the isolation level promises. Durability: once committed, a change survives a crash. A bank transfer is two updates that must both happen or neither.

Keys and normalisation. A primary key uniquely identifies a row. A foreign key refers to another table's key. Normalisation removes redundancy. 1NF requires atomic values. 2NF removes partial dependency on part of a composite key. 3NF removes dependency between non-key columns. Normalised tables avoid update anomalies. Denormalising trades that for faster reads, and needs a real query pattern to justify it.

Indexes. An index is an extra structure, usually a B-tree, that lets the engine find rows without scanning the whole table. It speeds up lookups, filters and joins on the indexed columns, but it slows writes and takes storage. A composite index on (a, b) helps queries that filter on a, or on a and b together, but generally not on b alone.

Joins. INNER returns rows that match on both sides. LEFT keeps every left-side row and fills the right side with NULL when nothing matches. RIGHT is the mirror image. FULL keeps unmatched rows from both sides. CROSS returns every combination. A self join joins a table to itself, for example an employee to their manager.

Transactions and isolation. The SQL standard defines four levels. Each level prevents a set of anomalies: dirty, non-repeatable and phantom reads. Serializable prevents all three. Databases implement levels differently, so check your database's documentation. Stronger isolation means more waiting or retries.

Aggregation and NULL. WHERE filters rows before grouping; HAVING filters groups after GROUP BY. NULL means unknown, so a comparison with NULL is never true. Use IS NULL.

Deletes and plans. DELETE removes chosen rows. TRUNCATE removes all rows, and its transactional behaviour varies by database. DROP removes the table itself. EXPLAIN shows how the engine plans to run a query; read it before guessing about performance.

SQL and NoSQL. Relational databases give you schemas, joins and transactions. Other stores trade some of these for flexibility or scale. Name the trade-off rather than declaring a winner.

Ten practice queries

Use this schema. SQLite needs no server, so it is a good place to start:

students(id, name, city)
courses(id, title, fee)
enrollments(student_id, course_id, enrolled_on, score)

-- 1. Pune students
SELECT name FROM students WHERE city = 'Pune';

-- 2. Students per city
SELECT city, COUNT(*) FROM students GROUP BY city;

-- 3. Courses over 5000, dearest first
SELECT title, fee FROM courses WHERE fee > 5000 ORDER BY fee DESC;

-- 4. Enrolled students
SELECT DISTINCT s.name FROM students s JOIN enrollments e ON e.student_id = s.id;

-- 5. Students with their course titles, including those with none
SELECT s.name, c.title
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
LEFT JOIN courses c ON c.id = e.course_id;

-- 6. Busy cities
SELECT city FROM students GROUP BY city HAVING COUNT(*) > 2;

-- 7. Average score per course
SELECT c.title, AVG(e.score)
FROM enrollments e JOIN courses c ON c.id = e.course_id
GROUP BY c.title;

-- 8. Students never enrolled
SELECT s.name FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
WHERE e.student_id IS NULL;

-- 9. Second highest fee
SELECT MAX(fee) FROM courses WHERE fee < (SELECT MAX(fee) FROM courses);

-- 10. Scores above their own course's average
SELECT s.name, c.title, e.score
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE e.score > (SELECT AVG(e2.score) FROM enrollments e2 WHERE e2.course_id = e.course_id);

Query 10 uses a correlated subquery. Be ready to explain how the engine evaluates it and how you would speed it up.

Try this

Create the three tables in SQLite and insert about ten rows. Run queries 4, 5 and 8, and check the results by hand. Then run EXPLAIN QUERY PLAN on query 4, add an index with CREATE INDEX ON enrollments(student_id), run the same EXPLAIN again, and note what changed.

Sources