SQL INTERVIEW GUIDE

FOR DATA ENGINEERS

32 Curated Questions & Answers — Explained Simply, with the Insight That
Makes Interviewers Take Notice

Covers Joins • Window Functions • CTEs • Slowly Changing Dimensions • Incremental Loads •
Indexing • Query Optimization • ACID & Schema Design

Prepared for Yash Kolhe | Azure Data Engineering Track

 What's Inside
Each question is answered in plain language first, followed by a ready-to-use SQL example where
relevant, and an Interview Tip that shows you what to add out loud to sound like an experienced Data
Engineer rather than someone reciting definitions.

SQL Fundamentals
1 4 questions

Joins & Set Logic

2 4 questions

Aggregation & Duplicates

3 3 questions

Window Functions
4 5 questions

Subqueries & CTEs

5 3 questions

Data Engineering Essentials

6 5 questions

Performance & Optimization

7 4 questions

Database Design & Transactions

8 4 questions

How to use this guide: read a question, try answering it out loud before reading the answer, then check whether
you would have naturally included the Interview Tip. That gap is usually exactly what separates a pass from a
strong pass.

SQL Interview Guide for Data Engineers Page 1

 1 SQL Fundamentals
4 Questions

Q1. What is the difference between WHERE and HAVING?

WHERE filters individual rows before any grouping happens, so it cannot reference aggregate functions.
HAVING filters groups after GROUP BY has run, so it works on aggregated results like COUNT() or
SUM().

SQL
SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE status = 'Active'
GROUP BY department
HAVING COUNT(*) > 5;

★ INTERVIEW TIP
Mention that WHERE reduces the row count before the expensive grouping step. It shows the interviewer you
think about query execution order, not just syntax.

Q2. What is the logical order in which an SQL query actually executes?
Even though we write SELECT first, the engine actually executes in this order: FROM, JOIN, WHERE,
GROUP BY, HAVING, SELECT, ORDER BY, then LIMIT/OFFSET.

★ INTERVIEW TIP
This is one of the most reliable 'instant credibility' answers. Very few candidates can state this order
confidently, so it tends to stand out.

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

DELETE removes specific rows using a WHERE clause and can be rolled back. TRUNCATE removes all
rows at once with minimal logging and resets identity columns, but you cannot filter which rows to remove.
DROP removes the entire table object, including its structure.

★ INTERVIEW TIP
Add that TRUNCATE is usually treated as DDL and cannot always be rolled back depending on the database.
That shows you know the trade-offs, not just definitions.

Q4. What's the difference between UNION and UNION ALL?

UNION combines result sets and removes duplicate rows, which needs an internal sort or distinct step.
UNION ALL combines result sets and keeps every row, including duplicates, so it runs faster.

★ INTERVIEW TIP
In pipelines, default to UNION ALL whenever you already know the sources do not overlap. It avoids an
unnecessary and costly deduplication step.

SQL Interview Guide for Data Engineers Page 2

 2 Joins & Set Logic
4 Questions

Q5. Explain the main types of SQL joins.

INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table
plus matches from the right, with NULLs where there is no match. RIGHT JOIN is the mirror of that. FULL
OUTER JOIN returns all rows from both sides. CROSS JOIN returns every combination of rows.

SQL
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

★ INTERVIEW TIP
Give a real data engineering example: LEFT JOIN a fact table to a dimension table and check for NULL
dimension keys. That is a standard data-quality check for orphaned records.

Q6. How would you find rows that exist in one table but not another?
The cleanest ways are a LEFT JOIN with a NULL check, or a NOT EXISTS subquery.

SQL
SELECT a.*
FROM table_a a
LEFT JOIN table_b b ON [Link] = [Link]
WHERE [Link] IS NULL;

★ INTERVIEW TIP
Mention that NOT EXISTS generally outperforms NOT IN, especially when the second column can contain
NULLs. NOT IN can silently return zero rows in that case, which is a classic bug.

Q7. What's the difference between a SELF JOIN and a CROSS JOIN?
A SELF JOIN joins a table to itself using a related column, typically for hierarchical data like an
employee-manager relationship. A CROSS JOIN has no join condition at all and returns every possible
pairing of rows from both tables.

