DBMS Interview Questions for Freshers: 25 Questions With Answers

Last updated: 13 October 2026 · By the Asuraa Team

Quick answer: The DBMS interview questions freshers face most cover keys, normalization (1NF, 2NF, 3NF, BCNF), ACID properties, isolation levels, deadlocks, indexing and SQL joins. Know one database's real behaviour: PostgreSQL's documentation states Read Committed is its default isolation level and that it never allows dirty reads. Answer each question with a definition, a small table example and, where it helps, a query.

Key takeaways

• Fresher DBMS interviews concentrate on keys, ER modelling, normalization, transactions and concurrency, indexing and SQL joins.
• PostgreSQL's documentation states that Read Committed is its default isolation level, that Serializable prevents all read phenomena, and that dirty reads never occur even at Read Uncommitted.
• PostgreSQL logs a transaction's updates to permanent storage before reporting the commit as complete, which is how durability survives a crash.
• Every index must be kept in sync with its table, so it speeds reads but adds overhead to inserts, updates and deletes.
• Asuraa's October 2026 job market report found SQL named in 20.1% of the fresher listings it analysed.

DBMS interviews for freshers cover five areas: database basics and keys, ER modelling, normalization, transactions and concurrency, and indexing plus SQL. The 25 questions below follow that order, each with a complete answer and a query where it helps. SQL itself is a hiring signal: Asuraa's October 2026 job market report found SQL named in 20.1% of the fresher listings it analysed, so DBMS theory and SQL practice pay off together.

Which DBMS topics carry the most weight in fresher interviews?

Normalization, keys, ACID properties and joins carry the most weight, because they appear in almost every technical round for software and data roles.

For all other subjects, use the interview questions by subject guide.

What are the basic DBMS questions for freshers?

1. What is the difference between DBMS and RDBMS?

A DBMS is any software that stores, retrieves and manages data, which can include file-based or hierarchical systems. An RDBMS stores data in tables (relations) of rows and columns, links tables through keys and uses SQL. RDBMS products such as PostgreSQL, MySQL, Oracle and SQL Server enforce integrity constraints and support transactions, which simple DBMSs may not.

2. Why use a DBMS instead of a file system?

A DBMS reduces redundancy and inconsistency, lets many users access data at once safely, enforces integrity rules, controls access through permissions and supports backup and recovery. With plain files, each program handles its own format, so the same customer address can exist in several files with different values. Queries are also far easier with SQL than with custom file-parsing code.

3. What are the different types of keys?

A super key is any set of columns that uniquely identifies a row. A candidate key is a minimal super key, and the primary key is the candidate key you choose; it cannot be null. An alternate key is any candidate key not chosen as primary. A foreign key is a column that refers to the primary key of another table, and a composite key uses more than one column.

CREATE TABLE enrolments (
  student_id INT REFERENCES students(id),
  course_id  INT REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

4. What are DDL, DML, DCL and TCL?

DDL (Data Definition Language) defines structure: CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation Language) works with rows: SELECT, INSERT, UPDATE, DELETE. DCL (Data Control Language) manages permissions with GRANT and REVOKE. TCL (Transaction Control Language) manages transactions with COMMIT, ROLLBACK and SAVEPOINT.

5. What is an ER model?

An entity-relationship model is a diagram of the data before you create tables. Entities are things such as Student or Course, attributes are their properties, and relationships connect entities with a cardinality of one-to-one, one-to-many or many-to-many. A many-to-many relationship becomes a separate junction table in the relational design, like enrolments above.

6. What is the three-level architecture of a DBMS?

The internal level describes how data is physically stored, the conceptual level describes the whole logical schema (tables, columns, relationships), and the external level gives each user or application its own view. The separation provides data independence: you can change storage without changing the logical schema, and change the logical schema without breaking every application.

What normalization questions are asked?

7. What is normalization and why is it needed?

Normalization is organising tables to reduce redundancy and avoid insert, update and delete anomalies. For example, if a single table stores the department name on every employee row, renaming a department means updating many rows, and missing one leaves inconsistent data. Normalization splits such data into related tables linked by keys.

8. Explain 1NF, 2NF and 3NF with an example.

1NF requires atomic values: no lists or repeating groups in a column. 2NF requires 1NF plus no partial dependency, meaning no non-key column depends on only part of a composite key. 3NF requires 2NF plus no transitive dependency, meaning non-key columns depend only on the key, not on other non-key columns. In orders(order_id, customer_id, customer_city), customer_city depends on customer_id, not on order_id, so 3NF moves it into a customers table.

9. What is BCNF?

