Computer Science & IT
🟢 75 Verified MCQs • 5 Complete Chapters Active
⭐ Interview & Exam Grade
DBMS & SQL: 5-Chapter Comprehensive Practice & 75 Verified MCQs
Master Database Management Systems (DBMS) & SQL with our complete 5-chapter question bank. Each verified MCQ provides four distinct options, verified solution keys, step-by-step technical rationale, and high-yield conceptual rules.
Curriculum Roadmap & Chapters Covered (All Live & Unlocked):
✓
Chapter 1: Relational Schema, Primary Keys & Integrity Constraints (Q1 – Q15)
✓
Chapter 2: Normalization (1NF, 2NF, 3NF, BCNF & Multivalued Dependencies) (Q16 – Q30)
✓
Chapter 3: Advanced SQL, Window Functions, CTEs & Aggregations (Q31 – Q45)
✓
Chapter 4: Indexing (B+ Trees, Hash Indexes, Clustered vs Non-Clustered) (Q46 – Q60)
✓
Chapter 5: Transactions, ACID Properties, Concurrency Control & WAL Recovery (Q61 – Q75)
📝 Total Questions: 75 Verified MCQs
📚 Modules: 5 Complete Chapters
⏱️ Est. Practice Time: ~90 minutes
🎯 Target Benchmark: 80% (60/75 Correct)
Select Chapter to Practice:
Interactive Practice Dashboard
Answered: 0 / 75
Correct: 0
Accuracy: 0%
Streak: 🔥 0
Curriculum Track • Module 1 of 5
Chapter 1: Relational Schema, Primary Keys & Integrity Constraints (Q1 – Q15)
Question 1 of 75
Chapter 1
Which normal form requires a table to be in 1NF and additionally ensures that no non-prime attribute is partially dependent on any candidate key?
Question 2 of 75
Chapter 1
What is the defining condition for a relation R to satisfy Third Normal Form (3NF) for every non-trivial functional dependency X -> Y?
Question 3 of 75
Chapter 1
Which ACID property guarantees that all database updates within a transaction are either completely committed to disk or entirely rolled back if an error occurs?
Question 4 of 75
Chapter 1
Which transaction isolation level allows a transaction to read uncommitted changes made by concurrent transactions, exposing it to 'Dirty Reads'?
Question 5 of 75
Chapter 1
In Boyce-Codd Normal Form (BCNF), what strict rule must hold for every non-trivial functional dependency X -> Y?
Question 6 of 75
Chapter 1
What is the primary role of Write-Ahead Logging (WAL) in database crash recovery mechanisms?
Question 7 of 75
Chapter 1
In relational algebra, what operation is represented by the Greek letter Sigma (σ)?
Question 8 of 75
Chapter 1
Under the Two-Phase Locking (2PL) protocol, what characterizes the 'shrinking phase' of a transaction?
Question 9 of 75
Chapter 1
What is a 'Phantom Read' anomaly in database transactions?
Question 10 of 75
Chapter 1
How does a Clustered Index differ physically from a Non-Clustered (Secondary) Index in relational databases like SQL Server and MySQL (InnoDB)?
Question 11 of 75
Chapter 1
What type of dependency is eliminated when moving from Second Normal Form (2NF) to Third Normal Form (3NF)?
Question 12 of 75
Chapter 1
In SQL, what is the consequence of executing 'DELETE FROM table_name' compared to 'TRUNCATE TABLE table_name'?
Question 13 of 75
Chapter 1
What is the primary characteristic of an outer join (e.g., LEFT OUTER JOIN) compared to an INNER JOIN in SQL?
Question 14 of 75
Chapter 1
In database normalization, when is a decomposition of a relation schema R into R1 and R2 guaranteed to be a 'Lossless-Join' decomposition?
Question 15 of 75
Chapter 1
What is the purpose of the SQL 'HAVING' clause, and how does it fundamentally differ from the 'WHERE' clause?
Curriculum Track • Module 2 of 5
Chapter 2: Normalization (1NF, 2NF, 3NF, BCNF & Multivalued Dependencies) (Q16 – Q30)
Question 16 of 75
Chapter 2
What is the primary objective of normalizing a relational database schema?
Question 17 of 75
Chapter 2
A relational table is in First Normal Form (1NF) if and only if:
Question 18 of 75
Chapter 2
Under what condition is a relation in 1NF guaranteed to already be in Second Normal Form (2NF)?
Question 19 of 75
Chapter 2
What defines Third Normal Form (3NF) in relational schema theory?
Question 20 of 75
Chapter 2
How does Boyce-Codd Normal Form (BCNF) differ strictly from Third Normal Form (3NF)?
Question 21 of 75
Chapter 2
Which normal form specifically addresses and eliminates Multivalued Dependencies (MVDs)?
Question 22 of 75
Chapter 2
Fifth Normal Form (5NF), also known as Project-Join Normal Form (PJNF), eliminates anomalies caused by:
Question 23 of 75
Chapter 2
What is the concept of 'Lossless-Join Decomposition' in relational database design?
Question 24 of 75
Chapter 2
When is a decomposition of a relation considered 'Dependency Preserving'?
Question 25 of 75
Chapter 2
Given Relation R(A, B, C, D) with FDs: {A -> B, B -> C, C -> D}. What is the Candidate Key of R?
Question 26 of 75
Chapter 2
In relation R(A, B, C) where A is Primary Key and FD is {B -> C}. In which normal form is R?
Question 27 of 75
Chapter 2
What is Armstrong's Axiom of 'Augmentation' for functional dependencies?
Question 28 of 75
Chapter 2
What is a 'Canonical Cover' (Minimal Cover) of a set of functional dependencies?
Question 29 of 75
Chapter 2
What is the primary operational trade-off of applying intentional 'Denormalization' in production OLAP data warehouses?
Question 30 of 75
Chapter 2
If relation R(A, B, C) has Candidate Keys {A, B} and {B, C}, and FD {C -> A}, why is R in 3NF but NOT in BCNF?
Curriculum Track • Module 3 of 5
Chapter 3: Advanced SQL, Window Functions, CTEs & Aggregations (Q31 – Q45)
Question 31 of 75
Chapter 3
What is the difference between RANK() and DENSE_RANK() window functions in SQL?
Question 32 of 75
Chapter 3
What is the result of applying a LEFT JOIN between Table A (10 rows) and Table B (5 rows) if none of the join conditions match?
Question 33 of 75
Chapter 3
How does the 'HAVING' clause differ from the 'WHERE' clause in SQL execution order?
Question 34 of 75
Chapter 3
What is a Common Table Expression (CTE) defined with the 'WITH' keyword in SQL?
Question 35 of 75
Chapter 3
What does the SQL window function LAG(column_name, 1) OVER (ORDER BY date) return?
Question 36 of 75
Chapter 3
What is the difference between UNION and UNION ALL operators in SQL?
Question 37 of 75
Chapter 3
In SQL, what is a Correlated Subquery?
Question 38 of 75
Chapter 3
What is the output of the expression 'SELECT COUNT(*), COUNT(col) FROM tbl' if 'col' contains 5 non-null numbers and 3 NULL values?
Question 39 of 75
Chapter 3
What does a CROSS JOIN between Table X (m rows) and Table Y (n rows) produce?
Question 40 of 75
Chapter 3
How does NULL behave in boolean ternary logic comparison (e.g. 'WHERE salary = NULL')?
Question 41 of 75
Chapter 3
What does the SQL COALESCE(val1, val2, val3, …) function do?
Question 42 of 75
Chapter 3
What is the purpose of the 'PARTITION BY' clause inside a SQL OVER() window definition?
Question 43 of 75
Chapter 3
What happens when an INNER JOIN condition matches multiple rows in both tables (a 1-to-many or many-to-many match)?
Question 44 of 75
Chapter 3
What does the 'EXISTS' operator evaluate to in an SQL query filter?
Question 45 of 75
Chapter 3
What is an SQL View, and does a standard (non-materialized) View store data on disk?
Curriculum Track • Module 4 of 5
Chapter 4: Indexing (B+ Trees, Hash Indexes, Clustered vs Non-Clustered) (Q46 – Q60)
Question 46 of 75
Chapter 4
Why do relational database storage engines (InnoDB, PostgreSQL) predominantly utilize B+ Trees over standard Binary Search Trees for disk-based indexing?
Question 47 of 75
Chapter 4
What is the primary architectural difference between a Clustered Index and a Non-Clustered (Secondary) Index?
Question 48 of 75
Chapter 4
What is a 'Covering Index' in relational database performance tuning?
Question 49 of 75
Chapter 4
How does the 'Leftmost Prefix Rule' apply to a composite index created on (col_a, col_b, col_c)?
Question 50 of 75
Chapter 4
What is the primary drawback of adding excessive indexes to high-write (OLTP) tables?
Question 51 of 75
Chapter 4
Why are standard Hash Indexes unsuitable for SQL queries containing 'WHERE age BETWEEN 20 AND 30'?
Question 52 of 75
Chapter 4
What does a 'Full Table Scan' (Seq Scan in PostgreSQL) signify in a database EXPLAIN query plan?
Question 53 of 75
Chapter 4
What causes an 'Index Page Split' in a B+ Tree data structure?
Question 54 of 75
Chapter 4
How does an LSM Tree (Log-Structured Merge-tree, used in RocksDB/Cassandra) differ fundamentally from a B+ Tree?
Question 55 of 75
Chapter 4
What is Index Cardinality in database statistics?
Question 56 of 75
Chapter 4
What happens when an expression or function wraps an indexed column in the WHERE clause (e.g. 'WHERE UPPER(last_name) = "SMITH"')?
Question 57 of 75
Chapter 4
What is the purpose of the Database Buffer Pool (Buffer Cache)?
Question 58 of 75
Chapter 4
What is a 'Bitmap Index' and in which environment is it most effectively utilized?
Question 59 of 75
Chapter 4
What is the difference between Clustered Index implementation in MySQL InnoDB vs PostgreSQL?
Question 60 of 75
Chapter 4
What is the role of the Cost-Based Optimizer (CBO) in a relational database engine?
Curriculum Track • Module 5 of 5
Chapter 5: Transactions, ACID Properties, Concurrency Control & WAL Recovery (Q61 – Q75)
Question 61 of 75
Chapter 5
What does the 'A' (Atomicity) in ACID transaction properties guarantee?
Question 62 of 75
Chapter 5
What phenomenon is referred to as a 'Dirty Read' in database concurrency?
Question 63 of 75
Chapter 5
In ANSI SQL isolation levels, what concurrency anomaly is permitted under 'Read Committed' but prevented under 'Repeatable Read'?
Question 64 of 75
Chapter 5
What is a 'Phantom Read' anomaly?
Question 65 of 75
Chapter 5
How does Multi-Version Concurrency Control (MVCC) eliminate read-write contention?
Question 66 of 75
Chapter 5
What is the Strict Two-Phase Locking (Strict 2PL) protocol?
Question 67 of 75
Chapter 5
How does a database engine detect deadlocks among concurrent transactions?
Question 68 of 75
Chapter 5
What is Write-Ahead Logging (WAL) and why is it essential for database Durability ('D' in ACID)?
Question 69 of 75
Chapter 5
What does the ARIES recovery algorithm perform during the 'Analysis Pass' following an unexpected database crash?
Question 70 of 75
Chapter 5
What is the difference between a Shared Lock (S-Lock) and an Exclusive Lock (X-Lock)?
Question 71 of 75
Chapter 5
What is 'Write Skew' anomaly under Snapshot Isolation?
Question 72 of 75
Chapter 5
What is a 'Cascading Abort' (Cascading Rollback) in transaction processing?
Question 73 of 75
Chapter 5
What is the role of 'Checkpoints' in database log management?
Question 74 of 75
Chapter 5
In distributed database transactions, what is the role of the Coordinator in the Two-Phase Commit (2PC) protocol?
Question 75 of 75
Chapter 5
What does the CAP theorem state regarding distributed data stores under network partition?
Explore Other Comprehensive 75-Question Subject Tracks
Continue your preparation across our active curriculum tracks. Every track provides 75 verified questions across 5 complete chapters.
🟢 100% Live Track
C Programming
75 Qs • 5 Chapters • Practice Now →
🟢 100% Live Track
Python Programming
75 Qs • 5 Chapters • Practice Now →
🟢 100% Live Track
Data Structures
75 Qs • 5 Chapters • Practice Now →
Active Track
📍 DBMS & SQL
75 Qs • 5 Chapters (You are here)
🟢 100% Live Track
Computer Networks
75 Qs • 5 Chapters • Practice Now →
🟢 100% Live Track
Operating Systems
75 Qs • 5 Chapters • Practice Now →
🟢 100% Live Track
Quantitative Aptitude
75 Qs • 5 Chapters • Practice Now →