Autocoder

DBMS Interview Questions and Answers

Last updated 2026-11-19

This page is a broad DBMS question bank for campus and fresher interviews. It covers the questions that come up in almost every database round: what a DBMS is, how SQL commands are grouped, how joins and aggregates behave, and what views, triggers and deadlocks do. Each answer is written the way you would say it aloud, with a short example where an example helps.

Deeper topics have their own pages: normalization, transactions and ACID, indexing and keys. For a study plan rather than questions, read how to crack a DBMS interview.

LevelEasy to medium, with a few hard ones
CodeSQL, standard syntax with MySQL and PostgreSQL notes
Best forFreshers and campus placement rounds

How DBMS rounds are usually structured

Interviewers tend to start with definitions, move to SQL on a small table you can draw on paper, and finish with one design or concurrency question. If you can state a definition in one sentence and then give a ten-row example, you are ahead of most candidates.

The sample table used in several answers below is employee(id, name, dept_id, salary) and department(dept_id, dept_name). Keep a similar pair in your head so that any question can be answered with a concrete query.

Common mistakes to avoid

DBMS Interview Questions and Answers: questions with worked answers

  1. 1

    What is a DBMS, and how is it better than storing data in files?

    easy

    A database management system is software that stores, retrieves and protects data for many users at once. Compared with flat files it gives a query language (SQL), controlled redundancy, integrity constraints, concurrent access with isolation, crash recovery through logging, and access control. With files each application re-implements parsing, locking and backup, and two programs can easily leave the data inconsistent. A DBMS centralises those concerns so applications only describe what data they want.

  2. 2

    Explain the three-schema architecture and data independence.

    easy

    The architecture has three levels. The internal (physical) schema describes storage: files, pages and indexes. The conceptual (logical) schema describes tables, columns and constraints for the whole organisation. The external schema is the per-user or per-application view. Physical data independence means you can change storage, for example add an index, without changing the conceptual schema. Logical data independence means you can change the conceptual schema, for example add a column, without breaking existing external views and applications. Logical independence is harder to achieve in practice.

  3. 3

    What are DDL, DML, DCL and TCL? Give an example of each.

    easy

    DDL defines structure: CREATE, ALTER, DROP, TRUNCATE. DML manipulates rows: INSERT, UPDATE, DELETE (and SELECT in most textbooks, though some put it under DQL). DCL controls permissions: GRANT and REVOKE. TCL controls transactions: COMMIT, ROLLBACK and SAVEPOINT. A common follow-up is that in MySQL and Oracle, DDL statements cause an implicit commit, while in PostgreSQL DDL is transactional and can be rolled back.

  4. 4

    What is the difference between DELETE, TRUNCATE and DROP?

    easy

    DELETE is DML: it removes rows that match an optional WHERE clause, fires row triggers and is logged row by row, so it can be rolled back inside a transaction. TRUNCATE is DDL: it removes all rows quickly by deallocating pages, takes no WHERE clause, usually resets auto-increment counters and does not fire row-level delete triggers. DROP removes the table itself, with its data, indexes and privileges. Use DELETE for selective removal, TRUNCATE to empty a table, DROP to remove the table.

  5. 5

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

    easy

    A primary key uniquely identifies each row, cannot be NULL and there is only one per table. A unique key also enforces uniqueness but a table can have several, and most systems allow NULLs in it (the number of NULLs allowed differs: SQL Server permits one, MySQL and PostgreSQL permit many). A foreign key is a column whose values must match a primary or unique key in another table, which enforces referential integrity. See DBMS keys questions for candidate and super keys.

  6. 6

    Explain INNER, LEFT, RIGHT and FULL joins with an example.

    medium

    INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row of the left table, with NULLs for right-table columns when there is no match. RIGHT JOIN is the mirror image. FULL OUTER JOIN returns all rows from both sides, matching where possible. To list employees together with their department name, including employees whose dept_id is NULL, you need a LEFT JOIN from employee to department. A CROSS JOIN produces the Cartesian product, m times n rows. More join practice is on the SQL joins page.

    sql
    SELECT e.name, d.dept_name
    FROM employee e
    LEFT JOIN department d ON d.dept_id = e.dept_id;
  7. 7

    What is the difference between WHERE and HAVING?

    medium

    WHERE filters individual rows before grouping, so it cannot reference aggregates. HAVING filters groups after GROUP BY, so it can. The logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. To find departments whose average salary exceeds 50000 while ignoring interns, filter interns in WHERE and the average in HAVING. Putting row conditions in WHERE is also faster because fewer rows reach the grouping step.

    sql
    SELECT dept_id, AVG(salary) AS avg_sal
    FROM employee
    WHERE name <> 'intern'
    GROUP BY dept_id
    HAVING AVG(salary) > 50000;
  8. 8

    What is a view? How does it differ from a materialized view?

    medium

    A view is a stored SELECT query that behaves like a virtual table. It stores no data, so it always reflects current base-table contents, and is useful for hiding columns, simplifying joins and restricting access. A materialized view stores the query result physically and must be refreshed, so reads are fast but data can be stale. Simple views over one table are usually updatable; views with aggregates, DISTINCT or multi-table joins generally are not, unless an INSTEAD OF trigger is defined. MySQL has no native materialized views; PostgreSQL and Oracle do.

  9. 9

    What is the difference between a stored procedure, a function and a trigger?

    medium

    A stored procedure is a named block of SQL that you call explicitly; it may return several result sets or output parameters and can contain transaction control. A function returns a value and can be used inside a query, for example SELECT tax(salary); in most systems it should not change data. A trigger is not called by you: it fires automatically before or after an INSERT, UPDATE or DELETE on a table, which makes it suitable for audit logs and derived columns. Overusing triggers hides logic and makes debugging harder.

  10. 10

    Write a query for the second-highest salary and explain edge cases.

    medium

    The subquery approach finds the maximum salary below the overall maximum. It returns NULL when no second distinct salary exists and handles ties correctly because it compares salary values, not row positions. The LIMIT/OFFSET approach after DISTINCT is equivalent but returns zero rows instead of NULL. For the Nth highest, use DENSE_RANK() over salary descending and filter rank = N, which also treats ties as one rank. Using ROW_NUMBER() would be wrong when two people share the top salary.

    sql
    -- NULL if there is no second distinct salary
    SELECT MAX(salary) AS second_highest
    FROM employee
    WHERE salary < (SELECT MAX(salary) FROM employee);
    
    -- Nth highest with window function (N = 2)
    SELECT DISTINCT salary
    FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
          FROM employee) t
    WHERE rnk = 2;
  11. 11

    How do NULLs behave in SQL?

    medium

    NULL means unknown or missing, not zero or empty string. Any comparison with NULL, including NULL = NULL, yields UNKNOWN, and WHERE keeps only rows where the condition is TRUE, so use IS NULL and IS NOT NULL. Arithmetic with NULL gives NULL. Aggregates such as SUM and AVG skip NULLs, while COUNT(*) counts rows and COUNT(col) counts non-NULL values. NOT IN against a subquery that returns a NULL returns no rows at all, which is a classic trap; use NOT EXISTS instead. COALESCE(col, 0) replaces NULL with a default.

  12. 12

    What is a deadlock in a database and how is it handled?

    medium

    A deadlock occurs when two transactions each hold a lock the other needs, for example T1 locks row A and wants B while T2 locks B and wants A, so neither can proceed. Databases detect it with a wait-for graph; a cycle means deadlock, and the engine aborts one transaction, the victim, which the application should retry. Prevention techniques include acquiring locks in a consistent order, keeping transactions short and using lower isolation levels or row-level locks. Details on locking protocols are on the concurrency control page.

  13. 13

    What is the difference between a clustered and a non-clustered index, and what is a covering index?

    hard

    A clustered index defines the physical order of rows, so the table data itself is the leaf level of the B+ tree; there is only one per table, and in InnoDB the primary key plays this role. A non-clustered index is a separate structure whose leaves hold the key plus a pointer (a row id or the clustered key). A covering index contains every column a query needs, so the engine answers from the index alone without visiting the table. Indexes speed reads but slow writes and use space; see the indexing page.

  14. 14

    Find employees who earn more than the average salary of their own department.

    hard

    This needs a correlated subquery or a join to a derived table of per-department averages. The join version computes each average once, so it usually performs better than a correlated subquery that may be re-evaluated per row. Window functions give a third option that scans the table once. Mention that NULL salaries are ignored by AVG, so such employees are never returned.

    sql
    SELECT e.name, e.dept_id, e.salary
    FROM employee e
    JOIN (SELECT dept_id, AVG(salary) AS avg_sal
          FROM employee GROUP BY dept_id) d
      ON d.dept_id = e.dept_id
    WHERE e.salary > d.avg_sal;

Frequently asked questions

How many DBMS questions should a fresher prepare?

Cover the fourteen above first, then go deep on normalization, ACID and indexing, because follow-ups concentrate there. Being able to write ten or fifteen SQL queries from memory matters more than memorising definitions.

Is DBMS the same as RDBMS?

No. DBMS is the general term for software that manages data. An RDBMS stores data in tables with relations and uses SQL, and follows Codd rules loosely. MongoDB is a DBMS but not relational.

Which SQL dialect should I use in interviews?

Write standard SQL and mention dialect differences only when asked. LIMIT is MySQL and PostgreSQL, TOP is SQL Server, and FETCH FIRST is standard SQL.

Do interviews ask about NoSQL in a DBMS round?

Often a single question on when to prefer NoSQL appears. See the NoSQL questions page for CAP, document stores and key-value stores.

Related guides

More in CS Fundamentals Interview Questions

See all in this section