How to Use This Guide

This interview prep guide covers 30 essential questions organized by topic. Each question includes a detailed answer that goes beyond definitions to demonstrate deep understanding. Click any question to reveal the answer.

These questions reflect what senior engineers and interviewers ask at tech companies. Study them not to memorize, but to understand the reasoning behind each concept. You'll encounter variations of these in real interviews.

SQL Fundamentals (8 Questions)

1. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, while HAVING filters rows after grouping. This distinction is critical because aggregate functions like COUNT(), SUM(), AVG() only work in HAVING, not in WHERE.

Use WHERE to exclude individual rows based on column values. Use HAVING to exclude groups based on aggregate results. For example, you might use WHERE to find orders over $100, and HAVING to find customers whose total spending exceeds $1000.

sql
2. Explain INNER JOIN vs LEFT JOIN vs FULL OUTER JOIN

INNER JOIN returns only rows with matches in both tables. If a customer has no orders, they won't appear. This is the most restrictive join.

LEFT JOIN returns all rows from the left table plus matches from the right. If a customer has no orders, they still appear with NULL in the order columns. Use this when you want all records from the primary table.

FULL OUTER JOIN returns all rows from both tables. Unmatched rows get NULL values. This is the most inclusive join and is useful for finding orphaned records (e.g., customers with no orders AND orders with invalid customer_ids).

sql
3. What is a CTE and when would you use one?

A Common Table Expression (CTE) is a named temporary result set defined within a query using the WITH clause. It improves readability by breaking complex logic into smaller, named steps. CTEs are especially useful for recursive queries (tree/hierarchy navigation) and multi-step transformations.

Use a CTE to make your query more maintainable. Instead of nesting subqueries deeply, define intermediate results at the top. You can reference a CTE multiple times in the same query, avoiding duplication. Recursive CTEs let you traverse hierarchies like org charts or comment threads.

sql
4. Explain window functions — ROW_NUMBER vs RANK vs DENSE_RANK

Window functions perform calculations across a set of rows without collapsing them like GROUP BY. ROW_NUMBER assigns a unique number to every row in order, skipping no numbers. RANK assigns the same rank to ties, then skips numbers (1, 1, 3). DENSE_RANK also handles ties but doesn't skip numbers (1, 1, 2).

Use ROW_NUMBER to find the "top N per group" (e.g., top 3 orders per customer). Use RANK for competitions where tied scores skip positions. Use DENSE_RANK when you need ranks without gaps. All three avoid collapsing rows, letting you keep all original data.

sql
5. What is the difference between UNION and UNION ALL?

UNION combines result sets and removes duplicate rows. It sorts the results, which adds overhead. UNION ALL combines result sets but keeps duplicates and is faster because it doesn't deduplicate.

Use UNION when you need to remove duplicates (e.g., combining customer lists from multiple regions). Use UNION ALL when you know duplicates don't exist or they're acceptable, and speed matters. UNION ALL is typically 2-5x faster for large datasets.

sql
6. What are the different types of SQL constraints?

The main constraints are: NOT NULL (column must have a value), UNIQUE (no duplicates), PRIMARY KEY (unique + not null, one per table), FOREIGN KEY (references another table), CHECK (custom validation), and DEFAULT (provides default value).

Constraints are the database's way of enforcing business rules at the schema level, not in application code. This prevents bad data from ever entering the database. Every production table should have constraints matching your business logic.

sql
7. Explain ACID properties

Atomicity — A transaction either succeeds completely or fails completely. No partial updates. If a transfer moves money from account A to B, both operations succeed or both fail; you can't have money disappear.

Consistency — The database moves from one valid state to another. Constraints are always satisfied. If a rule says "product quantity >= 0", it's true before and after every transaction.

Isolation — Concurrent transactions don't interfere. Two simultaneous updates to the same record are handled safely, not corrupted.

Durability — Once a transaction commits, it stays committed even if the server crashes. Data written to disk is permanent.

ACID guarantees are what make databases trustworthy for financial systems, inventory, and critical data. Every production database should support ACID or you risk data loss.

8. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes rows and supports WHERE clauses. It's logged (slow for large tables) but reversible in a transaction. Triggers fire. Identity seeds aren't reset.

TRUNCATE removes all rows in a table much faster (minimal logging). It resets identity seeds. No WHERE clause. Triggers don't fire.

DROP removes the entire table structure and data. Very fast. Cannot be rolled back unless in a transaction.

Use DELETE for selective row removal (with WHERE). Use TRUNCATE to empty a table quickly when you don't need a backup. Use DROP when decommissioning a table. Always backup before DROP in production.

sql

