# SQL Interview Situational Questions: Part 2 Six more SQL interview situational scenarios — deadlocks, stakeholder pushback, messy upstream data, zero-downtime migrations, security concerns, and conflicting metrics. - Author: SqlInt team - Published: 2026-07-09T09:00:00+00:00 - Updated: 2026-09-15T10:49:56.912877+00:00 - Canonical: https://sqlint.com/articles/sql-interview-situational-questions-part-2 - Category: Interview Prep - Tags: situational-interview, behavioral-questions, sql-interview, career, interview-strategy --- ## Picking Up Where Part 1 Left Off Our first situational questions guide covered the core framework and six scenarios, each with a full sample answer written the way you'd actually say it in an interview. This second set covers six more that show up just as often — concurrency incidents, scope disagreements, messy upstream data, zero-downtime migrations, discovering a security issue mid-task, and conflicting metric definitions between teams. ## 7. "Two processes are deadlocking against each other in production. How do you approach it?" What they're testing: Whether you understand concurrency at a practical level, not just the textbook definition of a deadlock. Sample answer: "First I'd find out whether this is a one-off under unusual load or something happening repeatedly, because those call for pretty different responses. Then I'd pull whatever deadlock information the database logs — Postgres writes this to its log, SQL Server has a deadlock graph, MySQL has SHOW ENGINE INNODB STATUS — to see exactly which two transactions were involved and what locks each was waiting on. Most of the time the root cause is that two code paths are acquiring the same tables or rows in a different order, so I'd look for that specifically and fix it by enforcing one consistent access order across the codebase, rather than trying to tune it away at the database level. I'd also make sure the application already retries on a deadlock-victim error, because deadlocks are a normal, expected part of running concurrent transactions — the goal isn't to make them impossible, it's to make sure the app recovers cleanly when one happens." See our transactions and ACID guide for the underlying locking mechanics. ## 8. "A stakeholder asks for a solution you believe is technically the wrong approach. What do you do?" What they're testing: Whether you can push back constructively without simply refusing or silently complying with something you think is wrong. Sample answer: "I'd first make sure I actually understand what they're trying to achieve, because often the specific solution they're asking for is just their best guess at solving a problem, not the problem itself. Once I understand the real goal, I'd explain my concern in terms of impact rather than technical purity — for example, saying this approach means the dashboard will always be a day stale, rather than just saying it's not best practice. I'd try to bring an alternative to the table at the same time as the concern, since people respond a lot better to 'here's another way to get what you want' than to just being told no. If, after all that, they still want to go with their original approach, I'd implement it professionally and just make sure the tradeoff I raised is documented somewhere, because at that point it's a legitimate call for them to make, and I've done my job by flagging it clearly." ## 9. "You're building a pipeline on top of upstream data that's often incomplete or inconsistently formatted. How do you handle it?" What they're testing: Defensive data engineering habits, since almost no real-world data source is as clean as an interview dataset. Sample answer: "I wouldn't just silently drop or coerce bad rows, because that hides real problems and makes debugging later much harder. I'd explicitly flag or quarantine anything that doesn't meet expectations — missing required fields, malformed dates, values outside a sane range — so there's a trail of what got excluded and why. I'd be deliberate about NULL handling throughout, since a lot of subtle bugs come from an aggregate silently ignoring NULLs, or a NOT IN filter unexpectedly returning nothing because of one NULL in a subquery. I'd also build the pipeline so it's safe to re-run, because eventually the upstream source will get fixed and I'll need to reprocess a batch without duplicating anything. And I wouldn't just quietly work around bad data forever — I'd flag the data quality issue to whoever owns the upstream system, because otherwise there's no pressure on them to actually fix it." The NULL-handling traps mentioned here are covered in full in our NULL handling guide, and the re-run safety pattern in our idempotent SQL guide. ## 10. "You need to add a column or change a data type on a large production table with no downtime allowed. How do you approach it?" What they're testing: Real production migration experience, since a naive ALTER TABLE can lock a busy table for an unacceptable amount of time. Sample answer: "It depends a lot on what the change actually is. Adding a new nullable column is usually a fast, metadata-only operation in most modern databases, so that's low risk. But changing an existing column's type, or adding a NOT NULL constraint with a default value to a huge existing table, can require rewriting the whole table, which is where the real risk is. For something like that, I'd add the new column as nullable first, backfill the data in small batches so I'm not holding one long lock, and only add the NOT NULL constraint once the backfill is fully done. On Postgres specifically, I'd also use NOT VALID when adding a new foreign key or check constraint, since that skips the expensive validation scan up front and lets me validate it separately afterward. And before running any of this against production, I'd test the actual timing against a copy of the table that's genuinely production-sized, because something that's instant on a small staging table can behave completely differently at real scale." See our constraints guide for more on NOT VALID and constraint validation. ## 11. "While doing a routine task, you notice something that looks like a security or privacy problem — for example, data access that's way broader than it should be. What do you do?" What they're testing: Judgment and integrity, not a specific SQL skill. Sample answer: "I would report it immediately through whatever the right channel is — my manager, a security team, an established process — rather than trying to quietly fix it myself or just finishing my original task and ignoring it. I also wouldn't go poking around further than what's needed to confirm the issue is real, since digging into sensitive data beyond that isn't necessary and could make things worse. This is one of those situations where doing the technically clever thing matters a lot less than just handling it responsibly and making sure the right people know quickly." ## 12. "Two teams have dashboards showing different numbers for what should be the same metric. How do you resolve it?" What they're testing: Whether you can resolve a definitional disagreement, not just assume it's a bug. Sample answer: "Before assuming either dashboard has a bug, I'd check whether the two teams are actually computing the same thing in the first place, because a lot of these mismatches come down to a different definition of something like 'active user,' a different date range, or one team excluding internal test accounts and the other not. I'd trace each dashboard back to its actual underlying query and compare them side by side rather than just comparing the final numbers. Once I find the actual difference, I'd propose a single shared definition going forward, ideally backed by one shared view both teams query from instead of each maintaining their own separate version. And I'd document that agreed definition somewhere durable, so the same disagreement doesn't come back the next time someone new builds a dashboard from scratch." See our views guide for how a shared view enforces a single source of truth. ## Common Mistakes Specific to This Second Set - Treating a deadlock as something to eliminate entirely rather than handle gracefully with retries — shows a gap in practical concurrency experience. - Either silently complying or flatly refusing when disagreeing with a stakeholder's requested approach, instead of explaining impact and offering an alternative. - Assuming a metric mismatch between teams is a bug before checking whether the definitions themselves actually differ. - Underestimating migration risk on a large production table — assuming a schema change that's instant on a small test table will behave the same way at production scale. - Trying to personally resolve a security/privacy concern instead of escalating it through the right channel — this is treated as a serious miss in most interview loops, not a minor stylistic preference. ## Frequently Asked Questions ### Are these scenarios asked in technical rounds or separate behavioral rounds? Both, depending on the company — some weave a situational follow-up directly into a technical SQL question ("and what would you do if this needed to run with zero downtime?"), while others dedicate a separate round specifically to judgment and collaboration scenarios. ### Is there a "correct" answer to these questions? Less than for a syntax question — interviewers are evaluating the soundness of your reasoning and communication as much as the specific final answer. A well-reasoned approach that reaches a slightly different conclusion than the interviewer expected is usually viewed far more favorably than a confident but unexamined answer. ## Practice the Full Picture Situational judgment is best built on top of real technical fluency — the two reinforce each other. Strengthen the underlying SQL with our SQL practice questions, and apply both together in realistic, messy scenarios in our case studies. For the first six scenarios and the core answering framework, see Part 1 of this guide. Want to practice reading someone else's SQL for bugs? Continue to Part 3: "What's Wrong With This Query?". --- Source: https://sqlint.com/articles/sql-interview-situational-questions-part-2