Easy everything in Databases & Architecture, page 2
Why developers read query plans
The plan shows the operators the optimizer chose to run a query; developers read it to find why a query is slow; a common thing to look for is a full table scan where an index was expected.
Stages of executing a SELECT query
Parse the SQL into a tree, bind and validate against the catalog, optimize into a physical plan, then execute the plan operators fetching data and returning rows.
Purpose of the Write-Ahead Log
WAL records changes sequentially and is flushed to disk before commit; the rule is log first, then data pages may lag; on crash the database replays the log to recover committed work.
What is a database page?
A page is a fixed-size block, often 8KB, holding multiple rows; databases read and write whole pages because disk and OS I/O are block-oriented, amortizing seek cost and matching the buffer pool unit.
Clustered versus non-clustered indexes
A clustered index orders the table's actual rows, one per table; a non-clustered index is a separate structure pointing to rows.
Composite index column order for multi-column filters
Create one index on (last_name, first_name); order matters because the index serves leftmost-prefix lookups.
What a database index is and when it helps
An index is a sorted lookup structure avoiding full scans, helps selective WHERE/JOIN columns, but costs write overhead.
Database deadlocks and how engines resolve them
Define a deadlock as mutual waiting on locks, name detection plus victim rollback, and prevention by consistent lock ordering.
The lost update anomaly explained
Both transactions read the same value, each adds one, the second overwrite erases the first.
The ACID properties of transactions
Define Atomicity, Consistency, Isolation, Durability and why each matters.
Modeling one-to-many versus many-to-many relationships
One-to-many uses a foreign key on the many side; many-to-many needs a junction table with two foreign keys.
Normalizing a flat orders table to 3NF
Split repeating data, remove partial dependencies, remove transitive dependencies, define keys.
What is the difference between DDL and DML in SQL?
DDL shapes schema with CREATE or ALTER; DML handles row-level data with SELECT, INSERT, UPDATE, or DELETE.
Explain ACID properties and why they matter for banking or e-commerce
Define each as a failure-handling guarantee; show how partial commits cause double-spending.
What is the difference between primary, foreign, and unique keys?
This tests relational integrity basics. Answer: primary keys identify rows, foreign keys reference tables, and unique keys are alternate candidates. Red flag: saying unique keys are just for indexing or omitting a non-PK example like email.
How SQL Queries Become Abstract Syntax Trees
An AST turns a flat SQL string into a tree of operations the database can reason about. The parser builds this tree before execution planning. Do not confuse it with the raw parse tree, which keeps punctuation and formatting the AST strips away.
DML: Insert, Update, and Delete
DML is the change-focused subset of database languages like SQL that adds, modifies, and removes data. It appears in every write to a table. The footgun is assuming SELECT belongs in DML; read-only querying is sometimes split out as DQL instead.
DDL: The Blueprint for Database Objects
DDL is the blueprint for your database. You reach for it when spinning up new tables, indexes, or user permissions, not when querying rows. The footgun is running DROP thinking you are deleting data, not vaporizing the entire table structure.
Data Retention Policy: Your Schedule for Deleting Data
A data retention policy is your company's official schedule for deleting data, not a plan to keep it forever. It's essential for legal compliance (like GDPR) and managing storage costs.
Vector Embeddings: Turning Meaning into Math
Vector embeddings turn complex data like words or images into lists of numbers (vectors). This lets computers measure "similarity" by calculating the distance between these vectors, powering search and recommendations.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles