EduConceptHub

Start MCQs

Database Systems

Database Systems

Introduction

Learn to design, query and manage structured data using database models, relational principles and SQL.

Topic-Wise Overview

Database Foundations

DBMS architecture, schemas, data independence and relational models

Relational Operations

Keys, constraints, relational algebra and joins

Database Design

Entity-relationship modelling, functional dependencies and normalisation

SQL

Data definition, queries, subqueries, joins and aggregation

Management

Transactions, concurrency, indexes, backup, recovery and NoSQL overview

Study Guidance

Explore the complete collection of 200 Database Systems MCQs below, each with four options, its correct answer and an explanation. The six sets cover relational databases, SQL, normalization, transactions, recovery, distributed databases, NoSQL, database security and analytics. Additional short questions and worked examples are planned.

Explore Other Subjects

Browse Computer Science subjects

Database Systems MCQs — Set 1 (Questions 1–30)

Study each question and reveal the correct answer with an explanation.

Question 1. What does DBMS stand for?

  1. Database Management System
  2. Data Binary Mapping Service
  3. Database Memory Scheduler
  4. Digital Backup Management System
Show correct answer and explanation

Correct answer: A. Database Management System

A DBMS manages databases, queries and controlled access to stored data.

Question 2. A database schema describes

  1. Only the current number of records
  2. The structure and constraints of stored data
  3. A backup schedule
  4. An individual user password
Show correct answer and explanation

Correct answer: B. The structure and constraints of stored data

The schema defines objects such as tables, columns and constraints.

Question 3. A database instance represents

  1. Only the database design
  2. The SQL language standard
  3. The actual data at a particular time
  4. A database product name
Show correct answer and explanation

Correct answer: C. The actual data at a particular time

An instance is the current contents of the database.

Question 4. Physical data independence means

  1. Removing every index
  2. Deleting the application layer
  3. Eliminating data types
  4. Changing physical storage without requiring changes to the logical schema
Show correct answer and explanation

Correct answer: D. Changing physical storage without requiring changes to the logical schema

Applications can retain logical access despite changes in internal storage organization.

Question 5. The relational model represents data primarily as

  1. Relations or tables
  2. Only nested files
  3. Only objects with methods
  4. Only binary trees
Show correct answer and explanation

Correct answer: A. Relations or tables

A relation is a set of tuples defined over named attributes.

Question 6. A row in a relational table is called a

  1. Domain
  2. Tuple
  3. Schema
  4. Constraint
Show correct answer and explanation

Correct answer: B. Tuple

Tuples are the records of a relation.

Question 7. A column in a relation is commonly called an

  1. Tuple
  2. Transaction
  3. Attribute
  4. Index page
Show correct answer and explanation

Correct answer: C. Attribute

Attributes specify the properties represented in each tuple.

Question 8. The domain of an attribute defines

  1. Its table’s total row count
  2. The transaction log size
  3. The index height
  4. Its permitted values
Show correct answer and explanation

Correct answer: D. Its permitted values

Domains constrain what values an attribute can take.

Question 9. A primary key must be

  1. Unique and not NULL
  2. Always a text string
  3. Repeated in several rows
  4. An optional duplicate value
Show correct answer and explanation

Correct answer: A. Unique and not NULL

A primary key uniquely identifies each row and disallows NULL.

Question 10. A candidate key is

  1. Any column containing numbers
  2. A minimal set of attributes uniquely identifying rows
  3. Every foreign key
  4. A nonunique sorting field
Show correct answer and explanation

Correct answer: B. A minimal set of attributes uniquely identifying rows

Candidate keys are minimal superkeys.

Question 11. A superkey is

  1. Only a single primary key column
  2. A key from another database
  3. A set of attributes that uniquely identifies each row
  4. A column with duplicate values
Show correct answer and explanation

Correct answer: C. A set of attributes that uniquely identifies each row

A superkey may contain additional attributes beyond a candidate key.

Question 12. A foreign key is used to

  1. Sort the database physically
  2. Encrypt every column
  3. Count duplicate records
  4. Enforce referential relationships between tables
Show correct answer and explanation

Correct answer: D. Enforce referential relationships between tables

Foreign key values refer to suitable unique or primary key values in another or the same table.

Question 13. Referential integrity requires

  1. Foreign key values to refer to permitted referenced rows or be NULL when allowed
  2. Every table to have exactly one row
  3. All strings to be uppercase
  4. Transactions to run sequentially
Show correct answer and explanation

Correct answer: A. Foreign key values to refer to permitted referenced rows or be NULL when allowed

Foreign key constraints prevent invalid references.

Question 14. Entity integrity in the relational model primarily requires

  1. All columns to be indexed
  2. Primary-key attributes not to be NULL
  3. Every table to have a foreign key
  4. All data to be encrypted
Show correct answer and explanation

Correct answer: B. Primary-key attributes not to be NULL

A primary key must identify a row with known values.

Question 15. An entity in ER modeling is

  1. A SQL sorting option
  2. A buffer frame
  3. A distinguishable real-world object or concept
  4. A query plan only
Show correct answer and explanation

Correct answer: C. A distinguishable real-world object or concept

ER models describe entities and relationships.

Question 16. A one-to-many relationship means

  1. Every entity relates to exactly one other
  2. No entity can be repeated
  3. Every row must have two primary keys
  4. One entity occurrence can relate to multiple occurrences of another entity type
Show correct answer and explanation

Correct answer: D. One entity occurrence can relate to multiple occurrences of another entity type

A department can have many employees, for example.

Question 17. A weak entity is typically identified using

  1. A partial key together with an identifying owner’s key
  2. Only a global independent primary key
  3. A row number without constraints
  4. An unrelated unique index
Show correct answer and explanation

Correct answer: A. A partial key together with an identifying owner’s key

Weak entities depend on an owner for identification.

Question 18. Which SQL statement retrieves rows?

  1. INSERT
  2. SELECT
  3. DROP
  4. GRANT
Show correct answer and explanation

Correct answer: B. SELECT

SELECT queries data from tables and views.

Question 19. Which SQL clause filters rows before grouping?

  1. HAVING
  2. ORDER BY
  3. WHERE
  4. LIMIT
Show correct answer and explanation

Correct answer: C. WHERE

WHERE applies predicates to individual rows before grouping.

Question 20. Which clause filters grouped results?

  1. WHERE
  2. FROM
  3. DISTINCT
  4. HAVING
Show correct answer and explanation

Correct answer: D. HAVING

HAVING filters groups, commonly using aggregate conditions.

Question 21. Which SQL clause sorts query results?

  1. ORDER BY
  2. GROUP BY
  3. WHERE
  4. JOIN
Show correct answer and explanation

Correct answer: A. ORDER BY

ORDER BY controls result order.

Question 22. Which SQL clause groups rows for aggregation?

  1. DISTINCT
  2. GROUP BY
  3. LIMIT
  4. UNION
Show correct answer and explanation

Correct answer: B. GROUP BY

GROUP BY forms groups that aggregates can summarize.

Question 23. Which aggregate function counts rows with COUNT(*)?

  1. SUM(*)
  2. AVG(*)
  3. COUNT(*)
  4. MIN(*)
Show correct answer and explanation

Correct answer: C. COUNT(*)

COUNT(*) counts rows, including those with NULL column values.

Question 24. What does COUNT(column_name) exclude?

  1. Duplicate non-NULL values always
  2. Zero values
  3. Negative values
  4. NULL values in that column
Show correct answer and explanation

Correct answer: D. NULL values in that column

COUNT(expression) counts its non-NULL evaluated values.

Question 25. What does DISTINCT do in SELECT?

  1. Removes duplicate result rows
  2. Deletes duplicate source records
  3. Sorts rows automatically
  4. Creates a primary key
