SQL Interview Preparation

Interview Questions on SQL: Practical Questions and Answers

Interview questions on SQL usually test whether you can turn a business question into a correct, readable, and efficient query—not whether you can recite every command from memory. A strong candidate clarifies the data model, identifies the correct level of detail, handles duplicates and NULL values deliberately, writes a valid query, and explains how the result would be checked.

This guide is designed for candidates preparing for analyst, developer, data engineer, database, reporting, and business-intelligence roles, as well as hiring teams building practical SQL assessments. It covers foundational questions, intermediate and advanced concepts, hands-on query exercises, scenario questions, common mistakes, evaluation criteria, and a preparation plan.

The examples use broadly portable SQL. Syntax can differ across PostgreSQL, MySQL, SQL Server, Oracle, and cloud data platforms, so state the assumed dialect when an interview question does not specify one. The principles—correct joins, clear grouping, deliberate NULL handling, controlled transactions, and evidence-based optimization—remain valuable across systems.

Quick Answer: How to Prepare for Interview Questions on SQL

Prepare by practising SQL against a small relational dataset and explaining every decision aloud. You should be comfortable with SELECT, WHERE, ORDER BY, joins, GROUP BY, HAVING, subqueries, common table expressions, window functions, data modification, keys, indexes, and transactions. For each topic, practise both a definition question and a realistic query problem.

During the interview, begin by clarifying tables, relationships, expected output, duplicate rules, NULL behaviour, date boundaries, and the SQL dialect. Write the simplest correct query first. Then validate it with a small example and discuss performance only after correctness is clear.

The biggest caution is memorizing polished solutions without understanding row grain. Many wrong answers look syntactically valid but duplicate records, omit unmatched entities, aggregate at the wrong level, or produce unstable results. Build the habit of predicting the output before running the query.

Key Takeaways

• Correctness comes before cleverness: solve the requested problem clearly before discussing optimization.
• Row grain controls the answer: know what one row represents before joining or aggregating.
• Joins can multiply data: verify key uniqueness and expected cardinality.
• NULL requires deliberate logic: comparisons, aggregates, and anti-joins can behave unexpectedly.
• Window functions are a core interview skill: use them for ranking, running totals, comparisons, and latest-record problems.
• Performance answers need evidence: discuss execution plans, access paths, selectivity, and workload rather than rules of thumb.
• Communication is evaluated: explain assumptions, trade-offs, tests, and edge cases as you work.

What This Page Covers

• Foundational SQL questions with concise answers.
• Join, aggregation, subquery, CTE, and window-function questions.
• Hands-on coding exercises with sample solutions.
• Database design, indexing, transaction, and optimization concepts.
• Scenario questions for analysts, developers, and data teams.
• A preparation checklist for candidates.
• A fair assessment framework for interviewers and hiring managers.

Table of Contents

1. How this guide was prepared
2. Fundamental SQL interview questions
3. Joins and data relationships
4. Aggregation and analytical SQL
5. Practical SQL coding exercises
6. Advanced concepts and performance
7. Role-based scenario questions
8. Candidate preparation plan
9. Hiring-team evaluation framework
10. Summary
11. Frequently asked questions

How this SQL interview guide was prepared

The question set is organized around the work SQL professionals actually perform: retrieving data, combining related tables, summarizing records, detecting data-quality issues, producing analytical results, modifying data safely, and investigating slow queries. The examples emphasize reasoning, because a technically correct answer is stronger when the candidate can explain assumptions and validation steps.

Database behaviour and syntax differ by platform. Readers should verify platform-specific details in authoritative documentation, including the PostgreSQL SQL tutorial, the MySQL SQL statement reference, the SQL Server guides, and the Oracle SQL Language Reference.

Recommended answer method: clarify the requirement, identify row grain, state assumptions, write a readable solution, test edge cases, and then discuss performance or alternatives.

Fundamental SQL interview questions and answers

1. What is SQL?

SQL, or Structured Query Language, is used to define, query, manipulate, and control data in relational database systems. In an interview, go beyond the acronym: explain that SQL is declarative. You describe the result you need, and the database optimizer determines an execution strategy. SQL implementations have platform-specific extensions, but common operations include selecting, joining, grouping, inserting, updating, deleting, and managing database objects.