Database Design (6 Questions)

9. What is normalization? Explain 1NF, 2NF, 3NF

Normalization is the process of organizing data to reduce redundancy and improve integrity. It involves breaking large tables into smaller, related ones.

1NF (First Normal Form) — Each column contains atomic (indivisible) values. No repeating groups. If you have a "hobbies" column with comma-separated values, split it into a separate table.

2NF (Second Normal Form) — Must be 1NF, plus non-key columns depend on the ENTIRE primary key, not just part of it. In a table with composite key (order_id, product_id), all other columns must relate to both, not just one.

3NF (Third Normal Form) — Must be 2NF, plus non-key columns don't depend on other non-key columns. If you store "product_name" and "product_price" together with order_id, extract them to a separate products table so price is maintained in one place.

Most production databases aim for 3NF as a balance between removing redundancy and query performance. Higher normal forms (BCNF, 4NF) are rarely needed in practice.

10. When would you denormalize a database?

Denormalization intentionally breaks normalization rules to improve read performance by reducing joins. You trade write complexity and storage for faster queries.

Denormalize when: (1) a normalized query is too slow despite proper indexing, (2) you have reporting tables with mostly reads (data warehouses), (3) a column is frequently queried alongside its related table, (4) you have many-to-many relationships that hurt performance.

Examples: storing customer_name in orders table (avoid a join on every order query), storing product_price in order_items (historical price tracking), caching aggregates like total_orders_count in the customers table (update on each order, but instant for reporting).

The key is: denormalize at the data model or reporting layer, never at the operational tables. Use materialized views or denormalized copies for reporting.

11. What is a star schema vs snowflake schema?

Both are data warehouse schemas designed for analytical queries. Star schema has one central fact table (quantitative data like sales) surrounded by dimension tables (descriptive data like products, customers, dates). Very flat, few joins needed.

Snowflake schema further normalizes the dimension tables. A product dimension might split into product and category tables. More tables and joins, but less redundancy and smaller storage.

Star schema is simpler and faster for analytics (fewer joins). Snowflake is more normalized (less storage) but requires more joins. Choose star for fast OLAP queries on moderate data, snowflake for large environments with strict storage limits.

Example: A fact_sales table with sales_amount and quantity, linked to dim_products (which itself links to dim_categories in snowflake). Queries on "sales by category" join fewer tables with star schema.

12. Explain slowly changing dimensions (SCD Types 1, 2, 3)

Slowly Changing Dimensions handle how to track changes to dimension data in data warehouses. Type 1 overwrites old data (no history). If a customer's address changes, overwrite it. Simple but you lose history.

Type 2 adds a new row with a new surrogate key and dates (full history). When customer_address changes, insert a new row with is_current = true, set the old row's is_current = false. This preserves all historical data but doubles your rows.

Type 3 adds columns for the previous value (limited history). Add current_address and previous_address columns. Simple and compact but stores only one prior value.

Use Type 1 for dimensions where history doesn't matter (product descriptions). Type 2 for critical dimensions (customer address matters for order fulfillment history). Type 3 for quick comparisons between current and previous.

13. How would you design a schema for an e-commerce platform?

Start with core entities: customers (id, name, email, address), products (id, name, price, category_id, stock), categories (id, name), orders (id, customer_id, order_date, total, status), order_items (id, order_id, product_id, quantity, unit_price), payments (id, order_id, method, status), reviews (id, product_id, customer_id, rating, text).

Add supporting tables: addresses (billing, shipping separately), inventory_logs (track stock changes), coupons (discounts), wishlist (customer product saves).

Key decisions: Store unit_price in order_items (historical price), not just product_id. Separate addresses table (customers have multiple). Track inventory changes (audit trail). Handle payment status separately (order may complete before payment confirms). Add soft-delete columns (is_active) for regulatory compliance.

Indexes: Primary keys on all tables. Foreign key columns. product_id in order_items. customer_id in orders. status columns (orders, payments). order_date for range queries. This schema scales from tens of thousands to millions of orders with proper indexing.

14. What is the difference between OLTP and OLAP?

OLTP (Online Transaction Processing) handles day-to-day operations: inserting orders, updating inventory, processing payments. Optimized for write speed, consistency, and small queries. Highly normalized. Examples: production databases for e-commerce, banking.

OLAP (Online Analytical Processing) analyzes historical data: sales trends, customer behavior, revenue forecasts. Optimized for read speed on large datasets. Denormalized (star/snowflake schemas). Examples: data warehouses, business intelligence.

