SQL MCQs - Indexes, Transactions, ACID and Normalization (Set 2)

Q1. What does a typical database index (a B-tree) mainly speed up?

a) Schema changes

b) Backups

c) Lookups, range scans and sorting on the indexed columns

d) INSERT statements

Q2. A table has a composite index on (last_name, first_name). Which query can use it efficiently?

a) WHERE first_name LIKE '%sha'

b) WHERE LOWER(first_name) = 'asha'

c) WHERE last_name = 'Sherpa'

d) WHERE first_name = 'Asha'

Q3. Why might WHERE LOWER(email) = 'a@b.com' ignore a normal index on email?

a) Indexes do not work on text

b) The query has a syntax error

c) LOWER is not valid SQL

d) The function changes the value, so the plain index on email cannot be used; you need an expression index on LOWER(email)

Q4. What does EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) show?

a) The list of users

b) Index sizes only

c) The query plan: how the database will execute the query, such as sequential scans, index scans and join methods

d) The table schema

Q5. What does the A in ACID stand for, and what does it mean?

a) Atomicity: all statements in a transaction succeed together or none of them take effect

b) Accuracy: values are always correct

c) Asynchrony: transactions run in the background

d) Availability: the database is always online

Q6. Which ACID property ensures that committed data survives a crash?

a) Durability

b) Atomicity

c) Isolation

d) Consistency

Q7. What is a dirty read?

a) Reading data from a corrupted disk

b) Reading the same row twice

c) Reading without an index

d) Reading changes from another transaction that has not committed yet

Q8. What is the default transaction isolation level in PostgreSQL?

a) READ COMMITTED

b) SERIALIZABLE

c) READ UNCOMMITTED

d) REPEATABLE READ

Q9. Two transactions each lock a row and then wait for the row locked by the other. What is this called, and what does the database do?

a) A race condition; nothing happens

b) A phantom read; both commit

c) A deadlock; the database detects it and aborts one of the transactions

d) Replication lag; it retries forever

Q10. How does optimistic concurrency control usually work in an application?

a) Use only SELECT statements

b) Lock the whole table before every read

c) Disable transactions

d) Store a version number; an update succeeds only if the version has not changed since it was read

Q11. A table stores a comma-separated list of phone numbers in one column. Which normal form does it violate?

a) None

b) Second normal form only

c) Third normal form only

d) First normal form (1NF)

Q12. A table orders(order_id, customer_id, customer_city) has customer_city depending on customer_id, not on order_id. Which normal form is violated?

a) 3NF (a transitive dependency)

b) 1NF

c) 2NF

d) BCNF only

Q13. When is denormalization a reasonable choice?

a) When read performance matters and you accept duplicate data and the work of keeping it in sync

b) Only for primary keys

c) Always, because joins are bad

d) Never

Q14. A page lists 50 posts and then runs one query per post to fetch its author. What is this problem called?

a) Index bloat

b) A deadlock

c) The N+1 query problem

d) The Cartesian problem

Q15. Why should you avoid adding an index to every column?

a) Databases allow only one index

b) Indexes break foreign keys

c) Each index uses storage and slows down inserts, updates and deletes, and unused indexes give no benefit

d) Indexes make reads slower