Blog Categories

Cloud Computing
Digital Marketing
Data Science
IT Service and Architecture
Big Data
Salesforce
Project Management
Corporate Training
Machine Learning AI
Technology
Cyber Security
Business Intelligence
Programming Languages
DevOps
Deep Learning
Robotic Process Automation
Artificial Intelligence
Microsoft Certifications
Data Analytics
Machine Learning
Mobile Development
Website Development
Testing
Blockchain
Others
ERP
Quizzes
Cheat Sheet
Interview Questions
Python
Java
Golang
R programming
C Programming
C Sharp
JavaScript
HTML
CSS
Ruby
C Plus Plus
Online Compilers

Blog Quizzes DBMS MCQs with Answers

DBMS MCQs with Answers

By: Piyush Verma

Last Updated: October 9th, 2026

Read Time: 15:00 Minutes

1. DBMS Fundamentals

2. ER Modeling & Database Design

3. Normalization & Schema Design

4. SQL & Querying

5. Transactions & Concurrency

6. Indexing, Storage & Performance

7. Security, Backup & Recovery

8. Modern Databases, NoSQL & Cloud DBMS

9. Your Quiz Result

Preparing for database jobs or interviews? This Top DBMS MCQs collection is your ultimate resource. It includes frequently asked multiple-choice questions, categorized by topic for comprehensive learning — test your knowledge, identify gaps, and improve fast.

Note: Score 60% or higher to unlock an exclusive offer of up to 50% off on all self-paced SQL courses.

Let's begin!

DBMS Fundamentals

1. What does DBMS stand for?

A) Database Management System
B) Data Backup Management Service
C) Distributed Binary Management System
D) Database Maintenance Standard

2. Which of the following is NOT a function of a DBMS?

A) Data storage and retrieval
B) Enforcing data integrity
C) Transaction management
D) Compiling application source code

3. Which data model is widely used in modern relational databases?

A) Hierarchical model
B) Relational model
C) Network model
D) Object-based model

4. Which language is primarily used to query relational databases?

A) HTML
B) Python
C) SQL
D) XML

5. Which of the following describes a relation in a relational database?

A) A table with rows and columns
B) A single column only
C) A stored procedure
D) Application code

6. What is a primary key?

A) A key used to encrypt the database
B) A column (or set) that uniquely identifies each row
C) A backup file
D) A type of index

7. Which constraint ensures a column cannot have NULL values?

A) UNIQUE
B) FOREIGN KEY
C) NOT NULL
D) CHECK

8. What is a foreign key used for?

A) Encrypting columns
B) Indexing text fields
C) Defining triggers
D) Maintaining referential integrity between tables

9. Which of the following is an example of DDL (Data Definition Language)?

A) SELECT
B) CREATE TABLE
C) INSERT INTO
D) UPDATE

10. Which SQL statement is used to remove all records from a table but keep the table structure?

A) DROP TABLE
B) DELETE TABLE
C) TRUNCATE TABLE
D) REMOVE ALL

ER Modeling & Database Design

11. What does ER in an ER diagram stand for?

A) Entity-Relationship
B) External-Resource
C) Event-Record
D) Entity-Registry

12. What symbol usually represents an entity in an ER diagram?

A) Arrow
B) Rectangle
C) Diamond
D) Ellipse

13. What does cardinality describe in database design?

A) Data type of a column
B) Primary key uniqueness
C) Number of relationships between entities
D) Normalization level

14. Which relationship type indicates many-to-many between two entities?

A) 1:0
B) 1:1
C) 1:N
D) M:N (many-to-many)

15. What is denormalization?

A) Introducing redundancy to improve read performance
B) Removing indexes
C) Encrypting sensitive columns
D) Converting schema to JSON

16. In normalization, which normal form removes repeating groups?

A) Third Normal Form (3NF)
B) First Normal Form (1NF)
C) Boyce-Codd Normal Form (BCNF)
D) Fifth Normal Form (5NF)

17. What is an associative (junction) table used for?

A) To store logs
B) To cache queries
C) To implement many-to-many relationships
D) To backup data

18. Which of the following is a modelling tool for databases?

A) ER diagram
B) CSS layout
C) UML for front-end only
D) Network topology map

19. What is a surrogate key?

A) A key copied from another table
B) An artificial key (e.g., auto-increment ID) used as primary key
C) A foreign key used as primary
D) A key used to encrypt rows

20. What should you do to enforce business rules in the database layer?

A) Only enforce them in application code
B) Ignore rules to speed up development
C) Store rules in client-side scripts
D) Use constraints, triggers or stored procedures to enforce them

Normalization & Schema Design

21. Which normal form eliminates partial dependency (dependency on part of a composite key)?

A) 3NF
B) 2NF
C) 1NF
D) BCNF

22. Which normal form requires that non-key attributes are not transitively dependent on the primary key?

A) Third Normal Form (3NF)
B) First Normal Form (1NF)
C) Second Normal Form (2NF)
D) Zeroth Normal Form

23. Which of the following can be a problem if a schema is too highly normalized?

A) Data inconsistency
B) Redundant writes
C) Many joins causing slower reads
D) Easier backups

24. Which design approach intentionally duplicates data to speed up queries?

A) Normalization
B) Sharding
C) Vertical partitioning
D) Denormalization

