41. What is the first normal form (1NF) requirement for a table?
- a) All attributes must contain atomic (indivisible) values
- b) All non-key attributes must depend on the primary key
- c) There should be no transitive dependencies
- d) The table must have at least three columns
Answer: A - 1NF requires that tables have no repeating groups and all values are atomic.
42. Which of the following is NOT a advantage of indexing?
- a) Faster data retrieval
- b) Reduced storage space
- c) Improved query performance
- d) Faster sorting operations
Answer: B - Indexes actually increase storage space as they require additional data structures.
43. What is a clustered index?
- a) An index that determines the physical order of data in a table
- b) A group of indexes stored together
- c) An index that clusters similar values
- d) A temporary index created during queries
Answer: A - A table can have only one clustered index as it defines the physical storage order.
44. Which of the following is NOT a NoSQL database type?
- a) Key-value stores
- b) Document databases
- c) Column-family stores
- d) Table-based stores
Answer: D - Main NoSQL types are key-value, document, column-family, and graph databases.
45. What is the CAP theorem in distributed databases?
- a) A database can only guarantee two of: Consistency, Availability, Partition Tolerance
- b) A theory about database capacity planning
- c) A rule for columnar data storage
- d) A principle for atomic transactions
Answer: A - CAP theorem states that distributed systems can't simultaneously guarantee all three properties.
46. What is the purpose of the SQL BETWEEN operator?
- a) To select values within a range
- b) To compare two tables
- c) To find values between two columns
- d) To insert values between existing records
Answer: A - BETWEEN filters values within a specified inclusive range (numbers, dates, or text).
47. What is a materialized view?
- a) A view that stores the query results physically
- b) A temporary view created during execution
- c) A view with materialized data types
- d) A read-only view of physical tables
Answer: A - Unlike regular views, materialized views store results and can be refreshed periodically.
48. Which SQL command is used to grant privileges to users?
- a) GRANT
- b) ALLOW
- c) PERMIT
- d) ENABLE
Answer: A - GRANT is part of DCL (Data Control Language) to give specific permissions.
49. What is the difference between CHAR and VARCHAR data types?
- a) CHAR is fixed-length, VARCHAR is variable-length
- b) CHAR is for numbers, VARCHAR is for text
- c) CHAR is faster but VARCHAR saves space
- d) Both A and C
Answer: D - CHAR pads values with spaces to fixed length, VARCHAR uses only required space.
50. What is a database trigger?
- a) A stored procedure that automatically executes in response to events
- b) A security feature to prevent unauthorized access
- c) A way to start database services
- d) A backup mechanism
Answer: A - Triggers fire automatically on INSERT, UPDATE, DELETE or other database events.
51. What is the purpose of the SQL EXISTS operator?
- a) To test for the existence of rows in a subquery
- b) To check if a database exists
- c) To verify table structures
- d) To test for NULL values
Answer: A - EXISTS returns TRUE if the subquery returns one or more records.
52. What is database sharding?
- a) Horizontal partitioning of data across multiple servers
- b) Vertical splitting of tables
- c) A backup technique
- d) A security protocol
Answer: A - Sharding distributes data across machines based on a shard key (e.g., user_id ranges).
53. Which of the following is NOT a valid SQL aggregate function?
- a) COUNT()
- b) SUM()
- c) AVERAGE()
- d) MAX()
Answer: C - The correct function is AVG(), not AVERAGE().
54. What is the purpose of the SQL CASE expression?
- a) To perform conditional logic in queries
- b) To handle exceptions
- c) To create case-sensitive comparisons
- d) To define transaction cases
Answer: A - CASE works like IF-THEN-ELSE logic within SQL statements.
55. What is a database cursor?
- a) A control structure for traversing records in a result set
- b) A pointer to the current row
- c) A graphical indicator in DBMS software
- d) A type of database index
Answer: A - Cursors enable row-by-row processing of result sets in procedural code.
56. Which SQL command is used to remove user privileges?
- a) REVOKE
- b) REMOVE
- c) DENY
- d) DELETE
Answer: A - REVOKE removes previously granted permissions (counterpart to GRANT).
57. What is the purpose of the SQL COALESCE function?
- a) To return the first non-NULL expression among its arguments
- b) To merge multiple columns
- c) To combine strings
- d) To calculate averages
Answer: A - COALESCE(NULL, 'default') would return 'default'.
58. What is database replication?
- a) Maintaining multiple copies of data across different servers
- b) Creating backup files
- c) Duplicating table structures
- d) Copying database schemas
Answer: A - Replication improves availability and fault tolerance through data redundancy.
59. What is the purpose of the SQL WITH clause (CTE)?
- a) To define a temporary result set that can be referenced in subsequent queries
- b) To create temporary tables
- c) To add conditions to JOIN operations
- d) To include external data sources
Answer: A - Common Table Expressions (CTEs) improve query readability and reusability.
60. What is the difference between 2NF and 3NF?
- a) 3NF eliminates transitive dependencies while 2NF doesn't
- b) 2NF requires atomic values while 3NF doesn't
- c) 3NF allows partial dependencies while 2NF doesn't
- d) There is no practical difference
Answer: A - 2NF removes partial dependencies, 3NF removes transitive dependencies.