OLTP uses row-based storage; OLAP uses columnar. OLTP enforces ACID strictly; OLAP prioritizes speed over immediate consistency. OLTP needs backups and recovery; OLAP can be rebuilt from OLTP.

Design decision: Don't run analytics on production OLTP databases (they'll slow down transactions). Extract, transform, and load (ETL) data to a separate OLAP warehouse for reporting.

Performance & Optimization (6 Questions)

15. How do indexes work? When should you NOT use an index?

Indexes are data structures (typically B-trees) that sort column values and store pointers to rows. They enable fast lookups without scanning the entire table. Creating an index on customer_id in orders means finding orders for a specific customer in milliseconds instead of seconds.

DON'T index: (1) Low-cardinality columns (gender, status with few values) — sequential scan is often faster, (2) Columns with many NULLs, (3) Small tables (< 10k rows), (4) Columns updated frequently (index maintenance overhead), (5) Columns in WHERE clauses with OR (indexes work best with AND).

The tradeoff: indexes speed up reads but slow down writes (INSERT/UPDATE/DELETE must update the index). A heavily-written table with few reads shouldn't have many indexes. A reporting table with millions of rows and mostly reads needs strategic indexes.

Index types: single-column (fast for simple lookups), composite (order matters: WHERE user_id AND created_at), partial (INDEX WHERE status = 'active'), full-text (for text search).

16. Explain EXPLAIN ANALYZE output — what to look for

EXPLAIN shows the query plan (how PostgreSQL will execute the query). EXPLAIN ANALYZE actually runs the query and shows real execution stats. Look for: (1) Seq Scan vs Index Scan (scans are slow on large tables), (2) rows estimates vs actual (big differences mean outdated stats), (3) total time and planning time.

Red flags: sequential scans on large tables, high estimated rows with few actual rows (missing WHERE filters), nested loops with high row counts (consider a hash join), Filter clauses after Seq Scan (apply these in the WHERE instead).

Example: If a query estimates 1 million rows but returns 100, the optimizer chose a bad plan. Run ANALYZE (refreshes statistics) and try again. If a Seq Scan shows high cost on orders (millions of rows), add an index on the WHERE column.

Cost units are arbitrary but comparable: Index Scan with cost 0.1 is much better than Seq Scan with cost 1000. Lower total cost is better.

17. What is a query execution plan?

A query execution plan is the database's roadmap for executing a query. It shows which tables to scan, in what order, what joins to use (Nested Loop, Hash, Sort-Merge), and what filters to apply.

The optimizer chooses the plan based on statistics (table sizes, index info, column distributions). It tries multiple plans and estimates the cost (time) of each, picking the cheapest.

Key plan nodes: Seq Scan (full table scan), Index Scan (fast lookup), Nested Loop Join (compare every row from left with every row from right — slow if large), Hash Join (build hash table of smaller set, probe with larger — fast), Sort (expensive if no index), Filter (apply WHERE after the scan).

A good plan scans small result sets, uses indexes where possible, and pushes filters as early as possible. A bad plan scans large tables, uses nested loops on millions of rows, or applies filters after gathering huge intermediate result sets.

18. How would you optimize a slow query?

Step 1: Run EXPLAIN ANALYZE to understand the execution plan. Identify sequential scans on large tables and high-cost operations.

Step 2: Check if indexes exist on WHERE columns. Add an index if it's missing (single-column or composite). Verify column statistics are fresh (run ANALYZE).