25. Which of the following is TRUE about functional dependency?

A) It relates two tables without keys
B) Attribute B is functionally dependent on A if A determines B
C) It only applies to NoSQL
D) It is irrelevant for normalization

26. What is a candidate key?

A) A key chosen by the DB admin
B) A foreign key candidate
C) A minimal set of attributes that can uniquely identify a tuple
D) A key used for encryption

27. Which of these is a benefit of proper normalization?

A) Reduced data redundancy
B) More complex queries always
C) Increased storage usage
D) Eliminates need for indexing

28. Which normal form does BCNF (Boyce-Codd Normal Form) strengthen?

A) 1NF
B) 3NF (it removes certain anomalies beyond 3NF)
C) 2NF
D) 4NF

29. Which of the following indicates multi-valued dependency?

A) Foreign key constraint
B) Referential integrity
C) Candidate key
D) 4NF addresses multi-valued dependencies

30. When designing for OLTP systems, which is generally prioritized?

A) Complex analytical queries
B) Denormalized wide tables
C) Fast transactional throughput and consistency
D) Columnar storage primarily

You Can Also Check:
1. What is a RDBMS
2. Data Science Tutorial for Beginners
3. Top 35 Data Science Interview Questions and Answers
4. Machine Learning Quiz
5. MongoDB vs MySQL

SQL & Querying

31. Which clause is used to filter rows returned by a SELECT query?

A) WHERE
B) GROUP BY
C) HAVING
D) ORDER BY

32. Which SQL clause is used to group rows that have the same values?

A) WHERE
B) ORDER BY
C) DISTINCT
D) GROUP BY

33. Which JOIN returns only rows that have matching values in both tables?

A) LEFT JOIN
B) INNER JOIN
C) RIGHT JOIN
D) FULL OUTER JOIN

34. Which function returns the number of rows in a group?

A) SUM()
B) AVG()
C) COUNT()
D) MAX()

35. Which statement changes data in an existing row?

A) SELECT
B) INSERT
C) CREATE
D) UPDATE

36. Which keyword ensures unique rows are returned by SELECT?

A) DISTINCT
B) UNIQUE
C) SINGLE
D) ONLY

37. Which SQL construct filters groups after aggregation?

A) WHERE
B) HAVING
C) ORDER BY
D) LIMIT

38. What does the SQL clause ORDER BY do?

A) Filter rows
B) Group rows
C) Sort result set
D) Join tables

39. Which SQL clause limits the number of rows returned?

A) WHERE
B) GROUP BY
C) HAVING
D) LIMIT (or TOP)

40. Which command removes a table and its data from the database?

A) DROP TABLE
B) TRUNCATE
C) DELETE TABLE
D) REMOVE TABLE

41. Which of these is a set operation combining results of two SELECTs but removes duplicates?

A) UNION ALL
B) INTERSECT
C) UNION
D) EXCEPT

42. What is a correlated subquery?

A) A subquery that runs once
B) A subquery that references outer query columns and may run per row
C) A subquery in FROM only
D) A subquery that is always faster

43. Which SQL construct can be used to create stored logic on the server side?

A) Stored procedure
B) View only
C) Index
D) Trigger only

44. What is an SQL view?

A) Physical table
B) Index structure
C) Backup file
D) A virtual table defined by a query

45. Which statement is true about indexes?

A) Indexes always improve write performance
B) Indexes replace the need for normalization
C) Indexes speed up read queries but add overhead to writes
D) Indexes are always automatically created for all columns

Transactions & Concurrency

46. Which property of transactions ensures all changes are saved permanently only if the transaction commits?

A) Atomicity
B) Consistency
C) Isolation
D) Durability

47. Which isolation level may allow dirty reads?

A) SERIALIZABLE
B) REPEATABLE READ
C) READ COMMITTED
D) READ UNCOMMITTED

48. What is a deadlock?

A) When a transaction fails checksum
B) When two or more transactions wait indefinitely for each other to release locks
C) When database runs out of disk space
D) When backup and restore overlap

49. Which SQL command begins a transaction in many DBMSs?

A) START
B) BEGINNING
C) BEGIN TRANSACTION (or BEGIN)
D) OPEN TX

50. What does COMMIT do?

A) Discards current transaction
B) Locks tables
C) Starts a new transaction
D) Makes all changes in the transaction permanent

51. What does ROLLBACK do?

A) Undo changes in the current transaction
B) Commit partial changes
C) Create a savepoint
D) Increase isolation level

52. What is a savepoint?

A) A full backup
B) A point within a transaction to which you can roll back
C) A replication snapshot
D) An index type

53. Which scheduling ensures serializability by producing the same result as some serial execution?

A) Optimistic scheduling
B) Round-robin scheduling
C) Serializability
D) Multiversion scheduling

54. In MVCC (Multi-Version Concurrency Control), what is stored to provide consistent reads?

A) Single global lock
B) A copy of application code
C) Index snapshots
D) Multiple versions of rows

55. Which of the following is a common method to resolve deadlocks?

A) Increase transaction timeout to infinity
B) Abort one of the transactions (deadlock victim)
C) Disable transactions
D) Remove indexes

Indexing, Storage & Performance

56. Which index type is typically implemented as a balanced tree?