2. What is the difference between DDL, DML, DCL, and transaction control?

Data Definition Language changes database structures, such as CREATE, ALTER, and DROP. Data Manipulation Language reads or changes rows, commonly SELECT, INSERT, UPDATE, and DELETE. Data Control Language manages permissions, such as GRANT and REVOKE. Transaction-control statements manage units of work, such as COMMIT, ROLLBACK, and SAVEPOINT. Exact categorization can vary by product, so explain the practical purpose rather than relying only on labels.

3. What is a primary key?

A primary key identifies each row uniquely and does not permit NULL. It may contain one column or several columns as a composite key. A good answer distinguishes the business identifier from a surrogate identifier. For example, an order may use a generated order_id as its primary key while an external order number is protected by a separate unique constraint.

4. What is a foreign key?

A foreign key enforces a relationship between a child table and a candidate key—usually the primary key—of a parent table. It protects referential integrity by preventing invalid references. Candidates should mention that delete and update behaviour can be restricted, cascaded, or otherwise configured, and that foreign-key columns may still require suitable indexes depending on workload.

5. What is NULL?

NULL represents an absent or unknown value; it is not the same as zero, an empty string, or false. SQL uses three-valued logic, so comparisons involving NULL generally produce UNKNOWN. Use IS NULL or IS NOT NULL rather than = NULL. Explain how NULL affects COUNT(column), NOT IN, joins, concatenation, and calculations in the database dialect being used.

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

DELETE removes selected rows and can normally use a WHERE clause. TRUNCATE removes all rows using a platform-specific operation that may have different logging, identity, locking, and transaction behaviour. DROP removes the database object itself. Avoid universal claims such as “TRUNCATE cannot be rolled back,” because behaviour depends on the database platform and transaction context.

7. What is normalization?

Normalization organizes data to reduce unintended redundancy and update anomalies. First normal form requires atomic values and a relational structure. Second and third normal forms address dependencies on keys and non-key attributes. In practice, interviewers want to know whether you can explain the trade-off: normalized models support consistency, while carefully controlled denormalization may improve reporting or performance for specific workloads.

Joins and data relationships

Join questions test whether you understand relationships and result cardinality. Before writing a join, identify which columns are keys, whether each side is unique, and whether unmatched records must remain in the output.

8. How can a LEFT JOIN become an INNER JOIN accidentally?

Suppose customers is left-joined to orders, but the WHERE clause contains orders.status = 'paid'. Unmatched customers have NULL order columns, so the WHERE condition removes them. Place the status condition in the ON clause when the requirement is to preserve all customers while matching only paid orders.

9. How do you prevent duplicate rows after a join?

Do not immediately add DISTINCT. First diagnose the relationship. The right table may contain several rows per key, the join condition may be incomplete, or both tables may be at different grains. Aggregate or rank the right-side data to the intended level before joining. DISTINCT can hide a modelling error and may also add avoidable work.

10. EXISTS versus IN: which should you use?

Use the expression that communicates the requirement correctly. EXISTS is natural for testing whether at least one related row exists and can avoid accidental row multiplication. IN can be clear for a fixed list or a subquery returning a single comparable column. Be careful with NOT IN when the subquery can return NULL; NOT EXISTS often expresses an anti-join more safely.

Aggregation, CTEs, and analytical SQL

11. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters grouped results after aggregate calculations. Filtering early can reduce work and usually communicates intent more clearly. Use HAVING when the condition depends on an aggregate, such as retaining departments with more than ten employees.

12. What is a common table expression?

A common table expression, introduced with WITH, names a query result for use within a statement. CTEs can make multi-stage logic easier to read and can support recursion where the platform allows it. Do not claim that a CTE is always materialized or always faster; optimization and materialization behaviour depend on the database and query.

13. What is a window function?

A window function calculates across a set of related rows while preserving each input row. The OVER clause defines partitioning and ordering. Common functions include ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and aggregate functions used over a window. Window frames matter for running and moving calculations, especially when duplicate ordering values exist.

Practical SQL coding exercises with sample answers

Exercise 1: Find customers who have never ordered

Assume: customers(customer_id, customer_name) and orders(order_id, customer_id, order_date).

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

