Theory questions you will be asked

How to answer: define, example, trade-off

A strong answer is short and has three parts. Define the idea in one sentence. Give a tiny example from a real table. Name a trade-off or a trap ("but TRUNCATE cannot have a WHERE"). This shows you understand it, not just remember it. If you do not know an engine detail, say so and say how you would check.

DELETE vs TRUNCATE vs DROP

SQLite has no TRUNCATE; DELETE FROM t with no WHERE is optimised to do the same job.

Quick pairs

• WHERE vs HAVING: WHERE filters rows before grouping; HAVING filters groups after aggregation. WHERE city = 'Pune' vs HAVING COUNT(*) > 2.
• UNION vs UNION ALL: UNION removes duplicate rows (extra sort or hash work); UNION ALL keeps every row and is faster. Use UNION ALL when duplicates are impossible or wanted.
• Primary key vs unique key: one primary key per table, never NULL, identifies the row. A table can have many unique keys, and they may hold NULLs (PostgreSQL, MySQL and SQLite allow several NULLs; SQL Server's unique index allows only one).
• View vs table: a table stores data; a view is a saved SELECT that runs when you query it. A materialized view (PostgreSQL, Oracle) stores the result and must be refreshed.
• CHAR vs VARCHAR: CHAR(n) is fixed length, padded with spaces (good for fixed codes like a 2-letter state code); VARCHAR(n) stores only what you put in. In PostgreSQL there is no speed benefit to CHAR.
• OLTP vs OLAP: OLTP (online transaction processing) runs many small reads and writes, like placing orders; OLAP (online analytical processing) runs big read-only reports over history, often in a warehouse with column storage.

Clustered vs non-clustered index

A clustered index decides the physical order of the table's rows: the table is stored inside the index, so there can be only one. A non-clustered index is a separate structure that points back to the rows; a table can have many. In SQL Server, the primary key creates the clustered index by default. MySQL's InnoDB always clusters the table on its primary key. PostgreSQL is different: tables are heaps (rows in no particular order) and every index is separate; the CLUSTER command reorders a table once but does not keep it ordered. Module 10 covers how indexes work.

Normalisation and ACID in one breath

Normalisation (module 9) removes repeated data so each fact is stored once: 1NF has atomic values, 2NF removes partial dependencies on part of a composite key, 3NF removes dependencies between non-key columns. The trade-off: more joins; reporting systems sometimes denormalise on purpose. ACID (module 8): Atomic (all or nothing), Consistent (rules hold), Isolated (transactions do not see each other's half-done work), Durable (committed data survives a crash).

Isolation levels

A dirty read sees another transaction's uncommitted change. A non-repeatable read gets a different value when reading the same row twice. A phantom read finds new rows appearing in a repeated range query. Defaults differ: PostgreSQL, SQL Server and Oracle use Read Committed; MySQL InnoDB uses Repeatable Read. Engines also go beyond the standard table: PostgreSQL's Repeatable Read does not allow phantoms.

Common mistakes

• Saying "TRUNCATE can never be rolled back". It depends on the engine; say so.
• Saying "a unique key cannot be NULL". Most engines allow NULLs in a unique column.
• Saying PostgreSQL has clustered indexes like SQL Server. It has heaps plus a one-time CLUSTER command.
• Reciting definitions with no example. Always add a five-second example.
• Bluffing an engine detail. "I believe the default is X; I would confirm in the docs" is a good answer.