A) B-tree (or B+ tree)
B) Hash index
C) Bitmap index
D) GiST

57. What is a clustered index?

A) An index stored separately from data pages
B) An index where the table rows are stored in the index order
C) An index only for text columns
D) An index used for backups

58. What is partitioning used for?

A) Encrypting columns
B) Replacing indexes
C) Splitting large tables for manageability and performance
D) Defragmenting storage

59. Which storage model is typically used by columnar databases to speed analytic queries?

A) Row-based storage
B) File-based storage only
C) Object storage
D) Column-based storage

60. What is query optimization primarily concerned with?

A) Finding efficient execution plans
B) Encrypting queries
C) Formatting results
D) Increasing table size

61. What is a query execution plan?

A) A DDL script
B) The series of operations the DBMS will perform to execute a query
C) A backup schedule
D) An index type

62. Which tool would you use to speed up full-text search in many DBMSs?

A) B-tree
B) Clustered index
C) Full-text index / search engine (e.g., ElasticSearch)
D) Foreign key

63. What is the effect of too many indexes on a write-heavy table?

A) Slower INSERT/UPDATE/DELETE due to index maintenance
B) Faster writes
C) No impact
D) Reduces storage usage

64. What does sharding refer to?

A) Index compression
B) Full backup
C) Single-node clustering
D) Horizontal partitioning across multiple machines

65. Which of these storage engines is commonly used by MySQL for transactional support?

A) MyISAM
B) InnoDB
C) CSV
D) MEMORY

Security, Backup & Recovery

66. Which practice helps protect sensitive data in the database?

A) Granting ALL privileges to public
B) Storing passwords in plaintext
C) Encrypting data at rest and controlling access
D) Disabling authentication

67. What is the purpose of database backup?

A) Recover data after failures or disasters
B) Speed up queries
C) Replace replication
D) Increase storage capacity

68. What is point-in-time recovery (PITR)?

A) Restoring backup to initial state only
B) Restoring the database to a specific moment using logs and backups
C) Incremental indexing
D) User role management

69. What is replication in databases used for?

A) Encrypting backups
B) Auditing user actions
C) Partitioning a table
D) Copying data from one database server to another for redundancy or scaling

70. Which of the following is a recommended practice for database security?

A) Granting admin privileges to all users
B) Disabling auditing
C) Principle of least privilege and regular patching
D) Storing credentials in application code

Modern Databases, NoSQL & Cloud DBMS

71. Which of the following is a NoSQL database?

A) Oracle
B) MongoDB
C) PostgreSQL
D) SQL Server

72. Which NoSQL database type stores data in key-value pairs?

A) Graph database
B) Column-family database
C) Key-value store
D) Relational database

73. Which feature is commonly associated with NoSQL databases?

A) Flexible schema
B) Fixed table structure only
C) Mandatory joins
D) Strict normalization always

74. Which database is best suited for highly connected data such as social networks?

A) Relational database
B) Columnar database
C) Key-value store
D) Graph database

75. What does CAP theorem describe?

A) Query optimization rules
B) Trade-offs between consistency, availability, and partition tolerance
C) Backup and recovery methods
D) Indexing techniques

76. Which cloud provider offers Amazon RDS?

A) Google Cloud
B) Microsoft Azure
C) AWS
D) IBM Cloud

77. What is database replication primarily used for?

A) High availability and scalability
B) Reducing normalization
C) Encrypting records
D) Creating indexes

78. Which SQL function is used to find the highest value in a column?

A) COUNT()
B) SUM()
C) AVG()
D) MAX()

79. Which SQL clause is used to remove duplicate rows from results?

A) UNIQUE
B) DISTINCT
C) FILTER
D) REMOVE

80. Which command is used to add a new column to an existing table?

A) ALTER TABLE
B) UPDATE TABLE
C) MODIFY COLUMN
D) INSERT COLUMN

81. Which type of JOIN returns all rows from the left table and matching rows from the right table?

A) INNER JOIN
B) FULL JOIN
C) CROSS JOIN
D) LEFT JOIN

82. Which SQL statement is used to add new records into a table?

A) UPDATE
B) MODIFY
C) INSERT
D) APPEND

83. What is the purpose of a database trigger?

A) To create backups
B) To automatically execute logic when specific events occur
C) To encrypt tables
D) To improve internet speed

84. Which command displays the structure of a table in many SQL databases?

A) DESCRIBE
B) STRUCTURE
C) SHOW TABLE
D) VIEW COLUMN

85. Which SQL function returns the lowest value in a column?

A) AVG()
B) COUNT()
C) SUM()
D) MIN()

86. Which database language is used for access control?

A) DML
B) DCL
C) DDL
D) TCL

87. Which SQL keyword is used to sort records in descending order?

A) DOWN
B) SORT DESC
C) DESC
D) DECREASE

88. Which command removes specific rows from a table?

A) DELETE
B) DROP
C) REMOVE
D) ERASE

89. Which of the following is a document-oriented database?

A) MySQL
B) Oracle
C) MariaDB
D) MongoDB

90. What is vertical scaling in databases?

A) Adding more servers
B) Increasing resources of an existing server
C) Splitting data into shards
D) Compressing indexes

91. Which SQL clause is used with aggregate functions to filter grouped data?