Show correct answer and explanation

Correct answer: A. Removes duplicate result rows

DISTINCT removes duplicate rows from the query result.

Question 26. Which SQL operator is used to test a NULL value?

  1. = NULL
  2. IS NULL
  3. LIKE NULL
  4. IN NULL
Show correct answer and explanation

Correct answer: B. IS NULL

IS NULL is the standard predicate for testing missing values.

Question 27. Which SQL join returns only matching combinations from both inputs?

  1. FULL OUTER JOIN
  2. CROSS JOIN
  3. INNER JOIN
  4. LEFT OUTER JOIN
Show correct answer and explanation

Correct answer: C. INNER JOIN

An inner join returns row combinations satisfying the join condition.

Question 28. A LEFT OUTER JOIN preserves

  1. Only matched right input rows
  2. Only unmatched right rows
  3. No NULL values
  4. All rows from the left input
Show correct answer and explanation

Correct answer: D. All rows from the left input

Unmatched left rows appear with NULL-extended right-side columns.

Question 29. A CROSS JOIN returns

  1. A Cartesian product of input rows
  2. Only rows having identical keys
  3. The union of both tables
  4. Only distinct shared attributes
Show correct answer and explanation

Correct answer: A. A Cartesian product of input rows

Every left row is paired with every right row.

Question 30. If table A has 3 rows and B has 4 rows, their CROSS JOIN has

  1. 7 rows
  2. 12 rows
  3. 1 row
  4. 4 rows
Show correct answer and explanation

Correct answer: B. 12 rows

A Cartesian product contains 3 × 4 = 12 row pairs.

Database Systems MCQs — Set 2 (Questions 31–60)

Study each question and reveal the correct answer with an explanation.

Question 31. Which SQL command adds a new row?

  1. UPDATE
  2. ALTER
  3. INSERT
  4. TRUNCATE
Show correct answer and explanation

Correct answer: C. INSERT

INSERT adds rows to a table.

Question 32. Which command modifies existing row values?

  1. CREATE
  2. SELECT
  3. GRANT
  4. UPDATE
Show correct answer and explanation

Correct answer: D. UPDATE

UPDATE changes existing rows satisfying its optional WHERE condition.

Question 33. Which command removes selected rows?

  1. DELETE
  2. DROP TABLE
  3. CREATE VIEW
  4. COMMIT
Show correct answer and explanation

Correct answer: A. DELETE

DELETE removes rows; without a WHERE clause it can remove all rows.

Question 34. Which statement creates a table?

  1. INSERT TABLE
  2. CREATE TABLE
  3. SELECT TABLE
  4. GRANT TABLE
Show correct answer and explanation

Correct answer: B. CREATE TABLE

CREATE TABLE defines a new table and its columns.

Question 35. Which statement changes a table definition?

  1. UPDATE TABLE
  2. ORDER TABLE
  3. ALTER TABLE
  4. SELECT TABLE
Show correct answer and explanation

Correct answer: C. ALTER TABLE

ALTER TABLE changes schema definitions.

Question 36. Which statement removes a table definition and its data?

  1. DELETE WHERE
  2. ROLLBACK
  3. GROUP BY
  4. DROP TABLE
Show correct answer and explanation

Correct answer: D. DROP TABLE

DROP TABLE removes the table object, subject to database rules and dependencies.

Question 37. A database view is generally

  1. A stored query definition presented as a virtual table
  2. Always a duplicated physical table
  3. Only a transaction log
  4. A temporary CPU cache
Show correct answer and explanation

Correct answer: A. A stored query definition presented as a virtual table

A conventional view derives its contents from underlying relations.

Question 38. An index is used mainly to

  1. Guarantee every query is instant
  2. Speed up selected data retrieval operations
  3. Remove every need for constraints
  4. Prevent all concurrent access
Show correct answer and explanation

Correct answer: B. Speed up selected data retrieval operations

Indexes provide access paths at storage and maintenance cost.

Question 39. A B-tree family index is particularly useful for

  1. Only image storage
  2. Only full-text translation
  3. Ordered searches and range queries
  4. Eliminating all transaction logs
Show correct answer and explanation

Correct answer: C. Ordered searches and range queries

Balanced tree structures support ordered lookups and ranges.

Question 40. A hash index is especially suitable for

  1. Ordered range scans
  2. Sorting all rows
  3. Detecting foreign keys
  4. Equality lookups
Show correct answer and explanation

Correct answer: D. Equality lookups

Hash access is well suited to finding records matching a key exactly.

Question 41. A functional dependency X → Y means

  1. Rows agreeing on X must agree on Y
  2. X and Y must be identical columns
  3. Y is a foreign key
  4. X can never be NULL
Show correct answer and explanation

Correct answer: A. Rows agreeing on X must agree on Y

A functional dependency captures a constraint among attribute values.

Question 42. First normal form requires

  1. Every table to have exactly one attribute
  2. Atomic values under the chosen relational domain interpretation
  3. Every relation to be in BCNF
  4. All attributes to be numeric
Show correct answer and explanation

Correct answer: B. Atomic values under the chosen relational domain interpretation

1NF rejects repeating groups and non-atomic field structures in the usual introductory model.

Question 43. Second normal form eliminates

  1. Every foreign key
  2. Every functional dependency
  3. Partial dependencies of non-prime attributes on candidate keys
  4. Every duplicate value
Show correct answer and explanation

Correct answer: C. Partial dependencies of non-prime attributes on candidate keys

2NF requires 1NF and no non-prime attribute partly dependent on a candidate key.

Question 44. Third normal form aims to reduce

  1. All joins from queries
  2. All indexes
  3. All primary keys
  4. Problematic transitive dependencies involving non-key attributes
Show correct answer and explanation

Correct answer: D. Problematic transitive dependencies involving non-key attributes

3NF restricts functional dependencies to limit redundancy and update anomalies.

Question 45. BCNF requires that for every nontrivial dependency X → Y

  1. X is a superkey
  2. Y is always a primary key
  3. X is a single attribute
  4. X equals Y
Show correct answer and explanation

Correct answer: A. X is a superkey

Boyce–Codd normal form requires each determinant of a nontrivial FD to be a superkey.

Question 46. An update anomaly arises when

  1. A query uses ORDER BY
  2. Redundant data require multiple coordinated changes
  3. A table has a unique key
  4. A transaction commits
Show correct answer and explanation

Correct answer: B. Redundant data require multiple coordinated changes

Denormalized repetition can produce inconsistent copies after incomplete updates.

Question 47. A lossless decomposition ensures

  1. Every table has one row
  2. No joins are ever needed
  3. The original relation can be reconstructed without spurious tuples
  4. All data are compressed
Show correct answer and explanation

Correct answer: C. The original relation can be reconstructed without spurious tuples

A lossless join preserves the original information through decomposition.

Question 48. A transaction is

  1. A column definition only
  2. A physical memory sector
  3. A single SQL keyword necessarily
  4. A logical unit of database work
Show correct answer and explanation

Correct answer: D. A logical unit of database work

Transactions group operations with defined commit and rollback semantics.

Question 49. Atomicity in ACID means

  1. All-or-nothing transaction effects
  2. Every read is instantaneous
  3. Every transaction uses one CPU
  4. No indexes are permitted
Show correct answer and explanation

Correct answer: A. All-or-nothing transaction effects

Atomicity prevents a partially applied committed transaction outcome.

Question 50. Consistency in ACID refers to

  1. Every query returning identical rows
  2. Preservation of declared integrity constraints across valid transactions
  3. All tables having equal size
  4. Automatic password recovery
Show correct answer and explanation

