DBMS Interview Questions — Set 1

Practice sheet: all 25 questions

Prefer reading first? Every question from this practice set, with the answer and a worked explanation. (Solving them in the practice set above earns XP — up to ≈150.)

1. Q1. Which problem of plain file-based data storage does a DBMS primarily solve?

   • a. Slow CPU execution
   • b. Data redundancy and inconsistency
   • c. Lack of programming languages
   • d. Hardware failures
   Show answer & explanation

   Answer: B. Data redundancy and inconsistency

   File systems duplicate the same data across files, and the copies drift apart — redundancy causing inconsistency. A DBMS centralises data with a schema, integrity constraints, transactions and concurrent access control.

2. Q2. Which two properties must a primary key always satisfy?

   • a. Unique and NOT NULL
   • b. Unique and numeric
   • c. Indexed and foreign
   • d. Auto-increment and short
   Show answer & explanation

   Answer: A. Unique and NOT NULL

   A primary key must uniquely identify every row and can never be NULL — a NULL key would identify nothing. Numeric type and auto-increment are common conveniences, not requirements.

3. Q3. A foreign key enforces which kind of integrity?

   • a. Entity integrity
   • b. Referential integrity
   • c. Domain integrity
   • d. Physical integrity
   Show answer & explanation

   Answer: B. Referential integrity

   A foreign key guarantees every referencing value matches an existing row in the referenced table (or is NULL) — referential integrity. Entity integrity is the primary key's job; domain integrity is about valid column values.

4. Q4. What is a candidate key?

   • a. Any column that contains unique values by coincidence
   • b. A minimal set of attributes that uniquely identifies each row
   • c. The key chosen by the DBA for indexing
   • d. A key imported from another table
   Show answer & explanation

   Answer: B. A minimal set of attributes that uniquely identifies each row

   A candidate key is a minimal superkey — dropping any attribute breaks uniqueness. A table may have several candidates; the one chosen becomes the primary key and the rest are alternate keys. "Minimal" is the word interviewers listen for.

5. Q5. A relation is in First Normal Form (1NF) when:

   • a. Every attribute holds only atomic (indivisible) values
   • b. There are no partial dependencies
   • c. There are no transitive dependencies
   • d. Every determinant is a candidate key
   Show answer & explanation

   Answer: A. Every attribute holds only atomic (indivisible) values

   1NF bans repeating groups and multi-valued cells — one value per cell. A "phone_numbers" column holding "98xxx, 97xxx" violates 1NF; split it into rows or a separate table. The other options describe 2NF, 3NF and BCNF respectively.

6. Q6. Second Normal Form (2NF) removes which kind of dependency?

   • a. Transitive dependency
   • b. Partial dependency (non-key attribute on part of a composite key)
   • c. Multi-valued dependency
   • d. Join dependency
   Show answer & explanation

   Answer: B. Partial dependency (non-key attribute on part of a composite key)

   2NF = 1NF + no non-key attribute depends on only part of a composite key. Example: in (student_id, course_id) → grade, storing student_name too violates 2NF because name depends on student_id alone. Tables with single-column keys are automatically 2NF.

7. Q7. A relation in 2NF also becomes 3NF when it has no:

   • a. Composite keys
   • b. NULL values
   • c. Transitive dependencies of non-key attributes on the key
   • d. Foreign keys
   Show answer & explanation

   Answer: C. Transitive dependencies of non-key attributes on the key

   3NF bans chains like emp_id → dept_id → dept_name, where a non-key attribute depends on the key only through another non-key attribute. Fix by splitting the department data into its own table. Mnemonic: every non-key attribute depends on "the key, the whole key, and nothing but the key".

8. Q8. A relation is in BCNF when:

   • a. Every determinant is a candidate key
   • b. It has exactly one candidate key
   • c. It contains no composite attributes
   • d. All attributes are prime
   Show answer & explanation

   Answer: A. Every determinant is a candidate key

   BCNF demands that for every non-trivial dependency X → Y, X is a (super)key. It's strictly stronger than 3NF — 3NF tolerates X → Y when Y is a prime attribute, BCNF does not. That corner case is the classic follow-up question.

