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.

Database Management Systems (DBMS) Module 5

SQL semantics: how queries really evaluate interview questions

Interview questions on “SQL semantics: how queries really evaluate”: warm-ups, then what each harder question tests and how a good answer starts.

18 questions: 6 warm-ups and 12 with model answers · Levels: Fresher to senior

Warm-up questions

Short questions with short answers, each answered in full.

  1. Fresher

    A table has CHECK (score BETWEEN 0 AND 100). Does inserting a row with a NULL score succeed, and why does the same condition behave differently in a WHERE clause?

    Answered in the lesson Three-valued logic: how SQL reasons about NULL

  2. Fresher

    What is the difference between a correlated and an uncorrelated subquery, and how does a database run each?

    Answered in the lesson Subqueries: IN, EXISTS, ANY and ALL

  3. Fresher

    What is the difference between a view and a materialized view?

    Answered in the lesson Views, updatable views and materialized views

  4. Fresher

    What is a recursive CTE, and what makes it stop?

    Answered in the lesson Recursive queries: WITH RECURSIVE and Datalog

  5. Fresher

    What is the difference between a stored procedure and a function in PostgreSQL?

    Answered in the lesson Triggers, stored procedures and functions

  6. Fresher

    What is SQL injection, and how do parameterised queries prevent it?

    Answered in the lesson Access control, row-level security, SQL injection

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 dashboard counts open orders with WHERE status <> 'cancelled' and shows 18,400. Finance counts 19,050 orders that are not cancelled in the same table. You find 650 orders whose status is NULL. Explain the gap with three-valued logic, then fix the query and the schema so that it cannot happen again.

    What it tests
    Applying three-valued logic to a real filter, deciding what a missing value means for the business, and fixing the schema with NOT NULL, a default and a check rather than patching one query.
    First step
    Evaluate the condition for a NULL status: NULL <> 'cancelled' is UNKNOWN, and WHERE keeps only TRUE rows, so all 650 orders drop out, which is exactly the gap between 19,050 and 18,400. Before changing anything, find out what a NULL status means: an order still being created, a failed import, or something else.
    Follow-ups

    From the lesson Three-valued logic: how SQL reasons about NULL

    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; nothing renews unless you choose to. 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 3 minutes

  2. Mid to senior

    An optimiser may rewrite a condition only if every row, NULLs included, gets the same result. Which rewrites are safe: NOT (a > 5) to a <= 5; a = a to TRUE; p OR NOT p to TRUE; a NOT IN (SELECT b FROM t) to NOT EXISTS (SELECT 1 FROM t WHERE t.b = a)? Justify each.

    What it tests
    Reasoning about equivalence under three truth values, knowing which classical laws survive, and the NOT IN against NOT EXISTS difference that affects real query plans.
    First step
    Test every rewrite on the row where the risky value is NULL and compare the two sides. NOT (NULL > 5) is UNKNOWN and NULL <= 5 is UNKNOWN, so the first rewrite agrees; NULL = NULL is UNKNOWN, not TRUE, so the second does not. Then do the same for a NULL inside the subquery.
    Follow-ups

    From the lesson Three-valued logic: how SQL reasons about NULL

    In the answer:

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

  3. Fresher to mid

    A daily report lists customers without orders using WHERE id NOT IN (SELECT customer_id FROM orders). It showed about 1,200 customers every day; the morning after an import of guest checkouts it shows none, although nobody placed new orders. Explain what changed, fix the query, and say how you would stop it happening again.

    What it tests
    NOT IN under three-valued logic with a NULL in the subquery, NOT EXISTS as an anti-join, and preventing the bug with schema constraints and tests rather than only rewriting one query.
    First step
    Check the subquery for NULLs: SELECT count(*) FROM orders WHERE customer_id IS NULL. Guest checkouts have no customer, so the import likely added rows with a NULL customer_id. Then expand the condition for one customer: id <> 7 AND id <> 9 AND id <> NULL, which is FALSE or UNKNOWN, never TRUE.
    Follow-ups

    From the lesson Subqueries: IN, EXISTS, ANY and ALL

    In the answer:

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

  4. Mid to senior

    To find students who beat everyone in section B, a query uses marks > ALL (SELECT marks FROM results WHERE section = 'B'). A colleague rewrites it as marks > (SELECT max(marks) FROM results WHERE section = 'B'). When do the two return different students? Give each case, with the rows that differ, and the rewrite you would accept.

    What it tests
    The semantics of ALL with empty sets and NULLs, how max treats NULLs and empty input, and stating a rewrite's preconditions precisely instead of trusting a test run.
    First step
    Compare the two on the cases that differ. Empty section B: ALL over no rows is TRUE, so every student qualifies, while max of no rows is NULL and the comparison is UNKNOWN, so nobody does. A NULL mark in section B: ALL becomes UNKNOWN for students above the other marks, while max ignores the NULL.
    Follow-ups

    From the lesson Subqueries: IN, EXISTS, ANY and ALL

    In the answer:

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

  5. Mid to senior

    A dashboard runs a 9-second aggregate over a 40-million-row orders table on every page load, and the page is opened about 3,000 times an hour. Would you use a view, a materialized view or a summary table kept by triggers? Defend the choice and say how you would keep it fresh.

    What it tests
    Weighing freshness, read cost and write cost with numbers, knowing what REFRESH and REFRESH CONCURRENTLY lock and need, and planning the refresh and its failure modes.
    First step
    Work out the read load before choosing: 3,000 loads an hour at 9 seconds each is 27,000 seconds of query time every hour, so about 7.5 copies of the aggregate run at any moment. A plain view changes nothing, because it runs the same query on every read. Then ask how old the numbers may be: that decides between a scheduled refresh and a result maintained on every write.
    Follow-ups

    From the lesson Views, updatable views and materialized views

    In the answer:

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

  6. Mid to senior

    An application role may change only the names of CSE students, so it gets a view: SELECT roll, name, dept FROM students WHERE dept = 'CSE'. A bug moves students to other departments through the view, and an audit finds the role can read the whole table too. What went wrong, and how do you fix both?

    What it tests
    Knowing that a view's WHERE does not limit the values a change writes, WITH CHECK OPTION, column privileges on a view, and whose privileges PostgreSQL checks for the base table.
    First step
    Separate the two failures. The moves happened because the view has no check option: an UPDATE through it may set dept to anything, and the row simply leaves the view. The table was readable because the role had privileges on students itself, which creating a view does not take away.
    Follow-ups

    From the lesson Views, updatable views and materialized views

    In the answer:

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

  7. Mid to senior

    A recursive query that lists every subcategory under 'Electronics' ran in 40 ms for months. After a bulk import of categories it never finishes and fills the disk with temporary files. What probably happened, how do you confirm it, and how do you make both the query and the data safe?

    What it tests
    Recognising a cycle in the data a recursive query walks, why UNION ALL or a depth column then loops, finding the cycle safely, and fixing the query, the database settings and the import.
    First step
    Suspect a cycle: the import made some category its own ancestor. With UNION ALL, or with UNION and a depth column, every lap around it produces new rows, so the working table never empties. Confirm it by running the recursion with a path column and a depth limit, and listing the rows whose next category is already on the path.
    Follow-ups

    From the lesson Recursive queries: WITH RECURSIVE and Datalog

    In the answer:

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

  8. Fresher to mid

    Why can relational algebra not express 'every city reachable from Chennai by any number of trains', and what do WITH RECURSIVE and Datalog add? Where does SQL restrict recursion, and why?

    What it tests
    Seeing that every algebra expression has a fixed number of joins, transitive closure as a least fixed point, semi-naive evaluation, and why aggregates and self-negation are refused.
    First step
    Count joins. One train is a selection on routes, two trains a join of routes with itself, and k trains need k − 1 joins. An algebra expression has a fixed number of joins, so it sees trips only up to a fixed length, and data with a longer chain defeats it.
    Follow-ups

    From the lesson Recursive queries: WITH RECURSIVE and Datalog

    In the answer:

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

  9. Mid to senior

    A nightly job updates 20 million rows of an accounts table. Since a row-level AFTER UPDATE audit trigger was added, the job takes three times as long. How do you find out where the time goes, and what would you change?

    What it tests
    Measuring trigger cost with EXPLAIN ANALYZE, the per-row call and the end-of-statement event queue, and choosing between WHEN conditions, transition tables and changing the job.
    First step
    Measure on a sample before changing anything: run the job's UPDATE on about 100,000 rows inside a transaction with EXPLAIN (ANALYZE), then roll it back. The output has one line per trigger with its calls and time, which separates the trigger's cost from the update's.
    Follow-ups

    From the lesson Triggers, stored procedures and functions

    In the answer:

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

  10. Mid to senior

    Your team wants to move order placement into the database: a trigger checks stock, a function computes the price with discounts, and an AFTER INSERT trigger sends the confirmation email. Argue for and against, and say what you would put where.

    What it tests
    Separating invariants from business rules and side effects, side effects that cannot roll back, races in read-then-write checks, and the pending-messages pattern.
    First step
    Sort the steps by what must hold for every writer and what is a business process. Stock never going below zero is an invariant the database can enforce in the order's own transaction; discount rules change often and need tests and releases; an email is a side effect outside the database that cannot be rolled back.
    Follow-ups

    From the lesson Triggers, stored procedures and functions

    In the answer:

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

  11. Mid to senior

    A SaaS app keeps 300 schools in shared PostgreSQL tables and isolates them with row-level security on current_setting('app.school_id'). Review the design: what can still leak one school's data to another, and how would you test the isolation?

    What it tests
    Who bypasses row-level security, why the setting must come from trusted code, pooling and SET LOCAL, views and integrity checks as side doors, and testing as the application role.
    First step
    List the paths around the policies before reading them: roles that bypass row-level security (superusers, BYPASSRLS, owners without FORCE), code that can change app.school_id, pooled connections that keep the last request's setting, and queries that reach the rows another way, such as views owned by the table owner.
    Follow-ups

    From the lesson Access control, row-level security, SQL injection

    In the answer:

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

  12. Fresher to mid

    A reporting page lets users choose the sort column and type a filter. A penetration test pulls other customers' email addresses through it. How did that probably work, and how do you fix both the filter and the sort?

    What it tests
    Recognising injection through a value and through a name, how injected SQL reads other tables, parameters for values, allowlists for names, and least privilege as damage control.
    First step
    Find where input reaches the SQL text. The filter was probably pasted into a WHERE clause, so a quote ended the string and appended SQL could read the customers table; the sort column was pasted into ORDER BY, where no placeholder can go. Read the code that builds the query, then reproduce the attack on a test copy.
    Follow-ups

    From the lesson Access control, row-level security, SQL injection

    In the answer:

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

All Database Management Systems (DBMS) interview questions · The Database Management Systems (DBMS) track