Correct answer: B. Preservation of declared integrity constraints across valid transactions

Transactions should take the database from one valid state to another.

Question 51. Isolation in ACID addresses

  1. Disk encryption only
  2. Physical table locations
  3. Interference between concurrently executing transactions
  4. Only backup frequency
Show correct answer and explanation

Correct answer: C. Interference between concurrently executing transactions

Isolation controls what effects concurrent transactions can observe.

Question 52. Durability in ACID means

  1. No transaction may be rolled back
  2. Every query runs without a lock
  3. Data never need backups
  4. Committed changes survive relevant failures under the system’s guarantees
Show correct answer and explanation

Correct answer: D. Committed changes survive relevant failures under the system’s guarantees

Durability requires persistent recovery of committed work.

Question 53. COMMIT usually

  1. Makes a transaction’s changes permanent under its durability guarantees
  2. Cancels every query
  3. Deletes the transaction log
  4. Reverses all inserted rows
Show correct answer and explanation

Correct answer: A. Makes a transaction’s changes permanent under its durability guarantees

COMMIT ends a transaction successfully.

Question 54. ROLLBACK usually

  1. Creates a new index
  2. Undoes uncommitted changes of a transaction
  3. Grants SELECT privileges
  4. Sorts all relations
Show correct answer and explanation

Correct answer: B. Undoes uncommitted changes of a transaction

ROLLBACK restores transactional state subject to the DBMS’s semantics.

Question 55. A dirty read occurs when

  1. Two rows have the same non-key value
  2. An index returns a match
  3. One transaction reads another’s uncommitted changes
  4. A transaction reads its own committed data
Show correct answer and explanation

Correct answer: C. One transaction reads another’s uncommitted changes

Uncommitted changes might later be rolled back.

Question 56. A nonrepeatable read occurs when

  1. A database has repeated rows
  2. A query contains DISTINCT
  3. An index is rebuilt
  4. Repeating a row read in a transaction yields a changed value due to another committed transaction
Show correct answer and explanation

Correct answer: D. Repeating a row read in a transaction yields a changed value due to another committed transaction

Concurrent modifications can change the value seen by repeated reads.

Question 57. A phantom read refers to

  1. Re-executing a predicate query and observing new or missing qualifying rows
  2. Reading an unreadable disk sector
  3. Counting NULL values
  4. Reading one file twice
Show correct answer and explanation

Correct answer: A. Re-executing a predicate query and observing new or missing qualifying rows

Concurrent inserts or deletes can alter the set of rows matching a predicate.

Question 58. Two-phase locking requires

  1. Exactly two database transactions
  2. A growing lock-acquisition phase followed by a shrinking release phase
  3. Only two tables
  4. All locks to be released before any are acquired
Show correct answer and explanation

Correct answer: B. A growing lock-acquisition phase followed by a shrinking release phase

Basic 2PL prohibits acquiring new locks after releasing the first lock.

Question 59. A deadlock in transaction locking can involve

  1. Only one transaction without resources
  2. Queries using arithmetic
  3. Transactions cyclically waiting for each other’s held locks
  4. Indexes without writes
Show correct answer and explanation

Correct answer: C. Transactions cyclically waiting for each other’s held locks

Circular lock waits can prevent progress.

Question 60. Write-ahead logging requires

  1. Data pages always to be written before logs
  2. No logging of committed changes
  3. All transactions to be read-only
  4. Relevant log records to be persisted before corresponding data-page changes are flushed
Show correct answer and explanation

Correct answer: D. Relevant log records to be persisted before corresponding data-page changes are flushed

WAL preserves recovery information ahead of propagating changes to data pages.

Database Systems MCQs — Set 3 (Questions 61–90)

Choose an option, then reveal the answer and explanation.

Question 61. What does SQL UNION do?

  1. Combine compatible results and eliminate duplicates
  2. Preserve all duplicate rows
  3. Join on a key
  4. Create an index
Show correct answer and explanation

Correct answer: A. Combine compatible results and eliminate duplicates

UNION returns distinct rows from compatible query results.

Question 62. What does UNION ALL do?

  1. Always sort descending
  2. Combine compatible results without removing duplicates
  3. Group by primary key
  4. Remove every NULL
Show correct answer and explanation

Correct answer: B. Combine compatible results without removing duplicates

UNION ALL preserves duplicate result rows.

Question 63. What does INTERSECT return?

  1. All left rows
  2. All right rows
  3. Rows common to both compatible results
  4. Every combination
Show correct answer and explanation

Correct answer: C. Rows common to both compatible results

INTERSECT finds the set intersection.

Question 64. What does SQL EXCEPT return?

  1. Only common rows
  2. All rows from both
  3. Only NULLs
  4. Rows in the first result not in the second
Show correct answer and explanation

Correct answer: D. Rows in the first result not in the second

EXCEPT implements set difference.

Question 65. What is SQL IN used for?

  1. Testing membership in a list or result
  2. Sorting rows
  3. Renaming attributes
  4. Committing writes
Show correct answer and explanation

Correct answer: A. Testing membership in a list or result

IN tests whether a value equals one of specified values.

Question 66. Which operator supports simple string patterns?

  1. COUNT
  2. LIKE
  3. DISTINCT
  4. BETWEEN
Show correct answer and explanation

Correct answer: B. LIKE

LIKE supports wildcard matching.

Question 67. What does % mean in a LIKE pattern?

  1. Exactly one character
  2. Exactly one digit
  3. Zero or more characters
  4. A literal space
Show correct answer and explanation

Correct answer: C. Zero or more characters

Percent matches arbitrary-length character sequences.

Question 68. What does _ mean in a LIKE pattern?

  1. Zero or more characters
  2. A word boundary
  3. Any numeric value only
  4. Exactly one character
Show correct answer and explanation

Correct answer: D. Exactly one character

Underscore is a one-character wildcard.

Question 69. Is BETWEEN inclusive in ordinary SQL?

  1. Yes, both endpoints are included
  2. No endpoints are included
  3. Only lower is included
  4. Only upper is included
Show correct answer and explanation

Correct answer: A. Yes, both endpoints are included

BETWEEN is generally equivalent to greater-than-or-equal and less-than-or-equal.

Question 70. What does EXISTS test?

  1. Whether every value is non-NULL
  2. Whether a subquery returns any rows
  3. Whether all tables have indexes
  4. Whether rows are sorted
Show correct answer and explanation

Correct answer: B. Whether a subquery returns any rows

EXISTS is true if its subquery produces at least one row.

Question 71. What makes a subquery correlated?

  1. It contains COUNT
  2. It uses SELECT
  3. It refers to an enclosing query’s attributes
  4. It has an alias
Show correct answer and explanation

Correct answer: C. It refers to an enclosing query’s attributes

Correlated subqueries depend on values from outer query blocks.

Question 72. Which PostgreSQL clause limits returned rows?

  1. GROUP BY
  2. HAVING
  3. UNION
  4. LIMIT
Show correct answer and explanation

Correct answer: D. LIMIT

LIMIT caps the number of result rows.

Question 73. Which expression obtains the highest salary?

  1. MAX(salary)
  2. MIN(salary)
  3. AVG(salary)
  4. COUNT(salary)
Show correct answer and explanation

Correct answer: A. MAX(salary)

MAX returns the largest non-NULL input.

Question 74. What does AVG ignore?

  1. Zero values
  2. NULL inputs
  3. Negative values
  4. Duplicates
Show correct answer and explanation

Correct answer: B. NULL inputs

AVG averages non-NULL values.

Question 75. Why use AS in a SELECT expression?

  1. To impose uniqueness
  2. To commit
  3. To assign an output alias
  4. To grant permissions
Show correct answer and explanation

Correct answer: C. To assign an output alias

