101. What is the purpose of SAVEPOINT in transactions?
- a) Creates rollback points within a transaction
- b) Commits partial changes
- c) Optimizes query execution
- d) Locks tables exclusively
Answer: A - SAVEPOINT allows partial rollbacks within a larger transaction.
102. Which database mirroring mode offers high availability?
- a) High-safety mode with automatic failover
- b) High-performance mode
- c) Offline mode
- d) Snapshot mode
Answer: A - High-safety mode with witness server enables automatic failover.
103. Which SQL function validates XML against a schema?
- a) XMLVALIDATE()
- b) CHECK_XML()
- c) VALIDATE_XSD()
- d) SCHEMA_VERIFY()
Answer: A - XMLVALIDATE ensures XML documents conform to XSD schemas (DB2/Oracle).
104. What is the purpose of SQL Server's PolyBase?
- a) Queries external data sources
- b) Encrypts databases
- c) Compresses backups
- d) Generates reports
Answer: A - PolyBase integrates Hadoop, Azure Blob Storage, and other external data.
105. Which statement creates a sequence?
- a) CREATE SEQUENCE
- b) MAKE SEQUENCE
- c) ADD SEQUENCE
- d) DEFINE SEQUENCE
Answer: A - Sequences generate unique numbers (alternative to IDENTITY).
106. What is the purpose of the SQL MACHINE LEARNING SERVICES?
- a) Executes Python/R scripts in-database
- b) Optimizes queries using AI
- c) Generates synthetic data
- d) Detects anomalies
Answer: A - Enables advanced analytics without data movement (SQL Server feature).
107. Which cloud database feature scales automatically?
- a) Auto-scaling
- b) Read replicas
- c) Elastic pools
- d) All of the above
Answer: D - Cloud providers offer multiple scaling options based on workload.
108. What does the SQL XQuery language process?
- a) XML data
- b) JSON data
- c) Spatial data
- d) Time-series data
Answer: A - XQuery extracts and manipulates XML documents in SQL databases.
109. Which statement creates a database snapshot?
- a) CREATE DATABASE SNAPSHOT
- b) MAKE SNAPSHOT
- c) BACKUP AS SNAPSHOT
- d) SAVE AS SNAPSHOT
Answer: A - Snapshots provide read-only, point-in-time database views.
110. What is the purpose of the SQL WAITFOR statement?
- a) Delays execution for specified time
- b) Pauses transactions
- c) Waits for locks
- d) Synchronizes replicas
Answer: A - WAITFOR DELAY '00:00:05' pauses for 5 seconds.
111. Which database service offers serverless computing?
- a) Azure SQL Database serverless
- b) AWS Aurora Serverless
- c) Google Cloud Spanner
- d) Both A and B
Answer: D - Major cloud providers offer serverless database options.
112. What is the purpose of the SQL TRY_CONVERT() function?
- a) Safe type conversion with NULL on failure
- b) Validates data formats
- c) Tests encoding compatibility
- d) Checks collation support
Answer: A - Returns NULL instead of errors when conversion fails (SQL Server).
113. Which statement manages database mail?
- a) sp_send_dbmail
- b) SEND MAIL
- c) EXECUTE MAIL
- d) CREATE MESSAGE
Answer: A - SQL Server's stored procedure for sending emails.
114. What is the purpose of the SQL STRING_AGG() function?
- a) Concatenates values with delimiter
- b) Splits strings
- c) Encrypts text
- d) Compresses data
Answer: A - Modern replacement for GROUP_CONCAT/STUFF-FOR-XML patterns.
115. Which feature enables cross-database queries?
- a) Linked servers
- b) Database links
- c) Federated queries
- d) All of the above
Answer: D - Terminology varies by DBMS (Oracle: DB links, SQL Server: Linked servers).
116. What is the purpose of the SQL APPROX_COUNT_DISTINCT() function?
- a) Faster distinct counts with slight inaccuracy
- b) Exact unique value counting
- c) Estimates table size
- d) Samples data
Answer: A - Uses probabilistic algorithms for big data scenarios.
117. Which statement creates a memory-optimized table?
- a) CREATE TABLE...WITH (MEMORY_OPTIMIZED=ON)
- b) MAKE IN-MEMORY TABLE
- c) CREATE MEMORY TABLE
- d) DEFINE CACHE TABLE
Answer: A - In-memory OLTP tables for high-throughput workloads.
118. What is the purpose of the SQL DATABASEPROPERTYEX() function?
- a) Retrieves database options/state
- b) Exports schema
- c) Validates backups
- d) Checks permissions
Answer: A - Checks recovery model, collation, status etc. (SQL Server).
119. Which cloud database offers multi-model support?
- a) Azure Cosmos DB
- b) AWS RDS
- c) Google Cloud SQL
- d) Oracle Autonomous
Answer: A - Supports document, key-value, graph, and column-family models.
120. What is the purpose of the SQL SESSION_CONTEXT() function?
- a) Accesses key-value pairs in session
- b) Manages transactions
- c) Tracks connections
- d) Controls isolation levels
Answer: A - Alternative to global temporary tables for session state.