Interview questions
Queries, indexes, transactions and data modelling.
An INNER JOIN returns rows with matches in both tables. A LEFT JOIN returns every row from the left table and matching rows from the right, filling gaps with NULL; a RIGHT JOIN is the mirror image. A FULL OUTER JOIN keeps unmatched rows from both sides, and a CROSS JOIN returns every combination of rows.
An index is a separate data structure — usually a B-tree — that keeps selected column values sorted with pointers to rows, so the database can find matches without scanning the whole table. Indexes speed up reads, filters, joins and sorting, but they use storage and slow down inserts and updates because every index must be maintained.
ACID describes guarantees for transactions. Atomicity: all changes commit or none do. Consistency: a transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not see each other’s partial work, according to the isolation level. Durability: committed changes survive crashes.
Choose a relational (SQL) database when data is structured and related, you need joins, strong consistency and multi-row transactions. Choose a NoSQL store when access patterns are simple and known, the schema changes often, or you need to scale writes horizontally with flexible documents, key-value lookups, wide columns or graph traversal.
Normalization organises tables to reduce redundancy and update anomalies. First normal form requires atomic values; second removes partial dependencies on part of a composite key; third removes transitive dependencies, so non-key columns depend only on the key. Denormalization deliberately reintroduces redundancy to speed up reads, usually in analytics or high-traffic read paths.
Start free, install the Windows app and run a practice session today.