A) WHERE
B) ORDER BY
C) HAVING
D) LIMIT

92. Which SQL operator is used to search for a pattern?

A) LIKE
B) MATCH
C) SEARCH
D) FIND

93. Which backup contains only data changed since the last backup?

A) Full backup
B) Mirror backup
C) Snapshot backup
D) Incremental backup

94. Which SQL function calculates the average value?

A) TOTAL()
B) AVG()
C) COUNT()
D) MEAN()

95. Which command gives users access privileges?

A) GRANT
B) ALLOW
C) ACCESS
D) ENABLE

96. Which command removes access privileges from a user?

A) REMOVE
B) DELETE
C) REVOKE
D) DENY

97. Which type of database is optimized for analytical workloads?

A) OLTP database
B) Key-value store
C) Graph database
D) Data warehouse

98. Which SQL keyword is used to rename a column or table alias?

A) AS
B) ALIAS
C) RENAME
D) CHANGE

99. What is database caching used for?

A) Encrypting queries
B) Improving performance by storing frequently accessed data
C) Creating backups
D) Preventing indexing

100. Which SQL command is used to permanently save transaction changes?

A) SAVE
B) STORE
C) PUSH
D) COMMIT

101. Which database model uses tables with rows and columns?

A) Relational model
B) Hierarchical model
C) Graph model
D) Network model

102. Which SQL function adds numeric values?

A) COUNT()
B) AVG()
C) SUM()
D) TOTAL()

103. Which SQL clause is used to sort results alphabetically?

A) GROUP BY
B) ORDER BY
C) SORT BY
D) ARRANGE

104. Which SQL keyword is used to retrieve data?

A) GET
B) FETCH
C) RETRIEVE
D) SELECT

105. Which SQL command creates a new database?

A) CREATE DATABASE
B) NEW DATABASE
C) ADD DATABASE
D) MAKE DATABASE

106. Which type of key uniquely identifies each row in a table?

A) Foreign key
B) Composite key
C) Primary key
D) Alternate key

107. Which SQL operator checks for NULL values?

A) = NULL
B) IS NULL
C) HAS NULL
D) NULL()

108. Which database object stores a predefined SQL query?

A) Trigger
B) Index
C) Constraint
D) View

109. Which database design technique reduces redundancy?

A) Normalization
B) Replication
C) Sharding
D) Partitioning

110. Which SQL command deletes an entire table structure?

A) DELETE
B) DROP TABLE
C) REMOVE TABLE
D) CLEAR TABLE

111. Which SQL keyword combines conditions?

A) COMBINE
B) MERGE
C) AND
D) WITH

112. Which command removes all rows but keeps the table?

A) DROP
B) DELETE DATABASE
C) REMOVE
D) TRUNCATE

113. Which database system is open-source?

A) PostgreSQL
B) Oracle Enterprise
C) Microsoft SQL Server Enterprise
D) IBM Db2 Enterprise

114. Which SQL clause filters individual rows before grouping?

A) HAVING
B) GROUP BY
C) WHERE
D) ORDER BY

115. Which SQL function returns the current date?

A) NOWDATE()
B) CURRENT_DATE
C) TODAY()
D) DATE()

116. Which SQL statement modifies existing records?

A) INSERT
B) ALTER
C) CHANGE
D) UPDATE

117. Which database operation improves query performance?

A) Indexing
B) Denormalization only
C) Removing constraints
D) Disabling caching

118. Which type of database stores data in JSON-like documents?

A) Graph database
B) Document database
C) Relational database
D) Hierarchical database

119. Which SQL command is used to rename a table?

A) CHANGE TABLE
B) MODIFY TABLE
C) RENAME TABLE
D) ALTER NAME

120. Which database concept ensures data remains accurate and consistent?

A) Replication
B) Partitioning
C) Sharding
D) Data integrity

121. Which ACID property ensures that a transaction is treated as a single indivisible unit?

A) Atomicity
B) Consistency
C) Isolation
D) Durability

122. Which ACID property ensures that committed data remains saved even after a system failure?

A) Atomicity
B) Isolation
C) Durability
D) Consistency

123. What is a composite key?

A) A key generated automatically by the DBMS
B) A key consisting of two or more attributes
C) A foreign key referencing multiple databases
D) An encrypted primary key

124. Which type of index is generally most suitable for exact-match lookups?

A) Bitmap index
B) Full-text index
C) Spatial index
D) Hash index

125. What is a phantom read in transaction processing?

A) New rows appear when the same query is executed again within a transaction
B) A transaction reads uncommitted data
C) A database loses committed records
D) An index points to a deleted table

126. Which transaction isolation level provides the strongest isolation in standard SQL?

A) READ UNCOMMITTED
B) READ COMMITTED
C) SERIALIZABLE
D) REPEATABLE READ

127. What is a dirty read?

A) Reading data from a corrupted table
B) Reading data modified by another transaction before it is committed
C) Reading duplicate records
D) Reading data without an index

128. What is the primary purpose of a database checkpoint?

A) Create a new database user
B) Delete old database records
C) Optimize every SQL query
D) Reduce recovery work by recording a known recovery point

129. Which database recovery technique records changes before they are written to data files?

A) Write-Ahead Logging (WAL)
B) Vertical partitioning
C) Data normalization
D) Query caching