★ INTERVIEW TIP
A good follow-up example: use a self join to list each employee next to their manager's name, both coming
from the same employees table.

SQL Interview Guide for Data Engineers Page 3

Q8. MySQL doesn't support FULL OUTER JOIN directly. How would you simulate
one?
Combine a LEFT JOIN and a RIGHT JOIN with UNION, not UNION ALL, to avoid duplicating the matching
rows.

SQL
SELECT * FROM a LEFT JOIN b ON [Link] = [Link]
UNION
SELECT * FROM a RIGHT JOIN b ON [Link] = [Link];

★ INTERVIEW TIP
Knowing practical workarounds for engine limitations shows real hands-on experience, not just textbook
knowledge.

SQL Interview Guide for Data Engineers Page 4

 3 Aggregation & Duplicates
3 Questions

Q9. How do you find duplicate records in a table?

Group by the column or columns that define a duplicate, then filter groups with HAVING COUNT(*) greater
than 1.

SQL
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

★ INTERVIEW TIP
Say that this is a standard first step before loading data into a warehouse. Catching duplicates early prevents
downstream reporting errors.

Q10. How would you remove duplicate rows but keep exactly one copy of each?
Use ROW_NUMBER() partitioned by the columns that define a duplicate, then delete every row where the
row number is greater than 1.

SQL
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY email ORDER BY created_at DESC
) AS rn
FROM users
)
DELETE FROM ranked WHERE rn > 1;

★ INTERVIEW TIP
This exact pattern shows up constantly in real Data Engineer interviews. Know it well enough to write it without
hesitating.

Q11. What's the difference between COUNT(*), COUNT(1), and

COUNT(column_name)?
COUNT(*) counts all rows regardless of NULLs. COUNT(1) behaves identically in virtually every modern
engine, it is not actually faster than COUNT(*). COUNT(column_name) only counts rows where that
specific column is not NULL.

★ INTERVIEW TIP
Correcting the old myth that COUNT(1) is faster than COUNT(*) shows you keep your knowledge current with
how modern optimizers actually work.

SQL Interview Guide for Data Engineers Page 5

 4 Window Functions
5 Questions

Q12. What are window functions, and how are they different from GROUP BY?
Window functions calculate a value across a set of related rows using the OVER() clause, without
collapsing those rows. Every original row stays in the result. GROUP BY, by contrast, collapses each
group into a single summary row.

★ INTERVIEW TIP
Point out that this makes window functions ideal for reporting layers where you need both row-level detail and
aggregate context in the same result set.

Q13. What's the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

ROW_NUMBER() gives every row a unique, sequential number, even for ties. RANK() gives tied rows the
same rank but skips the following number. DENSE_RANK() gives tied rows the same rank without leaving
any gap.

SQL
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rank_num,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_num
FROM employees;

★ INTERVIEW TIP
Walk through a concrete example with tied values out loud. It is the fastest way to prove you understand the
difference rather than having memorized it.

Q14. How would you calculate a running total in SQL?

Use a SUM() window function ordered by the relevant date column, with a frame that spans from the first
row to the current row.

SQL
SELECT sale_date, amount,
SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM sales;

★ INTERVIEW TIP
This exact pattern powers most 'revenue over time' and 'cumulative growth' dashboard queries, a very practical
detail to mention.

SQL Interview Guide for Data Engineers Page 6

Q15. What do LAG() and LEAD() do, and when would you use them?
LAG() fetches a value from a previous row within a partition, and LEAD() fetches a value from a following
row, without needing a self-join.

SQL
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly_sales;

★ INTERVIEW TIP
Mention this is exactly how month-over-month or year-over-year growth metrics are typically calculated, a very
'I have done this before' answer.

Q16. How do you find the Nth highest salary in a table?

Use DENSE_RANK() so tied salaries share a rank without skipping numbers, then filter for the rank you
want.

