81. What is a snowflake schema in data warehousing?
- a) A normalized version of star schema with dimension tables split into sub-dimensions
- b) A backup strategy with multiple copies
- c) A type of distributed database
- d) A graphical representation of table relationships
Answer: A - Snowflake schemas normalize dimension tables to reduce redundancy at the cost of query complexity.
82. What is the purpose of the SQL CUBE operator?
- a) To generate all possible grouping sets for multiple dimensions
- b) To encrypt data in 3D format
- c) To create cube-shaped data structures
- d) To visualize query results
Answer: A - CUBE produces subtotals for all combinations of specified columns (n-dimensional aggregation).
83. Which of the following is NOT a type of database backup?
- a) Full backup
- b) Incremental backup
- c) Differential backup
- d) Partial backup
Answer: D - Standard backup types are full, incremental, and differential (partial backup isn't a formal category).
84. What is a bitmap index best suited for?
- a) Columns with low cardinality (few distinct values)
- b) Primary key columns
- c) Text-heavy columns
- d) Foreign key columns
Answer: A - Bitmap indexes efficiently represent boolean values (e.g., gender, status flags) but perform poorly on high-cardinality data.
85. What is database replication lag?
- a) The delay between a write on the primary and its application on replicas
- b) The time taken to create a replica
- c) A performance bottleneck in distributed systems
- d) A type of deadlock
Answer: A - Replication lag causes temporary inconsistency between primary and replica nodes.
86. What is the purpose of the SQL LATERAL join?
- a) To reference columns from preceding tables in a subquery
- b) To join tables side-by-side
- c) To create horizontal partitions
- d) To implement geospatial queries
Answer: A - LATERAL allows subqueries to reference columns from tables earlier in the FROM clause (like a foreach loop).
87. What is a fact table in data warehousing?
- a) A table containing measurable quantitative data
- b) A metadata table
- c) A temporary calculation table
- d) A table with primary business entities
Answer: A - Fact tables store metrics (e.g., sales amount) linked to dimension tables (e.g., product, time).
88. What is role-based access control (RBAC) in databases?
- a) Assigning permissions to roles rather than individual users
- b) A backup access method
- c) A type of database encryption
- d) A query optimization technique
Answer: A - RBAC simplifies security management by grouping permissions (e.g., "admin", "analyst" roles).
89. What is the purpose of the SQL PARTITION BY clause?
- a) To divide result sets into partitions for window functions
- b) To create physical table partitions
- c) To distribute data across servers
- d) To split columns into multiple parts
Answer: A - PARTITION BY in window functions groups rows without collapsing results (unlike GROUP BY).
90. What is database encryption at rest?
- a) Encrypting stored data to prevent unauthorized access
- b) Encrypting network traffic
- c) Compressing database files
- d) A backup encryption method
Answer: A - Encryption at rest protects data if physical storage media is compromised.
91. What is the difference between HAVING and WHERE clauses?
- a) WHERE filters rows before grouping, HAVING filters after grouping
- b) WHERE works with aggregates, HAVING doesn't
- c) HAVING is faster than WHERE
- d) There is no functional difference
Answer: A - WHERE operates on individual rows, HAVING on grouped results.
92. What is a database connection pool?
- a) A cache of database connections for reuse
- b) A group of linked databases
- c) A backup connection strategy
- d) A type of distributed database
Answer: A - Connection pools reduce overhead by reusing existing connections rather than creating new ones.
93. What is the purpose of the SQL FIRST_VALUE() function?
- a) To return the first value in an ordered partition
- b) To find the earliest date
- c) To validate the first row
- d) To reset sequence numbers
Answer: A - FIRST_VALUE() is a window function that accesses data from the first row of the window frame.
94. What is eventual consistency in distributed databases?
- a) A model where all nodes will become consistent given enough time
- b) A guarantee that all replicas are always in sync
- c) A method to resolve conflicts immediately
- d) A backup consistency check
Answer: A - Eventual consistency prioritizes availability over immediate consistency (used in systems like DynamoDB).
95. What is the purpose of the SQL TABLESAMPLE clause?
- a) To retrieve a random sample of rows
- b) To create sample tables
- c) To test table structures
- d) To validate data quality
Answer: A - TABLESAMPLE is useful for approximate queries on large datasets (e.g., BERNOULLI or SYSTEM sampling).
96. What is a database savepoint?
- a) A point within a transaction that can be rolled back to
- b) A backup checkpoint
- c) A security access point
- d) A performance benchmark
Answer: A - Savepoints allow partial rollback within large transactions.
97. What is the purpose of the SQL LISTAGG function?
- a) To concatenate values from multiple rows into a single string
- b) To list all tables in a schema
- c) To aggregate list data types
- d) To generate numbered lists
Answer: A - LISTAGG (Oracle) or STRING_AGG (SQL Server) creates comma-separated lists from grouped data.
98. What is a 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.
99. What is the purpose of the SQL RANK() function?
- a) To assign a unique rank to each row with gaps for ties
- b) To categorize data
- c) To rate query performance
- d) To prioritize transactions
Answer: A - RANK() leaves gaps when values tie (e.g., 1, 2, 2, 4), unlike DENSE_RANK().
100. What is a database write-ahead log (WAL)?
- a) A log where changes are recorded before being applied to data files
- b) A backup transaction log
- c) A performance optimization technique
- d) A security audit log
Answer: A - WAL ensures durability by logging changes before committing them to disk.