AS introduces a name for a result expression or table reference.

Question 76. Which is the standard SQL equality operator?

  1. ==
  2. :=
  3. ===
  4. =
Show correct answer and explanation

Correct answer: D. =

SQL uses = for equality comparisons.

Question 77. What is a self-join?

  1. A table joined to another reference of itself
  2. A query with no FROM
  3. A union of databases
  4. A table without keys
Show correct answer and explanation

Correct answer: A. A table joined to another reference of itself

Aliases distinguish multiple roles of the same relation.

Question 78. What is the purpose of UNIQUE?

  1. Force all fields non-NULL
  2. Disallow duplicate key values under the DBMS’s NULL rules
  3. Sort table rows
  4. Create a backup
Show correct answer and explanation

Correct answer: B. Disallow duplicate key values under the DBMS’s NULL rules

UNIQUE constrains permitted repeated values.

Question 79. What is the purpose of CHECK?

  1. Define a join algorithm
  2. Sort query results
  3. Restrict rows using a declared predicate
  4. Log transactions
Show correct answer and explanation

Correct answer: C. Restrict rows using a declared predicate

CHECK disallows rows where its condition is false.

Question 80. What does DEFAULT supply?

  1. A guaranteed primary key
  2. A stored backup
  3. A query plan
  4. A value when one is omitted for a column
Show correct answer and explanation

Correct answer: D. A value when one is omitted for a column

Column defaults provide an expression when no explicit value is given.

Question 81. What does ON DELETE CASCADE do?

  1. Remove referencing rows when a referenced row is deleted
  2. Never delete referencing rows
  3. Drop the database
  4. Disable a foreign key
Show correct answer and explanation

Correct answer: A. Remove referencing rows when a referenced row is deleted

Referential action CASCADE propagates deletions.

Question 82. What does ON DELETE RESTRICT do?

  1. Delete all child rows
  2. Prevent deletion while referencing rows remain
  3. Replace child values with zero
  4. Commit a transaction
Show correct answer and explanation

Correct answer: B. Prevent deletion while referencing rows remain

RESTRICT blocks prohibited referenced-row deletion.

Question 83. Which type is intended for exact decimal quantities?

  1. FLOAT
  2. BLOB
  3. DECIMAL
  4. TEXT
Show correct answer and explanation

Correct answer: C. DECIMAL

DECIMAL(p,s) supports exact fixed-scale arithmetic.

Question 84. Why avoid binary FLOAT for exact money values?

  1. FLOAT cannot add numbers
  2. FLOAT is text
  3. FLOAT cannot be negative
  4. Some decimals cannot be represented exactly
Show correct answer and explanation

Correct answer: D. Some decimals cannot be represented exactly

Binary floating-point approximations can cause rounding discrepancies.

Question 85. What is a composite key?

  1. A key made from multiple attributes
  2. A key from another table only
  3. A nonunique attribute
  4. An index without values
Show correct answer and explanation

Correct answer: A. A key made from multiple attributes

Composite keys use attribute combinations for identification.

Question 86. What is a surrogate key?

  1. An external disk block
  2. An artificial identifier without direct business meaning
  3. A natural name
  4. A duplicate index
Show correct answer and explanation

Correct answer: B. An artificial identifier without direct business meaning

Generated record identifiers are common surrogate keys.

Question 87. What is a natural key?

  1. A random sequence number only
  2. A file offset
  3. A meaningful attribute or set that uniquely identifies an entity
  4. A NULL foreign key
Show correct answer and explanation

Correct answer: C. A meaningful attribute or set that uniquely identifies an entity

Natural keys originate in the modeled business domain.

Question 88. What causes an insertion anomaly?

  1. A simple SELECT
  2. A valid index
  3. An automatic commit
  4. A design forces unrelated information to be supplied to insert a fact
Show correct answer and explanation

Correct answer: D. A design forces unrelated information to be supplied to insert a fact

Badly combined facts can block clean insertion.

Question 89. What causes a deletion anomaly?

  1. Deleting one fact inadvertently removes another needed fact
  2. Deleting expired logs
  3. A dropped temporary table
  4. A database backup
Show correct answer and explanation

Correct answer: A. Deleting one fact inadvertently removes another needed fact

Redundancy can make unrelated information depend on a single row.

Question 90. Why calculate attribute closure?

  1. To calculate CPU speed
  2. To find attributes functionally implied by a set
  3. To count rows
  4. To sort columns
Show correct answer and explanation

Correct answer: B. To find attributes functionally implied by a set

Closure supports key discovery and FD implication tests.

Database Systems MCQs — Set 4 (Questions 91–120)

Choose an option, then reveal the answer and explanation.

Question 91. If A determines B and B determines C, then

  1. C determines A
  2. B determines A
  3. A determines C
  4. C determines B
Show correct answer and explanation

Correct answer: C. A determines C

Functional dependencies satisfy transitivity.

Question 92. Which dependency is trivial?

  1. A determines unrelated C
  2. B determines unrelated D
  3. C determines unrelated A
  4. AB determines A
Show correct answer and explanation

Correct answer: D. AB determines A

A dependency is trivial when the dependent attributes are included in the determinant.

Question 93. Why preserve dependencies during decomposition?

  1. To enforce constraints without recombining relations unnecessarily
  2. To ban joins
  3. To avoid keys
  4. To remove all indexes
Show correct answer and explanation

Correct answer: A. To enforce constraints without recombining relations unnecessarily

Dependency preservation allows local constraint enforcement.

Question 94. What is the key BCNF requirement?

  1. Every table has one column
  2. Every nontrivial FD’s determinant is a superkey
  3. Every column is numeric
  4. All tuples are ordered
Show correct answer and explanation

Correct answer: B. Every nontrivial FD’s determinant is a superkey

BCNF requires determinants of nontrivial dependencies to be superkeys.

Question 95. Does BCNF imply 3NF in the standard relational model?

  1. No
  2. Only for empty tables
  3. Yes
  4. Only for tables without keys
Show correct answer and explanation

Correct answer: C. Yes

BCNF is stronger than third normal form.

Question 96. How are many-to-many relationships typically implemented?

  1. With one delimited text column
  2. With an OS thread
  3. With a trigger only
  4. With an association table
Show correct answer and explanation

Correct answer: D. With an association table

A junction table stores participating key pairs.

Question 97. How does a materialized view differ from a normal view?

  1. It persists derived query results
  2. It cannot have a SELECT
  3. It always contains exactly one row
  4. It never needs refresh
Show correct answer and explanation

Correct answer: A. It persists derived query results

Materialized views store the computed result.

Question 98. Why can many indexes slow writes?

  1. Indexes disable transactions
  2. Indexes must be updated when relevant rows change
  3. Indexes prevent SELECT
  4. Indexes are always locked forever
Show correct answer and explanation

Correct answer: B. Indexes must be updated when relevant rows change

Maintaining index entries adds write overhead.

Question 99. What is an important benefit of a B-tree index?

  1. Always removing NULLs
  2. Avoiding all disk reads
  3. Supporting ordered range access
  4. Automatically normalizing tables
Show correct answer and explanation

Correct answer: C. Supporting ordered range access

B-trees efficiently support lookup and range retrieval.

Question 100. For which predicate is a hash index commonly useful?

  1. An arbitrary ordered range
  2. LIKE with any leading wildcard
  3. ORDER BY all columns
  4. Equality on an indexed key
Show correct answer and explanation

Correct answer: D. Equality on an indexed key

Hashing commonly accelerates exact-key matches.

Question 101. What is query selectivity?

  1. The fraction of records qualifying under a predicate
  2. The number of CPUs
  3. The length of the column name
  4. The query timestamp
