41. Which SQL feature prevents SQL injection attacks?
- a) Prepared statements
- b) Stored procedures
- c) Input validation
- d) All of the above
Answer: D - All these methods help mitigate SQL injection risks.
42. What is the purpose of the COALESCE() function?
- a) Returns first non-NULL expression
- b) Counts NULL values
- c) Merges tables
- d) Encrypts data
Answer: A -
COALESCE(expr1, expr2,...) returns the first non-NULL argument.
43. Which statement creates an index?
- a) CREATE INDEX
- b) ADD INDEX
- c) MAKE INDEX
- d) DEFINE INDEX
Answer: A -
CREATE INDEX index_name ON table(column).
44. What is a materialized view?
- a) Physically stored query result
- b) Virtual table
- c) Temporary cache
- d) Backup snapshot
Answer: A - Materialized views store precomputed results for faster access.
45. Which window function assigns row numbers?
- a) ROW_NUMBER()
- b) RANK()
- c) DENSE_RANK()
- d) NTILE()
Answer: A -
ROW_NUMBER() assigns sequential integers to rows.
46. What does the ON DELETE CASCADE option do?
- a) Automatically deletes child records
- b) Prevents deletion
- c) Logs deletions
- d) Archives data
Answer: A - Cascade delete maintains referential integrity by removing dependent records.
47. Which SQL standard introduced Common Table Expressions?
- a) SQL:1999
- b) SQL:2003
- c) SQL:2008
- d) SQL:2011
Answer: B - CTEs were standardized in SQL:2003.
48. What is the purpose of the MERGE statement?
- a) Performs UPSERT operations
- b) Combines databases
- c) Backs up tables
- d) Compresses data
Answer: A -
MERGE inserts/updates based on conditions (also called UPSERT).
49. Which function converts data types?
- a) CAST()
- b) CONVERT()
- c) TRY_PARSE()
- d) All of the above
Answer: D - All these functions handle type conversion (syntax varies by DBMS).
50. What is a deadlock in databases?
- a) Two transactions blocking each other
- b) Corrupted index
- c) Full transaction log
- d) Network timeout
Answer: A - Deadlocks occur when transactions wait indefinitely for locked resources.
51. Which statement grants permissions?
- a) GRANT
- b) ALLOW
- c) PERMIT
- d) ENABLE
Answer: A -
GRANT SELECT ON table TO user gives specific privileges.
52. What does the LAG() window function do?
- a) Accesses previous row's data
- b) Skips rows
- c) Creates time lags
- d) Delays execution
Answer: A -
LAG(column, offset) retrieves data from prior rows in the result set.
53. Which NoSQL database type uses key-value pairs?
- a) Redis
- b) MongoDB
- c) Cassandra
- d) Neo4j
Answer: A - Redis is a popular key-value store, while MongoDB is document-based.
54. What is database sharding?
- a) Horizontal partitioning across servers
- b) Vertical column splitting
- c) Data compression
- d) Backup strategy
Answer: A - Sharding distributes rows across multiple database instances.
55. Which SQL clause filters groups after aggregation?
- a) HAVING
- b) WHERE
- c) FILTER
- d) GROUP BY
Answer: A -
HAVING works on aggregated data, unlike WHERE.
56. What is the purpose of the SQL CASE expression?
- a) Conditional logic in queries
- b) Error handling
- c) Loop control
- d) Transaction management
Answer: A -
CASE WHEN condition THEN result END implements if-then logic.
57. Which database object improves read performance?
- a) Index
- b) Trigger
- c) Constraint
- d) Cursor
Answer: A - Indexes accelerate search operations at the cost of write speed.
58. What does the UNION operator do?
- a) Combines results from multiple SELECTs
- b) Joins tables horizontally
- c) Creates backups
- d) Merges databases
Answer: A -
UNION stacks result sets vertically (with duplicate removal).
59. Which normalization form eliminates transitive dependencies?
- a) 3NF
- b) 1NF
- c) 2NF
- d) BCNF
Answer: A - Third Normal Form (3NF) removes non-key attributes dependent on other non-key attributes.
60. What is the purpose of the SQL EXCEPT operator?
- a) Returns distinct rows from first query not in second
- b) Combines all rows
- c) Finds common rows
- d) Filters NULL values
Answer: A -
EXCEPT performs set difference (called MINUS in some DBMS).