DBMS Interview Questions for Freshers (with SQL Examples)

Q: What is the difference between a primary key and a unique key?

Both prevent duplicate values. A primary key cannot be NULL and a table can have only one. A unique key can usually allow NULL and a table can have several.

Q: What are ACID properties in DBMS?

Atomicity, Consistency, Isolation and Durability. Together they make sure database transactions are processed reliably.

Q: Which database should I practise SQL on?

MySQL is the most common choice for freshers in India and is free. The queries in this guide run on MySQL 8 and most other SQL databases.

Why DBMS Is Asked in Almost Every Technical Interview

Database Management Systems (DBMS) is one of the three core subjects interviewers test freshers on, along with OOPs and operating systems. Almost every software application stores data, so companies such as TCS, Infosys, Wipro, Accenture, Cognizant and product companies expect you to understand keys, normalization, transactions and basic SQL.

Interviewers usually ask a few theory questions and then one or two SQL queries, such as finding the second-highest salary. Freshers lose marks by memorising definitions without understanding them, or by freezing on simple queries.

This guide covers the most frequently asked DBMS questions with short, clear answers, then gives you 8 commonly asked SQL queries with their output. Every query has been run on the sample tables shown below, and the output is exactly what you will see.

Basic DBMS Interview Questions

Q1. What is a DBMS, and how is it different from an RDBMS?

A DBMS is software that stores, manages and retrieves data. An RDBMS (Relational DBMS) stores data in tables with rows and columns and links tables using keys. MySQL, PostgreSQL, Oracle and SQL Server are RDBMSs. Most interview questions assume an RDBMS.

Q2. What are the advantages of a DBMS over a file system?

A DBMS reduces duplicate data, keeps data consistent, controls who can access what, supports many users at the same time, provides backup and recovery, and lets you search data quickly with a query language such as SQL.

Q3. Explain the different types of keys.

Keys are one of the most asked topics. Learn this table:

Key	Meaning	Example (employee table)
Super key	Any set of columns that uniquely identifies a row	{emp_id}, {emp_id, name}
Candidate key	A minimal super key, with no extra columns	emp_id, or email if unique
Primary key	The candidate key chosen to identify rows; unique and not NULL	emp_id
Alternate key	A candidate key not chosen as primary	email
Foreign key	A column that refers to the primary key of another table	dept_id → department(dept_id)
Composite key	A key made of two or more columns	{student_id, course_id} in enrollments

Q4. What are the ACID properties?

Atomicity: a transaction happens completely or not at all. Consistency: the database moves from one valid state to another. Isolation: transactions running at the same time do not interfere with each other. Durability: once committed, changes survive a crash. A bank transfer is the classic example: money must leave one account and arrive in the other, or neither should happen.

Q5. What is normalization? Explain 1NF, 2NF, 3NF and BCNF.

Normalization organises tables to reduce duplicate data and avoid insert, update and delete problems.

Normal form	Rule	Simple meaning
1NF	Every column holds a single, atomic value	No lists like "Java, Python" in one cell
2NF	1NF + no partial dependency on part of a composite key	Every non-key column depends on the whole key
3NF	2NF + no transitive dependency	Non-key columns depend only on the key, not on other non-key columns
BCNF	For every dependency X → Y, X is a super key	A stricter version of 3NF

Example: if an employee table stores dept_id and dept_name together, dept_name depends on dept_id, not on emp_id. That breaks 3NF, so dept_name moves into a separate department table, as in our sample tables below.

Q6. What is denormalization, and when is it used?

Denormalization deliberately adds some duplicate data to make reads faster by avoiding joins. It is used in reporting systems and high-traffic applications where read speed matters more than storage.

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

A very common question:

Command	What it does	WHERE allowed	Can roll back	Type
DELETE	Removes selected rows	Yes	Yes, inside a transaction	DML
TRUNCATE	Removes all rows, keeps the table	No	Usually no (depends on the database)	DDL
DROP	Removes the whole table and its structure	No	No	DDL

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

DDL (Data Definition Language) defines structure: CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation Language) changes data: INSERT, UPDATE, DELETE. DCL (Data Control Language) manages permissions: GRANT, REVOKE. TCL (Transaction Control Language) manages transactions: COMMIT, ROLLBACK, SAVEPOINT. SELECT is sometimes grouped separately as DQL.

Intermediate DBMS Interview Questions

Q9. What is an index? What is the difference between clustered and non-clustered indexes?

An index is a data structure, usually a B-tree, that makes searching faster, like the index at the back of a book. A clustered index sets the physical order of rows in the table, so a table can have only one. A non-clustered index is a separate structure that points to the rows, so a table can have many. Indexes speed up reads but slow down inserts and updates slightly.

Q10. What problems can happen when transactions run at the same time?

Dirty read: reading data another transaction has not committed yet. Non-repeatable read: reading the same row twice and getting different values because another transaction changed it. Phantom read: running the same query twice and getting new rows that another transaction inserted. Isolation levels (Read Uncommitted, Read Committed, Repeatable Read and Serializable) control which of these are allowed.

Q11. What is a deadlock?

A deadlock happens when two transactions each hold a lock the other needs, so both wait forever. For example, T1 locks row A and waits for row B, while T2 locks row B and waits for row A. Databases detect this and cancel one transaction so the other can continue.

Q12. What is a view?

A view is a saved SELECT query that behaves like a virtual table. It simplifies complex queries and can hide sensitive columns, for example showing employee names without salaries.

