SQL & DBMS interview questions and quick answers

ACID stands for Atomicity, Consistency, Isolation and Durability. A transaction either fully happens or not at all, leaves the data valid according to its rules, behaves as if it ran without interference from other transactions, and stays saved once committed, even if the server crashes.

WHERE runs before SELECT, so the alias does not exist yet when the filter is evaluated. Repeat the expression, or put the query in a subquery or CTE and filter outside.

In autocommit mode every SQL statement is its own transaction and is committed as soon as it succeeds. To group statements into one atomic unit, start an explicit transaction with BEGIN or turn autocommit off.

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.

NOT NULL forbids missing values, DEFAULT supplies a value when none is given on insert, and CHECK rejects rows whose values fail a condition. Together they keep bad data out at the database level.

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.

Mixing up ACID consistency with "consistency" in distributed systems (the C in CAP). In ACID it means the data obeys its rules; in CAP it means every node sees the same latest data.

Banking and UPI transfers: debit and credit must succeed together.