Boyce-Codd Normal Form is a stricter version of 3NF: for every non-trivial functional dependency X determines Y, X must be a super key. Most tables in 3NF are already in BCNF; the difference appears only with overlapping candidate keys. Interviewers usually just want the definition and the fact that BCNF is stronger than 3NF.

10. What is a functional dependency?

A functional dependency X determines Y means that whenever two rows have the same X value, they must have the same Y value. For example, roll_no determines student_name. Normal forms are defined in terms of these dependencies, which is why the concept comes before normalization questions.

11. What is denormalization and when is it used?

Denormalization deliberately adds redundancy, such as storing a computed total or a copied customer name, to make frequent reads faster by avoiding joins. It is common in reporting databases and data warehouses where reads far outnumber writes. The cost is extra storage and the work of keeping duplicated values in sync.

What transaction and concurrency questions come up?

12. What are the ACID properties?

Atomicity means a transaction happens completely or not at all. Consistency means it moves the database from one valid state to another, respecting constraints. Isolation means concurrent transactions do not see each other's intermediate states. Durability means committed changes survive crashes; the PostgreSQL transactions tutorial explains that updates are logged to permanent storage before a commit is reported as complete.

13. How do COMMIT, ROLLBACK and SAVEPOINT work?

A transaction groups statements between BEGIN and COMMIT; ROLLBACK cancels everything since BEGIN. A savepoint marks a point you can roll back to without discarding the whole transaction. The classic example is a bank transfer: the debit and credit must both happen or neither.

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

14. What are isolation levels and read anomalies?

The SQL standard defines four isolation levels: Read Uncommitted, Read Committed, Repeatable Read and Serializable. They control which anomalies can occur: dirty reads (seeing uncommitted data), non-repeatable reads (a row changes between two reads) and phantom reads (new rows appear in a repeated query). Per the PostgreSQL isolation documentation, Read Committed is PostgreSQL's default, Serializable prevents all these phenomena, and PostgreSQL never allows dirty reads even at Read Uncommitted.

15. What is a deadlock and how is it handled?

A deadlock happens when two transactions each hold a lock the other needs, so both wait forever. For example, T1 locks row A and wants row B, while T2 locks row B and wants row A. Databases detect the cycle and abort one transaction so the other can proceed. You reduce deadlocks by accessing rows in a consistent order and keeping transactions short.

16. What are shared and exclusive locks?

A shared lock allows a transaction to read an item, and many transactions can hold shared locks on it at once. An exclusive lock is needed to write, and no other transaction can hold any lock on that item meanwhile. Two-phase locking, where a transaction acquires all its locks before releasing any, guarantees serializable schedules but can cause deadlocks.

What indexing questions should freshers prepare?

17. What is an index and what does it cost?

An index is a separate data structure that lets the database find matching rows without scanning the whole table, much like the index at the back of a book. The PostgreSQL indexes introduction notes that after an index is created the system must keep it in sync with the table, which adds overhead to inserts, updates and deletes. Index columns that are frequently filtered or joined on, and drop indexes that queries never use.

CREATE INDEX idx_jobs_city ON jobs (city);

18. What is the difference between clustered and non-clustered indexes?

A clustered index stores the table's rows in the order of the index key, so a table can have only one. MySQL's InnoDB index documentation says the clustered index stores the row data and is typically the primary key. A non-clustered (secondary) index is a separate structure, and a table can have many; in InnoDB each secondary index entry also stores the primary key so the full row can be found. Range queries on the clustered key are fast because matching rows sit together.

19. Why are B-tree indexes the common default?

A B-tree keeps keys sorted in a balanced tree, so lookups, range queries (BETWEEN, <, >) and ORDER BY all work efficiently in logarithmic time. A hash index only supports equality lookups. Because most queries mix equality and ranges, most relational databases use a B-tree when you create an index without specifying a type.

What SQL questions are asked in DBMS rounds?

20. What is the difference between DELETE, TRUNCATE and DROP?

DELETE is DML: it removes selected rows with an optional WHERE, fires row triggers and can be rolled back. TRUNCATE is DDL: it removes all rows quickly while keeping the table structure. DROP removes the table itself, including its structure and indexes. Rollback behaviour for TRUNCATE varies by database, so mention that if asked.

21. What are the types of joins?

An INNER JOIN returns rows with matches in both tables. A LEFT JOIN returns all rows from the left table plus matches from the right, with nulls where there is no match; RIGHT JOIN is the mirror. A FULL OUTER JOIN returns all rows from both sides, and a CROSS JOIN returns every combination. A self join joins a table to itself, such as employees to their managers.

22. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping, and HAVING filters groups after GROUP BY and aggregation. You cannot use an aggregate like COUNT(*) in WHERE.

