61. Which join returns the Cartesian product of tables?
- a) CROSS JOIN
- b) NATURAL JOIN
- c) SELF JOIN
- d) EQUI JOIN
Answer: A - CROSS JOIN combines every row from the first table with every row from the second table.
62. What is a recursive CTE used for?
- a) Processing hierarchical data
- b) Repeating queries
- c) Creating backups
- d) Validating constraints
Answer: A - Recursive CTEs traverse tree structures (e.g., organizational charts).
63. Which SQL function extracts JSON values?
- a) JSON_VALUE()
- b) JSON_EXTRACT()
- c) JSON_QUERY()
- d) All of the above
Answer: D - Syntax varies by DBMS (SQL Server: JSON_VALUE, MySQL: JSON_EXTRACT).
64. What is database replication?
- a) Copying data to multiple servers
- b) Compressing tables
- c) Version control for databases
- d) Encrypting backups
Answer: A - Replication improves availability and read performance.
65. Which partitioning strategy divides data by ranges?
- a) RANGE partitioning
- b) LIST partitioning
- c) HASH partitioning
- d) KEY partitioning
Answer: A - Range partitioning groups data by value intervals (e.g., dates).
66. What does the SQL PIVOT operator do?
- a) Rotates rows into columns
- b) Compresses data
- c) Encrypts results
- d) Sorts hierarchically
Answer: A - PIVOT transforms unique row values into multiple columns.
67. Which isolation level prevents phantom reads?
- a) SERIALIZABLE
- b) READ COMMITTED
- c) READ UNCOMMITTED
- d) REPEATABLE READ
Answer: A - SERIALIZABLE locks ranges to prevent new rows matching WHERE conditions.
68. What is the purpose of the SQL ROLLUP operator?
- a) Generates subtotals and grand totals
- b) Reverts transactions
- c) Compresses logs
- d) Rotates tables
Answer: A - ROLLUP creates hierarchical aggregations in GROUP BY.
69. Which constraint ensures values match a pattern?
- a) CHECK
- b) VALIDATE
- c) PATTERN
- d) REGEX
Answer: A - CHECK (column REGEXP 'pattern') enforces data validation.
70. What is the purpose of the SQL CUBE operator?
- a) Produces all possible grouping sets
- b) Encrypts data
- c) Creates 3D visualizations
- d) Compresses backups
Answer: A - CUBE generates aggregates for all column combinations.
71. Which statement creates a database trigger?
- a) CREATE TRIGGER
- b) MAKE TRIGGER
- c) ADD TRIGGER
- d) DEFINE TRIGGER
Answer: A - Triggers automatically execute on specified DML events.
72. What does the SQL UNPIVOT operator do?
- a) Rotates columns into rows
- b) Decompresses data
- c) Decrypts results
- d) Flattens hierarchies
Answer: A - UNPIVOT is the inverse of PIVOT operations.
73. Which database object stores compiled SQL plans?
- a) Stored procedure
- b) View
- c) Materialized view
- d) Trigger
Answer: A - Stored procedures improve performance through plan reuse.
74. What is the purpose of the SQL LEAD() function?
- a) Accesses subsequent row's data
- b) Jumps to first row
- c) Skips NULL values
- d) Executes first in transactions
Answer: A - LEAD(column, offset) looks ahead in the result set.
75. Which NoSQL database is column-family based?
- a) Cassandra
- b) MongoDB
- c) Redis
- d) Neo4j
Answer: A - Cassandra organizes data into column families (wide-column store).
76. What is the purpose of the SQL INTERSECT operator?
- a) Returns common rows from both queries
- b) Combines all rows
- c) Filters duplicates
- d) Merges tables
Answer: A - INTERSECT finds rows present in both result sets.
77. Which database model uses graph structures?
- a) Neo4j
- b) MongoDB
- c) Cassandra
- d) Redis
Answer: A - Neo4j specializes in graph traversals (nodes/edges).
78. What is the Boyce-Codd Normal Form (BCNF)?
- a) Stricter version of 3NF
- b) Alternative to 1NF
- c) Special case of 2NF
- d) Denormalization technique
Answer: A - BCNF requires that every determinant be a candidate key.
79. Which SQL feature handles time-series data?
- a) Temporal tables
- b) Cursors
- c) Views
- d) Triggers
Answer: A - Temporal tables automatically track historical data changes.
80. What is the purpose of the SQL TABLESAMPLE clause?
- a) Retrieves random data sample
- b) Compresses tables
- c) Backs up subsets
- d) Tests performance
Answer: A - TABLESAMPLE is useful for statistical analysis on large datasets.