SQL Interview Preparation

Interview SQL Questions: Practical Questions and Answers

Interview SQL questions test more than memory of commands. They reveal whether a candidate can translate a business question into reliable data logic, choose the right tables and joins, handle duplicates and NULL values, explain trade-offs, and verify that the result actually answers the question. A technically valid query can still be wrong when its assumptions do not match the business definition.

This guide is designed for candidates preparing for analyst, data, engineering, reporting, business-intelligence, and database roles. It is also useful for hiring managers who need a structured way to evaluate SQL reasoning rather than rewarding memorized syntax. The examples use broadly recognizable SQL, but exact functions and clauses can vary across MySQL, PostgreSQL, SQL Server, Oracle, BigQuery, Snowflake, and other systems.

The strongest preparation method is to practise complete problem solving: clarify the output, inspect the data model, write the query in stages, test edge cases, discuss performance, and explain how the result would be used. That approach makes your answer easier to trust and shows the communication discipline required in real data work.

Quick Answer: How to Prepare for Interview SQL Questions

Prepare in layers. First, become fluent in SELECT, WHERE, ORDER BY, DISTINCT, GROUP BY, HAVING, and the main join types. Next, practise subqueries, common table expressions, CASE expressions, date logic, string handling, NULL behavior, and window functions. Finally, add query optimization, indexes, transactions, normalization, and database-specific features relevant to the role.

During an interview, do not rush directly into code. Restate the requested output, ask about ambiguous business definitions, identify keys and relationships, and mention how duplicates, missing values, date boundaries, and ties should be handled. Write readable SQL, then explain how you would validate it with a small sample and an independent check.

For hiring teams, evaluate reasoning and verification as carefully as syntax. A candidate who asks precise questions, exposes assumptions, and tests the output may be more effective than someone who produces a compact query without understanding the data.

Key Takeaways

• SQL interviews test reasoning: correct syntax matters, but business interpretation, data quality, and validation determine whether the answer is usable.
• Fundamentals carry the most weight: joins, aggregation, filters, and NULL handling appear in both basic and advanced problems.
• Explain before optimizing: establish correctness first, then discuss indexes, execution plans, data volume, and alternative designs.
• State assumptions: define what counts as a customer, order, active user, duplicate, current period, or tie.
• Use staged queries: CTEs can make complex logic easier to read, test, and discuss even when another form may later be optimized.
• Validate results: compare counts, inspect sample rows, check totals, and test edge cases instead of trusting the first output.
• Know the dialect: identify whether the interview uses MySQL, PostgreSQL, SQL Server, Oracle, or another platform.

What This Page Covers

• Core SQL concepts and the interview questions attached to them.
• Practical query examples for joins, aggregation, ranking, deduplication, and time-based analysis.
• A repeatable method for solving live SQL problems.
• Common mistakes and how to correct them.
• Guidance for beginner, intermediate, and advanced preparation.
• Evaluation criteria for hiring managers and interview panels.
• Ways specialist data support can help teams design realistic assessments and improve reporting workflows.

Table of Contents

1. How this guide was prepared
2. What SQL interviews actually assess
3. Foundational SQL questions and answers
4. Intermediate SQL questions
5. Advanced and performance questions
6. A step-by-step answer method
7. Practical SQL exercises
8. How hiring teams can evaluate answers
9. Common mistakes
10. Final preparation checklist

How this SQL interview guide was prepared

The guide is based on practical data-analysis, reporting, database, and hiring considerations. It prioritizes questions that expose how a person thinks about relational data, not merely whether they remember a keyword. The examples are intentionally compact so the underlying reasoning is visible.

SQL standards and database implementations evolve, and organizations use different schemas, naming conventions, data volumes, and governance controls. Candidates should confirm the requested database dialect and hiring teams should adapt examples to their own environment. Authoritative vendor documentation remains the best reference for exact syntax and engine behavior. Useful starting points include the official documentation for PostgreSQL, MySQL, Microsoft SQL Server, and Oracle Database.

What SQL interviews actually assess

A well-designed SQL interview assesses five connected abilities: relational understanding, query construction, business interpretation, data-quality awareness, and communication. Different roles place different weight on each area, but strong candidates usually demonstrate all five.

An interview should not confuse speed with competence. In real work, careful clarification often prevents more errors than fast typing. Hiring teams can still use time limits, but the scoring model should reward transparent reasoning and correct handling of ambiguity.

