Database and SQL interviews check that you can model data well, write correct queries and keep them fast as data grows. They come up for database engineers, backend developers and data analysts alike. These questions cover keys, joins and normalisation, then indexing, transactions and scaling.
Fundamentals, definitions and simple scenarios. Good for freshers and warm-ups.
A primary key uniquely identifies each row in a table and cannot be NULL; a table has one primary key, which can span several columns. A foreign key is a column that references the primary key of another table, so the database enforces that the referenced row exists. Rules such as ON DELETE CASCADE or RESTRICT decide what happens to child rows when the parent is deleted.
DELETE removes rows, can use a WHERE clause, logs each row and fires triggers. TRUNCATE removes all rows at once, is much faster, and in many databases resets identity counters. DROP removes the whole table, including its structure. Whether TRUNCATE can be rolled back depends on the database: PostgreSQL allows it inside a transaction, while MySQL and Oracle commit it immediately.
INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows from the left table plus matches from the right, with NULLs where there is no match; RIGHT JOIN is the reverse. FULL OUTER JOIN returns all rows from both. CROSS JOIN returns every combination. A self join joins a table to itself, for example to match employees with their managers.
WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates. For example, to find customers with more than five orders: SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 5.
Applied problems, trade-offs and questions about your own projects.
Normalisation organises tables to reduce duplicate data and avoid update anomalies. 1NF means each column holds atomic values with no repeating groups. 2NF means every non-key column depends on the whole primary key, not part of a composite key. 3NF means non-key columns depend only on the key, not on other non-key columns. For reporting or read-heavy workloads we sometimes denormalise on purpose for speed.
Most indexes are B-trees: sorted structures that let the database find rows in logarithmic time instead of scanning the whole table. They speed up WHERE, JOIN and ORDER BY. But each index uses storage and slows inserts, updates and deletes. In a composite index the column order matters, because only a leftmost prefix can be used. Wrapping the column in a function or using a leading wildcard like LIKE '%abc' usually stops the index from being used.
Atomicity: a transaction happens completely or not at all. Consistency: it takes the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not see each other's partial work, to the degree set by the isolation level. Durability: once committed, data survives a crash, usually thanks to a write-ahead log. A bank transfer is the classic example.
Relational databases suit structured, related data that needs joins, constraints and strong transactions, such as orders, payments and accounting. NoSQL covers document stores (MongoDB), key-value stores (Redis), wide-column stores (Cassandra) and graph databases. They offer flexible schemas and easy horizontal scaling, but you model data around your access patterns. I choose based on relationships, consistency needs, query patterns and scale, and many systems use both.
System design, deep internals, leadership and tough follow-ups.
Read Uncommitted allows dirty reads. Read Committed prevents dirty reads but allows non-repeatable reads; it is the default in PostgreSQL, Oracle and SQL Server. Repeatable Read guarantees a row reads the same within a transaction; the SQL standard still allows phantom rows, though MySQL InnoDB (where it is the default) and PostgreSQL largely prevent them. Serializable makes transactions behave as if run one at a time, but may abort some with serialisation errors that the application must retry.
I find slow queries with the slow query log or pg_stat_statements, then run EXPLAIN ANALYZE. I look for sequential scans on big tables, row estimates far from reality, nested loops over large sets, and sorts spilling to disk. Fixes include the right composite or covering index, rewriting the query (avoiding SELECT *, N+1 patterns and functions on indexed columns), refreshing statistics, pagination and caching. Then I measure again.
Replication copies the same data to several servers. It improves read capacity and availability, but asynchronous replicas can lag, so reads from them may be slightly stale. Sharding splits the data across servers by a shard key, which scales writes and storage. A good shard key spreads load evenly, but queries across shards, cross-shard transactions and rebalancing become hard. Large systems often use both.
A deadlock happens when two transactions each hold a lock the other needs, so neither can continue. The database detects it and aborts one with an error. To reduce deadlocks I access tables and rows in a consistent order, keep transactions short, add indexes so fewer rows are locked, and avoid user interaction inside a transaction. The application should catch deadlock errors and retry.