141. What is the primary advantage of an in-memory database?
- a) Faster data access by eliminating disk I/O
- b) Lower hardware costs
- c) Unlimited storage capacity
- d) Better compression than disk-based databases
Answer: A - In-memory DBs (e.g., Redis, SAP HANA) store data in RAM for microsecond latency but are constrained by memory size.
142. What is a column-family store?
- a) A NoSQL database that groups columns into families for efficient access
- b) A relational database with extra columns
- c) A backup of column definitions
- d) A type of data warehouse
Answer: A - Column-family stores (e.g., Cassandra, HBase) organize data by column families rather than rows.
143. Which of the following is NOT a characteristic of in-memory databases?
- a) Persistent storage to disk
- b) ACID compliance
- c) Unlimited data capacity
- d) Optimized for RAM access patterns
Answer: C - In-memory DBs are limited by available RAM (though some use disk as overflow).
144. What is the purpose of a bloom filter in databases?
- a) To test whether an element is possibly in a set or definitely not
- b) To filter out duplicate rows
- c) To compress data
- d) To create visualizations
Answer: A - Bloom filters are space-efficient probabilistic structures (may have false positives but no false negatives).
145. What is database virtualization?
a) Abstracting physical database resources to present logical databases
b) Creating virtual tables
c) Simulating database behavior
d) A backup technique
Answer: A - Virtualization enables multi-tenancy and resource pooling (e.g., Azure SQL Database Hyperscale).
146. What is a compensating transaction pattern?
- a) A transaction that undoes the effects of a previous transaction
- b) A transaction that runs in parallel
- c) A transaction that compensates for system delays
- d) A transaction that adjusts system clocks
Answer: A - Compensating transactions implement "undo" logic in eventually consistent systems.
147. What is the TPC-C benchmark used for?
- a) Measuring OLTP system performance
- b) Testing database compression
- c) Benchmarking cloud databases
- d) Evaluating backup speeds
Answer: A - TPC-C simulates a complete computing environment for order-entry systems.
148. What is the purpose of the SQL MEMORY_OPTIMIZED table type?
- a) To store tables in memory with optimized access patterns
- b) To cache frequently used tables
- c) To create temporary in-memory tables
- d) To optimize memory allocation
Answer: A - Memory-optimized tables (e.g., in SQL Server) use latch-free structures for high concurrency.
149. What is a wide-column store?
- a) A NoSQL database that uses column families with variable columns per row
- b) A relational table with many columns
- c) A data warehouse fact table
- d) A type of index
Answer: A - Wide-column stores (e.g., Cassandra) allow different columns for each row key.
150. What is the purpose of the SQL SEQUENCE object?
- a) To generate ordered numeric values
- b) To store DNA sequences
- c) To track query execution order
- d) To implement queues
Answer: A - SEQUENCE objects provide non-repeating numbers (often for surrogate keys) with better performance than IDENTITY.
151. What is database partitioning?
- a) Dividing large tables into smaller, more manageable pieces
- b) Creating database backups
- c) Splitting databases across geographic regions
- d) A security technique
Answer: A - Partitioning can be horizontal (by rows) or vertical (by columns).
152. What is the purpose of the SQL COLUMNSTORE index?
- a) To optimize analytical queries on large datasets
- b) To store columns in alphabetical order
- c) To encrypt column data
- d) To implement full-text search
Answer: A - Columnstore indexes (used in data warehouses) process columns in batches rather than rows.
153. What is a database container?
- a) An isolated runtime environment for database instances
- b) A storage unit for BLOBs
- c) A type of backup file
- d) A physical storage device
Answer: A - Containerization (e.g., Docker) packages databases with dependencies for portability.
154. What is the difference between horizontal and vertical scaling in databases?
- a) Horizontal adds more servers, vertical upgrades existing server resources
- b) Horizontal is for NoSQL, vertical is for SQL
- c) Horizontal partitions tables, vertical partitions columns
- d) Both A and C
Answer: D - Horizontal scaling (scale-out) is typically more flexible but complex than vertical (scale-up).
155. What is the purpose of the SQL OPTIMIZE FOR UNKNOWN hint?
- a) To generate a generic plan when parameter values are unpredictable
- b) To hide query optimization details
- c) To bypass the query optimizer
- d) To encrypt query parameters
Answer: A - This hint prevents parameter sniffing issues with highly variable inputs.
156. What is a database cartridge?
- a) A plug-in extending database functionality for specific domains
- b) A backup storage format
- c) A type of in-memory database
- d) A hardware component
Answer: A - Cartridges (in Oracle) add capabilities like spatial processing or text search.
157. What is the purpose of the SQL RESULT SET CACHING?
- a) To store query results for faster subsequent executions
- b) To cache database connections
- c) To buffer write operations
- d) To implement materialized views
Answer: A - Result set caching (e.g., in Snowflake) improves performance for repeated identical queries.
158. What is database federation?
- a) A collection of autonomous databases presented as a single system
- b) A type of replication
- c) A security federation protocol
- d) A backup strategy
Answer: A - Federated databases integrate multiple independent databases through a virtual layer.
159. What is the purpose of the SQL MEMORY_OPTIMIZED_FILEGROUP?
- a) To configure storage for memory-optimized tables
- b) To cache file operations
- c) To optimize memory allocation
- d) To store temporary files
Answer: A - This filegroup stores checkpoint files for durable memory-optimized tables.
160. What is a database accelerator?
- a) Specialized hardware or software to speed up specific operations
- b) A query optimization tool
- c) A type of index
- d) A backup compression technique
Answer: A - Accelerators include FPGA chips, GPU processing, or specialized query engines.