9. Q9. Which SQL keyword removes duplicate rows from a SELECT result?

   • a. UNIQUE
   • b. DISTINCT
   • c. DEDUPE
   • d. SINGLE
   Show answer & explanation

   Answer: B. DISTINCT

   SELECT DISTINCT city FROM students returns each city once. UNIQUE is a constraint used in table definitions, not a query keyword — mixing the two up is a favourite screening question.

10. Q10. What is the difference between WHERE and HAVING?

    • a. They are interchangeable
    • b. WHERE filters rows before grouping; HAVING filters groups after GROUP BY
    • c. HAVING is faster than WHERE
    • d. WHERE works only on indexed columns
    Show answer & explanation

    Answer: B. WHERE filters rows before grouping; HAVING filters groups after GROUP BY

    WHERE runs first, on individual rows; HAVING runs after aggregation, on the grouped result — so aggregate conditions like HAVING COUNT(*) > 5 can't be written in WHERE. Query logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

11. Q11. A table has 5 rows and its column c contains 2 NULLs. What does SELECT COUNT(c) FROM t return?

    Show answer & explanation

    Answer: 3

    COUNT(column) counts only non-NULL values → 5 − 2 = 3, while COUNT(*) counts rows and would return 5. Aggregates in SQL generally skip NULLs (SUM, AVG too) — a subtle behaviour interviews probe often.

12. Q12. An INNER JOIN between two tables returns:

    • a. All rows from both tables
    • b. Only rows that satisfy the join condition in both tables
    • c. All rows from the left table only
    • d. The cartesian product
    Show answer & explanation

    Answer: B. Only rows that satisfy the join condition in both tables

    INNER JOIN keeps only matching pairs — rows without a partner on the other side disappear. If unmatched rows must be preserved, that's what LEFT/RIGHT/FULL OUTER JOIN are for.

13. Q13. In A LEFT JOIN B, rows of A that have no match in B:

    • a. Are dropped from the result
    • b. Appear with NULLs in B's columns
    • c. Cause an SQL error
    • d. Are duplicated
    Show answer & explanation

    Answer: B. Appear with NULLs in B's columns

    LEFT JOIN preserves every row of the left table; missing right-side data is padded with NULLs. That's also the standard "find customers with no orders" trick: LEFT JOIN then WHERE b.id IS NULL.

14. Q14. Table X has 4 rows and table Y has 3 rows. How many rows does SELECT * FROM X CROSS JOIN Y return?

    • a. 7
    • b. 12
    • c. 4
    • d. 3
    Show answer & explanation

    Answer: B. 12

    A cross join (cartesian product) pairs every row of X with every row of Y: 4 × 3 = 12. It's also what you get by accident when a join condition is forgotten — the classic cause of exploding result sets.

15. Q15. In the ACID properties of transactions, what does the I stand for?

    • a. Integrity
    • b. Isolation
    • c. Indexing
    • d. Idempotence
    Show answer & explanation

    Answer: B. Isolation

    ACID = Atomicity, Consistency, Isolation, Durability. Isolation means concurrent transactions behave as if run one after another — intermediate states are invisible to each other. "Integrity" is the planted near-miss.

16. Q16. Atomicity guarantees that a transaction:

    • a. Executes in constant time
    • b. Either completes fully or leaves no effect at all
    • c. Can never be rolled back
    • d. Locks the entire database
    Show answer & explanation

    Answer: B. Either completes fully or leaves no effect at all

    Atomicity is all-or-nothing: a bank transfer debits and credits together, or neither happens. If a failure hits mid-way, the DBMS rolls back the partial work using its undo log. Money can't vanish in between.

17. Q17. What does the ROLLBACK command do?

    • a. Saves all changes of the current transaction permanently
    • b. Undoes the changes of the current uncommitted transaction
    • c. Deletes a table permanently
    • d. Rolls the log file to a new segment
    Show answer & explanation

    Answer: B. Undoes the changes of the current uncommitted transaction

    ROLLBACK aborts the current transaction and undoes its uncommitted changes; COMMIT is its opposite, making them permanent (durable). After a COMMIT there is no rollback — only a compensating transaction can reverse it.