Show correct answer and explanation

Correct answer: A. The fraction of records qualifying under a predicate

Selectivity indicates how strongly a predicate filters data.

Question 102. What is the job of a query optimizer?

  1. Choose a database owner
  2. Choose an execution plan
  3. Generate user passwords
  4. Change the application’s goals
Show correct answer and explanation

Correct answer: B. Choose an execution plan

The optimizer evaluates candidate plans using statistics and costs.

Question 103. What is a table scan?

  1. Deleting a relation
  2. Changing SQL types
  3. Examining table rows through a scan access path
  4. Adding a foreign key
Show correct answer and explanation

Correct answer: C. Examining table rows through a scan access path

A scan reads table data to evaluate conditions.

Question 104. What does a nested-loop join do?

  1. Encrypt every join key
  2. Merge only sorted data
  3. Build a B-tree always
  4. Match inner candidates for each outer row
Show correct answer and explanation

Correct answer: D. Match inner candidates for each outer row

Nested loops repeatedly search or examine one input for outer rows.

Question 105. How does a hash join commonly operate?

  1. Build a hash structure and probe it using the other input
  2. Use only sorted inputs
  3. Require every table to have one row
  4. Perform no comparison
Show correct answer and explanation

Correct answer: A. Build a hash structure and probe it using the other input

Hash joins are well suited to equijoin matching.

Question 106. What does sort-merge join exploit?

  1. Only random row order
  2. Sorted join-key order
  3. Automatic compression
  4. Filesystem inode numbers
Show correct answer and explanation

Correct answer: B. Sorted join-key order

Ordered inputs can be merged to find matching values.

Question 107. What is a query execution plan?

  1. A table backup
  2. A role assignment
  3. A sequence or tree of database operators chosen for a query
  4. A primary key definition only
Show correct answer and explanation

Correct answer: C. A sequence or tree of database operators chosen for a query

Plans specify scans, filters, joins, sorting and aggregation.

Question 108. What is optimistic concurrency control?

  1. Lock the entire database indefinitely
  2. Prohibit all reads
  3. Disable rollback
  4. Execute and later validate for conflicts
Show correct answer and explanation

Correct answer: D. Execute and later validate for conflicts

Optimistic schemes check conflicts before finalizing updates.

Question 109. What does MVCC stand for?

  1. Multiversion concurrency control
  2. Multiple virtual cache clusters
  3. Memory validation control code
  4. Metadata version checksum
Show correct answer and explanation

Correct answer: A. Multiversion concurrency control

MVCC retains data versions to support transaction visibility.

Question 110. What does serializable isolation guarantee?

  1. All transactions finish instantly
  2. Behavior equivalent to some serial transaction order
  3. Every query is read-only
  4. No transaction can abort
Show correct answer and explanation

Correct answer: B. Behavior equivalent to some serial transaction order

Serializable outcomes conform to an equivalent sequential execution.

Question 111. What is a lost update anomaly?

  1. A row contains NULL
  2. A backup fails
  3. A concurrent write overwrites another update improperly
  4. A SELECT returns no rows
Show correct answer and explanation

Correct answer: C. A concurrent write overwrites another update improperly

Uncoordinated concurrent writes can erase previous changes.

Question 112. What is a transaction schedule?

  1. A table schema
  2. A database username
  3. A physical disk layout
  4. An ordering or interleaving of transaction operations
Show correct answer and explanation

Correct answer: D. An ordering or interleaving of transaction operations

Schedules describe transaction operation sequences.

Question 113. What defines conflict serializability?

  1. Conflict equivalence to a serial schedule
  2. All queries run at once
  3. Only one row exists
  4. There are no SELECT statements
Show correct answer and explanation

Correct answer: A. Conflict equivalence to a serial schedule

Conflicting operations can be reordered to match some serial sequence.

Question 114. A cycle in a precedence graph proves a schedule is

  1. Always serial
  2. Not conflict serializable
  3. Guaranteed deadlock-free
  4. Read-only
Show correct answer and explanation

Correct answer: B. Not conflict serializable

A directed cycle prevents a topological serial transaction ordering.

Question 115. What does strict two-phase locking retain until commit or abort?

  1. Every shared lock necessarily in all definitions
  2. Only table names
  3. Exclusive locks
  4. Indexes but not locks
Show correct answer and explanation

Correct answer: C. Exclusive locks

Strict 2PL keeps write locks until transaction end.

Question 116. Which isolation level can permit dirty reads?

  1. SERIALIZABLE
  2. REPEATABLE READ
  3. SNAPSHOT
  4. READ UNCOMMITTED
Show correct answer and explanation

Correct answer: D. READ UNCOMMITTED

Read uncommitted may allow reading another transaction’s uncommitted data.

Question 117. What does a recovery checkpoint help reduce?

  1. The amount of log work needed during restart
  2. The number of SQL keywords
  3. The need for transaction logs entirely
  4. The size of every primary key
Show correct answer and explanation

Correct answer: A. The amount of log work needed during restart

Checkpoints record positions and state useful to restart recovery.

Question 118. What does REDO mean in database recovery?

  1. Reverse all committed work
  2. Reapply changes needed for durability
  3. Drop schemas
  4. Remove locks only
Show correct answer and explanation

Correct answer: B. Reapply changes needed for durability

Redo restores effects that should remain after failure.

Question 119. What does UNDO mean in recovery?

  1. Recreate all queries
  2. Replay only committed changes
  3. Reverse incomplete transaction effects
  4. Sort backup files
Show correct answer and explanation

Correct answer: C. Reverse incomplete transaction effects

Undo rolls back effects of transactions that did not commit.

Question 120. What does a differential backup usually capture?

  1. Changes only since the latest incremental backup
  2. Only database definitions
  3. Only transaction locks
  4. Changes since the latest full backup
Show correct answer and explanation

Correct answer: D. Changes since the latest full backup

Differential backup commonly uses the most recent full backup as its baseline.

Database Systems MCQs — Set 5 (Questions 121–160)

Read each question and reveal its answer and explanation.

Question 121. Which property of a transaction ensures all its operations succeed or none take effect?

  1. Atomicity
  2. Durability
  3. Availability
  4. Normalization
Show correct answer and explanation

Correct answer: A. Atomicity

Atomicity provides all-or-nothing transaction behavior.

Question 122. In two-phase commit, the coordinator initially asks participants to

  1. Discard every log
  2. Prepare to commit
  3. Delete schemas
  4. Release all data
Show correct answer and explanation

Correct answer: B. Prepare to commit

The prepare phase gathers votes from participants before deciding.

Question 123. The second phase of two-phase commit communicates the

  1. Table row count
  2. Index order
  3. Global commit or abort decision
  4. Query syntax
Show correct answer and explanation

Correct answer: C. Global commit or abort decision

Participants follow the coordinator’s final decision.

Question 124. A risk of two-phase commit is

  1. Guaranteed zero latency
  2. No transaction logging
  3. Permanent prevention of all deadlocks
  4. Blocking when the coordinator fails at a critical point
Show correct answer and explanation

Correct answer: D. Blocking when the coordinator fails at a critical point

Prepared participants can be forced to wait for the coordinator or recovery.

Question 125. A distributed transaction involves

  1. Operations across multiple participating resources or systems
  2. Exactly one SELECT statement
  3. Only a single index lookup
  4. One row in a local table
Show correct answer and explanation

Correct answer: A. Operations across multiple participating resources or systems

Distributed transactions coordinate updates across distinct participants.

Question 126. Sharding divides data primarily by

  1. Replicating every table everywhere
  2. Partitioning records across separate nodes or partitions
  3. Dropping the primary key
  4. Converting SQL into XML
Show correct answer and explanation

