A buffer manager uses the STEAL and NO-FORCE policies. Which recovery actions must the log support?
Correct answer D. Both undo and redo
Topic / 89 questions
DBMS and SQL MCQs
DBMS and SQL MCQs cover database management concepts and the SQL query language, a core subject in computer science degrees, IT recruitment tests and lecturer exams. Questions range from keys and normal forms to joins, GROUP BY and HAVING, transactions and concurrency control. Each answer includes an explanation, and SQL output questions were checked by running the queries.
In a B+ tree, what is the minimum occupancy rule for every node other than the root?
Correct answer B. It must be at least half full
How many clustered indexes can a single table have in SQL Server, and why?
Correct answer D. One, because the clustered index defines the physical order of the data rows
Advertisement
A column has a hash index and a B-tree index available. Which query condition can use the B-tree index but not the hash index?
Correct answer B. WHERE salary BETWEEN 3000 AND 5000
Why must a secondary index, built on a key that is not the file’s sort order, be a dense index?
Correct answer C. Because the records are not sorted on that key, so every key value needs its own index entry
A money transfer debits one account, and the system crashes before the matching credit is applied. Which ACID property requires that the debit be undone?
Correct answer A. Atomicity
A bank transfer commits and the customer is told it succeeded. Seconds later the server loses power. After restart the transfer is still recorded. Which ACID property guarantees this?
Correct answer D. Durability
In the transaction state model, what is the state of a transaction after its final statement has executed but before its commit has completed?
Correct answer B. Partially committed
Which SQL isolation level is the lowest one that prevents dirty reads, meaning reading data written by a transaction that has not yet committed?
Correct answer B. Read Committed
Advertisement
Which pair of operations from two different transactions T1 and T2 does NOT conflict?
Correct answer B. R1(X) and R2(X)
Schedule S is R1(A), W2(A), R2(B), W1(B). What does its precedence graph show?
Correct answer B. Edges T1 -> T2 and T2 -> T1, so S is not conflict serializable
T2 reads a value of X written by T1. For the schedule to be recoverable, what must hold?
Correct answer B. T1 must commit before T2 commits
Under the two-phase locking (2PL) protocol, what is a transaction forbidden to do once it has released its first lock?
Correct answer D. Acquire any new lock
What does strict two-phase locking add to basic 2PL, and what problem does it remove?
Correct answer B. Exclusive locks are held until commit or abort, which removes cascading aborts
T1 holds a shared (S) lock on item X. T2 now requests an exclusive (X) lock on the same item. What happens under standard lock compatibility?
Correct answer B. T2 must wait until T1 releases its shared lock
Advertisement
Under the wait-die deadlock prevention scheme, an older transaction T1 requests a lock held by a younger transaction T2. What happens?
Correct answer B. T1 waits for T2
Under basic timestamp ordering, transaction T with timestamp 10 tries to read item X whose write timestamp W-TS(X) is 15. What does the protocol do?
Correct answer D. Aborts T and restarts it with a new timestamp
What does the write-ahead logging (WAL) rule require?
Correct answer B. Log records describing a change must reach stable storage before the changed data page does
What is the main purpose of taking a checkpoint in a log-based recovery system?
Correct answer B. To limit how much of the log must be scanned and replayed after a crash
At a crash, T1 had committed but some of its updated pages were still only in memory, and T2 was still active with some of its updates already on disk. What does log-based recovery do?
Correct answer C. Redo T1 and undo T2
About DBMS and SQL MCQs
The first part covers database fundamentals: why a DBMS is preferred to file systems, data independence, the three-schema architecture, data models and the entity-relationship model with entities, attributes, relationships, cardinality and weak entities.
The relational model section deals with super, candidate, primary, foreign and composite keys, integrity constraints, NULL values and relational algebra. Normalisation questions test functional dependencies, attribute closure, 1NF to BCNF, update anomalies and lossless decomposition.
SQL questions cover DDL, DML, DCL and TCL commands, the difference between DELETE, TRUNCATE and DROP, WHERE versus HAVING, aggregate functions and NULLs, joins, subqueries, UNION versus UNION ALL and views. The last part covers indexing with B+ trees, transactions and ACID properties, serializability, locking protocols, deadlocks and log-based recovery.
Key facts to remember
- A primary key cannot contain NULL values and must be unique.
- WHERE filters rows before grouping; HAVING filters groups after GROUP BY.
- COUNT(*) counts all rows, while COUNT(column) ignores NULLs.
- ACID stands for Atomicity, Consistency, Isolation and Durability.
- Two-phase locking guarantees conflict serializability but not freedom from deadlock.
Exams that include DBMS and SQL MCQs
Pakistan
- NTS
- PPSC Computer Science Lecturer
- FPSC Database Administrator and IT posts
- University exams
India
- GATE CS
- UGC NET Computer Science
- Campus placements
- IBPS IT Officer
Bangladesh
- BCS (technical cadre)
- Bank IT officer tests
- Government programmer and database posts
How to prepare for DBMS and SQL MCQs
- Practise finding candidate keys from a set of functional dependencies using attribute closure.
- Learn the normal forms in order and the exact condition each one adds.
- For SQL output questions, work through FROM, WHERE, GROUP BY, HAVING, SELECT and ORDER BY in that logical order.
- Draw a precedence graph to test conflict serializability: a cycle means the schedule is not serializable.
Frequently asked questions
What is the difference between DELETE, TRUNCATE and DROP?
DELETE is a DML command that removes selected rows and can use a WHERE clause. TRUNCATE removes all rows quickly but keeps the table structure. DROP removes the table itself, including its structure and data.
Which DBMS topics are most important for GATE and job tests?
Normalisation and functional dependencies, keys, SQL queries with joins and aggregates, relational algebra, transactions and serializability, and indexing with B and B+ trees are asked most often.
Last reviewed October 2026