Q13. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping and cannot use aggregate functions such as COUNT. HAVING filters groups after GROUP BY and can use aggregates. Query 3 below uses both ideas.

Q14. What is a trigger?

A trigger is code that runs automatically when an INSERT, UPDATE or DELETE happens on a table. For example, it can write a record to an audit table every time a salary changes.

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

SQL databases store data in tables with a fixed structure and are strong for transactions and complex queries. NoSQL databases, such as MongoDB, store data as documents, key-value pairs or graphs with a flexible structure, and scale easily for very large or fast-changing data.

8 SQL Queries Interviewers Ask Most

All queries below use these two sample tables. Copy them into MySQL or any SQL editor and run the queries yourself:

SQL

CREATE TABLE department (
    dept_id   INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employee (
    emp_id     INT PRIMARY KEY,
    name       VARCHAR(50),
    salary     INT,
    dept_id    INT,
    manager_id INT,
    FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);

INSERT INTO department VALUES (1, 'IT'), (2, 'HR'), (3, 'Sales');

INSERT INTO employee VALUES
(101, 'Asha',   90000, 1, NULL),
(102, 'Ravi',   70000, 1, 101),
(103, 'Neha',   95000, 1, 101),
(104, 'Karan',  50000, 2, NULL),
(105, 'Priya',  60000, 2, 104),
(106, 'Ravi',   70000, 1, 101),
(107, 'Meera',  40000, NULL, NULL);

Query 1: Second-Highest Salary

SQL

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Output: 90000

Find the highest salary below the maximum. This is the single most asked SQL question in fresher interviews.

Query 2: Nth-Highest Salary (Here, 3rd)

SQL

SELECT DISTINCT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employee
) ranked
WHERE rnk = 3;

Output: 70000

DENSE_RANK gives equal salaries the same rank without skipping numbers, so duplicates are handled correctly. Change 3 to any N. This needs MySQL 8 or later.

Query 3: Departments with More Than One Employee

SQL

SELECT d.dept_name, COUNT(*) AS employees
FROM employee e
JOIN department d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING COUNT(*) > 1
ORDER BY employees DESC;

Output: IT | 4 HR | 2

GROUP BY makes one group per department, and HAVING filters the groups. Meera is not counted because she has no department.

Query 4: Find Duplicate Records

SQL

SELECT name, salary, COUNT(*) AS copies
FROM employee
GROUP BY name, salary
HAVING COUNT(*) > 1;

Output: Ravi | 70000 | 2

Grouping by the columns that should be unique, then keeping groups with more than one row, finds duplicates.

Query 5: Employees Who Earn More Than Their Manager

SQL

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employee e
JOIN employee m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

Output: Neha | 95000 | Asha | 90000 Priya | 60000 | Karan | 50000

This is a self join: the employee table is joined to itself, once as the employee (e) and once as the manager (m).

Query 6: Highest-Paid Employee in Each Department

SQL

SELECT d.dept_name, e.name, e.salary
FROM employee e
JOIN department d ON e.dept_id = d.dept_id
WHERE e.salary = (
    SELECT MAX(salary) FROM employee WHERE dept_id = e.dept_id
)
ORDER BY d.dept_name;

Output: HR | Priya | 60000 IT | Neha | 95000

A correlated subquery finds the maximum salary for each employee's own department. If two people tie, both are returned.

Query 7: Employees Without a Department

SQL

SELECT e.name
FROM employee e
LEFT JOIN department d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

Output: Meera

A LEFT JOIN keeps every employee. Where no department matches, the department columns are NULL, which the WHERE clause picks out.

Query 8: Departments with No Employees

SQL

SELECT d.dept_name
FROM department d
LEFT JOIN employee e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

Output: Sales

The same LEFT JOIN idea, reversed, finds departments that no employee belongs to.

Common Mistakes Freshers Make

Confusing primary key and unique key. Both are unique, but a primary key cannot be NULL and a table has only one. A table can have many unique keys.
Using WHERE with COUNT. Aggregate conditions belong in HAVING, not WHERE.
Forgetting NULL rules. NULL is never equal to anything, even another NULL. Use IS NULL, not = NULL.
Giving definitions without examples. Always add a small example, such as a bank transfer for atomicity.
Not knowing your project's database. If your project uses MySQL, be ready to explain your tables, keys and one query you wrote.

How to Prepare DBMS in One Week

Day 1: Keys and the ER model. Draw the tables and keys for your own project.
Day 2: Normalization. Take a messy table and normalise it to 3NF on paper.
Day 3: ACID, transactions, concurrency problems and deadlocks.
Day 4: Indexes, views, triggers, and DELETE vs TRUNCATE vs DROP.
Days 5–6: Install MySQL or use an online SQL editor. Type and run all 8 queries above, then change them, for example to the 2nd-lowest salary.
Day 7: Mock interview: answer 10 random questions from this page out loud in under a minute each.

DBMS is usually asked alongside OOPs and coding. Prepare all three with our OOPs Interview Questions, Stack and Queue and Binary Tree guides.

Frequently Asked Questions

What are the most important DBMS topics for interviews?

Keys, normalization, ACID properties, transactions and concurrency, indexing, joins, and DELETE vs TRUNCATE vs DROP, plus basic SQL queries.

How do I find the second-highest salary in SQL?

Use SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee). For any Nth salary, use DENSE_RANK in MySQL 8 or later.

What is the difference between a primary key and a unique key?