SQL
WITH ranked AS (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT DISTINCT salary FROM ranked WHERE rnk = 2;

★ INTERVIEW TIP
Explicitly explain why DENSE_RANK is safer than ROW_NUMBER here. If two people tie for first place,
ROW_NUMBER would wrongly treat one of them as second highest.

SQL Interview Guide for Data Engineers Page 7

 5 Subqueries & CTEs
3 Questions

Q17. What is a CTE, and why use one instead of a subquery?

A CTE, written with WITH, is a named temporary result set that only exists for the duration of one query. It
improves readability, can be referenced more than once in the same query, and supports recursion.

★ INTERVIEW TIP
Mention that CTEs make multi-step transformation logic in ETL scripts far easier for teammates to read and
maintain. Readability matters to interviewers too.

Q18. What is a recursive CTE, and when would you use one?
A recursive CTE references itself to walk through hierarchical or graph-like data, such as an org chart, a
bill of materials, or a folder structure, one level at a time.

★ INTERVIEW TIP
Bring up a manager-employee hierarchy example. It is the classic use case interviewers expect to hear.

Q19. What's the difference between a correlated and a non-correlated subquery?

A non-correlated subquery runs once, independently of the outer query. A correlated subquery references
a column from the outer query, so conceptually it runs once per outer row, which can hurt performance on
large tables.

★ INTERVIEW TIP
Mention that correlated subqueries can often be rewritten as JOINs for better performance, a strong
optimization instinct to demonstrate.

SQL Interview Guide for Data Engineers Page 8

 6 Data Engineering Essentials
5 Questions

Q20. What is a Slowly Changing Dimension (SCD), and what are the common types?
An SCD defines how a data warehouse handles changes to dimension attributes over time. Type 1 simply
overwrites the old value with no history kept. Type 2 inserts a new row with effective and expiry dates to
preserve full history. Type 3 adds a separate column to store just the previous value.

★ INTERVIEW TIP
Say that Type 2 is the most common in enterprise warehouses because it lets analysts run accurate 'as of that
date' historical reports. That single sentence signals real warehouse experience.

Q21. How would you implement an incremental load instead of reloading a full table
every time?
Track a watermark column, such as last_updated_at or a change-tracking ID, and only pull rows newer
than the last successful load. Then use a MERGE, or upsert, to apply inserts and updates to the target
table in one atomic statement.

SQL
MERGE INTO target_table AS tgt
USING staging_table AS src
ON [Link] = [Link]
WHEN MATCHED THEN
UPDATE SET [Link] = [Link], tgt.updated_at = src.updated_at
WHEN NOT MATCHED THEN
INSERT (id, value, updated_at)
VALUES ([Link], [Link], src.updated_at);

★ INTERVIEW TIP
This maps directly to how Azure Data Factory and Synapse pipelines typically implement incremental loads, a
great way to connect your answer to real pipeline design.

Q22. How do you handle NULL values in SQL queries?

Use COALESCE() to substitute a default value, NULLIF() to turn a specific value into NULL, and IS NULL
or IS NOT NULL for comparisons. Regular equality never works for NULL, since NULL represents
'unknown' rather than a comparable value.

★ INTERVIEW TIP
Pointing out that 'NULL = NULL' evaluates to unknown, not true, is a small detail that trips up a lot of
candidates. Getting it right stands out.

SQL Interview Guide for Data Engineers Page 9

Q23. What is a MERGE (UPSERT) statement, and why is it useful in ETL pipelines?
MERGE combines INSERT, UPDATE, and optionally DELETE into a single atomic statement based on
whether a match condition succeeds, which is exactly what is needed to sync a target table with an
incoming batch of source data.

★ INTERVIEW TIP
Note that doing this in one atomic statement avoids race conditions you would get from running separate
INSERT and UPDATE statements, a subtle but important reliability point.

Q24. How do you pivot rows into columns in SQL?

Use the PIVOT operator where it is supported, or the more portable approach of conditional aggregation
with CASE WHEN inside a GROUP BY.

SQL
SELECT
product_id,
SUM(CASE WHEN quarter = 'Q1' THEN sales ELSE 0 END) AS Q1,
SUM(CASE WHEN quarter = 'Q2' THEN sales ELSE 0 END) AS Q2
FROM sales_data
GROUP BY product_id;

★ INTERVIEW TIP
The CASE WHEN version works on every SQL engine, so mentioning it shows you can write portable code, not
just syntax tied to one platform.

SQL Interview Guide for Data Engineers Page 10

 7 Performance & Optimization
4 Questions

Q25. What is indexing, and how does it improve query performance?

An index is a separate data structure, usually a B-tree, that lets the database find rows without scanning
the entire table. It speeds up lookups and joins significantly, at the cost of extra storage and slightly slower
writes, since indexes must be updated too.

★ INTERVIEW TIP
Add that you would not index low-cardinality columns, like a boolean flag, or heavily written tables without good
reason. That shows you understand trade-offs, not just benefits.

Q26. What's the difference between a clustered and a non-clustered index?

A clustered index physically orders the table's data on disk according to the index key, so a table can only
have one. A non-clustered index is a separate structure that stores pointers back to the actual rows, and a
table can have several.

★ INTERVIEW TIP
Mention that the primary key usually becomes the clustered index by default in most relational databases.

Q27. What's the difference between partitioning and sharding?

Partitioning splits a large table into smaller pieces, by range, list, or hash, within the same database
instance, mainly for performance and easier maintenance. Sharding splits data across multiple separate
database servers to scale horizontally.

★ INTERVIEW TIP
Bring up 'partition pruning': when a query filters on the partition key, the engine skips scanning irrelevant
partitions entirely, a huge performance win on large fact tables.

Q28. How would you approach optimizing a slow-running query?

Start by looking at the execution plan to see where time is actually being spent. From there: avoid
SELECT *, add indexes on columns used in WHERE, JOIN, and ORDER BY, avoid wrapping indexed
columns in functions, replace correlated subqueries with joins where possible, and consider partitioning
very large tables.

★ INTERVIEW TIP
Simply saying 'I would start by checking the execution plan' before touching anything is one of the strongest
opening lines you can give in a live SQL interview.

SQL Interview Guide for Data Engineers Page 11

 8 Database Design & Transactions
4 Questions

Q29. What are the ACID properties of a transaction?

Atomicity means a transaction fully completes or fully rolls back, never partially. Consistency means the
database moves from one valid state to another. Isolation means concurrent transactions do not interfere
with each other. Durability means once committed, changes survive even a system crash.

★ INTERVIEW TIP
Give one real example, like a bank transfer, where all four properties clearly matter together. Concrete
examples land better than definitions alone.

Q30. What is normalization, and when would you deliberately denormalize instead?
Normalization organizes tables to reduce data redundancy, generally up through third normal form, where
each non-key column depends only on the primary key. Data warehouses often deliberately denormalize
into a star schema instead, trading some redundancy for much faster read and aggregation performance.

★ INTERVIEW TIP
This OLTP-versus-OLAP framing, normalized for transactional systems and denormalized for analytics, is
exactly the kind of big-picture thinking Data Engineer interviewers listen for.

Q31. What's the difference between a Primary Key, a Unique Key, and a Foreign Key?
A Primary Key uniquely identifies each row and cannot be NULL, with only one allowed per table. A
Unique Key also enforces uniqueness but does allow one NULL value in most databases, and a table can
have several. A Foreign Key enforces a relationship by referencing a primary or unique key in another
table.

Q32. What's the difference between a View and a Materialized View?

A View is a saved query that runs fresh every time you select from it, so it is always current but adds no
extra storage. A Materialized View stores the actual computed result physically, so it is much faster to
query, but needs to be refreshed periodically to stay up to date.

★ INTERVIEW TIP
Mention that materialized views are especially useful for expensive aggregations in a reporting layer, where
slightly stale data is an acceptable trade-off for speed.

SQL Interview Guide for Data Engineers Page 12

 You're Ready.
Confidence in SQL interviews comes from explaining the 'why' behind an answer, not just the
syntax. Revisit the Interview Tips one more time before your interview — they're what turn a
correct answer into a memorable one.

All the best for your Data Engineer interviews!