61. What is Boyce-Codd Normal Form (BCNF)?
- a) A stricter version of 3NF where every determinant must be a superkey
- b) A normalization form that allows partial dependencies
- c) A special case of 2NF for binary relationships
- d) A denormalization technique
Answer: A - BCNF eliminates all non-trivial functional dependencies where the determinant is not a superkey.
62. What is query optimization in DBMS?
- a) The process of selecting the most efficient execution plan for a query
- b) Rewriting queries to use less storage
- c) Compressing database tables
- d) Indexing all columns in a table
Answer: A - The query optimizer evaluates multiple execution plans and chooses the one with lowest cost.
63. Which of the following is NOT a join algorithm?
- a) Nested-loop join
- b) Hash join
- c) Merge join
- d) Index join
Answer: D - Common join algorithms are nested-loop, hash, and merge joins.
64. What is a covering index?
- a) An index that includes all columns needed for a query
- b) An index that spans multiple tables
- c) A backup index
- d) An index with encrypted values
Answer: A - Covering indexes eliminate table access by storing all required data in the index itself.
65. What is the purpose of the SQL WINDOW function?
- a) To perform calculations across a set of rows related to the current row
- b) To display data in a graphical window
- c) To limit query results to a time window
- d) To create temporary tables
Answer: A - Window functions (e.g., ROW_NUMBER(), RANK()) operate on a "window" of rows without collapsing results.
66. What is multiversion concurrency control (MVCC)?
- a) A technique to maintain multiple versions of data for concurrent transactions
- b) A backup strategy with versioning
- c) A method to merge database copies
- d) A way to encrypt historical data
Answer: A - MVCC allows readers to access snapshot data without blocking writers (used in PostgreSQL, Oracle).
67. What is the difference between OLTP and OLAP systems?
- a) OLTP handles transactional queries, OLAP handles analytical queries
- b) OLTP is for NoSQL, OLAP is for SQL
- c) OLTP uses denormalized data, OLAP uses normalized data
- d) OLTP is read-only, OLAP is write-heavy
Answer: A - OLTP (Online Transaction Processing) vs OLAP (Online Analytical Processing) have different optimization goals.
68. What is a database checkpoint?
- a) A point where all dirty pages are written to disk
- b) A backup of the transaction log
- c) A validation step for user permissions
- d) A snapshot of database statistics
Answer: A - Checkpoints reduce recovery time by periodically flushing buffers to disk.
69. What is the purpose of the SQL PIVOT operator?
- a) To transform rows into columns
- b) To rotate table structures
- c) To create summary tables
- d) To encrypt sensitive data
Answer: A - PIVOT converts unique values from one column into multiple columns (cross-tabulation).
70. What is Two-Phase Locking (2PL)?
- a) A concurrency control protocol with growing and shrinking phases
- b) A method to lock entire databases
- c) A backup strategy with two copies
- d) A two-step authentication process
Answer: A - 2PL ensures serializability by acquiring all locks before releasing any.
71. What is the purpose of the SQL EXCEPT operator?
- a) To return distinct rows from the first query that aren't in the second
- b) To handle exceptions in queries
- c) To exclude specific columns
- d) To skip error records
Answer: A - EXCEPT performs set difference (equivalent to MINUS in some databases).
72. What is a database schema?
- a) The logical structure/organization of a database
- b) A graphical representation of tables
- c) A backup of table definitions
- d) A user permission template
Answer: A - A schema defines tables, relationships, constraints, and other database objects.
73. What is the purpose of the SQL INTERSECT operator?
- a) To return only rows common to both queries
- b) To find overlapping date ranges
- c) To merge tables horizontally
- d) To create composite keys
Answer: A - INTERSECT returns only rows that appear in both result sets.
74. What is database fragmentation?
- a) Dividing a database into smaller parts for performance or management
- b) Corrupted data storage
- c) A security vulnerability
- d) A backup technique
Answer: A - Fragmentation can be horizontal (rows) or vertical (columns).
75. What is the purpose of the SQL OFFSET-FETCH clause?
- a) To implement pagination by skipping and limiting rows
- b) To handle out-of-bounds errors
- c) To fetch data from remote servers
- d) To bypass corrupted records
Answer: A - OFFSET skips N rows, FETCH returns the next M rows (e.g., for "Page 2 of results").
76. What is a phantom read in transaction isolation?
- a) When a transaction sees new rows inserted by another transaction
- b) Reading uncommitted data
- c) A corrupted read operation
- d) A security breach
Answer: A - Phantom reads occur when a transaction re-executes a query and sees newly inserted rows.
77. What is the purpose of the SQL MERGE statement?
- a) To perform INSERT, UPDATE, or DELETE operations in a single statement
- b) To combine two databases
- c) To merge table structures
- d) To resolve data conflicts
Answer: A - MERGE (UPSERT) handles "insert or update" scenarios efficiently.
78. What is a star schema in data warehousing?
- a) A design with one fact table connected to dimension tables
- b) A database backup strategy
- c) A type of index structure
- d) A distributed database topology
Answer: A - Star schemas optimize OLAP queries with denormalized dimensions.
79. What is the purpose of the SQL ROLLUP operator?
- a) To generate subtotals and grand totals in result sets
- b) To undo transactions
- c) To compress data
- d) To rotate tables
Answer: A - ROLLUP creates hierarchical aggregates (e.g., totals by year > month > day).
80. What is a database transaction log?
- a) A sequential record of all database modifications
- b) A user activity audit trail
- c) A backup of SQL commands
- d) A performance monitoring tool
Answer: A - The transaction log enables recovery and supports ACID properties.