Primary key vs foreign key
A primary key uniquely identifies a row in its own table, while a foreign key points to a related row in another table.
- Primary key is table identity
- Foreign key creates relationship
- Helps enforce referential integrity
Quick recall
374 cards
Subquery vs join
Joins combine tables directly, while subqueries nest one query inside another; either can be correct depending on clarity and plan quality.
- Joins are often easier to optimize
- Subqueries can express stepwise logic
- Check the execution plan
What are common SQL join types and when do you use them?
Inner joins return matches, outer joins preserve unmatched rows from one or both sides depending on the type.
- INNER for only matches
- LEFT when you need all left rows
- FULL when you need everything from both sides
What are transactions?
Transactions group multiple statements into one atomic unit so partial updates do not leak through on failure.
- Begin commit rollback
- Protects consistency
- Works with isolation levels
What are window functions?
Window functions compute values across a related set of rows without collapsing them into one row per group.
- Use OVER clause
- Great for ranking and running totals
- Keeps detail rows
What does GROUP BY do?
GROUP BY collects rows by one or more columns so aggregate functions can run per group.
- Works with count sum avg
- Every selected non-aggregate column must be grouped
- HAVING filters groups
What is an execution plan?
An execution plan shows how the database intends to run a query, including scans, joins, sorts, and index usage.
- Look for full scans
- Check join strategy
- Used to explain slow queries
What is denormalization?
Denormalization intentionally duplicates or precomputes data to improve read performance.
- Speeds reads
- Adds consistency risk
- Often used in analytics or caching-heavy systems
What is normalization?
Normalization reduces redundancy and update anomalies by splitting data into related tables.
- Separate repeating facts
- Use keys for relationships
- Trade joins for consistency
Why are indexes important?
Indexes speed reads by avoiding full table scans, but they add write overhead and storage cost.
- Great for filters and joins
- Too many indexes slow writes
- Choose columns based on query patterns
AWS S3 basics
S3 is durable object storage used for files, backups, logs, static assets, and data lake style workloads.
- Object storage not block storage
- Highly durable
- Great for static files and archives
Blue-green deployment vs rolling deployment
Rolling replaces instances gradually, while blue-green keeps old and new environments side by side and switches traffic in one move.
- Rolling is resource-efficient
- Blue-green is cleaner for fast rollback
- Trade cost for safety