This anti-join asks whether a matching order exists for each customer. It preserves one output row per customer and avoids the NULL sensitivity associated with some NOT IN patterns. A LEFT JOIN solution can also be valid when written carefully.

Exercise 2: Return the second-highest distinct salary

WITH ranked_salaries AS (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM employees
)
SELECT salary
FROM ranked_salaries
WHERE salary_rank = 2;

DENSE_RANK treats equal salaries as the same rank. Clarify what should happen when fewer than two distinct salaries exist. An interviewer may also ask for a solution without window functions, which can be written using MAX with a filter below the maximum salary.

Exercise 3: Find the latest order for every customer

WITH ranked_orders AS (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, order_id DESC
         ) AS row_num
  FROM orders AS o
)
SELECT *
FROM ranked_orders
WHERE row_num = 1;

The order_id tie-breaker makes the result deterministic when two orders share the same date. Ask whether “latest” means timestamp, business-effective date, or highest identifier. Also ask whether customers with no orders should appear.

Exercise 4: Calculate monthly revenue and month-over-month change

WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', order_date) AS revenue_month,
         SUM(order_amount) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY DATE_TRUNC('month', order_date)
), compared AS (
  SELECT revenue_month,
         revenue,
         LAG(revenue) OVER (ORDER BY revenue_month) AS previous_revenue
  FROM monthly_revenue
)
SELECT revenue_month,
       revenue,
       previous_revenue,
       revenue - previous_revenue AS absolute_change
FROM compared
ORDER BY revenue_month;

DATE_TRUNC is not universal syntax, so adapt it to the specified database. A complete answer should also clarify currency, refunds, time zone, missing months, and whether revenue is recognized at order, payment, shipment, or invoice date.

Exercise 5: Detect duplicate email addresses

SELECT LOWER(TRIM(email)) AS normalized_email,
       COUNT(*) AS occurrence_count
FROM customers
WHERE email IS NOT NULL
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

This solution demonstrates normalization before comparison, but the correct normalization rules depend on the application. Do not assume all email providers treat case, punctuation, or aliases identically. The interviewer may be testing data-quality thinking as much as syntax.

Advanced SQL concepts and performance questions

14. What is an index?

An index is a data structure that can help the database locate rows without scanning an entire table. Index usefulness depends on predicates, join columns, sorting, selectivity, index key order, included data, table size, and the proportion of rows requested. Indexes also add storage and write-maintenance cost. Strong candidates explain that execution plans and workload evidence should guide design.

15. What is a query execution plan?

An execution plan shows the operations selected by the optimizer, such as scans, seeks, joins, sorts, and aggregates. Explain the difference between estimated and actual plans where supported. Review row estimates versus actual rows, expensive operators, spills, repeated lookups, broad scans, join choices, and filter placement. Cost values are optimizer estimates, not direct elapsed-time measurements.

16. What are ACID properties?

Atomicity means a transaction completes as a unit or not at all. Consistency means a committed transaction moves the database between valid states under defined constraints. Isolation concerns how concurrent transactions observe one another. Durability means committed changes survive failures according to the system’s guarantees. Follow-up questions often cover isolation anomalies, locking, row versioning, and deadlocks.

17. What is a deadlock?

A deadlock occurs when transactions wait on resources held by one another in a cycle and cannot proceed. Database systems typically detect the cycle and choose a victim transaction to roll back. Prevention techniques include accessing resources in a consistent order, keeping transactions short, indexing access paths appropriately, avoiding unnecessary user interaction inside transactions, and implementing safe retry logic.

18. How would you investigate a slow query?

1. Confirm the exact query, parameters, database version, and workload context.
2. Measure duration, CPU, reads, waits, and returned row count using platform tools.
3. Inspect the actual execution plan and row-estimation differences.
4. Check filtering, joins, data types, implicit conversions, sorting, and aggregation.
5. Review existing indexes and statistics rather than adding an index automatically.
6. Test a rewritten query or index change in a representative environment.
7. Measure write overhead and regression risk before deployment.

The SQL Server index design guide is a useful example of platform documentation that connects index decisions with workload, selectivity, and execution-plan analysis.

Role-based SQL scenario questions

