MySmartCoPilot
Ask Passes Your pass 7 days left 1 day left Last day

Your country

Tools that support it use your country for local currency, number formats, units and paper size. Your choice is saved only in this browser.

SQL (PostgreSQL, MySQL, SQLite) Module 18

Using SQL from application code interview questions

Interview questions on using SQL from application code: warm-ups, then what each harder question tests and how a good answer starts.

18 questions: 5 warm-ups and 13 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

  1. Fresher to mid

    What does with conn: do for an sqlite3 connection, and how is psycopg 3 different?

    Answered in the lesson SQL from Python: sqlite3 and psycopg

  2. Fresher

    Why does conn.execute("... WHERE city = ?", ("Pune")) fail, and how do you pass one value?

    Answered in the lesson SQL from Python: sqlite3 and psycopg

  3. Fresher to mid

    In JDBC, when do you use a Statement and when a PreparedStatement?

    Answered in the lesson SQL from Node.js and Java

  4. Fresher to mid

    What is lazy loading in an ORM, and what is eager loading?

    Answered in the lesson ORMs, query builders and migration tools

  5. Fresher to mid

    When a test checks a query's rows, why compare them as a multiset rather than as a list or a set?

    Answered in the lesson Testing SQL queries and data quality

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

  1. Fresher to mid

    A small web app stores sign-ups with Python's sqlite3. Users see their new account straight away, but after the app restarts some accounts are missing. The code runs INSERTs with conn.execute() on one shared connection. What is happening, how do you fix it, and how would a test have caught it?

    What it tests
    sqlite3's implicit transactions in its default mode, why a connection sees its own uncommitted rows, committing and closing correctly, and testing persistence with a second connection.
    First step
    Ask whether the code ever calls commit(). In sqlite3's default mode an INSERT opens a transaction implicitly, and nothing is saved until commit(). The same connection reads its own uncommitted rows, so users see their account, but a restart closes the connection without a commit and SQLite rolls the work back.
    Follow-ups

    From the lesson SQL from Python: sqlite3 and psycopg

    Opening the model answers with your pass…

    Model answers open with any MySmartCoPilot pass.

    • Pro pass · 1 week₹79$2.99
    • 30 days₹179$5.99
    • 1 year₹1,699$45

    One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.

    Choose a passI have a licence key

    It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 2 minutes

  2. Mid to senior

    After a deploy, a Node.js API on node-postgres slowly stops answering: after an hour every request hangs, while the PostgreSQL server shows almost no activity. Restarting the API fixes it for another hour. What do you suspect, how do you confirm it, and what do you change?

    What it tests
    Recognising a connection leak, the pool's defaults (10 clients, no wait limit), reading pool counts and pg_stat_activity, and fixing release with finally.
    First step
    A healthy database plus requests that hang suggests the requests are waiting for the pool, not for the database. node-postgres keeps at most 10 clients by default and waits for ever for an idle one. Check the pool's totalCount, idleCount and waitingCount, then look in the deploy's diff for a code path that can return or throw between pool.connect() and client.release().
    Follow-ups

    From the lesson SQL from Node.js and Java

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  3. Fresher to senior

    An order history page takes 40 ms on a developer's laptop and 1.6 seconds in production, where the database is in another zone with a 30 ms round trip. The page lists 50 orders with their items. What is the likely cause, how do you prove it, and how do you fix it?

    What it tests
    Recognising N+1 from the gap between local and remote latency, counting statements per request, choosing between a join and an IN list, and testing for the pattern.
    First step
    Divide the time by the round trip: 1.6 s at 30 ms is about 53 round trips, which matches one query for the orders plus one per order. On the laptop each round trip costs microseconds, so the loop was invisible. Prove it by counting the statements one request sends in the driver's log.
    Follow-ups

    From the lesson SQL from Node.js and Java

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  4. Fresher to mid

    A Java service saves an order and then its items with JDBC PreparedStatements. Now and then the reports show orders with no items at all. The code catches SQLException, logs it and returns an error to the client. What is wrong, and what does a correct version look like?

    What it tests
    JDBC's auto-commit default, grouping statements in one transaction, rollback on failure, try-with-resources, and repairing data that is already inconsistent.
    First step
    Ask how the connection is set up: a new JDBC connection commits every statement on its own, so the order row is committed before the first item is inserted. When an item fails, the catch block logs the error, but the order is already saved. The order and its items must be one transaction.
    Follow-ups

    From the lesson SQL from Node.js and Java

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 2 minutes

  5. Mid to senior

    A list page of 200 orders got slow after a release that only changed its template, to show each order's customer name and city. The ORM's log now shows 201 statements per request instead of 1. How do you find the cause, fix it, and stop it from happening again?

    What it tests
    Reading an ORM's SQL log, recognising lazy loading in a template, choosing an eager-loading strategy for a many-to-one relation, and guarding the page with a statement-count test.
    First step
    Group the 201 statements by shape: one loads the orders, and 200 load a customer by its key, one per order. The template reads order.customer for every row, and the relation is loaded lazily, so each read is a query. The query that builds the list must load the customers too.
    Follow-ups

    From the lesson ORMs, query builders and migration tools

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  6. Mid to senior

    A developer fixed a wrong default value by editing migration 0042, which had already run in production last week, and the fix works on their laptop. Today the production deploy stops because the migration tool reports that 0042 has changed. What went wrong, how do you get the deploy moving, and what rule do you set?

    What it tests
    Why migration tools record applied migrations and their checksums, rolling forward with a new migration, repairing the history safely, and keeping environments on the same schema.
    First step
    Recognise that the tool is protecting you: production ran the old 0042, so editing the file cannot change production's schema, and the edited file now disagrees with what the history table says ran. Restore 0042 exactly as it was, and put the fix in a new migration, 0043, that changes the default.
    Follow-ups

    From the lesson ORMs, query builders and migration tools

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 2 minutes

  7. Senior to staff

    Your team writes all data access through an ORM. A monthly revenue report per category, with growth against the previous month, takes 40 seconds and sends about 12,000 queries. Someone proposes rewriting it in hand-written SQL. How do you decide, and if you rewrite it, how do you keep it safe and maintainable?

    What it tests
    Knowing what ORMs express badly, measuring before rewriting, set-based SQL with grouping and window functions, parameters in hand-written SQL, and testing a report.
    First step
    Measure first: 12,000 queries in 40 seconds means the report loops over rows in code and the time goes into round trips, not into any one query. A report that groups by month and category and compares each month with the one before is one query with GROUP BY and a window function such as lag(), which an ORM expresses poorly.
    Follow-ups

    From the lesson ORMs, query builders and migration tools

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  8. Mid to senior

    A sales report's SQL has unit tests that run on SQLite, and all of them pass. On PostgreSQL in production, one query fails with "must appear in the GROUP BY clause", and another lists the rows with no region last instead of first. Explain both, fix them, and say what you change in the tests.

    What it tests
    Differences between SQLite and PostgreSQL that tests on the wrong engine hide: bare columns in GROUP BY and the default position of NULL in ORDER BY, and running tests on the production engine.
    First step
    Both are engine differences, not bugs the tests could have caught on SQLite. SQLite lets a column that is neither grouped nor aggregated take a value from some row of the group, and PostgreSQL refuses it. SQLite sorts NULL before other values in ascending order, and PostgreSQL after them. The tests need PostgreSQL.
    Follow-ups

    From the lesson Testing SQL queries and data quality

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  9. Mid to senior

    After last quarter's orders were imported from an old system, the revenue dashboard shows almost twice the figure the finance team has. Nobody changed the dashboard's query. How do you find the cause with SQL, repair the data and make sure it cannot happen again?

    What it tests
    Using data-quality checks to locate duplicates, reconciling with a source total, repairing data safely in a transaction, and making loads idempotent with keys and constraints.
    First step
    Reconcile before guessing: compare row counts and totals per day between the dashboard's tables and the finance figures to find where they diverge. A ratio close to 2 suggests the import ran twice, so look for orders that appear more than once by their key from the old system.
    Follow-ups

    From the lesson Testing SQL queries and data quality

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 2 minutes

  10. Senior to staff

    You take over 40 reporting queries that feed finance dashboards and have no tests at all. You have two weeks before a database upgrade from PostgreSQL 16 to 18. How do you plan the testing, and what do you have in place at the end?

    What it tests
    Prioritising by risk, fixtures with edge cases, comparing results before and after on a frozen copy of the data, data-quality checks, and running everything on the target engine.
    First step
    Rank the queries by risk first: which ones produce money figures or feed decisions, and which ones use outer joins, grouping, window functions or nullable columns. Before touching any query, take a frozen copy of production and record every query's current result on version 16, so that you can compare results after the upgrade.
    Follow-ups

    From the lesson Testing SQL queries and data quality

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  11. Mid to senior

    A product manager used an AI assistant to write "revenue per category for last month" and put the query on a dashboard. The numbers look plausible but are 18 % above the finance team's. How do you review the query to find the cause, and how do you make sure the fixed version is right?

    What it tests
    Reviewing generated SQL against the schema and the business definition: fan-out, status filters, date ranges and time zones, and proving a fix with a fixture and a reconciliation.
    First step
    Pin down the definition first: which orders count as revenue, net of what, in which time zone. Then read the joins for fan-out, because a total that is too high is most often rows counted twice: an order joined to its items and to its payments or shipments at once.
    Follow-ups

    From the lesson Reviewing SQL written by AI assistants

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  12. Mid to senior

    A developer asked an assistant to "delete the test accounts created last week" and ran its DELETE on production. About 30 rows should have gone; 40,000 did. How can a plausible DELETE do that, how do you recover, and what rules would have stopped it?

    What it tests
    Operator precedence and pattern traps in generated conditions, recovery from backups or point-in-time restore, and safe execution: read-only exploration, transactions and row counts.
    First step
    Read the WHERE clause for the two usual causes: an OR without brackets, which binds more loosely than AND and widens the condition, and a LIKE pattern such as '%test%' that also matches real names and addresses. Then stop further writes to the table and find the newest backup or the point-in-time recovery window.
    Follow-ups

    From the lesson Reviewing SQL written by AI assistants

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 3 minutes

  13. Senior to staff

    Your team wants to use AI assistants to write SQL for reports, ad hoc fixes and schema migrations. As the lead, what rules do you set, how do you enforce them, and what do you leave open?

    What it tests
    Setting rules by risk, separating read and write access, tests and review for generated SQL, data protection, and keeping the rules light enough to be followed.
    First step
    Sort the work by what a wrong query can damage: a read-only report can mislead, an ad hoc fix can destroy data, a migration can break every environment. Then give each class its rail: read-only roles for exploration, counted transactions and a second reviewer for fixes, and the usual migration pipeline for schema changes.
    Follow-ups

    From the lesson Reviewing SQL written by AI assistants

    In the answer:

    • Model answer
    • Follow-up answers
    • Common mistakes
    • Rubric
    • Variants
    • about 2 minutes

All SQL (PostgreSQL, MySQL, SQLite) interview questions · The SQL (PostgreSQL, MySQL, SQLite) track