Interview questions › DBMS
DBMS interview questions
1. What is a DBMS? Why not just use files?
Easy
A Database Management System stores, retrieves and manages data. Compared to plain files it removes redundancy and inconsistency, supports concurrent access, transactions, security, backup/recovery and powerful queries (SQL).
2. What is an RDBMS?
Easy
A relational DBMS stores data in tables (relations) of rows and columns with relationships through keys, and uses SQL. Examples: MySQL, PostgreSQL, Oracle, SQL Server.
3. What are the different keys in DBMS?
Easy
Super key (any set that uniquely identifies a row), candidate key (minimal super key), primary key (chosen candidate key, unique + not null), alternate key, foreign key (references another table's primary key) and composite key (multiple columns).
4. Primary key vs unique key?
Easy
Both enforce uniqueness. A table has only one primary key and it can't be NULL; it can have many unique keys and they can allow NULL (one NULL in some DBs). The primary key is often the clustered index.
5. What is a foreign key?
Easy
A column (or set) in one table that refers to the primary key of another table, enforcing referential integrity — you can't insert an order for a customer that doesn't exist. Actions like ON DELETE CASCADE define what happens to child rows.
6. What is normalization?
Easy
The process of organising tables to reduce redundancy and avoid insertion, update and deletion anomalies, by splitting tables based on functional dependencies (1NF, 2NF, 3NF, BCNF).
7. Explain 1NF, 2NF, 3NF and BCNF.
Medium
1NF: atomic values. 2NF: 1NF + no partial dependency on part of a composite key. 3NF: 2NF + no transitive dependency (non-key → non-key). BCNF: for every dependency X → Y, X is a super key.
8. What is denormalization?
Medium
Intentionally adding redundancy (combining tables, storing derived data) to make reads faster and avoid expensive joins, at the cost of more storage and harder updates. Common in reporting and NoSQL systems.
9. What is a functional dependency?
Medium
X → Y means the value of X uniquely determines the value of Y (e.g. roll_no → name). Functional dependencies are the basis for normalization.
10. What are anomalies in DBMS?
Easy
Problems caused by redundancy: insertion anomaly (can't add data without unrelated data), update anomaly (must change the same fact in many rows), deletion anomaly (deleting a row loses other facts).
11. What is an ER diagram?
Easy
An Entity-Relationship diagram models data with entities (rectangles), attributes (ovals) and relationships (diamonds), including cardinalities like 1:1, 1:N and M:N. It is used in database design before creating tables.
12. What is a transaction?
Easy
A logical unit of work containing one or more operations that must all succeed or all fail, e.g. a money transfer (debit + credit).
13. What are ACID properties?
Easy
Atomicity (all or nothing), Consistency (constraints remain valid), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes).
14. What are the transaction states?
Easy
Active → Partially committed → Committed; or Active → Failed → Aborted (rolled back), after which it may be restarted or killed.
15. What are dirty read, non-repeatable read and phantom read?
Medium
Dirty read: reading uncommitted changes of another transaction. Non-repeatable read: the same row returns different values in one transaction. Phantom read: re-running a query returns new/missing rows.
16. What are the isolation levels?
Medium
Read Uncommitted (allows dirty reads), Read Committed (no dirty reads), Repeatable Read (no non-repeatable reads), Serializable (no phantoms — transactions behave as if run one after another). Higher isolation = more locking, less concurrency.
17. What is a deadlock in DBMS and how is it handled?
Medium
Two transactions wait for locks held by each other. Databases detect it with a wait-for graph and abort one transaction (the victim), or prevent it with timestamp schemes like wait-die / wound-wait.
18. What is two-phase locking?
Hard
A protocol where a transaction first acquires all locks it needs (growing phase) and then only releases them (shrinking phase). It guarantees conflict-serializability; strict 2PL holds exclusive locks until commit to avoid cascading rollbacks.
19. Shared lock vs exclusive lock?
Easy
A shared (S) lock allows reading; many transactions can hold it together. An exclusive (X) lock allows writing; only one transaction can hold it and no other locks are allowed on that item.
20. What is serializability?
Hard
A concurrent schedule is serializable if its result equals some serial (one-after-another) execution of the same transactions. Conflict-serializability is checked with a precedence graph — no cycle means serializable.
21. What is an index? Pros and cons?
Easy
An index is a data structure (usually a B+ tree) that speeds up searching rows by certain columns. Pros: much faster SELECT/JOIN/ORDER BY. Cons: extra storage and slower INSERT/UPDATE/DELETE because indexes must be maintained.
22. Clustered vs non-clustered index?
Medium
A clustered index stores table rows physically in index order — only one per table. A non-clustered index is a separate structure with pointers to rows — a table can have many.
23. Why do databases use B+ trees?
Hard
B+ trees are balanced with high fan-out, so lookups need very few disk reads (O(log n)). All records are in linked leaf nodes, making range queries and sequential scans efficient.
24. What is the difference between DELETE, TRUNCATE and DROP?
Easy
DELETE removes selected rows (WHERE), logs each row, can be rolled back, fires triggers. TRUNCATE removes all rows quickly and resets storage. DROP deletes the whole table structure and data.
25. What are DDL, DML, DCL and TCL?
Easy
DDL defines structure (CREATE, ALTER, DROP, TRUNCATE). DML manipulates data (INSERT, UPDATE, DELETE, SELECT). DCL controls access (GRANT, REVOKE). TCL controls transactions (COMMIT, ROLLBACK, SAVEPOINT).
26. WHERE vs HAVING?
Easy
WHERE filters individual rows before grouping and can't use aggregates. HAVING filters groups after GROUP BY and can use aggregates like COUNT(*) > 5.
27. What are the types of joins?
Easy
INNER (matching rows), LEFT (all left rows + matches), RIGHT (all right rows + matches), FULL OUTER (all rows from both), CROSS (Cartesian product) and SELF join (table joined with itself).
28. Write a query to find the second highest salary.
Medium
SELECT MAX(salary) FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);
Or with DENSE_RANK(): SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) r FROM employee) t WHERE r = 2;
29. Write a query to find duplicate records.
Easy
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
30. What is a subquery? Correlated vs non-correlated?
Medium
A query nested inside another. A non-correlated subquery runs once independently. A correlated subquery refers to the outer query's columns and runs once for each outer row (slower).
31. What is a view?
Easy
A virtual table defined by a stored SELECT query. It simplifies complex queries and restricts access to certain columns/rows; it stores no data itself (except materialized views, which store the result).
32. What is a stored procedure?
Medium
A precompiled set of SQL statements stored in the database and executed by name with parameters. It reduces network traffic, reuses logic and can improve security.
33. What is a trigger?
Medium
SQL code that runs automatically on an event (INSERT, UPDATE, DELETE) on a table — e.g. writing an audit log whenever a salary changes.
34. What are aggregate functions?
Easy
Functions that compute one value from many rows: COUNT, SUM, AVG, MIN, MAX. Usually used with GROUP BY.
35. What are window functions?
Hard
Functions that compute over a set of rows related to the current row without collapsing them: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), running SUM() OVER (...).
36. RANK vs DENSE_RANK vs ROW_NUMBER?
Medium
For salaries 100, 100, 90: ROW_NUMBER → 1, 2, 3 (always unique); RANK → 1, 1, 3 (gaps after ties); DENSE_RANK → 1, 1, 2 (no gaps).
37. UNION vs UNION ALL?
Easy
UNION combines results of two queries and removes duplicates (extra sort/hash work). UNION ALL keeps duplicates and is faster.
38. What is a NULL value?
Easy
NULL means "unknown/missing", not zero or an empty string. Comparisons with NULL return unknown, so use IS NULL / IS NOT NULL; aggregate functions except COUNT(*) ignore NULLs.
39. CHAR vs VARCHAR?
Easy
CHAR(n) is fixed length — always uses n characters (padded). VARCHAR(n) is variable length — uses only what is needed plus a small length overhead.
40. What are constraints?
Easy
Rules on columns: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK (e.g. age ≥ 18) and DEFAULT. They keep data valid.
41. What is SQL injection and how do you prevent it?
Medium
An attack where user input is inserted into SQL text and changes the query (e.g. ' OR '1'='1). Prevent it with parameterised queries/prepared statements, ORMs, input validation and least-privilege DB users.
42. What is the difference between SQL and NoSQL?
Easy
SQL databases are relational with fixed schemas, joins and strong ACID transactions, and usually scale vertically. NoSQL databases (document, key-value, column, graph) have flexible schemas and are built to scale horizontally, often with eventual consistency.
43. What are the types of NoSQL databases?
Easy
Document (MongoDB), key-value (Redis, DynamoDB), wide-column (Cassandra, HBase) and graph (Neo4j).
44. What is sharding?
Medium
Splitting a large database horizontally across multiple servers by a shard key (e.g. user_id), so each server holds part of the data. It scales writes and storage.
45. What is replication?
Medium
Keeping copies of the database on multiple servers. Usually one primary handles writes and replicas handle reads; it improves read throughput and availability (failover).
46. What is the CAP theorem?
Medium
A distributed data store can guarantee at most two of Consistency, Availability and Partition tolerance. Since network partitions happen, real systems choose between CP and AP behaviour during a partition.
47. What is a checkpoint in DBMS?
Hard
A point where the DBMS writes all modified buffers and log records to disk. During crash recovery it only needs to redo/undo from the last checkpoint, which speeds up recovery.
48. What is the write-ahead log (WAL)?
Hard
Before any change is written to the data files, the change is first written to a log on stable storage. After a crash, the log is used to redo committed and undo uncommitted transactions — ensuring atomicity and durability.
49. What is a cursor?
Medium
A database object that lets you iterate over the rows of a query result one at a time inside stored procedures. Useful for row-by-row logic but slower than set-based SQL.
50. What is data independence?
Medium
Physical data independence: changing storage (indexes, files) doesn't affect the logical schema. Logical data independence: changing the logical schema (adding a column) doesn't break application views.