### 🏦 The Fintech Database Engineering Frontier When you are designing database architectures for digital banks, neobanks, or financial software, the margin for error is absolute zero. Unlike regular applications where a slow page load is just a minor annoyance, in fintech systems, a missing lock, a race condition, or an unindexed query can lead to lost money, regulatory penalties, or a total system crash. Fintech system design is where database engineering meets rigorous corporate compliance. In this comprehensive handbook, we have curated a master database of **50+ hard-hitting banking database interview questions** categorized into 10 high-value architectural blocks. Whether you are looking to clear a backend engineering loop at Stripe, Google, or Chase, or designing your next secure payment ledger, this is your ultimate blueprint. --- ### 🗄️ PART 1: Database Design & Data Modeling #### 1. Design a schema for a bank with customers, accounts, transactions, and loans. Explain relationships and constraints. A standard core-banking database requires separation of user identities from balance states: * **Customers:** Stores identity meta (`customer_id` [PK], `full_name`, `dob`, `tax_id`). * **Accounts:** Linked many-to-one to Customers (`account_id` [PK], `customer_id` [FK], `account_type`, `current_balance`, `status`). * **Transactions:** Holds balance mutation entries (`transaction_id` [PK], `account_id` [FK], `amount`, `transaction_type` (deposit, withdrawal, transfer), `timestamp`). * **Loans:** Tracks principal assets (`loan_id` [PK], `customer_id` [FK], `principal_amount`, `interest_rate`, `tenure_months`, `outstanding_balance`). * **Loan Payments:** Many-to-one with loans (`payment_id` [PK], `loan_id` [FK], `due_date`, `paid_amount`, `status`). * **Safety Constraints:** Add Check constraints on balances (`current_balance >= 0`), unique constraints on tax IDs, and set foreign key relations to `ON DELETE RESTRICT` to prevent orphan transaction records! #### 2. How would you handle multiple currencies in a banking system? Never store monetary values as loose floats without explicit currency context. To build a multi-currency asset: * Maintain a currency exchange rate registry table (`currency_code`, `exchange_rate_to_base`, `updated_at`). * Each bank account must explicitly reference a foreign currency ID (e.g., `USD`, `EUR`). * Every balance transaction ledger should store three matching attributes: `amount_in_original_currency`, `currency_code`, and `amount_in_base_currency` calculated based on a historical exchange rate snapshot captured at transaction runtime. #### 3. Design a ledger with double-entry accounting. What are the key tables? In standard corporate accounting, money is never created or destroyed; it is transferred from one ledger account to another. Debits must equal Credits: * **Chart of Accounts:** (`account_code` [PK], `name`, `account_type` (asset, liability, equity, income, expense)). * **Journal Entries:** Groups double-entry operations together to ensure atomicity (`journal_entry_id` [PK], `timestamp`, `description`, `transaction_reference`). * **Ledger Lines:** (`line_id` [PK], `journal_entry_id` [FK], `account_code` [FK], `debit` (money in), `credit` (money out)). * **Sanity Rule:** Every committed journal transaction must strictly satisfy: `SUM(debit) = SUM(credit)`. #### 4. How do you model a joint account (two customers sharing one account)? Avoid storing multiple customer ID columns directly inside your accounts table (e.g., `customer_id_1`, `customer_id_2`). That design breaks normalization. * Instead, create an **Account Owners Bridge Table** (`account_id` [FK], `customer_id` [FK], `ownership_percentage`, `is_primary_holder`). This forms an elegant many-to-many relationship, letting you attach infinite co-owners, secondary signatories, or power-of-attorney profiles to any single bank account. #### 5. Explain how you would track historical changes to a customer’s address (SCD Type 2). For compliance and KYC (Know Your Customer) tracking, we cannot simply overwrite existing addresses when a user relocates. * We use a **Slowly Changing Dimension Type 2 (SCD2)** schema. * The customer address history table is configured with columns: `address_id`, `customer_id`, `street_address`, `effective_from` (timestamp), `effective_to` (timestamp), and `is_current` (boolean). * When the customer moves, we run an atomic database transaction that updates the current row's `effective_to` to the current timestamp and toggles `is_current = false`, then appends a new record with the updated address details, setting `effective_from = current_timestamp`, `effective_to = NULL`, and `is_current = true`. --- ### 📊 PART 2: SQL Queries (Basic to Advanced) #### 6. Write a query to get the total balance of all accounts for each customer. ```sql SELECT c.customer_id, c.full_name, SUM(a.current_balance) AS total_balance FROM customers c INNER JOIN accounts a ON c.customer_id = a.customer_id GROUP BY c.customer_id, c.full_name; ``` #### 7. Find customers who have not performed any transaction in the last 30 days. ```sql SELECT c.customer_id, c.full_name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM transactions t INNER JOIN accounts a ON t.account_id = a.account_id WHERE a.customer_id = c.customer_id AND t.transaction_date >= CURRENT_DATE - INTERVAL '30 days' ); ``` #### 8. Write a query to calculate the running balance for a specific account. We utilize SQL Window aggregate functions to calculate the progressive cumulative asset balance: ```sql SELECT transaction_date, amount, transaction_type, SUM(CASE WHEN transaction_type IN ('withdrawal','fee') THEN -amount ELSE amount END) OVER (ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_balance FROM transactions WHERE account_id = 101; ``` #### 9. Rank customers by total deposit amount (highest first) using a window function. ```sql SELECT c.customer_id, SUM(t.amount) AS total_deposits, RANK() OVER (ORDER BY SUM(t.amount) DESC) AS deposit_rank FROM customers c INNER JOIN accounts a ON c.customer_id = a.customer_id INNER JOIN transactions t ON a.account_id = t.account_id WHERE t.transaction_type = 'deposit' GROUP BY c.customer_id; ``` #### 10. Retrieve the month-wise closing balance for all accounts in a given year. We isolate the final running balance entry for each partition block using CTEs: ```sql WITH monthly_closing AS ( SELECT account_id, DATE_TRUNC('month', transaction_date) AS month, LAST_VALUE(running_balance) OVER (PARTITION BY account_id, DATE_TRUNC('month', transaction_date) ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS closing_balance FROM ( SELECT account_id, transaction_date, SUM(CASE WHEN transaction_type IN ('withdrawal','fee') THEN -amount ELSE amount END) OVER (PARTITION BY account_id ORDER BY transaction_date) AS running_balance FROM transactions ) sub ) SELECT DISTINCT account_id, month, closing_balance FROM monthly_closing WHERE EXTRACT(YEAR FROM month) = 2024 ORDER BY account_id, month; ``` #### 11. Find accounts where the current balance is negative. How would you prevent that? Identify negative account balances easily: ```sql SELECT account_id, current_balance FROM accounts WHERE current_balance < 0; ``` **Prevention:** Enforce database-level schema constraints: ```sql ALTER TABLE accounts ADD CONSTRAINT balance_non_negative CHECK (current_balance >= 0); ``` #### 12. Write a query to detect suspicious transfers: same customer transferring > 50% of their balance to another account in one day. ```sql WITH daily_transfers AS ( SELECT t.account_id, a.customer_id, t.amount, t.transaction_date FROM transactions t INNER JOIN accounts a ON t.account_id = a.account_id WHERE t.transaction_type = 'transfer' ) SELECT dt.customer_id, dt.account_id, dt.amount, a.current_balance, (dt.amount / a.current_balance) * 100 AS percent_balance FROM daily_transfers dt INNER JOIN accounts a ON dt.account_id = a.account_id WHERE dt.amount > 0.5 * a.current_balance AND dt.transaction_date = CURRENT_DATE; ``` --- ### 🔒 PART 3: Transactions, Concurrency, and ACID #### 13. Explain ACID properties in the context of a money transfer between two accounts. Let us break down a $100 transfer from Account A to Account B: * **Atomicity:** Either BOTH actions execute (Debit $100 from A AND Credit $100 to B), or NONE do. There can never be a halfway state where A's money vanishes but B never receives it. * **Consistency:** The total global money supply across the bank stays stable ($A + B = \text{Total}$). No transaction can leave Account A with a negative balance if checking constraints are active. * **Isolation:** If 1,000 other transfers are executing concurrently, none of them should see intermediate balance values until this transaction is committed. * **Durability:** Once the database returns a "COMMIT Success" confirmation, the record persists on non-volatile disks, even if a power blackout occurs immediately afterward. #### 14. Write a transaction that transfers $100 from account A to account B. Include error handling. We write structured PL/pgSQL database logic with transactional rollback blocks: ```sql BEGIN; UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 'A'; UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 'B'; -- Insufficient funds assertion check IF (SELECT current_balance FROM accounts WHERE account_id = 'A') < 0 THEN ROLLBACK; RAISE EXCEPTION 'Insufficient funds available'; ELSE COMMIT; END IF; ``` #### 15. What is a deadlock? Give an example in a banking system. A deadlock occurs when Transaction 1 holds Lock X and requests Lock Y, while Transaction 2 holds Lock Y and requests Lock X. Both processes wait forever: * **Scenario:** * T1: `UPDATE accounts SET balance = balance - 100 WHERE id = 'A'` (Acquires Row A Lock) * T2: `UPDATE accounts SET balance = balance - 50 WHERE id = 'B'` (Acquires Row B Lock) * T1: `UPDATE accounts SET balance = balance + 100 WHERE id = 'B'` (Waits for Row B Lock) * T2: `UPDATE accounts SET balance = balance + 50 WHERE id = 'A'` (Waits for Row A Lock $\rightarrow$ Deadlock!) * **Solution:** Always perform multi-row updates in a **strict ordered sequence** (e.g., sorting account IDs alphabetically or numerically before running locking statements). This prevents resource loops! #### 16. Which isolation level would you use for balance enquiry vs. money transfer? * **Balance Enquiry (Read-Only):** `READ COMMITTED` or `REPEATABLE READ`. This ensures the reader only accesses committed state data and prevents dirty reads, without putting long-lasting locks on the database. * **Money Transfer (Critical Mutation):** `REPEATABLE READ` or `SERIALIZABLE`. This completely eliminates phantom reads, lost updates, and write skew. If concurrent modification conflicts occur, the database rejects the conflicting action, prompting the application layer to execute a clean retry. #### 17. Explain SELECT FOR UPDATE. When would you use it in banking? In concurrent applications, if you read an account balance and then modify it within your app code, another connection could change the balance between your read and update steps (resulting in a **lost update**). * `SELECT ... FOR UPDATE` locks the queried rows until the current transaction completes. ```sql BEGIN; SELECT current_balance INTO v_balance FROM accounts WHERE id = 101 FOR UPDATE; IF v_balance >= 100 THEN UPDATE accounts SET current_balance = current_balance - 100 WHERE id = 101; END IF; COMMIT; ``` --- ### ⚡ PART 4: Performance & Query Optimization #### 18. How would you improve the performance of a daily report that scans millions of transactions? * **Partitioning:** Horizontal physical partition of the transaction tables by month or year. * **Indexes:** Creating composite indexes targeting query parameters (e.g., `(transaction_date, status)`). * **Materialized Views:** Using background aggregated materialized summaries that refresh during off-peak night cycles. * **Covering Indexes:** Writing select parameters matching exact index trees to prevent disk table reads. #### 19. What indexes would you create on the transactions table for the following queries? * *Get transactions for an account in range:* Composite index on `(account_id, transaction_date)`. * *Sum of deposits per customer per month:* Index on `(transaction_type, transaction_date)`. * *Find high-value transactions (> $10,000) in last week:* Partial index: ```sql CREATE INDEX idx_high_value_trans ON transactions (transaction_date) WHERE amount > 10000; ``` #### 20. Explain the difference between WHERE and HAVING. Give banking examples. * `WHERE` filters records *before* grouping occurs. * *Example:* Find transactions that occurred in the Engineering department: ```sql SELECT * FROM employees WHERE department_id = 10; ``` * `HAVING` filters aggregate summaries *after* grouping is executed. * *Example:* Find departments whose cumulative monthly employee payout exceeds $500,000: ```sql SELECT department_id, SUM(salary) FROM employees GROUP BY department_id HAVING SUM(salary) > 500000; ``` #### 21. You have a slow query joining customers, accounts, and transactions. How do you debug? 1. Prepend **`EXPLAIN (ANALYZE, BUFFERS)`** to check index usage, actual execution costs, and sequential scans. 2. Check for join types (e.g., hash joins, nested loops) and look for unindexed foreign keys. 3. Execute `ANALYZE table_name;` to verify query planner statistics are up to date. 4. Verify that index selections are SARGable and do not contain blocking operations (like column transformations). --- ### 📊 PART 5: Reporting & Analytics (Banking Specific) #### 22. Write a query to compute Year-Over-Year (YOY) monthly deposit growth. We combine SQL common table expressions with statistical window lags: ```sql WITH monthly_deposits AS ( SELECT DATE_TRUNC('month', transaction_date) AS month, SUM(amount) AS total_deposits FROM transactions WHERE transaction_type = 'deposit' GROUP BY DATE_TRUNC('month', transaction_date) ) SELECT month, total_deposits, LAG(total_deposits, 12) OVER (ORDER BY month) AS deposits_same_month_prev_year, (total_deposits - LAG(total_deposits, 12) OVER (ORDER BY month)) / NULLIF(LAG(total_deposits, 12) OVER (ORDER BY month), 0) * 100 AS yoy_growth_pct FROM monthly_deposits ORDER BY month; ``` #### 23. Calculate the NPA (Non-Performing Asset) ratio: total defaulted loans outstanding / total loan portfolio. ```sql SELECT SUM(CASE WHEN l.status IN ('default', 'written_off') THEN l.outstanding_principal ELSE 0 END) AS npa_amount, SUM(l.outstanding_principal) AS total_loan_portfolio, (SUM(CASE WHEN l.status IN ('default', 'written_off') THEN l.outstanding_principal ELSE 0 END) / NULLIF(SUM(l.outstanding_principal), 0)) * 100 AS npa_ratio FROM loans l WHERE l.status != 'paid'; ``` #### 24. Generate a daily balance sheet (assets = liabilities + equity) using ledger entries. ```sql SELECT entry_date, SUM(CASE WHEN account_type = 'asset' THEN debit - credit ELSE 0 END) AS total_assets, SUM(CASE WHEN account_type = 'liability' THEN credit - debit ELSE 0 END) AS total_liabilities, SUM(CASE WHEN account_type = 'equity' THEN credit - debit ELSE 0 END) AS total_equity FROM ledger_entries l INNER JOIN chart_of_accounts c ON l.account_code = c.account_code GROUP BY entry_date HAVING total_assets = total_liabilities + total_equity; ``` #### 25. Find the top 5 customers by interest income generated for the bank in the last quarter. ```sql SELECT c.customer_id, c.full_name, SUM(lp.interest_part) AS total_interest_paid FROM loan_payments lp INNER JOIN loans l ON lp.loan_id = l.loan_id INNER JOIN customers c ON l.customer_id = c.customer_id WHERE lp.paid_date >= DATE_TRUNC('quarter', CURRENT_DATE - INTERVAL '3 months') AND lp.status = 'paid' GROUP BY c.customer_id, c.full_name ORDER BY total_interest_paid DESC LIMIT 5; ``` --- ### 🛡️ PART 6: Security & Compliance #### 26. How would you store sensitive information like credit card numbers or CVV? * **CVV:** Under PCI-DSS compliance regulations, storing CVV records on local disks is **strictly prohibited**. Never capture, write, or log CVV parameters. * **Credit Cards (PANs):** Store credit cards as securely encrypted assets using AES-256 (via hardware security modules / HSM keys). If possible, replace raw card numbers with safe, non-decryptable tokens (tokenization) to prevent direct database exposibility. #### 27. Implement row-level security so that a teller can only see accounts for their branch. We configure row-level isolation rules at the engine tier: ```sql -- Enable Row Level Security on target table ALTER TABLE accounts ENABLE ROW LEVEL SECURITY; -- Establish custom filter policy CREATE POLICY teller_branch_policy ON accounts USING (branch_id = (SELECT branch_id FROM tellers WHERE user_id = current_user)); ``` #### 28. What is a SQL injection? Give an example in a banking login form and how to prevent it. SQL Injection occurs when untrusted user input is directly concatenated into database execution strings: * *Exploit string:* `' OR '1'='1` * *Result:* `SELECT * FROM users WHERE username = '' OR '1'='1' AND password = ''` (Bypasses security entirely). * *Prevention:* Always use strictly parameterized query definitions (Preprepared Statements). ```java PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE username = ?"); ps.setString(1, inputUsername); ``` #### 29. How would you audit who changed a customer’s address and when? We maintain complete non-modifiable history archives using trigger monitors: ```sql CREATE TABLE customer_audit ( action VARCHAR(10), old_address TEXT, new_address TEXT, changed_by VARCHAR(50), changed_at TIMESTAMP ); CREATE OR REPLACE FUNCTION log_address_change() RETURNS TRIGGER AS $$ BEGIN INSERT INTO customer_audit VALUES ('UPDATE', OLD.address, NEW.address, current_user, CURRENT_TIMESTAMP); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER audit_customer_address AFTER UPDATE ON customers FOR EACH ROW WHEN (OLD.address IS DISTINCT FROM NEW.address) EXECUTE FUNCTION log_address_change(); ``` --- ### ⚙️ PART 7: System Design & Scalability (Backend Context) #### 30. How would you design a banking system that supports millions of transactions per day? * **ACID Ledger Storage:** Use a robust, highly optimized relational engine (such as PostgreSQL or Oracle) for the core balance ledgers to maintain strict transactional consistency. * **Caching:** Place low-latency caching systems (like Redis) in front of balance lookups to resolve frequent status queries without overloading disk storage. * **Replication & Reading:** Configure multiple physical read-replicas, isolating analytical reports from the main write pipeline. * **Asynchronous Processing:** Feed non-critical auditing or telemetry operations to decoupled event pipelines (such as Apache Kafka). #### 31. Explain the difference between vertical and horizontal sharding. Which is more suited for accounts table? * **Vertical Sharding:** Splitting a table by columns. For example, storing personal information in one database table and transaction balances in another. * **Horizontal Sharding:** Splitting a table by rows across multiple databases (e.g., storing account IDs 1–1,000,000 in DB 1, and account IDs 1,000,001–2,000,000 in DB 2). * *Fintech Match:* For high-scale core-banking systems, **horizontal sharding** using a hash of the `customer_id` or `account_id` is ideal. This distributes data evenly, avoiding database bottlenecks during peak trading periods. #### 32. You are asked to generate end-of-day reports while the system live handles transactions. How do you avoid locking issues? 1. Set up read-only analytical replica instances that synchronize asynchronously. 2. Run analytical queries using low isolation barriers (such as `READ COMMITTED` or `MVCC` snapshot reads) to pull a consistent point-in-time state without acquiring locks on write tables. 3. Maintain partitioned transaction tables, running historical reports against completed, closed partitions (which do not receive live updates). #### 33. A customer’s account is showing incorrect balance due to a duplicate transaction. How would you fix it using SQL? Never simply run a raw `DELETE` or `UPDATE` statement on committed audit ledger rows. That breaks compliance tracking. * **Correct Way:** Perform a transactional rollback correction by writing a compensating transaction, then update the current account balance inside an atomic block: ```sql BEGIN; -- Record compensating transaction reversal INSERT INTO transactions (account_id, amount, transaction_type, description) VALUES (123, -500, 'correction', 'Reversal of duplicate deposit id 9999'); -- Adjust current balance UPDATE accounts SET current_balance = current_balance - 500 WHERE account_id = 123; COMMIT; ``` --- ### 💻 PART 8: Hands-On Coding (Whiteboard) #### 34. Write a stored procedure that automatically applies monthly interest to all savings accounts. ```sql CREATE OR REPLACE PROCEDURE apply_monthly_interest() LANGUAGE plpgsql AS $$ DECLARE acc RECORD; BEGIN FOR acc IN SELECT account_id, current_balance, interest_rate FROM accounts WHERE account_type = 'savings' AND status = 'active' LOOP UPDATE accounts SET current_balance = current_balance * (1 + (acc.interest_rate / 100 / 12)), last_interest_date = CURRENT_DATE WHERE account_id = acc.account_id; END LOOP; END; $$; ``` #### 35. Write a trigger that prevents a withdrawal if the account would go below the minimum balance (e.g., $100). ```sql CREATE OR REPLACE FUNCTION check_min_balance() RETURNS TRIGGER AS $$ BEGIN IF NEW.transaction_type = 'withdrawal' THEN IF (SELECT current_balance - NEW.amount FROM accounts WHERE account_id = NEW.account_id) < 100 THEN RAISE EXCEPTION 'Action Rejected: Account must maintain a minimum balance of $100.'; END IF; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_withdrawal_check BEFORE INSERT ON transactions FOR EACH ROW EXECUTE FUNCTION check_min_balance(); ``` --- ### 🧠PART 9: Scenario-Based & Open-Ended #### 36. How would you design a system to detect fraudulent patterns (e.g., multiple failed logins, then a large transfer)? We use a layered security architecture: * Maintain a real-time ledger auditing table tracking failed authorization events. * Implement event streaming pipelines (like Apache Flink) to parse login attempts in real time. * Calculate moving-window risk scores and flag accounts with high risk scores for manual security review before approving withdrawals. #### 37. A customer complains that a transaction appears twice in their statement. How would you investigate? Query the ledger to locate duplicate transactions based on identical timestamp windows, transaction amounts, and reference IDs: ```sql SELECT amount, transaction_date, reference_id, COUNT(*) FROM transactions WHERE account_id = 123 GROUP BY amount, transaction_date, reference_id HAVING COUNT(*) > 1; ``` #### 38. Explain how you would implement a loan amortization schedule purely in SQL. We leverage recursion inside SQL Common Table Expressions to calculate principal and interest repayments over the life of a loan: ```sql WITH RECURSIVE amortization(payment_no, opening_balance, payment, interest, principal, closing_balance) AS ( SELECT 1, principal_amount, (principal_amount * (interest_rate/12)) / (1 - POWER(1 + interest_rate/12, -tenure)), principal_amount * (interest_rate/12), (principal_amount * (interest_rate/12)) / (1 - POWER(1 + interest_rate/12, -tenure)) - (principal_amount * (interest_rate/12)), principal_amount - ((principal_amount * (interest_rate/12)) / (1 - POWER(1 + interest_rate/12, -tenure)) - (principal_amount * (interest_rate/12))) FROM loans WHERE loan_id = 456 UNION ALL SELECT payment_no + 1, closing_balance, payment, closing_balance * (interest_rate/12), payment - (closing_balance * (interest_rate/12)), closing_balance - (payment - (closing_balance * (interest_rate/12))) FROM amortization, (SELECT interest_rate, tenure FROM loans WHERE loan_id = 456) AS l WHERE payment_no < l.tenure ) SELECT * FROM amortization; ``` #### 39. What is the difference between INNER JOIN, LEFT JOIN, and CROSS JOIN in a banking context? * **INNER JOIN:** Returns matching records from both tables (e.g., listing only customers who have an open bank account). * **LEFT JOIN:** Returns all records from the left table, with matching records from the right table if they exist (e.g., listing all customers, even those who don't have an account yet). * **CROSS JOIN:** Returns the Cartesian product of both tables (e.g., multiplying a list of 1,000 customers by a list of 5 active loan products to generate promotional options). #### 40. How would you handle a situation where the database runs out of transaction log space during EOD batch? 1. Break massive batch updates into smaller, bite-sized commit cycles (such as committing every 5,000 rows) to allow the engine to recycle transaction log space. 2. Temporarily allocate more storage space to the write-ahead log (WAL) volume. 3. Use fast staging pipelines or unlogged intermediate tables for non-critical sorting operations before committing the final results. --- ### ⚡ PART 10: Quick Fire (Yes/No & Definitions) | Question Prompt | Short Technical Answer | | :--- | :--- | | Can a foreign key reference a non-primary key column? | **Yes**, as long as the targeted foreign column is marked with a `UNIQUE` constraint. | | Is NULL equal to NULL in standard SQL? | **No**. Standard comparisons evaluate to `UNKNOWN`. You must explicitly check using `IS NULL`. | | What does ON DELETE CASCADE do? | It automatically deletes dependent child rows when their parent record is deleted. | | Name three SQL aggregate functions. | `SUM()`, `AVG()`, `COUNT()`, `MAX()`, or `MIN()`. | | What is the difference between DELETE and TRUNCATE? | `DELETE` is a logging DML statement that filters specific rows. `TRUNCATE` is a non-logged DDL statement that resets table storage instantly. | | What is a materialized view? | A physical table that caches the results of a query and updates on demand. | | What is the purpose of VACUUM in PostgreSQL? | Reclaims storage by cleaning up dead rows left behind by updates or deletes. | | Can a table have multiple primary keys? | **No**. A table can only have one primary key constraint, though it can consist of multiple columns (composite primary key). | | What does the SQL COALESCE function do? | Returns the first non-NULL value from a list of arguments. | | Is index seek faster than index scan? | **Yes**. An index seek traverses direct node paths to match specific search filters, while an index scan reads the entire index tree. | --- ### 💡 Final Interview Prep Blueprint Mastering the technical interview loop requires more than just memorizing SQL syntax. You need to understand the architectural trade-offs behind your decisions: * **Consistency vs. Performance:** Know when to sacrifice speed to guarantee data integrity, and when read-replicas are appropriate. * **Execution Plans:** Practice using `EXPLAIN` on complex queries to quickly identify sequential scans and slow nested joins. * **Dialect-Specific Nuances:** Understand how different relational engines (PostgreSQL, MySQL, SQLite) handle indexing, locking, and transaction isolation.