SQL fundamentals

SQL fundamentals interviews test relational modeling and query reasoning shared by PostgreSQL, MySQL, and SQLite. Interviewers expect you to design tables and keys, choose join shapes, index for real predicates, and explain transaction isolation when concurrency matters.

Subtopics: tables and constraints, primary and foreign keys, joins, indexes, transactions, isolation levels, locks, query plans, normalization, and denormalization.

On interviews: given a schema sketch, explain how you would enforce an invariant, which index supports a hot query, and what anomaly appears at a weaker isolation level.

Common pitfalls: surrogate keys without uniqueness rules; foreign keys without cascade strategy; indexes on every column; denormalization before measuring read pain.

The trade-off is balancing normalization for write correctness against denormalization for read speed — justify with access patterns and measured plans.

Checklist:

• Map entities to tables with explicit constraints.
• Choose join type by cardinality and null semantics.
• Index predicates, joins, and ordering you actually run.
• Pick isolation by tolerable anomalies, not defaults.