18. Q18. A dirty read happens when a transaction:

    • a. Reads data written by another transaction that has not committed yet
    • b. Reads the same row twice and gets different committed values
    • c. Reads from a corrupted disk block
    • d. Reads a row that is locked
    Show answer & explanation

    Answer: A. Reads data written by another transaction that has not committed yet

    A dirty read sees uncommitted data — if the writer then rolls back, the reader acted on data that never existed. Option b describes a non-repeatable read, a different anomaly; isolation levels exist to rule these out step by step.

19. Q19. Two-phase locking (2PL) guarantees:

    • a. Deadlock freedom
    • b. Conflict-serializable schedules
    • c. Maximum concurrency
    • d. That no locks are ever needed
    Show answer & explanation

    Answer: B. Conflict-serializable schedules

    2PL (all lock acquisitions before any release) guarantees conflict-serializability — the schedule is equivalent to some serial order. It does NOT prevent deadlocks; transactions can still block each other in a cycle, which is why 2PL systems pair it with deadlock detection.

20. Q20. Why are B+ trees preferred over hash indexes for range queries?

    • a. Hash indexes cannot store numbers
    • b. B+ tree leaves are sorted and linked, so a range scan walks them sequentially
    • c. B+ trees use less disk space
    • d. Hash indexes only work in memory
    Show answer & explanation

    Answer: B. B+ tree leaves are sorted and linked, so a range scan walks them sequentially

    In a B+ tree all data sits in sorted, linked leaf nodes: find the range's start, then walk siblings — perfect for BETWEEN and ORDER BY. A hash index scatters keys deliberately, so it answers equality lookups fast but has no notion of "next value".

21. Q21. How many clustered indexes can a table have?

    • a. One
    • b. Two
    • c. One per column
    • d. Unlimited
    Show answer & explanation

    Answer: A. One

    A clustered index defines the physical order of the rows themselves, and data can only be stored in one order — so exactly one per table. Non-clustered indexes are separate structures pointing at the rows, so a table can have many.

22. Q22. TRUNCATE TABLE is classified as which category of SQL command?

    • a. DML
    • b. DDL
    • c. DCL
    • d. TCL
    Show answer & explanation

    Answer: B. DDL

    TRUNCATE is DDL: it deallocates the table's data pages rather than deleting rows one by one, which is why it's fast, takes no per-row locks, and (in most systems) can't be filtered or rolled back like DELETE. DELETE, by contrast, is DML.

23. Q23. A view in SQL is best described as:

    • a. A physical copy of a table
    • b. A virtual table defined by a stored query
    • c. An index on multiple columns
    • d. A backup file
    Show answer & explanation

    Answer: B. A virtual table defined by a stored query

    A view stores a query, not data — each reference re-runs the definition over the base tables, so it's always current. Uses: hiding columns for security, simplifying complex joins. A materialized view is the variant that does store results.

24. Q24. In the ER model, a weak entity is one that:

    • a. Has very few attributes
    • b. Cannot be uniquely identified without its owner entity's key
    • c. Has no relationships
    • d. Is stored without an index
    Show answer & explanation

    Answer: B. Cannot be uniquely identified without its owner entity's key

    A weak entity (double rectangle) lacks a full key of its own — it's identified by its owner's key plus its partial key, e.g. Payment(installment_no) under Loan. Its link to the owner is an identifying relationship with total participation.

25. Q25. Which of the following are DML commands? (Select all that apply.)

    • a. INSERT
    • b. UPDATE
    • c. CREATE
    • d. DELETE
    Show answer & explanation

    Answer: A. INSERT · B. UPDATE · D. DELETE

    INSERT, UPDATE and DELETE manipulate the data inside tables — DML. CREATE (with ALTER, DROP, TRUNCATE) defines or changes the schema itself — DDL. The classification question is a fixture of written screening rounds.