130. What is the main purpose of a database connection pool?

A) Store database backups
B) Combine multiple databases into one
C) Reuse database connections to reduce connection overhead
D) Encrypt SQL queries

131. Which type of database is specifically designed to store and query relationships between entities?

A) Key-value database
B) Graph database
C) Columnar database
D) Time-series database

132. What is eventual consistency in a distributed database?

A) Every replica is always immediately identical
B) Data can never be replicated
C) Transactions cannot modify data
D) Replicas may temporarily differ but converge if no new updates occur

133. Which database architecture distributes data across multiple independent nodes?

A) Distributed database
B) Centralized database
C) Flat-file database
D) In-memory cache only

134. What does horizontal scaling typically involve?

A) Adding more CPU to one server
B) Increasing RAM on an existing server
C) Adding more servers or nodes to distribute the workload
D) Compressing database files

135. Which type of database is optimized for storing data points indexed primarily by time?

A) Graph database
B) Time-series database
C) Document database
D) Hierarchical database

136. What is a materialized view?

A) A virtual table that never stores results
B) A temporary database connection
C) A backup of an entire database
D) A database object that physically stores the result of a query

137. What is the purpose of the EXPLAIN command in many relational databases?

A) Display the execution plan chosen for a query
B) Explain database syntax errors in natural language
C) Create a database backup
D) Display user passwords

138. Which SQL feature assigns a rank or calculation across a set of related rows without collapsing them into one row?

A) Trigger
B) Stored procedure
C) Window function
D) Foreign key

139. Which SQL function can assign a unique sequential number to rows within a result set?

A) COUNT()
B) ROW_NUMBER()
C) SUM()
D) GROUP_NUMBER()

140. What does a Common Table Expression (CTE) provide in SQL?

A) Permanent physical storage
B) Automatic database replication
C) Database encryption
D) A named temporary result set usable within a query

141. Which SQL JOIN produces a Cartesian product of two tables?

A) CROSS JOIN
B) INNER JOIN
C) LEFT JOIN
D) SELF JOIN

142. What is a self-join?

A) Joining databases on different servers
B) Joining a table with a view only
C) Joining a table to itself
D) Joining two databases without a condition

143. What is the main purpose of database auditing?

A) Increase query execution speed
B) Track database activities and changes for security and compliance
C) Automatically normalize tables
D) Reduce database storage

144. Which security technique replaces sensitive data with non-sensitive substitute values?

A) Indexing
B) Replication
C) Normalization
D) Data masking

145. What is a read replica primarily used for?

A) Offloading read queries from the primary database
B) Replacing all database backups
C) Normalizing database tables
D) Encrypting database connections

146. What is database failover?

A) Permanently deleting a failed database
B) Moving old records to an archive
C) Switching operations to a standby system when the primary system fails
D) Disabling replication during high traffic

147. Which type of partitioning divides table rows based on ranges of column values?

A) Hash partitioning
B) Range partitioning
C) Vertical partitioning
D) Random partitioning

148. What is the primary purpose of database schema migration?

A) Execute SELECT queries faster
B) Encrypt database backups
C) Create read replicas
D) Apply controlled changes to a database structure over time

149. What does database observability help administrators understand?

A) Database performance, health, and behavior using metrics, logs, and traces
B) Only the physical size of database tables
C) Only user passwords and credentials
D) The programming language used by an application

150. What is a serverless database designed to provide?

A) A database that runs without any computing infrastructure
B) A database that can only run on local machines
C) Managed infrastructure with automatic scaling and reduced server management
D) A database that does not support SQL queries

151. What is a vector database primarily designed to store and search?

A) Only relational tables
B) High-dimensional vector embeddings
C) Only transaction logs
D) Operating system files

152. Which technique is commonly used by vector databases to find similar embeddings efficiently?

A) Two-phase commit
B) Database normalization
C) Approximate Nearest Neighbor (ANN) search
D) Foreign key constraints

153. Which distance metric is commonly used to compare vector embeddings?

A) Cosine similarity
B) Transaction latency
C) Referential integrity
D) Row cardinality

154. What is pgvector primarily used for?

A) Encrypting PostgreSQL backups
B) Managing PostgreSQL users
C) Replacing SQL with NoSQL
D) Adding vector similarity search capabilities to PostgreSQL

155. What is Hybrid Search in modern databases?

A) Combining two database backups
B) Combining keyword-based and semantic/vector search
C) Combining two primary keys
D) Combining OLTP and backups

156. In a Retrieval-Augmented Generation (RAG) system, why is a vector database commonly used?

A) To compile the language model
B) To replace the LLM
C) To retrieve semantically relevant information from embeddings
D) To manage operating system processes

157. What does JSONB provide in PostgreSQL?

A) A binary representation of JSON optimized for processing and indexing
B) A replacement for SQL transactions
C) A database backup format only
D) A type of database connection

158. Which PostgreSQL index type is commonly used for efficient JSONB queries?

A) Hash-only index
B) Bitmap-only index
C) Spatial index only
D) GIN index

159. What is Change Data Capture (CDC) used for?

A) Compressing database tables
B) Capturing and propagating changes made to database data
C) Creating database passwords
D) Removing database indexes

160. Which technology is commonly used to stream database changes in real time?