Scenario 1: Business or data analyst

Question: A dashboard total does not match the finance report. How would you investigate?

A strong answer checks metric definition, source systems, time zones, date boundaries, status filters, currency conversion, refunds, late-arriving data, duplicate joins, snapshot timing, and aggregation grain. The candidate should reconcile progressively—from source totals to transformed tables to dashboard output—and document the agreed definition.

Scenario 2: Application developer

Question: An API endpoint loads one customer and hundreds of related records slowly. What would you check?

The candidate should examine generated SQL, request frequency, N+1 query behaviour, selected columns, indexes, join and filter patterns, pagination, transaction scope, locking, caching suitability, and execution plans. The answer should not jump directly to “add an index” without measuring the current access path.

Scenario 3: Data engineer

Question: A daily pipeline sometimes inserts duplicate business events. How would you design an idempotent load?

Good answers discuss stable business keys or event identifiers, staging tables, deduplication rules, merge or upsert behaviour, transaction boundaries, watermark reliability, replay handling, audit columns, late data, and validation counts. The candidate should explain what makes two events duplicates and how corrections differ from duplicates.

Scenario 4: Database or platform engineer

Question: A release causes blocking and timeouts during peak traffic. What information do you collect?

Look for transaction duration, lock graphs, blocked and blocking sessions, isolation level, query plans, new indexes or schema changes, deployment statements, retry patterns, batch sizes, and workload changes. A safe response includes rollback or mitigation criteria and avoids changing isolation or disabling constraints without understanding consequences.

A practical preparation plan for candidates

Use a repeatable schema with customers, orders, products, employees, and events. Write each problem in three stages: expected output, initial query, and validation. Then create counterexamples—for instance, duplicate order lines, customers without orders, same-day ties, NULL status values, and missing months. These edge cases expose fragile solutions quickly.

For Indian candidates applying to global or domestic teams, prepare to discuss both business context and platform context. A services company may emphasize client reporting and data reconciliation. A product company may emphasize transactional queries and API performance. A data or analytics team may emphasize warehousing, window functions, slowly changing dimensions, and data quality. Read the job description and prepare examples that match the actual role.

How hiring teams can evaluate SQL capability fairly

A fair SQL interview measures job-relevant capability under consistent conditions. Start by defining the level expected: basic data retrieval, independent analytical work, production application queries, data-pipeline development, or database performance engineering. Do not use one puzzle as a universal test for every role.

Give candidates a compact schema, sample rows, data dictionary, dialect, and explicit time limit. Separate syntax recall from reasoning by allowing documentation when the role normally permits it. Use the same core task and scoring rubric for comparable candidates. Review the assessment for accessibility, hidden cultural assumptions, ambiguous business language, and unnecessary pressure.

For a take-home exercise, request a short README explaining assumptions, validation, and trade-offs. Avoid asking candidates to perform unpaid production work or analyze confidential business data. For live exercises, offer small hints consistently and score how the candidate incorporates new information.

Common mistakes when answering SQL interview questions

• Writing before clarifying: the candidate solves a different problem from the one intended.
• Ignoring grain: totals are inflated because order headers are joined to multiple order lines.
• Using DISTINCT as a repair: duplicate symptoms disappear without fixing the relationship error.
• Assuming NULL equals zero: filters and calculations silently exclude unknown values.
• Using unstable ordering: latest-row queries return unpredictable ties.
• Overgeneralizing database behaviour: platform-specific details are presented as universal SQL rules.
• Optimizing without measurement: an index or rewrite is suggested without an execution plan or workload context.
• Not validating: the query is never tested against no matches, duplicates, ties, or empty inputs.

How Rudrriv can support SQL and data work

Rudrriv can support organizations that need practical data and technology capability for a defined project, dedicated professional, ongoing support arrangement, or managed team. Relevant work may include SQL reporting, data-quality checks, dashboard data preparation, analytics support, database-related development, workflow documentation, and coordinated delivery across business and technical stakeholders.

The appropriate engagement begins with a clear requirement: source systems, expected outputs, data sensitivity, platform, access controls, owners, milestones, validation criteria, documentation, and handover. Explore Rudrriv data and AI services, development support, or specialist talent options when those models match the business need.