Correct answer: B. Partitioning records across separate nodes or partitions

Sharding distributes subsets of data.

Question 127. Horizontal partitioning divides a table by

  1. Columns
  2. SQL users
  3. Rows
  4. Index types
Show correct answer and explanation

Correct answer: C. Rows

Horizontal partitioning places different rows in different partitions.

Question 128. Vertical partitioning divides a table by

  1. Only row ranges
  2. Transactions
  3. CPU threads
  4. Columns or attribute groups
Show correct answer and explanation

Correct answer: D. Columns or attribute groups

Vertical partitioning groups different attributes, often retaining keys to reconstruct rows.

Question 129. Database replication means

  1. Maintaining copies of data on multiple nodes
  2. Splitting one row into attributes
  3. Deleting historical versions
  4. Disabling consistency checks
Show correct answer and explanation

Correct answer: A. Maintaining copies of data on multiple nodes

Replicas provide multiple copies for availability or read distribution.

Question 130. Synchronous replication generally requires

  1. No network communication
  2. Waiting for specified replica acknowledgments before completion
  3. Never persisting logs
  4. All replicas to be offline
Show correct answer and explanation

Correct answer: B. Waiting for specified replica acknowledgments before completion

A write may need acknowledgments from replica participants.

Question 131. Asynchronous replication may cause

  1. Guaranteed identical instantaneous replicas
  2. No possibility of failover
  3. Replication lag
  4. Automatic removal of conflicts
Show correct answer and explanation

Correct answer: C. Replication lag

Replica changes may arrive after a primary acknowledges a write.

Question 132. A database failover is

  1. Dropping all indexes
  2. Rewriting SQL as Java
  3. Deleting transaction logs
  4. Switching service to an alternate node after a failure
Show correct answer and explanation

Correct answer: D. Switching service to an alternate node after a failure

Failover aims to restore service using another available node.

Question 133. A quorum-based replication protocol generally requires

  1. A threshold number of participant acknowledgments or votes
  2. One local row only
  3. Every user to be an administrator
  4. No message exchange
Show correct answer and explanation

Correct answer: A. A threshold number of participant acknowledgments or votes

Quorums coordinate distributed decisions or reads and writes.

Question 134. Eventual consistency means

  1. Every read immediately sees all writes
  2. Replicas may converge after updates stop and communication proceeds
  3. No replica ever diverges
  4. All operations are serializable
Show correct answer and explanation

Correct answer: B. Replicas may converge after updates stop and communication proceeds

Convergence is expected after propagation under the relevant assumptions.

Question 135. Strong consistency is often discussed in terms of

  1. The length of table names
  2. File compression level
  3. How operations appear ordered and visible to clients
  4. Average row width
Show correct answer and explanation

Correct answer: C. How operations appear ordered and visible to clients

Consistency models govern visibility and ordering guarantees.

Question 136. Linearizability requires each operation to appear

  1. Delayed until midnight
  2. Before its invocation
  3. Independent of real-time order
  4. Atomic at a point between invocation and response
Show correct answer and explanation

Correct answer: D. Atomic at a point between invocation and response

Linearizable operations respect real-time precedence.

Question 137. CAP theorem concerns tradeoffs among

  1. Consistency, availability and partition tolerance
  2. Caching, authorization and performance
  3. Compression, audit and privacy
  4. Concurrency, access and parsing
Show correct answer and explanation

Correct answer: A. Consistency, availability and partition tolerance

With network partitions, a distributed system cannot guarantee both full availability and linearizable consistency.

Question 138. A network partition means

  1. A table loses its primary key
  2. Some nodes cannot communicate reliably with others
  3. A database uses a composite key
  4. A transaction contains two SQL statements
Show correct answer and explanation

Correct answer: B. Some nodes cannot communicate reliably with others

Connectivity failures split communicating groups.

Question 139. A document database commonly stores

  1. Only fixed-width tuples with no nesting
  2. Only files named document
  3. Flexible records such as JSON-like documents
  4. Only CPU registers
Show correct answer and explanation

Correct answer: C. Flexible records such as JSON-like documents

Document stores organize data around document-shaped records.

Question 140. A key-value store primarily retrieves data using

  1. Only relational joins
  2. Graph path queries
  3. A mandatory SQL schema
  4. A key mapped to an associated value
Show correct answer and explanation

Correct answer: D. A key mapped to an associated value

Key-value stores offer simple lookup and update by key.

Question 141. A graph database is particularly suitable for

  1. Traversing relationships between connected entities
  2. Only computing numeric averages
  3. Replacing every backup
  4. Sorting a single file
Show correct answer and explanation

Correct answer: A. Traversing relationships between connected entities

Graph databases model entities and edges for relationship queries.

Question 142. A wide-column database often organizes data as

  1. Only a single binary value
  2. Rows with flexible sets of columns grouped by families or similar structures
  3. A fixed two-column matrix always
  4. No keys
Show correct answer and explanation

Correct answer: B. Rows with flexible sets of columns grouped by families or similar structures

Wide-column systems can support sparse and distributed records.

Question 143. Denormalization is sometimes used to

  1. Guarantee BCNF
  2. Remove every data inconsistency risk
  3. Reduce read-time joins at the cost of redundancy
  4. Make all queries read-only
Show correct answer and explanation

Correct answer: C. Reduce read-time joins at the cost of redundancy

Duplicated data may improve some reads but complicate updates.

Question 144. When choosing between SQL and NoSQL stores, a key consideration is

  1. Only the name of the product
  2. Only disk color
  3. Avoiding all testing
  4. Application requirements for data model, transactions and scaling
Show correct answer and explanation

Correct answer: D. Application requirements for data model, transactions and scaling

No single database architecture is ideal for every workload.

Question 145. A schema migration is

  1. A controlled change to database structure
  2. A forced power outage
  3. A routine SELECT only
  4. A disk health report
Show correct answer and explanation

Correct answer: A. A controlled change to database structure

Migrations evolve tables and constraints while managing existing data.

Question 146. A backward-compatible schema change aims to

  1. Disable old queries immediately
  2. Keep existing consumers functioning during rollout
  3. Remove all old records
  4. Prevent software updates
Show correct answer and explanation

Correct answer: B. Keep existing consumers functioning during rollout

Compatibility facilitates gradual application deployments.

Question 147. An online schema change seeks to

  1. Guarantee no resource use
  2. Never touch metadata
  3. Modify schema with limited service interruption
  4. Require a full database rebuild every time
Show correct answer and explanation

Correct answer: C. Modify schema with limited service interruption

Online changes seek to minimize downtime.

Question 148. A data dictionary contains

  1. Only users’ passwords
  2. Only the newest rows
  3. The operating system’s bootloader
  4. Descriptions of tables, columns and other database metadata
Show correct answer and explanation

Correct answer: D. Descriptions of tables, columns and other database metadata

Catalogs describe database objects and definitions.

Question 149. Statistics maintained by a query optimizer help estimate

  1. Cardinalities and selectivities
  2. User identity
  3. Physical monitor resolution
  4. Password entropy only
Show correct answer and explanation

Correct answer: A. Cardinalities and selectivities

Statistics guide cost-based plan choices.

Question 150. Cardinality of a relation means

  1. Its number of databases
  2. Its number of tuples or rows
  3. The size of a password
  4. Its disk controller count
Show correct answer and explanation

Correct answer: B. Its number of tuples or rows

In relational algebra, cardinality is the relation’s row count.

Question 151. A covering index is one that

  1. Always covers every table
  2. Stores the transaction log
  3. Contains data needed to satisfy a query without fetching certain base rows
  4. Never needs maintenance
Show correct answer and explanation

Correct answer: C. Contains data needed to satisfy a query without fetching certain base rows

