DBMS Interview Questions and Answers: 60 Questions from Easy to Hard
This guide is for students and freshers in India preparing for the DBMS part of campus placements and technical interviews, and for developers with a few years of experience who want to revise the theory behind the databases they use every day. It has 60 questions with answers in three levels: easy covers keys, ER diagrams, normal forms and basic SQL; medium covers joins, normalization problems, transactions, locking and indexing; hard covers concurrency control, recovery, query costs and tricky normal-form cases. Every query result, closure and candidate key in the answers was checked by running it. Try each question aloud before reading the answer, then take the DBMS mock test.
- 60 questions, 3 levels
- Updated
- CoreHRx Jobs
- 17,463 open IT jobs
What DBMS interviews cover
- Fundamentals: the relational model, keys, ER diagrams and how to turn them into tables. Almost every campus technical round starts here.
- SQL: joins, GROUP BY with HAVING, subqueries, NULL handling and window functions, often written live on paper or in a shared editor.
- Design theory: functional dependencies, attribute closure, candidate keys and the normal forms from 1NF to BCNF, a favourite of written tests.
- Transactions: ACID, isolation levels and their anomalies, serializability, locking, deadlocks and crash recovery.
- Storage and performance: indexes, B+ trees, hashing, basic query optimization, and when a NoSQL database fits better than a relational one.
How to prepare
- Freshers: learn the definitions exactly (super key versus candidate key, 3NF versus BCNF), then solve closure and key problems on paper until each takes under five minutes.
- Install MySQL 8 or PostgreSQL and run every query in this guide on a small table you build yourself. Reading SQL is not the same as writing it.
- Practise drawing precedence graphs and tracing a crash log. Both follow fixed methods and are easy marks once the method is clear.
- Experienced candidates: be ready to explain a slow query you fixed with EXPLAIN and an index, and how you handled a deadlock or a lost update in production.
- Finish with the DBMS mock test, note the topics where you lose marks, and revise those first.
Common mistakes and interview-day tips
- Do not confuse COUNT(*) with COUNT(column), or write = NULL instead of IS NULL. Interviewers test NULL handling on purpose.
- When asked for the normal form of a table, list all candidate keys first. Most wrong answers come from a missed key.
- Name the database when behaviour differs: the default isolation level is REPEATABLE READ in MySQL 8 but READ COMMITTED in PostgreSQL.
- For a design question, ask about data volume and the main queries before drawing tables, and state the trade-off of your choice.
- If you do not know an answer, reason aloud from first principles, such as what the log must hold to undo a change, instead of guessing a term.
DBMS interview questions: Easy questions (freshers)
20 questions1. What is a DBMS, and why use one instead of storing data in plain files?
A DBMS is software that stores, retrieves and protects shared data, such as MySQL, PostgreSQL or Oracle. Compared with files it gives you a query language instead of custom parsing code, integrity constraints such as keys, safe access by many users at once, crash recovery through logging, access control, and indexes for fast search. With plain files, two clerks editing the same customer file can overwrite each other, and a power cut mid-write can leave half a record. The cost is setup and some overhead, so a tiny single-user tool may not need one.
2. Explain the three-schema architecture and the two kinds of data independence.
The ANSI-SPARC architecture has three levels. The internal (physical) level says how data is stored: files, pages and indexes. The conceptual (logical) level describes all tables, columns, keys and constraints. The external level is what each user or application sees, usually through views. Physical data independence means you can add an index or move a table to faster disks without changing the logical schema or the queries. Logical data independence means you can change the logical schema, such as adding a column, without breaking views and apps. Logical independence is harder to achieve.
3. In the relational model, what are a relation, tuple, attribute, domain, degree and cardinality?
A relation is a table with a fixed set of named columns. Each row is a tuple and each column is an attribute. The domain of an attribute is its set of allowed values, such as a cgpa from 0 to 10. Degree is the number of attributes and cardinality is the number of tuples, so this student relation has degree 4 and cardinality 3. Formally a relation is a set, so the order of rows and columns carries no meaning and duplicate tuples cannot exist, although an SQL table allows duplicate rows unless a key or UNIQUE constraint blocks them.
-- student:
-- roll_no | name | city | cgpa
-- 101 | Ananya | Chennai | 8.7
-- 102 | Farhan | Lucknow | 7.9
-- 103 | Ishita | Jaipur | 9.14. What is the difference between a super key, candidate key, primary key and alternate key?
A super key is any set of columns whose values identify a row uniquely. A candidate key is a minimal super key: remove any column and it stops being unique. The primary key is the candidate key the designer picks as the main identifier, and the other candidate keys are alternate keys. In student(roll_no, aadhaar_no, email, name), roll_no, aadhaar_no and email are each candidate keys. {roll_no, name} is a super key but not a candidate key, because roll_no alone is unique. This table has 14 super keys: every column set that contains at least one candidate key.
5. What is a foreign key, and what happens when the referenced row is deleted?
A foreign key is a column whose value must match a key in another table, or be NULL. It enforces referential integrity: you cannot point to a department that does not exist, so inserting Ravi with dept_id 99 is rejected. When a referenced row is deleted, the ON DELETE rule decides: RESTRICT or NO ACTION refuses the delete, CASCADE deletes the child rows too, and SET NULL clears the reference, as it does for Asha here. In MySQL 8, write FOREIGN KEY as a table-level clause: a REFERENCES written inside a column definition is parsed but silently ignored.
CREATE TABLE department (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES department (id)
ON DELETE SET NULL
);
INSERT INTO department VALUES (10, 'Finance');
INSERT INTO employee VALUES (1, 'Asha', 10);
INSERT INTO employee VALUES (2, 'Ravi', 99); -- rejected
DELETE FROM department WHERE id = 10; -- Asha's dept_id -> NULL6. How is a PRIMARY KEY different from a UNIQUE constraint?
Both reject duplicate values, but a primary key also forbids NULL, and a table has only one primary key while it can have many UNIQUE constraints. A UNIQUE column may hold NULL, and in MySQL 8 and PostgreSQL several rows may hold NULL, because one NULL is not equal to another; SQL Server allows only one NULL in a unique column. In MySQL InnoDB the primary key is also the clustered index that orders the rows on disk. A typical design is an id primary key plus a UNIQUE email, so users can be found by email and duplicate sign-ups are blocked.
7. What types of attributes appear in an ER diagram, and how is each stored in tables?
A simple attribute cannot be divided, like gender. A composite attribute has parts, like a name made of first and last name, and is stored as separate columns. A multivalued attribute can hold several values, like a customer's phone numbers, so it gets its own table (customer_id, phone), because a column should hold one value. A derived attribute, like age from date_of_birth, is usually computed instead of stored, so it never goes stale. A key attribute, shown underlined, identifies the entity. Storing phones as a comma-separated list breaks 1NF and makes searching hard.
8. What is a weak entity set? Give an example.
A weak entity cannot be identified by its own attributes alone; it depends on an owner entity. A loan EMI is a good example: EMI number 3 only means something together with its loan. The EMI has a partial key, emi_no, and its full key is the owner's key plus the partial key: (loan_id, emi_no). In the ER diagram it is drawn as a double rectangle, its identifying relationship as a double diamond, and its participation is total. In tables, loan_id is both part of the primary key and a foreign key to loan, usually with ON DELETE CASCADE.
9. How do you turn one-to-many and many-to-many relationships into tables?
For one-to-many, put the key of the one side as a foreign key in the many side: each employee row holds its dept_id. For one-to-one, put the foreign key on either side, ideally the side with total participation, and make it UNIQUE. A many-to-many relationship, such as students and courses, needs its own junction table whose primary key combines both foreign keys, and attributes of the relationship, like the grade, live there too. Adding a course_id column to student cannot work, because one student takes many courses.
CREATE TABLE student (
roll_no INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE course (
course_id VARCHAR(10) PRIMARY KEY,
title VARCHAR(80) NOT NULL
);
CREATE TABLE enrolment (
roll_no INT,
course_id VARCHAR(10),
grade CHAR(2),
PRIMARY KEY (roll_no, course_id),
FOREIGN KEY (roll_no) REFERENCES student (roll_no),
FOREIGN KEY (course_id) REFERENCES course (course_id)
);10. What are DDL, DML, DCL and TCL commands?
DDL (data definition) defines structure: CREATE, ALTER, DROP and TRUNCATE. DML (data manipulation) works on rows: SELECT, INSERT, UPDATE and DELETE; some books put SELECT in a separate DQL group. DCL (data control) handles permissions: GRANT and REVOKE. TCL (transaction control) groups changes: COMMIT, ROLLBACK and SAVEPOINT. One practical difference: in MySQL 8 most DDL statements cause an implicit commit, so you cannot roll back a CREATE TABLE or ALTER TABLE, while PostgreSQL can run most DDL inside a transaction and roll it back.
11. What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes rows that match an optional WHERE clause, fires delete triggers and can be rolled back. TRUNCATE removes all rows at once by deallocating storage, so it is much faster on big tables, and in MySQL it resets the AUTO_INCREMENT counter. DROP removes the table itself: data, structure, indexes and permissions. In MySQL 8, TRUNCATE causes an implicit commit and cannot be rolled back, and it fails if another table has a foreign key that references this one; in PostgreSQL, TRUNCATE is transactional. To remove only some rows, use DELETE with a WHERE.
12. What is the difference between WHERE and HAVING? What does this query return?
WHERE filters rows before grouping; HAVING filters groups after GROUP BY, so it can use aggregates such as COUNT(*). Here WHERE first removes Asha (38000). Grouping the rest gives Sales 2 (Ravi, Meena), IT 2 (Arjun, Divya) and HR 1 (Kiran), and HAVING drops HR, so the result is IT 2 and Sales 2. Writing WHERE COUNT(*) >= 2 is an error, because aggregates do not exist yet at the WHERE stage. Keep plain row conditions in WHERE even where HAVING would accept them: filtering early leaves less data to group.
-- emp:
-- id | name | dept | salary
-- 1 | Asha | Sales | 38000
-- 2 | Ravi | Sales | 52000
-- 3 | Meena | Sales | 61000
-- 4 | Arjun | IT | 75000
-- 5 | Divya | IT | 42000
-- 6 | Kiran | HR | 47000
SELECT dept, COUNT(*) AS cnt
FROM emp
WHERE salary > 40000
GROUP BY dept
HAVING COUNT(*) >= 2
ORDER BY dept;13. How does NULL behave in comparisons and aggregates? What do these queries return?
NULL means unknown, so any comparison with it, even bonus = NULL, gives UNKNOWN instead of TRUE, and the second query returns no rows. Use IS NULL, which returns Ravi and Arjun. Aggregates skip NULLs: COUNT(*) counts rows (4), COUNT(bonus) counts non-NULL values (2) and SUM(bonus) is 8000. So AVG(bonus) is 4000, the average of the two known bonuses, not 2000. If a missing bonus should count as zero, write AVG(COALESCE(bonus, 0)). Mixing up COUNT(*) and COUNT(column) is a classic interview trap.
-- emp_bonus:
-- id | name | bonus
-- 1 | Asha | 5000
-- 2 | Ravi | NULL
-- 3 | Meena | 3000
-- 4 | Arjun | NULL
SELECT COUNT(*), COUNT(bonus), SUM(bonus) FROM emp_bonus;
SELECT name FROM emp_bonus WHERE bonus = NULL;
SELECT name FROM emp_bonus WHERE bonus IS NULL ORDER BY id;14. What is the difference between an INNER JOIN and a LEFT JOIN? What does this LEFT JOIN return?
An INNER JOIN returns only rows that match in both tables. A LEFT JOIN returns every row of the left table, with NULLs in the right-side columns where nothing matches. Here it returns four rows: Priya 900, Priya 450, Rahul NULL and Sneha 1200; an inner join returns three, dropping Rahul, who has no orders. A common bug is adding WHERE o.amount > 500 to a LEFT JOIN: Rahul's NULL row fails the test, so the query quietly becomes an inner join. Put such a condition in the ON clause to keep every customer.
-- customers:
-- id | name
-- 1 | Priya
-- 2 | Rahul
-- 3 | Sneha
-- orders:
-- id | customer_id | amount
-- 101 | 1 | 900
-- 102 | 1 | 450
-- 103 | 3 | 1200
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;15. What are selection and projection in relational algebra, and how do they map to SQL?
Selection (sigma) keeps the rows that satisfy a condition, so it maps to WHERE. Projection (pi) keeps the chosen columns, so it maps to the SELECT list. Relational algebra works on sets, so projection also removes duplicate rows; SQL keeps duplicates unless you write DISTINCT, which is why the SQL version needs it to be exactly equivalent. The other basic operators are union, set difference, Cartesian product and rename, and a join is a product followed by a selection. Interviewers often ask you to translate a simple query both ways.
-- Student(roll_no, name, city, cgpa)
-- relational algebra:
pi name, cgpa ( sigma city = 'Pune' AND cgpa > 8 (Student) )
-- the same in SQL:
SELECT DISTINCT name, cgpa
FROM Student
WHERE city = 'Pune' AND cgpa > 8;16. What is a functional dependency, and what makes one trivial?
A functional dependency X -> Y means that any two rows with the same X values must have the same Y values. In a student table, roll_no -> name holds because one roll number belongs to one student. It is a rule about every valid state of the table, so it comes from the business meaning, not from the rows you happen to see today. X -> Y is trivial when Y is a subset of X, like {roll_no, name} -> name, which holds in every table. Non-trivial dependencies such as course_id -> course_title are what normalization uses to split tables.
17. What are insertion, update and deletion anomalies? Show them in this table.
They are problems caused by storing several facts in one table. Update anomaly: Ravi's city is stored in two rows, so if he moves to Bhopal and only one row changes, the data contradicts itself. Insertion anomaly: you cannot record that a new webcam costs Rs 1,200 until someone orders it, because order_id is the key. Deletion anomaly: deleting order 101 also deletes the only record that a keyboard costs Rs 900. Normalization fixes this by splitting the data into customers, products and orders tables, so each fact is stored once.
-- orders_flat:
-- order_id | customer | city | product | price
-- 101 | Ravi | Indore | Keyboard | 900
-- 102 | Ravi | Indore | Mouse | 450
-- 103 | Sneha | Surat | Mouse | 45018. Explain 1NF, 2NF and 3NF with an example.
1NF: every column holds one atomic value, with no lists or repeating groups. 2NF: 1NF, and no non-key column depends on only part of a composite candidate key. 3NF: 2NF, and no non-key column depends on another non-key column. In enrolment(roll_no, course_id, student_name, grade) with key (roll_no, course_id), student_name depends on roll_no alone, which breaks 2NF, so it moves to student(roll_no, student_name). In course(course_id, dept, hod) with dept -> hod, hod depends on the key only through dept, which breaks 3NF, so it moves to dept(dept, hod).
19. What is a transaction, and what do the ACID properties mean?
A transaction is a group of operations that succeed or fail as one unit, like moving Rs 500 from Asha's wallet to Ravi's: a debit and a credit. Atomicity: both happen or neither does, so a crash after the debit is rolled back. Consistency: the transaction takes the database from one valid state to another, keeping rules such as no negative balance. Isolation: concurrent transactions do not see each other's half-done work. Durability: once committed, the change survives a crash. Atomicity and durability come from logging and recovery; isolation comes from locking or MVCC.
20. What is an index, and why does it speed up reads but slow down writes?
An index is a separate sorted structure, usually a B+ tree, that maps column values to the rows holding them, like the index at the back of a book. Without one, WHERE phone = '9876543210' scans every row; with one, the database walks a few tree levels to the match, so the lookup costs O(log n) page reads instead of O(n). The price is that every INSERT, every DELETE and every UPDATE of an indexed column must also update each index, and indexes use disk and memory. Index the columns you filter, join and sort on, not every column.
Ready to check yourself? Take the free DBMS mock test
DBMS interview questions: Medium questions (1-3 years)
20 questions21. How do you find the second-highest salary? What does this query return, and what goes wrong with LIMIT and OFFSET?
It returns 70000: the inner query finds the top salary, 90000, and the outer query takes the largest salary below it. The tempting ORDER BY salary DESC LIMIT 1 OFFSET 1 returns 90000 here, because Ravi and Meena tie at the top; it needs SELECT DISTINCT salary to be correct. The MAX version also handles an edge case well: if everyone earns the same, it returns one row holding NULL, while the OFFSET version returns no row at all. For the Nth highest, use DENSE_RANK() in a subquery, which treats ties the same way.
-- emp:
-- id | name | salary
-- 1 | Asha | 70000
-- 2 | Ravi | 90000
-- 3 | Meena | 90000
-- 4 | Arjun | 55000
-- 5 | Divya | 70000
SELECT MAX(salary) AS second_highest
FROM emp
WHERE salary < (SELECT MAX(salary) FROM emp);22. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
All three number rows in the window order but treat ties differently. ROW_NUMBER gives every row a different number (1 to 5 here), so ties are broken arbitrarily unless you add a tie-breaker such as id. RANK gives tied rows the same number and then skips: Asha and Meena are both 1, the two 85s are both 3 and Arjun is 5. DENSE_RANK does not skip: 1, 1, 2, 2, 3. So DENSE_RANK = 2 finds the second-highest marks, while ROW_NUMBER = 2 is just the second row. These need MySQL 8 or later; MySQL 5.7 has no window functions.
-- scores:
-- id | name | marks
-- 1 | Asha | 91
-- 2 | Ravi | 85
-- 3 | Meena | 91
-- 4 | Arjun | 78
-- 5 | Divya | 85
SELECT name, marks,
ROW_NUMBER() OVER (ORDER BY marks DESC, id) AS rn,
RANK() OVER (ORDER BY marks DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY marks DESC) AS drnk
FROM scores
ORDER BY marks DESC, id;23. What is a correlated subquery? Who does this query return?
A correlated subquery uses a column of the outer query, so it is logically evaluated once per outer row. Here, for each employee, it computes the average salary of that employee's department. Sales averages 50000, so Ravi qualifies. IT averages 70000, so Meena does, but Arjun, at exactly 70000, does not. HR's only member equals its own average. The result is Ravi and Meena. Optimizers often rewrite such queries, but a join with a grouped derived table, or AVG(salary) OVER (PARTITION BY dept), is often faster and clearer on big tables.
-- emp:
-- id | name | dept | salary
-- 1 | Asha | Sales | 40000
-- 2 | Ravi | Sales | 60000
-- 3 | Meena | IT | 90000
-- 4 | Arjun | IT | 70000
-- 5 | Divya | IT | 50000
-- 6 | Kiran | HR | 45000
SELECT e.name, e.dept, e.salary
FROM emp e
WHERE e.salary > (SELECT AVG(salary) FROM emp
WHERE dept = e.dept)
ORDER BY e.id;24. How do you find duplicate rows and delete all but one copy?
Group by the column that should be unique and keep groups with COUNT(*) > 1: here asha@mail.in appears 3 times and ravi@mail.in twice. To delete, keep the lowest id for each email and remove every other row, which leaves ids 1, 2 and 4. The inner SELECT is wrapped in a derived table on purpose: MySQL 8 refuses a DELETE whose subquery reads the same table directly (error 1093), while a grouped derived table is materialized first. Afterwards add a UNIQUE constraint on email, or the duplicates will come back.
-- contacts:
-- id | name | email
-- 1 | Asha | asha@mail.in
-- 2 | Ravi | ravi@mail.in
-- 3 | Asha | asha@mail.in
-- 4 | Neha | neha@mail.in
-- 5 | Ravi | ravi@mail.in
-- 6 | Asha | asha@mail.in
SELECT email, COUNT(*) FROM contacts
GROUP BY email HAVING COUNT(*) > 1
ORDER BY email;
DELETE FROM contacts
WHERE id NOT IN (
SELECT keep_id FROM (
SELECT MIN(id) AS keep_id FROM contacts
GROUP BY email
) AS k
);
SELECT id, email FROM contacts ORDER BY id;25. What is a self join? Write a query that lists each employee with their manager's name.
A self join joins a table to itself through two aliases, here e for the employee and m for the manager, matching e.manager_id to m.id. The result is Kavya with NULL, Rohit with Kavya, Sana with Kavya and Vikram with Rohit. The LEFT JOIN is deliberate: Kavya is the top manager with no manager_id, and an inner join would drop her. Self joins also find pairs within one table, such as two employees in the same city. For a whole reporting chain of any depth, use a recursive CTE (WITH RECURSIVE), available in MySQL 8 and PostgreSQL.
-- emp:
-- id | name | manager_id
-- 1 | Kavya | NULL
-- 2 | Rohit | 1
-- 3 | Sana | 1
-- 4 | Vikram | 2
SELECT e.name AS employee, m.name AS manager
FROM emp e
LEFT JOIN emp m ON m.id = e.manager_id
ORDER BY e.id;26. Why does this NOT IN query return no rows, and how do you fix it?
Because orders.customer_id contains a NULL. id NOT IN (1, NULL) means id <> 1 AND id <> NULL, and a comparison with NULL is UNKNOWN, so the condition is never TRUE and no customer comes back, not even Rahul and Sneha, who have no orders. NOT EXISTS has no such problem: it only asks whether a matching row exists, so it returns Rahul and Sneha. A LEFT JOIN with WHERE o.id IS NULL also works. Prefer NOT EXISTS for these anti-joins, or add WHERE customer_id IS NOT NULL inside the subquery.
-- customers:
-- id | name
-- 1 | Priya
-- 2 | Rahul
-- 3 | Sneha
-- orders:
-- id | customer_id
-- 101 | 1
-- 102 | NULL
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.id)
ORDER BY c.id;27. How do you find the closure of an attribute set and the candidate keys? Take R(A, B, C, D, E, F) with AB -> C, C -> D, D -> A, BE -> F.
Start the closure with the given attributes and keep adding the right side of any dependency whose left side is already inside. (AB)+ grows to ABC, then ABCD, and stops, so AB is not a key. For keys, note that B and E never appear on a right side, so every key must contain both. (BE)+ is only BEF, but adding any one of A, C or D brings in everything, so ABE, BCE and BDE are the candidate keys, each of them minimal. This shortcut prunes the search; checking all 64 subsets by hand is what wastes time in exams.
28. What is a minimal (canonical) cover? Find one for F = {A -> BC, B -> C, AB -> D, D -> C}.
A minimal cover is an equivalent set of dependencies with a single attribute on each right side, no extra attribute on any left side and no redundant dependency. Split first: A -> B, A -> C, B -> C, AB -> D, D -> C. In AB -> D, B is extraneous, because A already determines B, so A -> D follows. Next, A -> C is redundant, since A -> B and B -> C give it. Nothing else can go, so a minimal cover is {A -> B, A -> D, B -> C, D -> C}. Minimal covers feed 3NF synthesis, and in general a set can have more than one.
29. What is the difference between 3NF and BCNF? Give a table that is in 3NF but not in BCNF.
BCNF requires that for every non-trivial dependency X -> Y, X is a super key. 3NF relaxes this: it also allows X -> Y when every attribute of Y is prime, meaning part of some candidate key. Take a Kota coaching centre table (student, subject, mentor) where each mentor teaches one subject (mentor -> subject) and a student has one mentor per subject ({student, subject} -> mentor). The candidate keys are {student, subject} and {student, mentor}. mentor -> subject breaks BCNF, since mentor is not a super key, but subject is prime, so the table is in 3NF.
30. What makes a decomposition lossless? Why does this split produce extra rows?
Splitting R into R1 and R2 is lossless when joining them always gives back exactly R, which holds when the common attributes determine all of R1 or all of R2. Here the common attribute is dept_name, which is not a key of either part, because two departments, one in Pune and one in Delhi, are both called Sales. The join returns 5 rows instead of 3, including the false rows (1, 20) and (2, 10). Splitting into (emp_id, dept_id) and (dept_id, dept_name) is lossless, because dept_id is the key of the second part, and it preserves both dependencies too.
-- emp:
-- emp_id | dept_id | dept_name
-- 1 | 10 | Sales
-- 2 | 20 | Sales
-- 3 | 30 | Finance
CREATE TABLE r1 AS SELECT emp_id, dept_name FROM emp;
CREATE TABLE r2 AS SELECT dept_id, dept_name FROM emp;
SELECT r1.emp_id, r2.dept_id, r1.dept_name
FROM r1 JOIN r2 ON r1.dept_name = r2.dept_name
ORDER BY r1.emp_id, r2.dept_id;31. How do you map a specialization hierarchy, such as permanent employees and contractors, into tables?
There are three common options. One table per entity: employee(id, name, joined_on) plus permanent(id, pf_number) and contractor(id, agency, rate), each sharing the employee id as primary and foreign key; clean, but most reads need a join. One table per subclass only, repeating name and joined_on in each; this works only when every employee is exactly one kind, and listing all employees needs a UNION. One table with a type column and nullable subclass columns; fastest to read, but rules like a required pf_number need CHECK constraints. Pick by query pattern and column count.
32. What are the SQL isolation levels, and which anomalies does each allow?
A dirty read sees another transaction's uncommitted change. A non-repeatable read gets a different value when it reads the same row twice, because someone committed in between. A phantom appears when a re-run range query returns new rows. The SQL standard defines the four levels in the table by which of these they must prevent. Engines often do more: MySQL 8 InnoDB defaults to REPEATABLE READ, and its snapshot reads do not show phantoms, while PostgreSQL defaults to READ COMMITTED and treats READ UNCOMMITTED as READ COMMITTED. Higher levels cost more blocking or retries.
level dirty read non-repeatable phantom
READ UNCOMMITTED possible possible possible
READ COMMITTED no possible possible
REPEATABLE READ no no possible
SERIALIZABLE no no no33. What is the lost update problem, and how do you prevent it?
Two transactions read the same value, each computes from it, and the second write silently overwrites the first. If two debits of Rs 100 both read a balance of 150 and each writes back 50, one debit is lost. The fix is to make the read and the write one step: SET balance = balance - 100, with the rule in the WHERE clause. With a balance of 150, the first statement updates one row and the second updates zero rows, so the app knows to reject it, and the balance ends at 50. Other fixes are SELECT ... FOR UPDATE, or optimistic locking with a version column.
-- wallet:
-- user_id | balance
-- 7 | 150
UPDATE wallet SET balance = balance - 100
WHERE user_id = 7 AND balance >= 100; -- 1 row
UPDATE wallet SET balance = balance - 100
WHERE user_id = 7 AND balance >= 100; -- 0 rows
SELECT balance FROM wallet WHERE user_id = 7;34. How do you test whether a schedule is conflict serializable? Check this one.
Draw a precedence graph: one node per transaction, and an edge Ti -> Tj when an operation of Ti conflicts with a later one of Tj (same item, different transactions, at least one write). T2 writes A before T3 reads it and before T1 writes it, and T3 reads A before T1 writes it: edges T2 -> T3, T2 -> T1 and T3 -> T1. T2 reading B before T3 writes it repeats T2 -> T3. There is no cycle, so the schedule is conflict serializable, and the topological order T2, T3, T1 is the equivalent serial schedule, even though T1 started first.
step T1 T2 T3
1 R(C)
2 W(A)
3 R(A)
4 R(B)
5 W(A)
6 W(B)
7 W(C)35. What are shared and exclusive locks, and what is two-phase locking?
A shared (S) lock lets a transaction read an item and is compatible with other S locks; an exclusive (X) lock is needed to write and is compatible with no other lock. Two-phase locking (2PL) means a transaction takes locks in a growing phase and releases them in a shrinking phase, and never takes a new lock after its first release. Every schedule that obeys 2PL is conflict serializable, in the order of the lock points. 2PL does not prevent deadlocks, and basic 2PL allows cascading rollbacks; strict 2PL, used in practice, holds X locks until commit or abort.
36. When is a hash index better than a B+ tree index, and when is it useless?
A hash index maps a hashed key to a bucket, so an equality lookup such as WHERE pan = 'ABCDE1234F' costs about one bucket read. But hashing destroys order, so it cannot help ranges (amount BETWEEN 100 AND 500), ORDER BY or prefix searches like name LIKE 'Ra%'; a B+ tree handles all of these and equality almost as well, which is why it is the default. PostgreSQL has a hash index type, crash-safe since version 10. MySQL 8 InnoDB does not support user hash indexes: USING HASH quietly becomes a B-tree, though InnoDB can build an internal adaptive hash index.
37. What is a deadlock, and how do databases detect and avoid it?
A deadlock is a cycle of transactions, each waiting for a lock another holds: here S1 holds row 1 and wants row 2, while S2 holds row 2 and wants row 1. Databases detect it with a wait-for graph, where a cycle means deadlock, and roll one transaction back as the victim. MySQL 8 InnoDB does this at once, prefers the transaction that changed fewer rows and returns error 1213, while innodb_lock_wait_timeout (50 seconds by default) handles ordinary waits. In application code, lock rows in a fixed order such as lowest id first, keep transactions short and retry the victim.
S1> UPDATE acct SET bal = bal - 100 WHERE id = 1;
S2> UPDATE acct SET bal = bal - 50 WHERE id = 2;
S1> UPDATE acct SET bal = bal + 100 WHERE id = 2; -- waits
S2> UPDATE acct SET bal = bal + 50 WHERE id = 1; -- deadlock38. What is the difference between a clustered and a secondary (non-clustered) index?
A clustered index stores the rows themselves in index order, so a table has at most one. In MySQL 8 InnoDB the table is a B+ tree clustered on the primary key; without one, InnoDB uses the first UNIQUE index whose columns are all NOT NULL, or else a hidden row id. A secondary index stores the key plus a way to reach the row: in InnoDB, the primary key value, so a lookup walks two trees unless the index covers every column the query needs. That is why a long primary key, such as a UUID string, makes every secondary index bigger. PostgreSQL tables are heaps, so all its indexes are secondary.
39. How is a composite index on (city, age) used? Which of these queries can use it?
A composite index is sorted by city first and by age within each city, like a phone book sorted by surname, then first name. So it helps queries that filter on the leading column: city = 'Pune', and city = 'Pune' AND age > 30, which reads one contiguous range. A query on age alone usually cannot seek on it, because people over 30 are spread across every city. MySQL 8.0.13 and later can sometimes use a skip scan when city has few distinct values, but do not design around it. Put equality columns first and range columns last.
CREATE INDEX idx_city_age ON customers (city, age);
-- can use the index: the leading column city is filtered
SELECT id FROM customers WHERE city = 'Pune';
SELECT id FROM customers WHERE city = 'Pune' AND age > 30;
-- usually cannot seek on it: no condition on city
SELECT id FROM customers WHERE age > 30;40. What are the main types of NoSQL databases, and when would you choose one over a relational database?
There are four main families: key-value stores such as Redis for caching and sessions, document stores such as MongoDB for JSON-like records whose fields vary, wide-column stores such as Cassandra for very high write volumes spread over many machines, and graph databases such as Neo4j for relationship-heavy queries. Choose NoSQL when the data shape changes often, the access pattern is simple and known in advance, or you need to scale out beyond one server. Choose a relational database when you need joins, ad hoc queries and multi-row transactions, as in payments or HR records.
Ready to check yourself? Take the free DBMS mock test
DBMS interview questions: Hard questions (experienced)
20 questions41. This schedule is not conflict serializable. Is it view serializable?
Yes. Conflict test: T1 reads A before T2 writes it (T1 -> T2) and T2 writes A before T1 does (T2 -> T1), a cycle, so it is not conflict serializable. View equivalence needs the same reads-from pairs and the same final writer of each item. In the serial order T1, T2, T3, T1 still reads the initial A and T3 still writes last, so the schedule is view equivalent to it. T2 and T3 write A without reading it, which are blind writes, and every schedule that is view but not conflict serializable contains one. Testing view serializability is NP-complete, so real systems enforce conflict serializability.
step T1 T2 T3
1 R(A)
2 W(A)
3 W(A)
4 W(A)42. What are recoverable, cascadeless and strict schedules? Classify this one.
Here T2 reads A written by T1 and commits before T1 finishes; when T1 then aborts, T2 has committed work based on a value that never officially existed, so the schedule is not recoverable. Recoverable means a transaction commits only after every transaction it read from has committed. Cascadeless is stronger: read only committed data, so one abort never forces others to abort. Strict is stronger still: nobody reads or overwrites an item until its last writer has committed or aborted, so undo can simply restore the before-image. Strict 2PL produces strict schedules.
step T1 T2
1 W(A)
2 R(A)
3 W(B)
4 COMMIT
5 ABORT43. How does timestamp-ordering concurrency control work, and what does Thomas's write rule change?
Each transaction gets a timestamp at start, and that fixes the serial order. Each item keeps R-TS and W-TS, the largest timestamps that read and wrote it. A read by T fails if TS(T) is below W-TS, because a younger transaction already overwrote the value; a write fails if TS(T) is below R-TS or W-TS. A failed transaction restarts with a new timestamp. Example: T1 (ts 10) writes A after T2 (ts 20) wrote it. Basic ordering rolls T1 back. Thomas's rule just ignores the obsolete write when no younger transaction has read A, which admits some view-serializable schedules.
44. What is write skew, and why does snapshot isolation allow it?
Under snapshot isolation each transaction reads from a snapshot and conflicts only when two transactions write the same row. In the example both doctors see two people on call, each sets only their own row to 0, the writes touch different rows, and both commit, leaving nobody on call. No row was overwritten, so first-committer-wins does not catch it. PostgreSQL REPEATABLE READ and MySQL 8 InnoDB REPEATABLE READ with plain SELECTs both allow it. PostgreSQL SERIALIZABLE detects the pattern and aborts one transaction. Elsewhere, lock the rows the rule reads with SELECT ... FOR UPDATE.
-- doctors: Asha and Ravi, both on_call = 1
-- rule: at least one doctor must stay on call
-- both transactions run at REPEATABLE READ
T1> SELECT COUNT(*) FROM doctors WHERE on_call = 1; -- 2
T2> SELECT COUNT(*) FROM doctors WHERE on_call = 1; -- 2
T1> UPDATE doctors SET on_call = 0 WHERE name = 'Asha';
T2> UPDATE doctors SET on_call = 0 WHERE name = 'Ravi';
T1> COMMIT;
T2> COMMIT; -- both succeed: nobody is on call45. How does InnoDB stop phantoms in a locking read? What happens to session 2 here?
Session 2 waits. In MySQL 8 InnoDB at REPEATABLE READ, a locking read such as SELECT ... FOR UPDATE takes next-key locks: a lock on each index record it scans plus the gap before that record. The insert of amount 1500 falls into a locked gap, so it blocks until session 1 ends, and re-running the range query in session 1 cannot show a phantom row. Without an index on amount, InnoDB scans and locks every row and gap, in effect the whole table. At READ COMMITTED, searches do not take gap locks, so the insert succeeds and phantoms become possible.
-- MySQL 8, InnoDB, REPEATABLE READ, index on orders(amount)
S1> START TRANSACTION;
S1> SELECT id FROM orders
WHERE amount BETWEEN 1000 AND 2000 FOR UPDATE;
S2> INSERT INTO orders (id, amount) VALUES (77, 1500);
-- S2 waits until S1 commits or rolls back46. Compare the wait-die and wound-wait deadlock prevention schemes.
Both use start timestamps (smaller means older), and both rule out cycles because waiting is allowed in one age direction only. In wait-die, an older transaction asking for a lock held by a younger one waits, while a younger requester dies, that is, rolls back. In wound-wait, an older requester wounds (rolls back) the younger holder, while a younger requester waits. A restarted transaction keeps its original timestamp, so it eventually becomes the oldest and cannot starve. Wait-die can kill the same young transaction again and again, so wound-wait usually causes fewer rollbacks.
47. What do steal and no-force mean in buffer management, and why do they need both undo and redo logging?
Steal means the buffer manager may write a dirty page of an uncommitted transaction to disk, for example to free memory; if that transaction later aborts or the system crashes, the change must be undone, so the log needs before-images. No-force means commit does not wait for the changed data pages to reach disk, only the log, so after a crash committed changes may be missing and must be redone from after-images. Steal with no-force gives the best performance, so most engines use it with write-ahead logging: a log record reaches disk before its data page, and a commit waits for its log records.
48. Explain the three passes of ARIES recovery.
Analysis starts at the last checkpoint and scans forward to rebuild the transaction table and the dirty page table, finding the losers (transactions with no commit) and the LSN where redo must begin. Redo repeats history from that LSN, reapplying every logged change, the losers' too, but only where the page's stored LSN shows the change is missing. Undo then rolls back the losers in reverse LSN order and writes a compensation log record (CLR) for each undone change. CLRs are redo-only and point to the next record to undo, so a crash during recovery never undoes the same change twice.
49. The system crashes after this log. With undo-redo recovery from the checkpoint, which transactions are redone and undone, and what are the final values?
T1 committed before the checkpoint, so its update of A is already on disk and needs no work. T3 committed after the checkpoint, so it is redone, setting C to 350. T2 and T4 have no commit record, so they are losers and are undone in reverse log order: E back to 500, D back to 400 and B back to 200. B was written before the checkpoint, which is why undo follows a loser back past the checkpoint to its start record; the checkpoint lists T2 as active for exactly this reason. The final values are A = 150, B = 200, C = 350, D = 400 and E = 500.
LSN log record
1 T1 start
2 T1 update A: 100 -> 150
3 T2 start
4 T2 update B: 200 -> 250
5 T1 commit
6 checkpoint, active = {T2}
7 T3 start
8 T3 update C: 300 -> 350
9 T2 update D: 400 -> 450
10 T3 commit
11 T4 start
12 T4 update E: 500 -> 550
-- crash --50. With 4 KB pages, 8-byte keys and 8-byte pointers, how many levels does a B+ tree need for 10 million keys?
An internal node with p child pointers holds p - 1 keys, so 8p + 8(p - 1) must fit in 4096 bytes, which gives p = 256. A leaf holds key-pointer pairs plus one next-leaf pointer, so 255 entries. Full leaves need ceil(10,000,000 / 255) = 39,216 leaves, the level above needs 154 nodes and one root covers those, so the tree has 3 levels. With nodes about two-thirds full, typical after random inserts, it needs 4. A balanced binary tree would need 24 levels; high fan-out is why B+ trees need so few page reads, and their top levels stay in memory.
51. What happens when a B+ tree leaf overflows, and how is that different from an internal node split?
Say a leaf holds at most 3 keys and contains 5, 15 and 25. Inserting 35 overflows it, so it splits into [5, 15] and [25, 35], and a copy of 25, the smallest key of the new right leaf, goes up into the parent. The key stays in the leaf because all data entries live in leaves. When an internal node overflows, its middle key is pushed up, not copied, because internal keys are only signposts. If the root splits, a new root is created, so the tree grows at the top and all leaves stay at the same depth. Leaves are linked, so a range scan finds its first key and walks the chain.
52. How does extendible hashing grow, and why can skewed keys blow up its directory?
Extendible hashing keeps a directory of 2^d pointers indexed by the last d bits of the hash (d is the global depth), and each bucket has a local depth. If a full bucket with local depth below d overflows, only it splits and a few pointers change. When its local depth equals d, the directory doubles first. Skew hurts: with buckets of 2 keys and h(k) = k, the keys 4, 12 and 20 share their last three bits (100), so inserting them pushes the global depth from 1 to 4, a 16-entry directory for three keys. Linear hashing avoids a directory by splitting buckets in a fixed round-robin order.
53. R has 1,000 pages, S has 500 pages and 52 buffer pages are free. What does a block nested-loop join cost, and what would a hash join cost?
In the standard cost model, block nested-loop reads the outer table once and the inner table once per chunk of M - 2 outer pages, keeping one page for the inner input and one for output. With S outside: 500 + ceil(500 / 50) x 1,000 = 10,500 I/Os. With R outside: 1,000 + 20 x 500 = 11,000, so the smaller table goes outside. A Grace hash join partitions both inputs and then joins each pair of partitions, about 3 x (1,000 + 500) = 4,500 I/Os, valid here since 52 buffers far exceed the square root of 500 (about 22). If both inputs were already sorted on the key, a merge join would cost 1,500.
54. How does an optimizer estimate result sizes, and what goes wrong when the data is skewed?
Without better statistics it assumes uniform data and independent columns. For emp with 100,000 rows and 20 distinct cities, it estimates city = 'Pune' at 100,000 / 20 = 5,000 rows. For R JOIN S ON A it estimates |R| x |S| / max(V(R, A), V(S, A)), so joining those 5,000 rows to a 200-row dept table on its key gives 5,000. If 40 percent of employees really are in Pune, the true count is 40,000, eight times the estimate, and the optimizer may choose a join method or join order that suits 5,000 rows. Histograms (ANALYZE TABLE ... UPDATE HISTOGRAM in MySQL 8, ANALYZE in PostgreSQL) fix this.
55. An index exists on email, yet the query is slow. Why might the optimizer not use the index?
The index stores raw email values in order, so it can seek only when the query compares the raw column. Wrapping the column in a function, as in LOWER(email), hides it; fix that with an expression index (MySQL 8.0.13 or later, PostgreSQL) or a stored normalized copy. A leading wildcard such as LIKE '%@mail.in' has no fixed prefix to seek to. In MySQL, comparing a VARCHAR column with a number converts every row and also skips the index. And if the condition matches a large share of the table, a full scan really is cheaper. EXPLAIN shows which case you are in.
CREATE INDEX idx_email ON users (email);
-- cannot seek on idx_email: a function wraps the column
SELECT id FROM users WHERE LOWER(email) = 'asha@mail.in';
-- cannot seek: no fixed prefix
SELECT id FROM users WHERE email LIKE '%@mail.in';
-- seeks on idx_email
SELECT id FROM users WHERE email = 'asha@mail.in';
-- fix for the first query (MySQL 8.0.13+, PostgreSQL)
CREATE INDEX idx_email_lower ON users ((LOWER(email)));56. Find all candidate keys of R(A, B, C, D, E) with A -> B, BC -> E, ED -> A, and give its highest normal form.
C and D never appear on a right side, so they are in every key, but (CD)+ is just CD. Adding one more attribute works three ways: (ACD)+ = ABCDE, (BCD)+ = ABCDE and (CDE)+ = ABCDE. So the candidate keys are ACD, BCD and CDE, and every attribute is prime. That makes the relation 3NF automatically, since 3NF accepts any dependency whose right side is prime. It is not in BCNF, because the left side of A -> B is not a super key, and the same is true of BC -> E and ED -> A. A common mistake is stopping after the first key found.
57. Can you always reach BCNF without losing a dependency? Use address(street, city, pin), where {street, city} -> pin and each PIN code belongs to one city (pin -> city).
No. The candidate keys are {street, city} and {street, pin}, and pin -> city breaks BCNF because pin is not a super key. The BCNF split is (pin, city) and (street, pin). It is lossless, since pin is the key of the first part, but {street, city} -> pin no longer fits inside any one table, so checking it needs a join on every insert. The original table is already in 3NF, so many designers stop there. 3NF synthesis always gives a lossless, dependency-preserving result, while BCNF guarantees only losslessness. That trade-off is the answer interviewers want.
58. How do you find students who have taken every required course? This is relational division.
Relational division, enrolment divided by required, returns the students paired with every row of required. SQL has no division operator, so use a double NOT EXISTS: keep a student for whom no required course is missing. Here Asha and Meena qualify; Ravi lacks OS, and Meena's extra AI course does not matter. An easier version keeps required courses, groups by student and checks HAVING COUNT(DISTINCT course) = (SELECT COUNT(*) FROM required). The two differ on an empty required table: NOT EXISTS returns every student, the correct division result, and the GROUP BY version returns nobody.
-- required:
-- course | credits
-- DBMS | 4
-- OS | 4
-- CN | 3
-- enrolment:
-- student | course
-- Asha | DBMS
-- Asha | OS
-- Asha | CN
-- Ravi | DBMS
-- Ravi | CN
-- Meena | OS
-- Meena | DBMS
-- Meena | CN
-- Meena | AI
SELECT DISTINCT e.student FROM enrolment e
WHERE NOT EXISTS (
SELECT 1 FROM required r
WHERE NOT EXISTS (
SELECT 1 FROM enrolment x
WHERE x.student = e.student
AND x.course = r.course))
ORDER BY e.student;59. When is denormalization the right choice, and what does it cost?
Denormalize when a measured read path is too slow and the data is read far more often than it changes, such as a dashboard summing lakhs of order rows. Typical moves are storing a derived value (orders.total_amount), copying a column to avoid a join, or keeping a daily summary table. The cost is that one fact now lives in two places, so every write must update both, through a transaction, a trigger or a batch job, and you need a check that finds drift. Try indexes and query fixes first. A shipping address copied into an order is not denormalization at all: it records the address at order time.
60. What does the CAP theorem actually say, and how does it affect choosing a distributed database?
CAP says that when a network partition splits a distributed system, each side must choose between consistency (every read sees the latest write) and availability (every request gets a non-error reply). It is not a free pick of two of three: without a partition you can have both, and the everyday trade-off is latency against consistency, which PACELC describes. A CP design, such as a primary with synchronous replicas, refuses some requests during a partition. An AP design, such as Cassandra at consistency level ONE, keeps answering and lets replicas converge later. Payments usually need CP.
Practice questions for November 2026
New every month3 questions from the DBMS mock test, the way they appear in it. A new set comes on the 1st of every month. Pick your answer, then open it.
-
In R(A, B, C), the only candidate key is A. How many superkeys does R have?
- 1
- 4
- 3
- 7
Show the answer
Answer: 4. A superkey is any set that contains a candidate key. With A fixed in, B and C can each be in or out, so there are 2 x 2 = 4: A, AB, AC and ABC. Counting by brute force over all 7 non-empty subsets gives the same 4.
-
F1 = { A -> B, B -> C, A -> C }, F2 = { A -> B, B -> C } and F3 = { A -> B, A -> C }. Which statement is true?
- F1 and F2 are equivalent; F3 is not equivalent to them
- F2 and F3 are equivalent; F1 is not
- None of the three sets is equivalent to another
- F1, F2 and F3 are all equivalent
Show the answer
Answer: F1 and F2 are equivalent; F3 is not equivalent to them. Two sets are equivalent when each implies every FD of the other. A -> C in F1 follows from F2 by transitivity, so F1 and F2 match. F3 cannot derive B -> C, because B+ under F3 is only B.
-
R(A, B, C, D) has F = { AB -> CD, D -> A }. What is the highest normal form R is in?
- BCNF
- 1NF
- 3NF
- 2NF
Show the answer
Answer: 3NF. B is in every key; AB+ = ABCD and BD+ = ABCD, so the keys are AB and BD and C is the only non-prime attribute. D -> A is allowed in 3NF because A is prime, but D is no superkey, so BCNF fails: 3NF.
Ready to apply?
There are 17,463 open IT jobs on CoreHRx right now. Create a free profile once and apply with it.