A) CSS
B) FTP only
C) Apache Kafka
D) HTML

161. What is a distributed SQL database?

A) A database that provides SQL capabilities across multiple nodes
B) A database that cannot use SQL
C) A single-user database
D) A database stored only in memory

162. Which of the following is an example of a distributed SQL database?

A) SQLite
B) Microsoft Access
C) Redis
D) CockroachDB

163. What is the main goal of distributed SQL databases?

A) Remove transaction support
B) Provide SQL transactions with horizontal scalability and high availability
C) Store only images
D) Eliminate database replication

164. What is a quorum commonly used for in distributed databases?

A) Compressing rows
B) Formatting SQL queries
C) Determining the required number of replicas to participate in an operation
D) Creating indexes automatically

165. What is split-brain in a distributed database system?

A) Multiple nodes incorrectly believe they are the active authority
B) A table is split into columns
C) A query is divided into two clauses
D) A database is normalized twice

166. What does leader election accomplish in distributed systems?

A) Creates database tables
B) Deletes inactive users
C) Compresses database logs
D) Selects a node to coordinate certain operations

167. What is replication lag?

A) Time required to create a database
B) Delay between a change on a primary and its application on a replica
C) Delay caused by normalization
D) Time needed to delete an index

168. What is read-after-write consistency?

A) Reads always return cached data
B) Writes are never replicated
C) A read after a successful write observes that write
D) Reads are disabled during writes

169. What is strong consistency in a distributed database?

A) Reads reflect the latest committed state according to the database's consistency model
B) Data is never replicated
C) Queries always use indexes
D) Every query runs on one server

170. What is horizontal partitioning also commonly called?

A) Normalization
B) Replication
C) Encryption
D) Sharding

171. What is database connection pooling primarily intended to reduce?

A) Storage size
B) The overhead of repeatedly opening database connections
C) Number of database tables
D) Number of indexes

172. What is a database connection leak?

A) A corrupted database file
B) A failed database backup
C) A connection that is not properly released back to the pool
D) A missing primary key

173. Which SQL feature is useful for breaking complex queries into readable logical steps?

A) Common Table Expression (CTE)
B) Foreign key
C) Trigger
D) Index

174. What is a recursive CTE commonly used for?

A) Encrypting data
B) Creating backups
C) Creating indexes
D) Querying hierarchical or recursive data

175. Which window function is commonly used to rank rows while allowing ties to share a rank?

A) ROW_NUMBER()
B) RANK()
C) COUNT()
D) GROUP()

176. What is the key difference between RANK() and ROW_NUMBER()?

A) RANK() cannot be used with ORDER BY
B) ROW_NUMBER() only works with integers
C) RANK() can assign the same rank to tied rows, while ROW_NUMBER() assigns unique sequential numbers
D) They always produce identical results

177. Which SQL function is commonly used to replace NULL with another value?

A) COALESCE()
B) REPLACE_NULL()
C) NULLIF_ONLY()
D) DEFAULT_NULL()

178. What does NULLIF() generally return when its two expressions are equal?

A) The first expression
B) Zero
C) An error
D) NULL

179. Which SQL clause is evaluated conceptually after GROUP BY to filter aggregated groups?

A) WHERE
B) HAVING
C) FROM
D) DISTINCT

180. What is a covering index?

A) An index that encrypts all columns
B) An index that automatically backs up a table
C) An index containing all columns needed by a query
D) An index used only for foreign keys

181. What is a composite index?

A) An index created on multiple columns
B) An index created on multiple databases only
C) An index that stores backups
D) An index containing only NULL values

182. In a composite B-tree index on (A, B), which query is generally most likely to benefit directly from the index?

A) A query filtering only on B
B) A query that never references A or B
C) A query filtering on an unrelated column
D) A query filtering on A

183. What does a database query planner do?

A) Creates user passwords
B) Chooses an efficient strategy for executing a query
C) Deletes unused databases
D) Compresses all query results

184. What is a full table scan?

A) Scanning only indexed rows
B) Scanning only primary keys
C) Reading rows across the table to evaluate a query
D) Scanning database backups

185. Which operation is generally expensive on a very large table without an appropriate index?

A) Searching for a selective value using a full table scan
B) Reading a cached value
C) Using a suitable primary key lookup
D) Returning one already-buffered row

186. What is database query parameterization primarily used to help prevent?

A) Replication lag
B) Deadlocks
C) Data normalization
D) SQL injection

187. Which database security principle gives users only the permissions they need?

A) Open access
B) Principle of least privilege
C) Full privilege inheritance
D) Anonymous access

188. What is role-based access control (RBAC)?

A) Access based only on IP addresses
B) Access based only on database size
C) Assigning permissions through roles assigned to users
D) Encrypting every row separately

189. What is database encryption at rest designed to protect?

A) Stored database data from unauthorized access to storage
B) SQL syntax from developers
C) Query execution plans
D) Database normalization

190. What does TLS provide for database connections?

A) Data normalization
B) Automatic indexing
C) Query optimization
D) Encryption and protection of data in transit

191. What is a database audit log used for?

A) Improving CPU speed
B) Recording database activities for monitoring and compliance
C) Creating indexes automatically
D) Replacing backups

192. What is tokenization in database security?