Step 3: Rewrite the query: push filters into WHERE (not HAVING), avoid functions on indexed columns (WHERE DATE(created_at) = ... won't use an index on created_at), break complex logic into CTEs or subqueries for readability and optimizer clarity.

Step 4: Check for missing JOINs (Cartesian products creating huge result sets). Denormalize if a normalized query is still slow after indexing. Use materialized views for frequently-run aggregates.

Step 5: Monitor: track slow queries with query logs, set thresholds (alert if > 1 second), use tools like pg_stat_statements (PostgreSQL) to find the worst offenders.

19. What are materialized views and when to use them?

A materialized view is a query result saved as a physical table. Unlike regular views (which recalculate on every query), materialized views are computed once and stored. This trades freshness for speed.

Use for: (1) Complex aggregates (monthly sales by region) run frequently, (2) Reporting views queried by many users, (3) Views on large tables where the aggregated result is small, (4) Data that doesn't need to be real-time (hourly or daily refresh is acceptable).

Cost: Storage (duplicate data), refresh overhead (must recalculate periodically), potential staleness (users see yesterday's data). Not suitable for real-time dashboards.

Example: A view calculating total_orders and avg_order_value per customer. Materialized view stores these pre-computed. Refresh nightly when traffic is low. Dashboards query the materialized view (instant), not the raw orders table (slow).

20. Explain connection pooling

Connection pooling maintains a pool of pre-opened database connections reused by multiple application requests. Opening a connection is expensive (handshake, authentication, setup). Pooling eliminates this overhead for every query.

Without pooling: 1000 requests = 1000 connection opens = slow. With pooling: 1000 requests share 10-20 pooled connections = fast. Once a query finishes, the connection returns to the pool for the next request.

Benefits: reduced latency (no open delay), better resource utilization (connections are shared), easier connection limit management (limit pool size, not incoming requests). Tools: PgBouncer (PostgreSQL), HikariCP (Java), SQLAlchemy with pool (Python).

Configuration: set pool_size (connections to maintain), max_overflow (temporary connections under load), timeout (how long to wait for a connection). Tune based on workload: high-concurrency apps need larger pools; low-traffic apps need smaller pools.

PostgreSQL Specific (4 Questions)

21. What is JSONB and when would you use it over a regular column?

JSONB is PostgreSQL's binary JSON data type. Unlike TEXT storing raw JSON, JSONB parses and indexes the data, enabling fast queries inside JSON documents. You can query nested fields directly without parsing in your application.

Use JSONB for: (1) Semi-structured data (one field has variable attributes per record), (2) Deep nesting (metadata, config documents), (3) Frequent queries inside JSON (e.g., WHERE metadata->>'color' = 'red'), (4) Avoiding schema changes (add fields to JSON instead of ALTER TABLE).

Don't use JSONB for: (1) Highly structured data (better as normalized columns), (2) Frequent sorting/filtering (denormalized columns are faster), (3) Complex transactions (ACID guarantees weaker for JSON fields).

Example: user_profile table with a metadata JSONB column storing {preferences: {theme: dark}, location: {city: SF}}. Query: WHERE metadata->'preferences'->>'theme' = 'dark'. Index: CREATE INDEX ON user_profile USING GIN (metadata) for fast JSON searches.

22. Explain Row-Level Security (RLS)

Row-Level Security restricts rows a user can access based on database policies. Instead of trusting application code to filter data, the database enforces it. A salesman sees only their own customer records; a manager sees their team's records; an admin sees all.

Policies are set per table and executed before any query. Even if a user runs SELECT *, they only get their permitted rows. The policy can reference user info (current_user), session variables, or custom functions.

Enable with: ALTER TABLE customers ENABLE ROW LEVEL SECURITY; CREATE POLICY sales_own_customers ON customers FOR SELECT USING (sales_person_id = current_user_id);.

Benefits: enforces data isolation at the database level (survives application bugs), audit trail (all access logged), compliance (GDPR, HIPAA separation). Downside: harder to debug, can reduce query performance if policies are complex.

23. What are PostgreSQL triggers and stored functions?

A trigger is a function that runs automatically when a table is modified (INSERT, UPDATE, DELETE). A stored function is reusable code in the database. Triggers call functions.

Use triggers for: (1) Maintaining audit logs (INSERT into audit table on every UPDATE), (2) Enforcing business rules (prevent deleting active users), (3) Cascading updates (if product price changes, update all active orders), (4) Calculated columns (update updated_at timestamp on every write).

Stored functions are useful for: (1) Complex calculations used in multiple queries, (2) Parameterized operations, (3) Reducing network round-trips (execute 100-row calculation in the database, return the result).

Downside: triggers hide logic from application code (harder to debug), stored functions require DBAs to maintain database code alongside application code. Use sparingly; most business logic belongs in the application.

24. How does VACUUM work in PostgreSQL?

VACUUM is PostgreSQL's garbage collection. When rows are updated or deleted, the old row versions remain on disk (PostgreSQL uses MVCC — multiversion concurrency control). VACUUM reclaims this space and maintains the visibility map (tracks pages with no dead rows, speeding up scans).

Autovacuum runs periodically (default every ~1 minute) and reclaims space from heavily-modified tables. You can run VACUUM ANALYZE to also refresh table statistics (helps the query optimizer).

If autovacuum is slow and space bloats, manually run VACUUM FULL (aggressive, locks the table, repacks data) or increase autovacuum frequency (decrease autovacuum_naptime).

Too much bloat degrades performance: sequential scans read dead rows, indexes grow, disk fills. Monitor with: SELECT last_vacuum, last_autovacuum FROM pg_stat_user_tables. Heavy UPDATE/DELETE tables need frequent vacuuming.

NoSQL & MongoDB (3 Questions)

25. When would you choose MongoDB over PostgreSQL?

Choose MongoDB when: (1) Your schema evolves rapidly (no fixed columns, BSON documents are flexible), (2) Data is highly nested or variable structure (user profiles with different attributes per user), (3) Horizontal scaling is critical (MongoDB shards easily), (4) You need ultra-fast writes (MongoDB trades consistency for write speed).

PostgreSQL is better when: (1) Data integrity is critical (ACID guarantees), (2) Complex relationships between entities (JOINs), (3) Complex queries and reporting, (4) You have a fixed, well-defined schema.

Example: An IoT sensor platform with variable device attributes. MongoDB shines (add new sensor types without schema migration). An e-commerce platform with orders, customers, payments. PostgreSQL shines (ACID transactions, complex queries).

Modern approach: Polyglot persistence. Use both. PostgreSQL for core business logic. MongoDB for flexible documents (user activity logs, feature flags, configurations).

26. Explain the MongoDB aggregation pipeline

The aggregation pipeline processes documents through stages: $match (filter rows), $project (select fields), $group (aggregate with COUNT, SUM, AVG), $sort, $limit, $lookup (join), $unwind (explode arrays), $addFields (computed fields).

Stages run in order, each outputting to the next. This is MongoDB's version of SQL GROUP BY + JOIN + ORDER BY combined. The pipeline is very efficient — early $match stages filter documents before expensive grouping.

sql
27. What is the CAP theorem?

The CAP theorem states a distributed system can guarantee at most two of three properties: Consistency (all nodes see the same data), Availability (system always responds), Partition tolerance (system survives network failures).

PostgreSQL (single-server) is CA: consistent and available, but can't survive network partitions. MongoDB and Cassandra are AP: available and partition-tolerant, but sacrifice consistency (eventual consistency — data eventually syncs). Some systems are CP: consistent and partition-tolerant, but slow/unavailable during partitions.

In practice, network partitions are rare, so the choice is between strong consistency (CP, like PostgreSQL) or high availability (AP, like MongoDB). Financial systems choose consistency. Social media chooses availability.

Redis & Caching (3 Questions)

28. Explain the cache-aside pattern

Cache-aside (lazy loading): check the cache first. If found (cache hit), return it. If not found (cache miss), fetch from the database, store in cache, then return.

Steps: (1) Request comes for user_profile(5), (2) Check Redis for key "user:5", (3) Not found, query database, (4) Store result in Redis with TTL (time-to-live, e.g., 1 hour), (5) Return to user. Next request for user_profile(5) hits Redis instantly.

Advantages: simple, flexible TTL (cache items age out), no coupling between cache and database. Disadvantages: cache misses are slower (database round-trip), cache can become stale (old data in cache after database update).

Implementation: Before database queries, check cache. On cache miss, query DB and cache result. On data change (UPDATE/DELETE), invalidate cache (DELETE key from Redis). This prevents serving stale data.

29. What Redis data structure would you use for a leaderboard? Why?

Use a Sorted Set (ZSET). It maintains elements with scores, automatically sorted by score descending. Operations like "top 10" are O(log N), very fast.

Commands: ZADD leaderboard 100 "player1", ZADD leaderboard 150 "player2". Query: ZRANGE leaderboard 0 9 WITHSCORES (top 10), ZRANK leaderboard "player1" (rank of player1), ZINCRBY leaderboard 50 "player1" (add 50 points).

Alternatives: Hash (store all scores, but ranking is slow — requires sorting all), String (serialized JSON, slow to update a single score). Sorted Set is built for leaderboards.

Scaling: Sorted Sets scale to millions of elements. For millions of players and frequent updates, use Redis Cluster (sharding). One master instance can handle ~100k updates/second.

30. How do you handle cache invalidation?

Cache invalidation is notoriously hard. Strategies: TTL (Time To Live) — cache expires automatically (simple, but data can be stale), Explicit invalidation — delete cache when data changes (complex but fresh, requires coordinating database updates with cache deletions), Event-driven — publish event when data changes, subscribers invalidate cache (decoupled, scalable).

Practice: Use short TTLs for frequently-changing data (session: 5 min), long TTLs for stable data (product catalog: 1 day). For critical data (user balance), invalidate explicitly: UPDATE balance = X WHERE user_id = Y; DELETE FROM cache WHERE key = 'balance:Y'.

Pattern: Cache + versioning. Store cache:user:5:v1, then v2, v3 as data changes. Old code uses v1, new code uses v3. Gradually transition (no hard invalidations).

Monitoring: track cache hit rate (Redis INFO stats). High hit rate (>80%) means caching works. Low hit rate means TTL is too short or patterns are ineffective.

Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

Ready to ace your database engineering interview?

Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Data Engineer →