121. What is the difference between FULL and BULK_LOGGED recovery models in SQL Server?
- a) FULL logs all operations, BULK_LOGGED minimally logs bulk operations
- b) FULL requires more storage, BULK_LOGGED compresses logs
- c) FULL allows point-in-time recovery, BULK_LOGGED doesn't
- d) Both A and C
Answer: D - BULK_LOGGED reduces log size for bulk operations but limits recovery options.
122. What is a partial index?
- a) An index that includes only a subset of rows meeting a condition
- b) An incomplete index being built
- c) An index covering only some columns
- d) A temporary index for query optimization
Answer: A - Partial indexes (e.g., CREATE INDEX ... WHERE status = 'active') reduce size and maintenance overhead.
123. Which of the following is a characteristic of Database as a Service (DBaaS)?
- a) Automatic scaling and maintenance handled by the provider
- b) Higher upfront costs than on-premises databases
- c) Limited to only open-source databases
- d) Requires in-house DBA expertise
Answer: A - DBaaS (e.g., AWS RDS, Azure SQL Database) offers managed database services with pay-as-you-go pricing.
124. What is blockchain database technology best suited for?
- a) Decentralized, tamper-evident record-keeping
- b) High-frequency transactional systems
- c) Complex analytical queries
- d) Temporary data storage
Answer: A - Blockchain provides immutable, distributed ledgers but has performance limitations.
125. What is the purpose of the SQL ST_Geometry data type?
- a) To store spatial data like points, lines, and polygons
- b) To implement geometric progressions
- c) To model graph structures
- d) To encrypt geometric patterns
Answer: A - ST_Geometry is part of SQL's spatial extension (ISO/IEC 13249-3 SQL/MM).
126. What is a GiST index?
- a) A generalized search tree index for complex data types
- b) A special index for GIS systems only
- c) A temporary in-memory index
- d) A compressed bitmap index
Answer: A - Generalized Search Tree (GiST) indexes support spatial, full-text, and other non-scalar data in PostgreSQL.
127. What is the difference between hot and cold backups?
- a) Hot backups occur while the database is running, cold backups require downtime
- b) Hot backups are faster, cold backups are slower
- c) Hot backups use compression, cold backups don't
- d) There is no technical difference
Answer: A - Hot backups enable continuous availability but may require additional log management.
128. What is the purpose of the SQL SPATIAL_INDEX?
- a) To optimize queries on spatial data columns
- b) To index multiple spaces in a database
- c) To create spatial partitions
- d) To map database storage physically
Answer: A - Spatial indexes accelerate operations like "find all points within 5km of X".
129. Which of the following is NOT a feature of NewSQL databases?
- a) ACID compliance
- b) Horizontal scalability
- c) Schema-less design
- d) SQL interface
Answer: C - NewSQL (e.g., CockroachDB, Google Spanner) combines SQL and scalability but retains schemas.
130. What is the purpose of the SQL TABLESPACE?
- a) To logically group database objects for storage management
- b) To create temporary workspaces
- c) To reserve space for future tables
- d) To define spatial boundaries
Answer: A - Tablespaces allow control over physical storage locations (e.g., fast SSD vs. slow HDD).
131. What is database sharding?
- a) Horizontal partitioning of data across multiple servers
- b) Vertical splitting of tables
- c) A backup technique
- d) A security protocol
Answer: A - Sharding distributes data based on a shard key (e.g., user_id ranges) to scale writes.
132. What is the purpose of the SQL ST_Distance function?
- a) To calculate the distance between two spatial objects
- b) To measure query execution distance
- c) To find distant backups
- d) To implement outlier detection
Answer: A - ST_Distance computes metric or degree-based distances (e.g., between GPS coordinates).
133. What is a database restore point?
- a) A named marker for recovery to a specific state
- b) A physical storage location
- c) A backup verification checkpoint
- d) A security access point
Answer: A - Restore points (CREATE RESTORE POINT) enable recovery without full backups.
134. What is the difference between logical and physical backups?
- a) Logical backups export data as SQL statements, physical backups copy binary files
- b) Logical backups are faster, physical backups are slower
- c) Logical backups require more storage
- d) There is no difference
Answer: A - Logical backups (e.g., pg_dump) are portable but slower than physical file copies.
135. What is the purpose of the SQL BRIN index?
- a) To index large tables by storing min/max values per block
- b) To create brief indexes
- c) To implement binary retrieval
- d) To speed up joins
Answer: A - Block Range Indexes (BRIN) are space-efficient for large, naturally ordered data.
136. What is the CAP theorem in distributed databases?
- a) A database can only guarantee two of: Consistency, Availability, Partition Tolerance
- b) A theory about database capacity planning
- c) A rule for columnar data storage
- d) A principle for atomic transactions
Answer: A - CAP theorem states that distributed systems can't simultaneously guarantee all three properties.
137. What is the purpose of the SQL ST_Contains function?
- a) To test if one geometry contains another
- b) To check if a column contains a value
- c) To validate data integrity
- d) To implement full-text search
Answer: A - ST_Contains returns TRUE if geometry A completely encloses geometry B.
138. What is database mirroring?
- a) Maintaining a standby copy of a database for failover
- b) A backup reflection technique
- c) A security encryption method
- d) A visualization approach
Answer: A - Mirroring keeps a secondary database synchronized with the primary (now largely replaced by Always On in SQL Server).
139. What is the purpose of the SQL ST_Intersects function?
- a) To test if two geometries share any space
- b) To find intersecting queries
- c) To detect index collisions
- d) To merge spatial datasets
Answer: A - ST_Intersects is fundamental for spatial queries (e.g., "find roads crossing a flood zone").
140. What is a database sandbox?
- a) An isolated environment for testing changes
- b) A backup storage area
- c) A type of NoSQL database
- d) A security quarantine zone
Answer: A - Sandboxes allow experimentation without affecting production data.