A) Splitting a table into shards
B) Compressing SQL statements
C) Replacing sensitive values with non-sensitive tokens
D) Creating primary keys

193. What is the main difference between encryption and hashing?

A) Encryption is generally reversible with a key, while hashing is designed to be one-way
B) Hashing always requires a database connection
C) Encryption cannot protect data
D) They are always identical

194. Which approach is recommended for storing user passwords?

A) Plaintext storage
B) Reversible encryption without protection
C) Base64 encoding
D) A strong password-hashing algorithm with a unique salt

195. What is a database vulnerability assessment primarily intended to identify?

A) New SQL syntax
B) Security weaknesses and misconfigurations
C) New database rows
D) Query formatting errors only

196. What is a database snapshot?

A) A query execution plan
B) A user role
C) A point-in-time representation of database data
D) A type of SQL JOIN

197. Which backup strategy generally provides the fastest complete restore from a single backup set?

A) Full backup
B) Incremental-only backup
C) Transaction log only
D) Metadata backup only

198. What does RPO represent in disaster recovery?

A) Maximum query execution time
B) Maximum database size
C) Maximum number of users
D) Maximum acceptable amount of data loss measured in time

199. What does RTO represent in disaster recovery?

A) Maximum storage capacity
B) Target maximum time to restore service after a disruption
C) Maximum number of replicas
D) Query timeout value only

200. Why should database backups be tested regularly?

A) To increase table size
B) To reduce query indexes
C) To verify that data can actually be restored when needed
D) To eliminate database security

201. What is an OLTP system primarily optimized for?

A) High-volume transactional operations
B) Long-running analytical queries only
C) Static document storage
D) Image rendering

202. What is an OLAP system primarily designed for?

A) Password management
B) High-frequency single-row updates only
C) Operating system scheduling
D) Analytical queries over large datasets

203. Which storage layout is commonly preferred by analytical databases for scanning selected columns?

A) Row-oriented storage
B) Column-oriented storage
C) Heap-only storage
D) Key-only storage

204. What is a data warehouse?

A) A temporary database cache
B) A database used only for authentication
C) A system designed to store and analyze integrated historical data
D) A replacement for every OLTP system

205. What is a data lake primarily designed to store?

A) Large volumes of raw structured, semi-structured, and unstructured data
B) Only normalized relational tables
C) Only transaction logs
D) Only database credentials

206. What is a data lakehouse?

A) A database without metadata
B) A database used only for backups
C) A replacement for SQL
D) An architecture combining data lake flexibility with warehouse-style management and analytics

207. Which technology is commonly associated with lakehouse table formats?

A) FTP
B) Apache Iceberg
C) SMTP
D) DNS

208. What is a star schema commonly used for?

A) Network security
B) Application authentication
C) Data warehousing and analytical workloads
D) Operating system scheduling

209. In a star schema, what does a fact table primarily contain?

A) Measures and keys linking to dimensions
B) Only user passwords
C) Only database metadata
D) Only application source code

210. What does ETL stand for?

A) Execute, Transfer, Load
B) Extract, Transform, Link
C) Export, Translate, Load
D) Extract, Transform, Load

211. What is ELT different from ETL primarily because transformation occurs where?

A) Before data is extracted
B) After loading data into the target platform
C) Only inside the application browser
D) Before the source database exists

212. What is HTAP designed to support?

A) Only backups
B) Only batch processing
C) Hybrid transactional and analytical processing
D) Only graph traversal

213. What is a materialized view commonly used for?

A) Storing query results to speed up repeated access
B) Encrypting database connections
C) Creating user accounts
D) Replacing transaction logs

214. What is query result caching intended to improve?

A) Referential integrity
B) Database normalization
C) Password complexity
D) Response time for repeated queries

215. Which cache is commonly used as an in-memory data store alongside databases?

A) SQLite
B) Redis
C) Oracle Forms
D) Apache HTTP Server

216. What is cache invalidation?

A) Creating a new database
B) Encrypting cached data
C) Removing or updating stale cached data
D) Normalizing cache tables

217. What is the N+1 query problem?

A) Executing one query to fetch a list and then additional queries for each returned item
B) Running exactly one query
C) Creating N+1 database servers
D) Using N+1 indexes

218. Which approach can help reduce an N+1 query problem?

A) Removing all indexes
B) Disabling transactions
C) Increasing table redundancy without analysis
D) Using joins, eager loading, or batched queries where appropriate

219. What is database connection multiplexing intended to improve?

A) Table normalization
B) Efficient use of database connections
C) Password encryption
D) Primary key uniqueness

220. What is database observability commonly based on?

A) Only database usernames
B) Only table names
C) Metrics, logs, traces, and query performance information
D) Only backup files

221. Which metric is particularly useful for identifying slow database queries?

A) Query latency
B) Password length
C) Number of database users only
D) Table naming convention

222. What does database throughput measure?

A) Database file size
B) Number of columns
C) Number of indexes
D) Amount of work or operations processed over a period

223. What is database connection saturation?

A) A database with no users
B) A condition where available database connections are heavily or fully utilized
C) A database with no indexes
D) A completed backup

224. What is a slow query log used for?

A) Storing user passwords
B) Creating database schemas
C) Identifying queries that exceed a configured execution-time threshold
D) Replacing indexes

