Interview Q&A / SQL & DBMS

1. What is the difference between a clustered and a non-clustered index?

   A clustered index decides the physical order in which table rows are stored, so the table itself is the index and there can be only one. A non-clustered index is a separate structure holding the indexed values plus a pointer back to the row, and a table can have many.

   AdvancedIn-depth4 min read
2. What is BCNF and how is it stricter than 3NF?

   A table is in BCNF if, for every non-trivial dependency X -> Y, X is a superkey. 3NF also lets Y be a prime attribute, which BCNF does not allow, so BCNF removes a few anomalies that 3NF misses.

   AdvancedQuick answer1 min read
3. What is MVCC in databases?

   MVCC keeps multiple versions of a row so readers see a consistent snapshot while writers create new versions. This lets reads and writes proceed without blocking each other. PostgreSQL, MySQL InnoDB and Oracle use it.

   AdvancedQuick answer1 min read
4. What is the default window frame, and why can LAST_VALUE give a surprising answer?

   With ORDER BY, the default frame runs from the first row to the current row, so LAST_VALUE returns the current row's value. To get the true last value, extend the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

   AdvancedQuick answer1 min read