Foundational interview SQL questions and answers

1. What is the logical order of SQL query processing?

A useful conceptual order is FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and then LIMIT or its platform equivalent. This helps explain why a SELECT alias may not be available in WHERE and why filtering before aggregation differs from filtering after aggregation. Database optimizers can physically execute work differently, but the logical order helps candidates reason about results.

2. What is the difference between WHERE and HAVING?

WHERE filters individual rows before aggregation. HAVING filters groups after GROUP BY. For example, to find customers with more than five completed orders, WHERE first removes non-completed orders, GROUP BY forms one group per customer, and HAVING keeps groups whose count exceeds five.

SELECT customer_id, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY completed_orders DESC;

3. Explain INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN

INNER JOIN keeps matching rows from both sides. LEFT JOIN keeps every left-side row and supplies NULL values when no right-side match exists. RIGHT JOIN is the mirror image, although many teams prefer rewriting it as LEFT JOIN for consistency. FULL OUTER JOIN keeps matched and unmatched rows from both sides where the database supports it.

The deeper interview issue is cardinality. Joining a customer table to an orders table changes the result from one row per customer to potentially many rows per customer. Aggregating after that join can inflate unrelated measures unless the candidate first controls the grain.

4. How does SQL handle NULL?

NULL represents an unknown or missing value, not zero and not an empty string. Comparisons such as column = NULL do not behave as expected; use IS NULL or IS NOT NULL. Aggregates also differ: COUNT(*) counts rows, while COUNT(column) ignores NULL values. A complete answer should mention three-valued logic and explain how NULL affects joins, filters, concatenation, and ordering in the chosen database.

5. What is the difference between UNION and UNION ALL?

UNION combines compatible result sets and removes duplicates. UNION ALL combines them without duplicate removal and is usually less expensive. Choose UNION only when duplicate elimination is required by the business definition; do not use it as a hidden repair for a faulty join.

6. What are primary and foreign keys?

A primary key uniquely identifies a row in a table. A foreign key references a candidate or primary key in another table and helps enforce referential integrity. Good answers also distinguish a natural key from a surrogate key and explain that a declared relationship does not automatically prevent every business-level duplicate.

Intermediate SQL interview questions

7. What is a common table expression?

A CTE is a named result set defined with WITH and referenced by the following statement. It is useful for making stages explicit, such as identifying eligible orders, aggregating customer totals, and then ranking customers. A CTE is primarily a readability and organization tool; performance characteristics vary by database and version.

8. How do subqueries differ from joins?

Both can express related logic. A join combines row sets, while a subquery can produce a scalar value, a list, or a derived table used by the outer query. EXISTS is often a clear way to ask whether a related row exists without introducing duplicates. The best choice depends on intent, readability, optimizer behavior, and the required output.

9. What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

ROW_NUMBER assigns a unique sequence even when values tie. RANK gives tied rows the same rank and leaves gaps afterward. DENSE_RANK gives ties the same rank without gaps. The interviewer should specify whether tied top performers must all be retained or whether exactly one row is required.

SELECT employee_id, department_id, salary,
       DENSE_RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS salary_rank
FROM employees;

10. How do you find the second-highest salary?

The safest answer first asks whether duplicate salary values count as one rank. When the goal is the second-highest distinct salary, DENSE_RANK is explicit. A scalar subquery using MAX below the maximum can also work, but window functions generalize more easily to top-N questions and preserve related row data.

11. How do you remove duplicate records?

First define duplicate according to business columns. Then rank rows within each duplicate group, usually by recency, source priority, or completeness, and keep one row. Deleting should be performed only after reviewing the candidate set and protecting referential integrity.

WITH ranked AS (
  SELECT customer_id, email, updated_at,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY updated_at DESC, customer_id DESC
         ) AS row_num
  FROM customers
)
SELECT customer_id, email, updated_at
FROM ranked
WHERE row_num = 1;

12. How do you calculate a running total?

Use an aggregate window function with an ordered frame. The frame definition matters when multiple rows share the same ordering value. A robust answer specifies whether the total should advance by transaction, day, or another grain.