225. What is database workload profiling?

A) Analyzing query patterns and resource usage to understand database workloads
B) Encrypting all tables
C) Removing database users
D) Creating a backup only

226. What is schema-on-read commonly associated with?

A) Traditional OLTP-only databases
B) Fixed schemas before every write
C) Primary key enforcement only
D) Applying structure when data is read or analyzed

227. What is schema-on-write?

A) Applying schema only during backup
B) Data must conform to a defined structure when it is written
C) Data is never validated
D) Schema is created after every SELECT

228. What is an idempotent database operation?

A) An operation that always fails
B) An operation that always creates duplicates
C) An operation that can be applied repeatedly with the same intended effect after the first successful application
D) An operation that cannot be retried

229. Why are idempotent operations useful in distributed applications?

A) They make retries safer when requests may be repeated
B) They remove the need for databases
C) They guarantee zero latency
D) They disable transactions

230. What is an upsert?

A) A query that only reads data
B) A backup operation
C) A database shutdown operation
D) An operation that inserts a row or updates it when a matching row already exists

231. Which SQL feature can enforce that duplicate values are not allowed in a column?

A) CHECK only
B) UNIQUE constraint
C) DEFAULT only
D) VIEW

232. What does a CHECK constraint do?

A) Creates a backup
B) Creates a foreign server
C) Restricts values according to a specified condition
D) Creates a database replica

233. What is referential integrity intended to ensure?

A) Foreign key references remain valid according to defined constraints
B) Every table has exactly one index
C) Every query is optimized
D) Every database is distributed

234. What is a cascading foreign key action?

A) Automatically creating an index for every column
B) Automatically encrypting all child rows
C) Automatically backing up the parent table
D) Automatically applying specified changes to related rows

235. What is a generated column?

A) A column generated by a user manually each time
B) A column whose value is derived from an expression based on other data
C) A database backup column
D) A column that cannot contain data

236. What is a database sequence commonly used for?

A) Encrypting passwords
B) Creating foreign databases
C) Generating sequential numeric values
D) Compressing indexes

237. What is database collation primarily concerned with?

A) Rules for comparing and sorting character data
B) Database replication
C) Transaction rollback
D) Backup scheduling

238. What is a database deadlock detector responsible for?

A) Finding missing indexes only
B) Encrypting transaction logs
C) Creating database replicas
D) Detecting transactions that are mutually waiting in a deadlock

239. Which technique can reduce deadlocks by ensuring transactions acquire locks in a consistent order?

A) Random lock ordering
B) Consistent lock ordering
C) Removing all constraints
D) Disabling commits

240. What is optimistic concurrency control based on?

A) Locking every row before reading
B) Disabling concurrent transactions
C) Allowing concurrent work and validating for conflicts before commit
D) Removing transaction isolation

241. What is pessimistic concurrency control primarily based on?

A) Acquiring locks to prevent conflicting concurrent operations
B) Never using locks
C) Disabling transactions
D) Storing only backups

242. What does MVCC help databases achieve?

A) Eliminate all backups
B) Remove all indexes
C) Prevent all queries from running concurrently
D) Provide consistent reads using multiple versions of data

243. What is a write conflict in concurrent database operations?

A) A failed SELECT syntax
B) Multiple operations attempting incompatible changes to the same data
C) A missing database backup
D) An unused index

244. What is transaction retry commonly used for in distributed databases?

A) Creating tables
B) Replacing backups
C) Re-executing transactions that fail due to transient conflicts or serialization errors
D) Removing database security

245. What is a distributed transaction?

A) A transaction involving resources on multiple database nodes or systems
B) A transaction that cannot be committed
C) A transaction containing only SELECT
D) A transaction that runs only offline

246. Which protocol is historically used to coordinate commit decisions across distributed transaction participants?

A) FTP
B) HTTP
C) DNS
D) Two-Phase Commit (2PC)

247. What is the main drawback of Two-Phase Commit?

A) It cannot coordinate transactions
B) Participants can remain blocked while waiting for the coordinator during failures
C) It cannot commit data
D) It only works with NoSQL databases

248. What is database failover testing intended to verify?

A) SQL formatting
B) Number of columns in a table
C) That workloads can switch to a standby system when the primary fails
D) That backups are unnecessary

249. Which database architecture pattern commonly separates read and write workloads?

A) Primary database with read replicas
B) Single flat file only
C) Database without indexes
D) Offline-only database

250. Which modern database capability is especially useful for AI applications that need semantic retrieval?

A) Removing all database indexes
B) Disabling transactions
C) Storing only plain-text passwords
D) Vector similarity search over embeddings

Your Quiz Result

Right Answers:

Wrong Answers:

Unattempted:

Score: %

Congratulations!
With a % score, you have unlocked up to 50% discount on all of our self-paced SQL courses.
Click on the Contact button to avail the offer.

About the Author

Piyush Verma

Piyush is a data science professional with experience building predictive models for retail and finance clients. He is an expert in statistical analysis, exploratory data analysis, feature engineering, and machine learning using Python and SQL. He applies data science methods to practical business problems and communicates results to stakeholders. He writes articles and blogs when he is completely free.

Enquiry is created successfully

Drop Us a Query

Fields marked * are mandatory

Name

Phone Number