Included key and payload columns can make index-only access possible under DBMS rules.

Question 152. A composite index on (a,b) commonly helps queries filtering by

  1. Only b under every plan
  2. Neither a nor b
  3. A column absent from the index
  4. The leading key a and sometimes both a and b
Show correct answer and explanation

Correct answer: D. The leading key a and sometimes both a and b

Leading-key order often influences index access.

Question 153. The leftmost-prefix principle refers to

  1. How ordered composite indexes are efficiently searchable by initial key columns
  2. The leftmost SQL keyword
  3. The first table in memory
  4. The oldest transaction only
Show correct answer and explanation

Correct answer: A. How ordered composite indexes are efficiently searchable by initial key columns

An index ordered by (a,b) generally supports predicates using a as its prefix.

Question 154. An execution plan’s estimated row count may differ from actual because

  1. SQL prohibits counting rows
  2. Statistics and assumptions may be imperfect
  3. All indexes are corrupt
  4. The optimizer never samples data
Show correct answer and explanation

Correct answer: B. Statistics and assumptions may be imperfect

Cardinality estimation is predictive rather than exact.

Question 155. What is an index seek?

  1. Reading every row without an index
  2. Deleting an index
  3. Navigating an index toward matching key entries
  4. Rebuilding a log
Show correct answer and explanation

Correct answer: C. Navigating an index toward matching key entries

A seek uses searchable key conditions to access index regions.

Question 156. Which operation is an aggregation?

  1. Deleting a column
  2. Changing a password
  3. Creating an operating-system process
  4. Computing SUM of sales grouped by region
Show correct answer and explanation

Correct answer: D. Computing SUM of sales grouped by region

Aggregation summarizes groups or entire result sets.

Question 157. A window function differs from ordinary GROUP BY aggregation because it can

  1. Calculate over related rows while retaining individual output rows
  2. Only insert new rows
  3. Delete duplicate source data
  4. Prevent all sorting
Show correct answer and explanation

Correct answer: A. Calculate over related rows while retaining individual output rows

Window functions compute analytical values without necessarily collapsing rows.

Question 158. ROW_NUMBER() with an OVER clause typically

  1. Counts physical database files
  2. Assigns sequential row numbers within a defined window order
  3. Guarantees a permanent primary key
  4. Drops duplicate rows automatically
Show correct answer and explanation

Correct answer: B. Assigns sequential row numbers within a defined window order

ROW_NUMBER numbers rows according to the specified partition and ordering.

Question 159. PARTITION BY in a window specification

  1. Physically partitions every table
  2. Creates disk volumes
  3. Separates rows into groups for window calculation
  4. Commits a transaction
Show correct answer and explanation

Correct answer: C. Separates rows into groups for window calculation

Window partitions define independent calculation groups.

Question 160. A common table expression is introduced by

  1. HAVING
  2. VACUUM
  3. REVOKE
  4. WITH
Show correct answer and explanation

Correct answer: D. WITH

A WITH clause defines query-scoped named subqueries.

Database Systems MCQs — Set 6 (Questions 161–200)

Read each question and reveal its correct answer and explanation.

Question 161. A recursive common table expression is useful for

  1. Traversing hierarchies or repeatedly expanding results
  2. Encrypting every transaction
  3. Creating only physical partitions
  4. Setting CPU affinity
Show correct answer and explanation

Correct answer: A. Traversing hierarchies or repeatedly expanding results

Recursive CTEs repeatedly evaluate a recursive term until no additional results arise.

Question 162. What does COALESCE(a,b) return?

  1. Always b
  2. The first non-NULL argument among a and b
  3. Always a even when NULL
  4. The sum of a and b
Show correct answer and explanation

Correct answer: B. The first non-NULL argument among a and b

COALESCE selects the first non-NULL expression.

Question 163. NULLIF(a,b) returns NULL when

  1. a differs from b
  2. a is always positive
  3. a equals b under SQL comparison semantics
  4. b is a primary key
Show correct answer and explanation

Correct answer: C. a equals b under SQL comparison semantics

NULLIF yields NULL if the two arguments compare equal.

Question 164. Which SQL expression handles conditional values?

  1. DROP
  2. VACUUM
  3. REINDEX
  4. CASE
Show correct answer and explanation

Correct answer: D. CASE

CASE evaluates conditions and chooses corresponding result expressions.

Question 165. What is a stored procedure?

  1. A database-resident program or routine invoked to perform operations
  2. A physical disk sector
  3. A query result row
  4. A mandatory primary key
Show correct answer and explanation

Correct answer: A. A database-resident program or routine invoked to perform operations

Stored procedures encapsulate data-related program logic.

Question 166. A database trigger is typically

  1. A client-only display format
  2. A routine automatically invoked by specified database events
  3. A filesystem mount point
  4. A column data type
Show correct answer and explanation

Correct answer: B. A routine automatically invoked by specified database events

Triggers respond to events such as insertion or updates.

Question 167. An AFTER INSERT trigger usually runs

  1. Before the table exists
  2. Only at server boot
  3. After the initiating INSERT event under the DBMS trigger semantics
  4. Only when a SELECT executes
Show correct answer and explanation

Correct answer: C. After the initiating INSERT event under the DBMS trigger semantics

Trigger timing specifies when the routine executes relative to the data operation.

Question 168. Why should triggers be used carefully?

  1. They eliminate all transaction errors
  2. They are always faster than queries
  3. They make constraints unnecessary
  4. Their implicit side effects can complicate reasoning and performance
Show correct answer and explanation

Correct answer: D. Their implicit side effects can complicate reasoning and performance

Hidden execution and cascading actions can be difficult to trace.

Question 169. A cursor in database programming supports

  1. Iterating through a query result
  2. Storing only images
  3. Erasing all rows
  4. Creating OS threads
Show correct answer and explanation

Correct answer: A. Iterating through a query result

Cursors provide controlled traversal of results.

Question 170. Prepared statements help prevent SQL injection when

  1. User input is inserted into query strings
  2. Untrusted values are passed as bound parameters rather than concatenated into SQL text
  3. All input validation is skipped
  4. Database permissions are removed
Show correct answer and explanation

Correct answer: B. Untrusted values are passed as bound parameters rather than concatenated into SQL text

Parameter binding separates data values from SQL syntax.

Question 171. SQL injection exploits

  1. An excessive CPU temperature
  2. A slow B-tree
  3. Untrusted input interpreted as part of an SQL command
  4. A failed hard disk
Show correct answer and explanation

Correct answer: C. Untrusted input interpreted as part of an SQL command

String construction can allow attacker input to alter intended query structure.

Question 172. Which database privilege grants permission to read table rows?

  1. INSERT
  2. UPDATE
  3. DELETE
  4. SELECT
Show correct answer and explanation

Correct answer: D. SELECT

SELECT privilege authorizes reading according to access rules.

Question 173. The GRANT statement is used to

  1. Assign database privileges
  2. Remove every table
  3. Start a transaction
  4. Compute an average
Show correct answer and explanation

Correct answer: A. Assign database privileges

GRANT gives specified permissions to users or roles.

Question 174. The REVOKE statement is used to

  1. Add a primary key automatically
  2. Withdraw specified privileges
  3. Read transaction logs
  4. Create backups
Show correct answer and explanation

Correct answer: B. Withdraw specified privileges

REVOKE removes privilege grants subject to DBMS semantics.

Question 175. A role in database access control is

  1. A database page size
  2. A join type
  3. A named collection or principal for permissions
  4. A SQL value type
Show correct answer and explanation

Correct answer: C. A named collection or principal for permissions

Roles simplify permission administration.