SELECT account_id, transaction_date, transaction_id, amount,
       SUM(amount) OVER (
         PARTITION BY account_id
         ORDER BY transaction_date, transaction_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_balance
FROM transactions;

13. How do you compare a value with the previous row?

Use LAG to retrieve a prior value within a defined partition and order. For month-over-month revenue, aggregate to one row per month first, then apply LAG. Otherwise, the query may compare individual transactions rather than monthly totals.

Advanced SQL and performance questions

14. What is an index and when can it help?

An index is a data structure that helps the database locate or order rows without scanning the entire table in the same way. It can help selective filters, joins, uniqueness checks, and some ordering or grouping patterns. It also consumes storage and adds work to writes. A strong answer connects the proposed index to a query and confirms effectiveness through an execution plan rather than assuming every index is beneficial.

15. What makes a query slow?

Common causes include scanning too much data, missing or ineffective indexes, non-selective predicates, functions applied to indexed columns, poor join order estimates, data-type mismatches, unnecessary sorting, repeated subqueries, wide intermediate results, parameter sensitivity, stale statistics, and contention. The correct diagnosis starts with the database's execution-plan and runtime tools.

16. What is normalization?

Normalization organizes data to reduce redundancy and update anomalies. First normal form requires atomic values and a consistent row structure. Second and third normal forms address dependencies on parts of keys and non-key attributes. Interviews should not treat normalization as an absolute target: analytical systems may deliberately denormalize for read patterns, but that trade-off should be explicit and governed.

17. What are ACID transactions?

Atomicity means a transaction completes fully or not at all. Consistency means valid rules remain satisfied. Isolation controls how concurrent transactions observe one another. Durability means committed changes survive failures according to the system's guarantees. Advanced answers can compare isolation levels and anomalies such as dirty reads, non-repeatable reads, and phantom reads.

18. How would you optimize a top-N-per-group query?

Start with the correct window-function logic, then inspect data volume, partition size, ordering columns, filters, and the execution plan. Apply restrictive predicates early where valid, select only needed columns, consider an index aligned with partition and order columns, and verify whether the platform offers a more specialized approach. Optimization must preserve tie and NULL behavior.

19. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes selected rows and can usually use a WHERE clause. TRUNCATE removes all rows using a database-specific operation with different logging, identity, locking, and transaction behavior. DROP removes the database object itself. Because behavior varies by engine, candidates should avoid universal claims about rollback or triggers and identify the target platform.

20. How would you design a query for incremental data processing?

Define a reliable watermark such as an ingestion timestamp, sequence, or change-tracking token. Handle late-arriving and updated records, make the load idempotent, record processing state, and include a reconciliation process. A simple updated_at > last_run filter may miss records when timestamps are delayed, duplicated, or changed during a run.

A step-by-step method for answering live SQL questions

1. Restate the output: describe the required columns and the intended row grain.
2. Clarify definitions: ask what counts as active, completed, unique, current, retained, or converted.
3. Identify source tables: state the table grain, keys, and relevant relationships.
4. Plan the stages: filtering, joining, aggregation, windowing, and final presentation.
5. Write readable SQL: use meaningful aliases and avoid compressing everything into one unreadable expression.
6. Test edge cases: missing matches, NULL values, duplicate events, ties, empty periods, and boundary dates.
7. Validate: compare row counts and totals, inspect examples, and use an alternative calculation when possible.
8. Discuss scale: only after correctness, mention indexes, partitioning, selective filters, and execution plans.

Practical SQL interview exercises

Exercise 1: Customers with no orders

Question: Return every customer who has never placed an order.

SELECT c.customer_id, c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

The key idea is an anti-join. NOT EXISTS is another strong answer and can be clearer because it directly expresses absence. The candidate should explain why filtering a right-side column in WHERE after a LEFT JOIN is intentional here.

Exercise 2: Top three products in each category

Question: Rank products by completed-order revenue within each category and return the top three positions.

WITH product_revenue AS (
  SELECT p.category_id, p.product_id,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM products AS p
  JOIN order_items AS oi ON oi.product_id = p.product_id
  JOIN orders AS o ON o.order_id = oi.order_id
  WHERE o.status = 'completed'
  GROUP BY p.category_id, p.product_id
), ranked AS (
  SELECT category_id, product_id, revenue,
         DENSE_RANK() OVER (
           PARTITION BY category_id
           ORDER BY revenue DESC
         ) AS revenue_rank
  FROM product_revenue
)
SELECT category_id, product_id, revenue, revenue_rank
FROM ranked
WHERE revenue_rank <= 3
ORDER BY category_id, revenue_rank, product_id;

The interviewer should ask how ties should be handled. DENSE_RANK can return more than three products when several share a rank. ROW_NUMBER returns exactly three rows per category but requires a deterministic tie-breaker.

Exercise 3: Monthly retention

Question: For each signup month, calculate the percentage of users who performed a qualifying action in the following calendar month.

This problem is valuable because syntax is not the hardest part. The candidate must define signup cohort, time zone, qualifying action, duplicate events, month boundaries, and whether users can belong to only one cohort. A robust solution first creates one row per user with a cohort month, creates distinct user-month activity, joins cohort members to the following month, and divides retained users by cohort size using safe numeric arithmetic.

Exercise 4: Detect overlapping subscriptions

Question: Find customers whose subscription date ranges overlap.

The candidate should self-join subscriptions for the same customer, prevent a row from matching itself, and use a consistent interval rule. For closed intervals, two ranges overlap when the first start is on or before the second end and the second start is on or before the first end. The interviewer should ask whether end dates are inclusive, whether NULL means ongoing, and how duplicate pairs will be prevented.

Exercise 5: Explain a wrong result

Question: A revenue report doubled after joining orders to support tickets. Why?

Both tables may contain multiple rows per customer or order, creating a many-to-many multiplication. The fix is not DISTINCT by default. Aggregate each source to the required grain before joining, or join through a uniquely keyed relationship. This question exposes whether the candidate understands grain and cardinality—two of the most important concepts in production SQL.

How hiring teams can evaluate SQL answers

A consistent scorecard improves fairness and makes interviewer feedback more useful. The panel should decide in advance which signals are essential for the role and which are optional.

For take-home exercises, provide a data dictionary, realistic sample data, expected deliverables, time guidance, and a clear policy on external references and AI assistance. Avoid assignments that reproduce unpaid production work. Score the explanation and tests alongside the final query.

Common SQL interview mistakes and how to avoid them

• Writing before clarifying: ask about output grain, time period, duplicate rules, and business definitions.
• Using SELECT *: return only required columns unless exploration is the stated purpose.
• Forgetting join cardinality: check whether each key is unique and whether joins multiply rows.
• Using DISTINCT as a repair: investigate why duplicates exist instead of hiding them.
• Ignoring NULL: test missing values explicitly and know how aggregates treat them.
• Assuming order: use ORDER BY when result order matters.
• Mixing grains: aggregate each source to the intended level before combining measures.
• Over-optimizing too early: establish correctness, then use evidence from execution plans.
• Giving dialect-specific syntax silently: name the database and mention alternatives when relevant.
• Skipping validation: explain how you would prove the output is complete and accurate.

Three realistic preparation scenarios

Scenario 1: A junior data analyst candidate

The candidate has used spreadsheets and basic SQL but struggles with joins. The best preparation is not a list of 200 questions. It is repeated practice on a small commerce schema: customers, orders, order items, products, and returns. Each exercise should require a stated output grain and a row-count check. Once joins and aggregation are stable, add window functions and date-based metrics.

Scenario 2: A data engineer interviewing for a high-volume platform

The candidate must go beyond query syntax. Preparation should include execution plans, partition pruning, indexing, incremental processing, transactions, concurrency, idempotency, and data-quality controls. The candidate should be ready to explain how a logically correct query behaves when tables contain billions of rows, events arrive late, or a job is retried.

Scenario 3: A hiring manager creating a fair assessment

The manager replaces trivia with a realistic reporting problem and provides a concise schema. Candidates are asked to clarify definitions, produce a query, identify edge cases, and describe tests. The scorecard separates correctness, reasoning, communication, and performance awareness. This structure reduces interviewer bias and gives candidates multiple ways to demonstrate competence.

Interview SQL questions: final preparation checklist

• Confirm the database dialect and role level.
• Practise SELECT, filtering, sorting, aggregation, and all required join patterns.
• Explain WHERE versus HAVING and COUNT(*) versus COUNT(column).
• Use CTEs, subqueries, CASE, and window functions confidently.
• Practise top-N-per-group, running totals, deduplication, gaps, and retention problems.
• Review primary keys, foreign keys, normalization, transactions, and indexes.
• Learn to read a basic execution plan in the target database.
• State table grain and output grain before complex queries.
• Test NULLs, ties, duplicate records, empty sets, and date boundaries.
• Describe at least two independent validation checks.
• Prepare concise examples from your own work without exposing confidential data.
• For hiring teams, use a written scorecard and consistent prompts.

Summary: Interview SQL Questions

Successful SQL interview preparation combines reliable fundamentals with disciplined reasoning. Candidates should be able to define the requested output, choose correct relationships, control row grain, handle missing and duplicate data, use aggregation and window functions, and explain how a result will be validated. Advanced roles add performance, transactions, data modeling, incremental processing, and platform-specific behavior.

Hiring teams should select questions that resemble the real decisions of the role. The assessment should make scope, expected output, time limits, allowed tools, and evaluation criteria clear. Quality assurance comes from checking assumptions, test cases, result counts, performance evidence, and the candidate's ability to explain revisions when an initial approach is incomplete.

When an organization needs broader support—such as designing data assessments, improving SQL reporting, building dashboards, documenting metrics, reviewing data quality, or adding specialist delivery capacity—Rudrriv can help define the requirement and connect it with relevant data and AI support, a dedicated professional, a defined project, ongoing assistance, or a managed team. Scope, ownership, access, acceptance criteria, documentation, and handover should be agreed before work begins.

FAQs About Interview SQL Questions

What are the most common SQL interview questions?

Common SQL interview questions cover SELECT statements, filtering, sorting, joins, aggregation, GROUP BY, HAVING, subqueries, common table expressions, window functions, indexes, normalization, transactions, NULL handling, and query optimization. Strong interviews also include practical tasks that require explaining assumptions, testing edge cases, and discussing performance.

How should a beginner prepare for SQL interview questions?

Begin with SELECT, WHERE, ORDER BY, GROUP BY, HAVING, and the main join types. Then practise on small tables, predict the result before running each query, and explain why the query works. Add subqueries, CTEs, window functions, and basic indexing only after the fundamentals are reliable.

What is the difference between WHERE and HAVING in SQL?

WHERE filters rows before grouping and aggregation. HAVING filters grouped results after GROUP BY has been applied. For example, WHERE can exclude cancelled orders before totals are calculated, while HAVING can keep only customers whose total order value exceeds a threshold.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that match in both joined tables. LEFT JOIN returns every row from the left table and matching rows from the right table; unmatched right-side columns become NULL. Choose the join according to whether unmatched left-side records must remain visible.

How do I answer SQL query questions during an interview?

State the required output, identify the tables and join keys, describe filters and grouping, then write the query in readable stages. Mention assumptions about duplicates, NULL values, date boundaries, and ties. Finally, explain how you would validate the result and what indexes or design changes might matter at scale.

What SQL window functions are commonly asked in interviews?

Frequently tested window functions include ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, AVG OVER, and partitioned running totals. Interviewers use them for top-N-per-group, period comparisons, deduplication, ranking, cohort analysis, and calculations that must retain row-level detail.

What is a CTE and when should I use one?

A common table expression is a named temporary result set defined with WITH and used by the following statement. It can make multi-stage logic easier to read, test, and discuss. Recursive CTEs can also traverse hierarchies, although syntax and behavior vary across database systems.

How are indexes discussed in SQL interviews?

Explain that an index can reduce the work needed to locate or order rows, but it consumes storage and adds maintenance cost to inserts, updates, and deletes. Good answers connect an index to a real query pattern, column selectivity, join or filter conditions, and the database execution plan.

What SQL mistakes should candidates avoid?

Avoid using SELECT * without reason, forgetting join conditions, mixing aggregated and non-aggregated columns incorrectly, ignoring NULL behavior, applying functions that prevent useful index access, and assuming result order without ORDER BY. Also avoid giving syntax without explaining correctness, edge cases, and performance.

Do SQL interview questions differ between MySQL, PostgreSQL, SQL Server, and Oracle?

The core relational concepts are similar, but syntax and features differ. Date functions, string functions, limit or top syntax, identity handling, NULL ordering, recursive queries, execution-plan tools, and transaction behavior may vary. State the dialect you are using and adapt when the interviewer specifies a platform.

Need help with SQL, reporting, or data capability?

Share the business problem, current data sources, required outputs, platform, internal capacity, and delivery timeline. Rudrriv can help structure a defined SQL or data project, specialist engagement, ongoing support arrangement, or managed team with clear responsibilities, quality checks, documentation, and handover.

Discuss your requirement

At Rudrriv, we make it easier for businesses to access the right expertise, execute important work, and scale with confidence.