Interview Q&A / SQL & DBMS

61. What is the difference between IN and EXISTS?

    IN compares a value against a list of values returned by a subquery. EXISTS only checks whether the subquery returns any row and stops at the first match. EXISTS handles NULLs more safely, especially with NOT.

    IntermediateQuick answer1 min read
62. What is the difference between optimistic and pessimistic locking?

    Pessimistic locking locks the row up front so others must wait, assuming conflicts are likely. Optimistic locking takes no lock and checks at update time, usually with a version number, whether someone else changed the row.

    IntermediateQuick answer1 min read
63. What is the difference between putting a filter in ON versus WHERE in a LEFT JOIN?

    A filter on the right table in ON limits which rows match while still keeping every left row. The same filter in WHERE runs after the join and removes the NULL-extended rows, turning the LEFT JOIN into an INNER JOIN.

    IntermediateQuick answer1 min read
64. Why might the database ignore an index on a column?

    Common reasons are wrapping the column in a function, a leading wildcard in LIKE, a type mismatch, a condition that matches most rows, or outdated statistics. The optimizer then prefers a full scan.

    IntermediateQuick answer1 min read
65. Write a query to calculate a running total of daily sales.

    Use SUM(amount) OVER (ORDER BY day) to get a cumulative sum. Add PARTITION BY to restart the total per group, and use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to be explicit.

    IntermediateQuick answerCoding1 min read
66. Write a query to find the highest-paid employee in each department.

    Compute MAX(salary) per department in a subquery and join back, or use RANK() OVER (PARTITION BY dept ORDER BY salary DESC) and keep rank 1. Both return all tied employees.

    IntermediateQuick answerCoding1 min read
67. 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
68. 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
69. 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