SELECT city, COUNT(*) AS openings
FROM jobs
WHERE status = 'open'
GROUP BY city
HAVING COUNT(*) > 10;

23. How do you find the second-highest salary?

Use a subquery that finds the maximum below the overall maximum, or a window function such as DENSE_RANK, which also handles ties and generalises to the Nth highest.

SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees) t
WHERE r = 2;

24. What is a view?

A view is a stored query that behaves like a virtual table; it holds no data itself unless it is a materialised view. Views simplify complex joins for users and restrict access, for example exposing employee names but not salaries. Simple views can be updatable; views with joins or aggregates usually are not.

25. What is the difference between SQL and NoSQL databases?

SQL databases store data in tables with a fixed schema, support joins and offer strong ACID transactions, which suits banking, orders and HR systems. NoSQL databases (document, key-value, wide-column, graph) use flexible schemas and are designed to scale horizontally, which suits rapidly changing or very large datasets. Many applications use both; the choice depends on the data's shape and consistency needs.

How should you revise DBMS in 7 days before the interview?

Pair theory with queries every day, using a free local PostgreSQL or MySQL install and a small students-courses dataset. The technical interview preparation guide covers how to answer under time pressure.

1. Day 1: Basics and keys. DBMS vs RDBMS, keys, DDL/DML/DCL/TCL. Create three related tables with constraints.
2. Day 2: ER modelling. Draw ER diagrams for a library and a college system, then convert them to tables.
3. Day 3: Normalization. Take one messy table and normalise it to 3NF, writing the functional dependencies first.
4. Day 4: Transactions. ACID, isolation levels and anomalies, deadlock. Run a transfer with BEGIN and ROLLBACK.
5. Day 5: Indexing. Create an index, compare query plans with EXPLAIN, and revise clustered vs non-clustered.
6. Day 6: SQL practice. Write fifteen queries: joins, GROUP BY with HAVING, subqueries, second-highest salary, window functions.
7. Day 7: Mock round. Answer all 25 questions aloud and explain the schema of one of your own projects.

For a deeper SQL bank, see SQL interview questions, and for a learning path, how to learn SQL for jobs.

What do most guides on DBMS interview questions get wrong?

Most guides give textbook definitions of normal forms but never show a table being normalised, so candidates freeze when the interviewer writes a real table on the board. Practise identifying functional dependencies first; the normal form follows from them. Guides also present isolation levels as if every database behaves identically, while PostgreSQL's documentation shows it treats Read Uncommitted as Read Committed and prevents phantom reads at Repeatable Read. Finally, many lists say "indexes make queries faster" without the write cost. Saying when not to add an index is what makes an answer stand out.

Related guides

• Interview Questions by Subject: Java, Python, SQL, DSA, Cloud, AI
• React Interview Questions for Freshers: 22 Questions With Answers
• OOPs Interview Questions for Freshers: 25 Questions With Code Examples
• JavaScript Interview Questions for Freshers: 25 Questions With Answers

FAQ

What are the most important DBMS topics for a fresher interview?

Focus on keys (primary, candidate, foreign), normalization up to BCNF, ACID properties, isolation levels and anomalies, deadlocks, indexing including clustered vs non-clustered, and SQL joins with GROUP BY and HAVING. Expect at least one query to write, commonly the second-highest salary or a join with aggregation.

How do I explain normalization in an interview?

Say that normalization organises tables to remove redundancy and avoid update, insert and delete anomalies. Then show an example: a table storing customer city on every order row violates 3NF because city depends on the customer, not the order, so you move it into a customers table linked by customer_id.

What is the default isolation level in PostgreSQL and MySQL?

PostgreSQL's official documentation states that Read Committed is its default isolation level. MySQL's documentation states that InnoDB uses Repeatable Read by default. Interviewers rarely need the exact default, but mentioning that it differs between databases, and what each level prevents, shows real understanding.

Is DBMS asked in interviews for non-database roles?

Yes. DBMS is a core computer science subject and comes up in technical rounds for software developer, data analyst and testing roles because almost every application stores data. The depth varies: developers get normalization and transactions, while analyst roles lean more on SQL queries and joins.

How many SQL queries should I practise before a DBMS interview?

Practise enough to write joins, GROUP BY with HAVING, subqueries and a window function without looking anything up, which for most freshers means around fifteen to thirty varied queries on a small sample database. Run them on a real database such as PostgreSQL or MySQL rather than only reading answers.

Final thoughts

DBMS rounds reward freshers who can take a real table, find the redundancy and fix it, then write the query that answers a business question. Practise on an actual database every day of the plan, and be ready to explain the schema of your own project. When you start applying, look for software and data roles on Asuraa jobs.