Summary: Interview Questions on SQL

SQL interviews evaluate a combination of data reasoning, query correctness, communication, and platform awareness. Candidates should prepare core syntax, joins, aggregation, CTEs, window functions, database design, indexes, transactions, and execution-plan concepts, but they should practise these topics through realistic problems rather than isolated definitions.

The most reliable interview approach is to clarify the requirement, identify row grain, state assumptions, write a readable solution, validate edge cases, and discuss performance using evidence. Hiring teams should define role-specific expectations, provide consistent conditions, and score reasoning and correctness separately from memorized syntax.

For business delivery, the same discipline applies: define scope, choose the right specialist or support model, agree timelines and communication, set quality checks and revision rules, retain ownership of data and outputs, verify delivery against acceptance criteria, and complete a documented handover.

FAQs About Interview Questions on SQL

What SQL questions are most commonly asked in interviews?

Common SQL interview questions cover SELECT statements, filtering, joins, aggregation, subqueries, common table expressions, window functions, keys, normalization, indexes, transactions, NULL handling, query optimization, and practical data problems. The exact balance depends on whether the role is in analytics, application development, data engineering, database administration, or business intelligence.

How should a beginner prepare for interview questions on SQL?

Start with one small relational dataset and practise writing queries without copying answers. Cover filtering, sorting, joins, GROUP BY, HAVING, subqueries, CTEs, and basic window functions. Then explain each query aloud, test edge cases such as NULL values and duplicate rows, and compare alternative solutions. Interview readiness comes from reasoning clearly, not memorizing syntax alone.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and aggregation. HAVING filters groups after GROUP BY has produced aggregate results. For example, WHERE can limit orders to the current year 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 rows that match in both joined inputs. LEFT JOIN returns every row from the left input and matching rows from the right input; when no match exists, right-side columns are NULL. Candidates should also explain how filters placed in WHERE can unintentionally remove unmatched rows from a LEFT JOIN result.

Why are window functions important in SQL interviews?

Window functions calculate values across related rows without collapsing those rows into one result per group. They are commonly used for ranking, running totals, period comparisons, moving averages, and selecting the latest record per entity. Interviewers use them to test whether a candidate can solve analytical problems cleanly and efficiently.

How do indexes affect SQL query performance?

Indexes can reduce the amount of data a database must scan for selective filters, joins, and sorting. However, indexes consume storage and add maintenance cost to inserts, updates, and deletes. A strong answer considers the query pattern, column selectivity, index order, included columns, execution plan, and workload rather than claiming that every indexed column makes every query faster.

What is the best way to answer a SQL coding question during an interview?

Clarify the tables, keys, expected output, duplicate policy, NULL behaviour, date boundaries, and SQL dialect before coding. State a simple approach, write readable SQL in stages, test it with a small example, and discuss performance only after correctness is established. When relevant, mention an alternative and explain why you chose one solution.

What SQL mistakes do interviewers commonly notice?

Frequent mistakes include joining on the wrong key, creating duplicate rows, confusing WHERE with HAVING, mishandling NULL, using NOT IN with nullable data, forgetting deterministic ordering, grouping at the wrong level, applying functions that prevent efficient index use, and optimizing before confirming correctness. Not asking clarifying questions is also a common weakness.

Should SQL interview answers use a specific database dialect?

Use the dialect named by the interviewer or employer. ANSI-style SQL is a good starting point, but PostgreSQL, MySQL, SQL Server, Oracle, and cloud warehouses differ in date functions, limiting syntax, string operations, recursive queries, procedural features, and optimizer behaviour. State your assumed dialect when the question does not specify one.

How can a hiring team design a fair SQL assessment?

Define the role competencies first, use a realistic but compact schema, provide clear assumptions, and score reasoning, correctness, communication, edge-case handling, and maintainability separately. Avoid puzzle-heavy questions unrelated to the job. Give candidates equivalent conditions and review whether the task can be completed within the allotted time without relying on obscure syntax.

Need specialist support for SQL, analytics, or data delivery?

Share the platform, data sources, required outputs, access constraints, timelines, and internal capacity. Rudrriv can help structure a defined project, dedicated-professional arrangement, ongoing support plan, or managed team with clear responsibilities, validation, 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.