81. Which index type is optimized for text search?
- a) Full-text index
- b) B-tree index
- c) Hash index
- d) Bitmap index
Answer: A - Full-text indexes enable efficient word-based searches in large text fields.
82. What is query plan caching?
- a) Storing execution plans for reuse
- b) Backing up SQL statements
- c) Encrypting queries
- d) Compressing result sets
Answer: A - Cached plans reduce compilation overhead for repeated queries.
83. Which SQL feature encrypts data at rest?
- a) Transparent Data Encryption (TDE)
- b) SSL/TLS
- c) Column-level encryption
- d) Both A and C
Answer: D - TDE encrypts entire databases, while column-level encryption provides granular protection.
84. What is the purpose of the SQL CONTAINS() function?
- a) Full-text search with advanced operators
- b) Substring matching
- c) NULL value detection
- d) Data type validation
Answer: A -
CONTAINS() supports proximity, inflectional, and weighted searches.
85. Which database role has unrestricted access?
- a) sysadmin
- b) db_owner
- c) superuser
- d) All of the above
Answer: D - Terminology varies by DBMS (SQL Server: sysadmin, PostgreSQL: superuser).
86. What is a covering index?
- a) Index that includes all queried columns
- b) Index spanning multiple tables
- c) Encrypted index
- d) Temporary query index
Answer: A - Covering indexes eliminate table access by storing extra columns.
87. Which SQL function calculates spatial distance?
- a) ST_Distance()
- b) GEO_DISTANCE()
- c) SPATIAL_LENGTH()
- d) MAP_DISTANCE()
Answer: A -
ST_Distance() measures between geometry/geography objects.
88. What is parameter sniffing in SQL?
- a) Using parameter values to optimize plans
- b) Detecting SQL injection
- c) Validating input formats
- d) Encrypting query parameters
Answer: A - Parameter sniffing can cause plan reuse issues with skewed data distributions.
89. Which isolation level allows dirty reads?
- a) READ UNCOMMITTED
- b) READ COMMITTED
- c) REPEATABLE READ
- d) SERIALIZABLE
Answer: A - READ UNCOMMITTED sees uncommitted changes from other transactions.
90. What is the purpose of the SQL FREETEXT() function?
- a) Natural language text search
- b) Exact phrase matching
- c) Regular expression search
- d) Data compression
Answer: A -
FREETEXT() matches meaning rather than exact wording.
91. Which index type is best for low-cardinality columns?
- a) Bitmap index
- b) B-tree index
- c) Hash index
- d) Full-text index
Answer: A - Bitmap indexes efficiently represent few distinct values (e.g., gender flags).
92. What is the purpose of the SQL ST_Within() function?
- a) Tests if geometry is inside another
- b) Calculates area
- c) Measures perimeter
- d) Generates buffers
Answer: A -
ST_Within(geom1, geom2) checks spatial containment.
93. Which statement creates a role?
- a) CREATE ROLE
- b) MAKE ROLE
- c) ADD ROLE
- d) DEFINE ROLE
Answer: A - Roles simplify permission management by grouping users.
94. What is index fragmentation?
- a) Disordered pages reducing efficiency
- b) Broken index links
- c) Encrypted index corruption
- d) Partition misalignment
Answer: A - Fragmentation occurs from heavy DML operations (REBUILD/REORGANIZE fixes it).
95. Which SQL feature tracks data changes over time?
- a) Change Data Capture (CDC)
- b) Temporal tables
- c) Transaction logs
- d) All of the above
Answer: D - All methods capture history (CDC streams changes, temporal tables auto-version).
96. What is the purpose of the SQL SOUNDEX() function?
- a) Phonetic similarity matching
- b) Audio data storage
- c) Volume calculation
- d) Encryption
Answer: A -
SOUNDEX() finds names sounding similar (e.g., "Smith" and "Smyth").
97. Which constraint ensures values exist in another table?
- a) FOREIGN KEY
- b) CHECK
- c) EXISTS
- d) VALIDATE
Answer: A - Foreign keys enforce referential integrity between tables.
98. What is a clustered index?
- a) Index that physically orders table data
- b) Composite index
- c) Full-text index
- d) Temporary index
Answer: A - Each table can have only one clustered index (usually PRIMARY KEY).
99. Which SQL function generates UUIDs?
- a) NEWID() / UUID()
- b) GENERATE_ID()
- c) UNIQUE_ID()
- d) RANDOM_ID()
Answer: A - Syntax varies (SQL Server:
NEWID(), MySQL: UUID()).
100. What is the purpose of the SQL ST_Buffer() function?
- a) Creates a polygon around a geometry
- b) Compresses spatial data
- c) Measures density
- d) Encrypts coordinates
Answer: A -
ST_Buffer(geom, distance) generates proximity zones.