DBMS · SQL · Interview prep
DBMS and SQL Interview Questions, Explained
Interviewers ask the same DBMS questions again and again, then follow up on why. This guide explains each concept with a worked example, so you can answer the follow-up too.
By the TestLab team · · 11 min read
DBMS is one of the four core subjects in almost every fresher technical interview, and it shows up in experienced-developer interviews as SQL and database design questions. The good news is that the question bank is small and stable. The bad news is that interviewers know candidates memorise definitions, so they follow up with “why” and “show me”. This guide explains each concept the way you would explain it at a whiteboard, with a small example you can reuse.
All SQL below is standard SQL that runs on PostgreSQL, MySQL 8 and SQL Server with at most small changes.
Keys: the vocabulary everything else uses
Take a table of students:
| roll_no | name | dept_id | |
|---|---|---|---|
| 101 | asha@college.edu | Asha | 1 |
| 102 | ravi@college.edu | Ravi | 2 |
-
A super key is any set of columns that uniquely identifies a row:
{roll_no},{email},{roll_no, name}. -
A candidate key is a minimal super key, meaning you cannot remove a column and keep uniqueness. Here,
{roll_no}and{email}are both candidate keys.{roll_no, name}is not, becausenameis unnecessary. -
The primary key is the candidate key you choose as the main identifier. It cannot be NULL. Here we would pick
roll_no. -
An alternate key is a candidate key you did not choose:
email. -
A foreign key is a column that refers to a key in another table:
dept_idrefers todepartments.id. It enforces referential integrity, so you cannot insert a student with a department that does not exist. -
A composite key is a key made of more than one column, such as
(student_id, course_id)in an enrolments table.
Common follow-up: “Can a table have no primary key?” Yes, SQL allows it, but you lose a reliable way to identify and update individual rows, and many tools and ORMs expect one. In practice, every table should have one.
Normalization, with one table taken all the way to BCNF
Normalization removes redundancy so that each fact is stored once. Redundancy causes three kinds of anomaly:
-
Update anomaly: a fact is stored in many rows, and you change it in some but not all.
-
Insert anomaly: you cannot store one fact without inventing another (for example, you cannot add a new course until a student enrols in it).
-
Delete anomaly: deleting one fact deletes another (the last student leaves and the course details vanish).
Start with this enrolment table:
| student_id | student_name | course_id | course_name | instructor | phones |
|---|---|---|---|---|---|
| 1 | Asha | C1 | DBMS | Rao | 98xxx, 97xxx |
| 1 | Asha | C2 | Networks | Iyer | 98xxx, 97xxx |
| 2 | Ravi | C1 | DBMS | Rao | 99xxx |
First normal form (1NF)
Every column holds one atomic value, and there are no repeating groups. phones holds a list, so it breaks 1NF. Fix it by moving phones into a separate table student_phones(student_id, phone).
Second normal form (2NF)
The table is in 1NF and no non-key column depends on only part of a composite key. The key of the enrolment table is (student_id, course_id). But student_name depends only on student_id, and course_name and instructor depend only on course_id. Those are partial dependencies. Split them out:
-
students(student_id, student_name) -
courses(course_id, course_name, instructor) -
enrolments(student_id, course_id)
A table whose primary key is a single column is automatically in 2NF, because there is no “part” of the key to depend on.
Third normal form (3NF)
The table is in 2NF and no non-key column depends on another non-key column (no transitive dependency). Suppose courses also stored instructor_phone. Then course_id → instructor → instructor_phone: the phone depends on the instructor, not on the course. Move it into instructors(instructor, instructor_phone).
Boyce-Codd normal form (BCNF)
For every functional dependency X → Y, X must be a super key. 3NF allows one exception: Y may be part of a candidate key. BCNF removes that exception.
The classic example: each instructor teaches exactly one subject, and a student takes each subject from one instructor. The table is (student, subject, instructor) with dependencies (student, subject) → instructor and instructor → subject. Both (student, subject) and (student, instructor) are candidate keys, so subject is a prime attribute and the table is in 3NF. But instructor → subject has a left side that is not a super key, so it is not in BCNF. Decompose into (instructor, subject) and (student, instructor).
Common follow-up: “Is BCNF always better?” No. The BCNF decomposition above loses the ability to enforce (student, subject) → instructor with a simple key constraint. That is why some designs stop at 3NF, which can always be reached while preserving dependencies. And many real systems denormalize on purpose, for example by storing an order’s total, to make frequent reads cheaper. The trade-off is that the application then has to keep the copies consistent.
Joins, with the same two tables every time
-- employees -- departments -- id | name | dept_id -- id | name -- 1 | Asha | 10 -- 10 | Engineering -- 2 | Ravi | 20 -- 20 | Sales -- 3 | Meera | NULL -- 30 | Legal
-
INNER JOIN returns only matching rows: Asha–Engineering, Ravi–Sales.
-
LEFT JOIN (employees left, departments right) returns every employee, with NULLs where there is no match: the two pairs above plus Meera–NULL.
-
RIGHT JOIN returns every department: the two pairs plus NULL–Legal.
-
FULL OUTER JOIN returns everything from both sides: the two pairs, Meera–NULL and NULL–Legal. MySQL does not support it directly, so you combine a LEFT and a RIGHT join with UNION.
-
CROSS JOIN returns every combination: 3 × 3 = 9 rows.
-
A self join joins a table to itself, for example employees to their managers in the same table.
A question that catches many candidates: “Find departments with no employees.”
SELECT d.name FROM departments d LEFT JOIN employees e ON e.dept_id = d.id WHERE e.id IS NULL;
The WHERE e.id IS NULL keeps only the departments that found no match. If you put that condition inside the ON clause instead, it changes the meaning completely. Conditions in ON decide which rows match. Conditions in WHERE filter the joined result.
WHERE, GROUP BY and HAVING
SQL evaluates a query in a logical order that is different from the order you write it in:
-
FROMandJOIN -
WHEREfilters rows -
GROUP BYforms groups -
HAVINGfilters groups -
SELECTcomputes the output columns -
ORDER BY -
LIMIT/OFFSET(orFETCH FIRST)
This explains several classic questions:
-
Why can’t I use an aggregate in WHERE? Because WHERE runs before groups exist.
WHERE COUNT(*) > 5is invalid. UseHAVING COUNT(*) > 5. -
Why can’t I use a SELECT alias in WHERE? Because SELECT runs after WHERE. Most databases do allow aliases in ORDER BY, because ORDER BY runs later.
-- Departments with more than 5 employees earning over 50,000 SELECT dept_id, COUNT(*) AS headcount FROM employees WHERE salary > 50000 -- row filter, before grouping GROUP BY dept_id HAVING COUNT(*) > 5; -- group filter, after grouping
The “Nth highest salary” family
This is probably the single most-asked SQL question. Know at least two answers.
-- Second highest salary, using a subquery SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); -- Nth highest distinct salary, using a window function (N = 3 here) SELECT DISTINCT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees ) ranked WHERE rnk = 3;
Be ready to explain the difference between the three ranking functions, using the salaries 90, 80, 80, 70:
| salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| 90 | 1 | 1 | 1 |
| 80 | 2 | 2 | 2 |
| 80 | 3 | 2 | 2 |
| 70 | 4 | 4 | 3 |
ROW_NUMBER never repeats, RANK leaves gaps after ties, and DENSE_RANK has no gaps. For “Nth highest distinct salary”, DENSE_RANK is the right choice.
Also be ready for “highest salary per department”: add PARTITION BY dept_id to the window, then filter rnk = 1.
DELETE vs TRUNCATE vs DROP
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| What it removes | Rows you choose (with WHERE) | All rows | The table itself |
| WHERE clause | Yes | No | No |
| Row-level triggers | Fire | Do not fire | Do not fire |
| Speed on large tables | Slower, row by row | Fast | Fast |
| Structure kept | Yes | Yes | No |
The rollback trap: many candidates say “TRUNCATE cannot be rolled back”. That depends on the database. In PostgreSQL and SQL Server, TRUNCATE inside an explicit transaction can be rolled back. In MySQL and Oracle, TRUNCATE is DDL and commits implicitly. Saying “it depends on the database, and here is why” is a better answer than either one-word version.
Indexes: what they are and when they hurt
An index is a separate data structure, usually a B+ tree, that keeps a column’s values sorted with pointers to the rows. It turns a full table scan into a search that takes a few page reads, even on millions of rows.
-
Clustered index: the table rows themselves are stored in index order. There can be only one per table. In MySQL’s InnoDB engine, the primary key is the clustered index. In SQL Server, the primary key is clustered by default.
-
Non-clustered (secondary) index: a separate structure that points to the rows. A table can have many.
-
Composite index: an index on several columns, such as
(last_name, first_name). It helps queries that filter on the leftmost columns:last_name, orlast_nameandfirst_name. It usually does not help a query that filters only onfirst_name. This is the leftmost-prefix rule, and it is a favourite follow-up. -
Covering index: an index that contains every column a query needs, so the database never has to read the table itself.
When indexes hurt: every INSERT, UPDATE and DELETE must also update every index on the table, so write-heavy tables pay for each extra index. Indexes also take disk space and memory. And an index on a low-selectivity column, such as a boolean flag where 50% of rows are true, is often ignored by the planner because scanning the table is cheaper.
When indexes are skipped even though they exist: wrapping the column in a function (WHERE LOWER(email) = ... without an expression index), a leading wildcard (LIKE '%gmail.com'), or comparing a column with a value of a different type, which forces a conversion.
Transactions and ACID
A transaction is a group of operations that succeed or fail as a unit. The standard example is a bank transfer: debit one account, credit another. You never want only one of the two to happen.
-
Atomicity: all or nothing. If the credit fails, the debit is undone. Databases implement this with an undo or write-ahead log.
-
Consistency: a transaction moves the database from one valid state to another. Constraints, such as “balance cannot be negative” or a foreign key, are never left violated.
-
Isolation: concurrent transactions do not see each other’s half-finished work, to the degree set by the isolation level.
-
Durability: once a transaction commits, it survives a crash. Databases do this by writing the log to disk before acknowledging the commit.
Isolation levels and the anomalies they prevent
This is where good candidates separate themselves. Learn the three read anomalies first:
-
Dirty read: you read data another transaction has written but not yet committed. If it rolls back, you read something that never existed.
-
Non-repeatable read: you read a row twice in one transaction and get different values, because another transaction updated it and committed in between.
-
Phantom read: you run the same query twice and get a different set of rows, because another transaction inserted or deleted matching rows.
The SQL standard defines four levels by which anomalies they allow:
| Isolation level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read uncommitted | Possible | Possible | Possible |
| Read committed | Prevented | Possible | Possible |
| Repeatable read | Prevented | Prevented | Possible |
| Serializable | Prevented | Prevented | Prevented |
Real databases add nuance worth mentioning. PostgreSQL’s default is read committed, and its repeatable read uses snapshots that also prevent phantoms. MySQL InnoDB defaults to repeatable read. Higher isolation costs throughput: more locking or more transactions aborted and retried.
Deadlocks
A deadlock happens when two transactions each hold a lock the other needs. Transaction A locks row 1 and wants row 2. Transaction B locks row 2 and wants row 1. Neither can continue.
Databases detect this, usually by finding a cycle in a wait-for graph, and abort one transaction (the victim) so the other can proceed. Your application should catch that error and retry.
To reduce deadlocks, access tables and rows in a consistent order everywhere in your code, keep transactions short, and do not wait for user input or network calls while holding locks.
The same four conditions from operating systems apply: mutual exclusion, hold and wait, no preemption, and circular wait. Interviewers like it when you connect the two subjects.
Views, stored procedures and triggers
-
A view is a saved query you can select from like a table. It is used to simplify complex joins and to expose only some columns to some users. A materialized view stores the result and must be refreshed, so it trades freshness for speed.
-
A stored procedure is code stored and run inside the database. It reduces round trips, but it moves business logic into the database, where it is harder to test and version.
-
A trigger runs automatically on INSERT, UPDATE or DELETE. It is useful for audit logs, but hidden side effects make bugs hard to trace, so use triggers sparingly.
SQL vs NoSQL in one honest answer
Relational databases give you schemas, joins, transactions and a mature query language. They suit data with clear relationships and strong consistency needs, such as orders, payments and inventory. NoSQL databases cover several models: document (MongoDB), key-value (Redis, DynamoDB), wide-column (Cassandra) and graph (Neo4j). They trade some of those guarantees for flexible schemas, simpler horizontal scaling, or a model that fits the data better.
The answer interviewers want is not “NoSQL is faster”. It is “it depends on the access patterns and the consistency the data needs”, with an example of each.
How to practise this
Reading explanations is the first step. Being able to recognise the right answer quickly and explain it out loud is the goal.
-
Answer MCQs topic by topic in TestLab’s free DBMS practice test and introduction to databases test. Each answer comes with an explanation.
-
If you work with a specific database, practise its dialect too: PostgreSQL and SQL Server.
-
Then practise saying the answers. TestLab’s interview questions give each question a short answer you can say in thirty seconds and a full answer for the follow-ups.
-
Finally, write the queries. Install PostgreSQL or use any online SQL sandbox, create the two tables from the joins section, and run every query in this guide. Watching the results change when you move a condition from WHERE into ON teaches more than any explanation.