# Database and SQL interview questions for software engineers Practise writing and explaining queries on realistic schemas, then move to the reasoning interviewers probe: what an index costs on writes, how isolation levels change correctness, how to read a slow query's plan, and how you would model a new feature's data. Say the trade-off out loud, not just the keyword. About 6 min read. ## On this page - [Practise queries on a realistic schema, not trivia](https://preprole.com/guides/database-sql-interview-questions#query-practice) - [Explain what an index buys and what it costs](https://preprole.com/guides/database-sql-interview-questions#indexes) - [Talk about transactions as correctness guarantees](https://preprole.com/guides/database-sql-interview-questions#transactions) - [Narrate how you would diagnose a slow query](https://preprole.com/guides/database-sql-interview-questions#slow-query) - [Worked example: finding each customer's latest order](https://preprole.com/guides/database-sql-interview-questions#worked-example) - [Model a new feature's data aloud](https://preprole.com/guides/database-sql-interview-questions#design) - [Build a query-then-reason rehearsal loop](https://preprole.com/guides/database-sql-interview-questions#practice-plan) ## Practise queries on a realistic schema, not trivia SQL interview questions usually involve joins across a few related tables, aggregation with grouping and filtering, and one wrinkle such as a self-join, a window function, or deduplication. Practise on a small but plausible schema, such as users, orders, and payments, and write the query before reading any solution. The interviewable skill is narrating the query as you build it: which table anchors the result, what each join adds, and where rows are filtered versus aggregated. Run your queries for real. A plausible-looking query that fails on an edge case costs more credibility than a slower, correct one you can explain. - Joins: inner versus outer and what each preserves - Aggregation: GROUP BY, HAVING versus WHERE, and NULL behaviour - Window functions: ranking, running totals, and deduplication - Subqueries and CTEs: choose for readability, not cleverness ## Explain what an index buys and what it costs An index speeds up reads that can use its ordering, at the cost of extra storage and slower writes because every insert and update must maintain it. That trade-off sentence is the core of most indexing questions. From there, interviewers probe which columns belong in the index, whether a composite index's column order matches the query, and what a covering index is for. PostgreSQL's documentation describes the index types and when each applies, including the default B-tree and cases for other structures. Prepare to explain why an index on a low-cardinality column may not help and why an unindexed filter over a large table turns into a sequential scan. Say what you would verify in a query plan rather than claiming an index always helps. Reference: [PostgreSQL documentation: indexes](https://www.postgresql.org/docs/current/indexes.html) ## Talk about transactions as correctness guarantees Transaction questions test whether you understand what concurrent access breaks. Explain atomicity, isolation, and the anomalies each level prevents in practical terms: a lost update, a non-repeatable read, a phantom. Candidates who map isolation levels to the bugs they prevent answer better than those who recite acronyms. Prepare one concrete scenario: two concurrent transfers from the same balance, or two bookings for the same slot. Walk through what goes wrong without a transaction and what your chosen isolation level or locking strategy guarantees. If the interviewer asks about performance, acknowledge that stronger isolation costs throughput and explain the trade-off you accepted. ## Narrate how you would diagnose a slow query A common practical question is: this query is slow, what do you do? Answer with a process, not a guess. Reproduce it, look at the query plan, and identify where the database is doing work the query does not need: a sequential scan over a filtered column, a join order that blows up intermediate results, or a function on an indexed column that defeats the index. Then discuss the fix and its cost. An index helps reads and hurts writes; denormalising helps the query and adds update complexity; a materialised view helps at the cost of staleness. The interviewer is checking that you treat diagnosis as evidence-gathering, not as reaching for the most familiar optimisation. ## Worked example: finding each customer's latest order A typical interview question: for each customer, return their most recent order. Several correct answers exist. A window function ranks each customer's orders by date and picks the first; a join against a per-customer MAX(order_date) aggregate achieves the same; a correlated subquery works but can be slower at scale. Naming the options and choosing one with a reason is the evaluated behaviour. Explain your choice in terms of the data. If orders are indexed by customer and date, the correlated lookup can be efficient; if you already need the whole result set, the window function is often clearer. Then mention the edge cases the interviewer will probe: ties on the same timestamp, customers with no orders, and what the query returns for each. > I would use a window function: rank each customer's orders newest first and filter to rank one, which handles ties predictably and stays readable. If the table is large, I would check that an index on customer and order date supports it, and I would confirm whether customers with no orders should appear at all before choosing a LEFT JOIN. ## Model a new feature's data aloud Schema-design questions ask you to model something small, such as a booking system or a tagging feature. Start with the entities and the queries the feature must answer, then sketch tables, keys, and constraints. Normalise by default and denormalise only when you can name the read that needs it. State the constraints the design enforces: uniqueness, foreign keys, and the invariants the database guarantees versus the ones the application must. Interviewers reward designs that make wrong states hard to write, not schemas that assume callers behave. ## Build a query-then-reason rehearsal loop Alternate two drills. First, write queries against a real database until joins, aggregation, and one window function feel routine. Second, rehearse spoken explanations of your schema and query decisions for a project you worked on, including the ones you would now change. The second drill is the one candidates skip, and it is what interviews grade. A correct query with no explanation scores lower than a correct query whose trade-offs you can defend. Practise saying the reasoning while you write. ## Frequently asked questions ### Do I need to know a specific database? Use the one the job post names if you can defend it; otherwise practise on a mainstream relational database and say so. Concepts transfer. What does not transfer is claiming deep familiarity with a database you have not used. ### Are NoSQL questions common? They come up when the role uses a document store, key-value store, or similar. Prepare the comparison honestly: what you gain in flexibility and scale characteristics versus what you give up in queryability and transactional guarantees. ### How deep do transactions and isolation questions go? Usually to the level of naming an anomaly and explaining which level prevents it. Prepare the practical mapping rather than the formal taxonomy, and be ready to discuss one real concurrency bug you have seen or could construct. ### What if I freeze on a query mid-interview? Narrate what you know: the tables, the join keys, and the result shape. Ask whether your direction is right rather than going silent. Interviewers often help a candidate who is communicating and correct a drifting one. ## Practise the next answer Use a Technical Deep-Dive Mock Session to rehearse explaining a schema you designed and a query problem you solved. Where a session includes the Code Pad, the AI Interviewer reads it but never executes code, so write and run SQL against a real database in your own environment before the interview. Practise for your job: https://preprole.com/setup ## Sources and scope Employer sources describe their own processes, which may change. The exercises and illustrative examples are PrepRole editorial guidance, not employer questions or hiring criteria. - [PostgreSQL documentation: indexes](https://www.postgresql.org/docs/current/indexes.html) - [Amazon Careers: software development interview topics](https://amazon.jobs/content/en/how-we-hire/interview-prep/software-development-topics) ## Related guides - [Place the data layer in the wider backend picture](https://preprole.com/guides/backend-engineering-interview-preparation) - [Prepare the live problem-solving format](https://preprole.com/guides/coding-interview-preparation) - [Rehearse defending your own data decisions](https://preprole.com/guides/technical-deep-dive-interview) AI feedback can be wrong. A practice Verdict is not a hiring decision.