Question 176. Row-level security is used to

  1. Change all data types
  2. Eliminate all indexes
  3. Automatically compress text
  4. Restrict which rows a user or role can see or modify
Show correct answer and explanation

Correct answer: D. Restrict which rows a user or role can see or modify

Policies can filter accessible records according to identity.

Question 177. Encryption at rest protects

  1. Stored data against certain unauthorized storage access
  2. Every query from logical errors
  3. All application accounts automatically
  4. Availability after power failure
Show correct answer and explanation

Correct answer: A. Stored data against certain unauthorized storage access

At-rest encryption helps protect persisted bytes.

Question 178. Encryption in transit typically protects

  1. Only database backups
  2. Data sent between clients and servers over networks
  3. CPU caches
  4. Row order
Show correct answer and explanation

Correct answer: B. Data sent between clients and servers over networks

Transport encryption secures communication against eavesdropping and tampering.

Question 179. What is database auditing?

  1. Sorting every result
  2. Erasing transaction logs
  3. Recording and reviewing selected database activities
  4. Changing query syntax
Show correct answer and explanation

Correct answer: C. Recording and reviewing selected database activities

Audit trails support accountability and incident investigations.

Question 180. A SQL transaction’s savepoint allows

  1. Permanent commit of only one row outside a transaction
  2. Automatic replication to all servers
  3. Removal of every constraint
  4. Partial rollback to a named point within the current transaction
Show correct answer and explanation

Correct answer: D. Partial rollback to a named point within the current transaction

SAVEPOINT marks a recoverable position for transaction rollback.

Question 181. ROLLBACK TO SAVEPOINT generally

  1. Undoes changes after the named savepoint while keeping the transaction active
  2. Drops all tables
  3. Commits the entire transaction
  4. Disables logging
Show correct answer and explanation

Correct answer: A. Undoes changes after the named savepoint while keeping the transaction active

It reverts later transactional work without necessarily ending the transaction.

Question 182. A read replica is commonly used to

  1. Replace primary-key constraints
  2. Serve selected reads using replicated data
  3. Run only schema migrations
  4. Prevent all stale reads
Show correct answer and explanation

Correct answer: B. Serve selected reads using replicated data

Read replicas can spread read workloads, subject to replication lag.

Question 183. Point-in-time recovery aims to

  1. Always restore only the oldest backup
  2. Delete logs permanently
  3. Restore a database to a chosen recoverable instant
  4. Remove every user account
Show correct answer and explanation

Correct answer: C. Restore a database to a chosen recoverable instant

Backups and logs can reconstruct state at a selected time.

Question 184. RPO stands for

  1. Read Performance Option
  2. Relational Partition Order
  3. Replica Processing Output
  4. Recovery Point Objective
Show correct answer and explanation

Correct answer: D. Recovery Point Objective

RPO specifies acceptable potential data loss measured in time.

Question 185. RTO stands for

  1. Recovery Time Objective
  2. Row Transaction Order
  3. Relational Table Offset
  4. Recovery Transfer Option
Show correct answer and explanation

Correct answer: A. Recovery Time Objective

RTO specifies the target duration to restore service.

Question 186. A lower RPO target generally requires

  1. Less attention to backups
  2. More frequent or continuous protection of data changes
  3. No transaction logs
  4. Fewer restore tests
Show correct answer and explanation

Correct answer: B. More frequent or continuous protection of data changes

Less acceptable data loss requires more up-to-date recoverable data.

Question 187. Backups stored offsite can help protect against

  1. Only SQL syntax errors
  2. Only slow index scans
  3. A site-wide loss affecting local systems
  4. Only missing aliases
Show correct answer and explanation

Correct answer: C. A site-wide loss affecting local systems

Geographically separate copies reduce correlated-loss risk.

Question 188. A database connection pool helps

  1. Eliminate authentication entirely
  2. Guarantee no deadlocks
  3. Store query results permanently
  4. Reuse established database connections for multiple application requests
Show correct answer and explanation

Correct answer: D. Reuse established database connections for multiple application requests

Pooling reduces repeated connection setup costs.

Question 189. Connection leaks can cause

  1. Exhaustion of available database connections
  2. Automatic data normalization
  3. Guaranteed index efficiency
  4. A reduction in server load always
Show correct answer and explanation

Correct answer: A. Exhaustion of available database connections

Connections not returned to pools can exhaust capacity.

Question 190. A hot spot in a sharded database occurs when

  1. All shards have equal workload
  2. A small subset of partitions receives disproportionately heavy traffic
  3. No node has data
  4. Only a backup is running
Show correct answer and explanation

Correct answer: B. A small subset of partitions receives disproportionately heavy traffic

Skewed partition keys can overload particular shards.

Question 191. A poor shard key can cause

  1. Automatic BCNF
  2. Perfect replica consistency
  3. Uneven data distribution and query load
  4. Removal of network traffic
Show correct answer and explanation

Correct answer: C. Uneven data distribution and query load

Shard selection strongly affects balance and access patterns.

Question 192. Consistent hashing is useful for

  1. Guaranteeing transaction serializability
  2. Replacing SQL joins
  3. Encrypting all values
  4. Distributing keys while limiting reassignment when nodes change
Show correct answer and explanation

Correct answer: D. Distributing keys while limiting reassignment when nodes change

Consistent hashing reduces the proportion of remapped keys under changes.

Question 193. Which workload is characterized by many short transactional operations?

  1. OLTP
  2. OLAP
  3. Offline archiving only
  4. Batch file compression only
Show correct answer and explanation

Correct answer: A. OLTP

Online transaction processing emphasizes frequent small reads and writes.

Question 194. Which workload typically emphasizes analytical scans and aggregations?

  1. OLTP only
  2. OLAP
  3. Device-driver scheduling
  4. Socket polling
Show correct answer and explanation

Correct answer: B. OLAP

Online analytical processing studies large datasets across dimensions.

Question 195. A data warehouse is primarily designed for

  1. Running only kernel interrupts
  2. Storing no historical information
  3. Integrating data for analytical queries and reporting
  4. Replacing all source applications
Show correct answer and explanation

Correct answer: C. Integrating data for analytical queries and reporting

Warehouses commonly support decision-making analytics.

Question 196. A star schema in data warehousing usually has

  1. Only one table with no keys
  2. A cyclic inheritance hierarchy
  3. Only graph edges
  4. A central fact table linked to dimension tables
Show correct answer and explanation

Correct answer: D. A central fact table linked to dimension tables

Star schemas organize measures and descriptive dimensions.

Question 197. A fact table in a star schema usually stores

  1. Measurements and keys to relevant dimensions
  2. Only user passwords
  3. Only procedure source code
  4. Only indexes
Show correct answer and explanation

Correct answer: A. Measurements and keys to relevant dimensions

Facts capture events and quantitative measures for analysis.

Question 198. A dimension table generally stores

  1. Only transaction log records
  2. Descriptive context such as product or date attributes
  3. Physical disk tracks
  4. Uncommitted row versions only
Show correct answer and explanation

Correct answer: B. Descriptive context such as product or date attributes

Dimensions describe entities used to slice and filter facts.

Question 199. ETL stands for

  1. Encrypt, Transfer, Lock
  2. Evaluate, Test, Log
  3. Extract, Transform, Load
  4. Export, Trigger, Link
Show correct answer and explanation

Correct answer: C. Extract, Transform, Load

ETL processes source data before loading it into a target system.

Question 200. ELT differs from ETL because transformation typically occurs

  1. Before extraction starts
  2. Only on a client keyboard
  3. Without any storage
  4. After loading source data into a target platform
Show correct answer and explanation

Correct answer: D. After loading source data into a target platform

ELT loads first and uses target compute for transformations.

Privacy Policy
Powered by CookieAdmin