SQL Fundamentals for Data Engineering Interviews

Master the fundamentals of SQL with practical examples designed for Data Engineering interviews. In this chapter, you’ll learn essential SQL concepts from filtering and aggregating data to handling NULL values, applying conditional logic, and converting data types using real-world examples.

By the end, you’ll have a strong foundation to solve SQL interview questions with confidence.

In this chapter

Sample dataset
SELECT, WHERE, ORDER BY, DISTINCT
GROUP BY and HAVING
Aggregate functions
NULL handling
CASE WHEN
Data types and type casting
Interview practice

Sample dataset used throughout this chapter

Imagine you’re a Data Engineer working for an e-commerce company. You have an orders table containing customer purchases.

order_id	customer	amount	status	city
101	Alice	1200	Completed	Bangalore
102	Bob	800	Pending	Mumbai
103	Alice	1500	Completed	Bangalore
104	Charlie	NULL	Cancelled	Delhi
105	Bob	2000	Completed	Mumbai
106	David	800	Pending	NULL

We’ll use this table to understand each SQL concept step by step.

1. SELECT, WHERE, ORDER BY, DISTINCT

1.1 SELECT - Retrieve data from a table

The SELECT statement specifies which columns you want to retrieve from a table.

SELECT column1, column2
FROM table_name;

Example: Retrieve customer names and order amounts.

SELECT customer, amount
FROM orders;

To retrieve all columns, use:

SELECT *
FROM orders;

Data Engineering tip: Avoid SELECT * in production pipelines when possible. Explicitly selecting columns makes transformations easier to maintain and reduces the risk of unexpected schema changes affecting downstream models.

1.2 WHERE - Filter rows

The WHERE clause filters records based on a condition.

SELECT *
FROM orders
WHERE status = 'Completed';

You can combine conditions using AND, OR, and NOT.

SELECT *
FROM orders
WHERE status = 'Completed'
  AND amount > 1000;

This returns only completed orders with amounts greater than 1,000.

Operator	Purpose	Example
`=`	Equal to	`status = 'Completed'`
`!=` or `<>`	Not equal to	`status <> 'Pending'`
`>` / `<`	Greater or less than	`amount > 1000`
`BETWEEN`	Within a range, inclusive	`amount BETWEEN 500 AND 1500`
`IN`	Matches a list	`city IN ('Delhi', 'Mumbai')`
`LIKE`	Pattern matching	`customer LIKE 'A%'`
`IS NULL`	Checks missing values	`city IS NULL`

1.3 ORDER BY - Sort results

ORDER BY sorts records in ascending (ASC) or descending (DESC) order.

SELECT order_id, customer, amount
FROM orders
ORDER BY amount DESC;

You can sort by multiple columns:

SELECT *
FROM orders
ORDER BY status ASC, amount DESC;

This sorts by status first, then by amount within each status.

Interview question: What happens if you omit ASC or DESC? ASC is the default in standard SQL. NULL ordering may vary by database unless you explicitly specify it.

1.4 DISTINCT - Remove duplicate values

DISTINCT returns unique combinations of the selected columns.

SELECT DISTINCT city
FROM orders;

This returns each distinct city once. Because the sample data contains a NULL city, the result may include one NULL entry.

SELECT DISTINCT customer, city
FROM orders;

This removes duplicate pairs, not duplicate customers alone.

Common interview trap: DISTINCT applies to the complete selected row. If you select two columns, both values together determine uniqueness.

Quick practice

Write a query to find the three highest-value completed orders.

SELECT order_id, customer, amount
FROM orders
WHERE status = 'Completed'
ORDER BY amount DESC
LIMIT 3;

Note: LIMIT is supported by databases such as PostgreSQL, MySQL, and BigQuery. Other databases may use different syntax.

2. GROUP BY and HAVING

2.1 GROUP BY - Aggregate records into groups

GROUP BY groups rows that share the same value in one or more columns. It is commonly used with aggregate functions such as COUNT, SUM, and AVG.

Example: Calculate the total order amount for each customer.

SELECT
    customer,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer;

customer	total_amount
Alice	2700
Bob	2800
Charlie	NULL
David	800

Why is Charlie’s total NULL? Because the only amount in Charlie’s group is NULL, and SUM() ignores NULL values. When every value in a group is NULL, SUM() returns NULL.

Example: Count orders by status.

SELECT
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY status;

status	order_count
Completed	3
Pending	2
Cancelled	1

GROUP BY creates one output row per group, rather than returning every original record.

2.2 HAVING - Filter aggregated groups

HAVING filters groups after aggregation. In contrast, WHERE filters individual rows before grouping.

SELECT
    customer,
    SUM(amount) AS total_amount
FROM orders
GROUP BY customer
HAVING SUM(amount) > 2000;

customer	total_amount
Alice	2700
Bob	2800

WHERE	HAVING
Filters individual rows	Filters aggregated groups
Applied before grouping	Applied after grouping
Commonly filters source records	Commonly filters aggregate results
Usually does not contain aggregate conditions	Can contain aggregate conditions

Example using both: Find customers with completed orders totaling more than 1,000.

SELECT
    customer,
    SUM(amount) AS total_amount
FROM orders
WHERE status = 'Completed'
GROUP BY customer
HAVING SUM(amount) > 1000;

Pending and cancelled orders are removed before the remaining records are grouped.

Interview tip: Use HAVING COUNT(*) > 1 to find groups with multiple records, such as customers with more than one order.

SELECT customer, COUNT(*) AS order_count
FROM orders
GROUP BY customer
HAVING COUNT(*) > 1;

3. Aggregate Functions

Aggregate functions calculate a single result from multiple rows or from each group of rows. Common functions are COUNT, SUM, AVG, MIN, and MAX.

3.1 COUNT() - Count rows or values

SELECT COUNT(*) AS total_orders
FROM orders;

Result: 6. COUNT(*) counts all rows, regardless of NULL values in individual columns.

SELECT COUNT(amount) AS orders_with_amount
FROM orders;

Result: 5. COUNT(amount) counts only rows where amount is not NULL.

SELECT COUNT(DISTINCT customer) AS unique_customers
FROM orders;

Result: 4. This counts unique, non-NULL customer values.

3.2 SUM() - Calculate totals

SELECT SUM(amount) AS total_revenue
FROM orders
WHERE status = 'Completed';

Result: 4700. This assumes amount represents revenue and completed orders are the appropriate revenue definition for the business.

SUM() ignores NULL values. If all values are NULL, it returns NULL rather than zero.

3.3 AVG() - Calculate averages

SELECT AVG(amount) AS average_order_amount
FROM orders;

Result: 1260. The average is calculated using the five non-NULL amounts:

(1200 + 800 + 1500 + 2000 + 800) / 5 = 1260

NULL values are excluded from the average calculation.

3.4 MIN() and MAX()

SELECT
    MIN(amount) AS minimum_amount,
    MAX(amount) AS maximum_amount
FROM orders;

Result: minimum = 800, maximum = 2000.

Function	Purpose	NULL behavior
`COUNT(*)`	Counts rows	Counts every row
`COUNT(column)`	Counts non-NULL values	Ignores NULL
`COUNT(DISTINCT column)`	Counts unique non-NULL values	Ignores NULL
`SUM(column)`	Calculates total	Ignores NULL
`AVG(column)`	Calculates average	Ignores NULL
`MIN(column)`	Finds minimum	Ignores NULL
`MAX(column)`	Finds maximum	Ignores NULL

Real-world Data Engineering example

Imagine building a daily sales summary table for a dashboard.

SELECT
    city,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY city;

This is a typical aggregation step in a warehouse model. In a production pipeline, define how to handle missing cities and amounts, and whether cancelled orders should be included.

4. NULL Handling

NULL represents an unknown, missing, or unavailable value. It is not the same as zero, an empty string, or the text 'NULL'.

4.1 IS NULL and IS NOT NULL

Incorrect:

SELECT *
FROM orders
WHERE city = NULL;

This does not correctly test for NULL because comparisons with NULL generally evaluate to UNKNOWN.

Correct:

SELECT *
FROM orders
WHERE city IS NULL;

To retrieve records with a known city:

SELECT *
FROM orders
WHERE city IS NOT NULL;

4.2 COALESCE() - Replace NULL with a fallback

COALESCE() returns the first non-NULL expression.

SELECT
    order_id,
    COALESCE(city, 'Unknown') AS city
FROM orders;

For order 106, the city becomes Unknown.

You can also use it in aggregations:

SELECT
    SUM(COALESCE(amount, 0)) AS total_amount
FROM orders;

This treats missing amounts as zero for the calculation. However, that is a business decision, not a universally correct rule. A missing amount could indicate incomplete data rather than a genuine zero-value order.

4.3 NULL and arithmetic

SELECT 100 + NULL AS result;

The result is NULL under standard SQL semantics. Similarly, a comparison such as amount > 1000 does not evaluate to TRUE or FALSE when amount is NULL; it evaluates to UNKNOWN. A WHERE clause retains only rows for which the condition is TRUE.

4.4 NULL handling in aggregate functions

SELECT
    COUNT(*) AS total_rows,
    COUNT(amount) AS known_amounts,
    SUM(amount) AS total_amount,
    AVG(amount) AS average_amount
FROM orders;

total_rows	known_amounts	total_amount	average_amount
6	5	6300	1260

SUM(amount) and AVG(amount) ignore the NULL amount, while COUNT(*) still counts that order.

Important interview distinction: SUM(amount) returns 6,300, but SUM(COALESCE(amount, 0)) also returns 6,300 in this dataset. They differ for an all-NULL group: the former returns NULL, while the latter returns zero.

5. CASE WHEN

CASE WHEN lets you implement conditional logic inside SQL queries. It is similar to if-else statements in Python. It is commonly used for categorization, business rules, data quality checks, and feature engineering.

5.1 Basic CASE WHEN

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

SQL evaluates conditions in order and returns the result for the first matching condition.

Example: Categorize orders by amount.

SELECT
    order_id,
    amount,
    CASE
        WHEN amount >= 1500 THEN 'High'
        WHEN amount >= 1000 THEN 'Medium'
        WHEN amount IS NULL THEN 'Unknown'
        ELSE 'Low'
    END AS order_category
FROM orders;

order_id	amount	order_category
101	1200	Medium
102	800	Low
103	1500	High
104	NULL	Unknown
105	2000	High
106	800	Low

Important: The order of conditions matters. If amount >= 1000 appeared before amount >= 1500, an amount of 1500 would be classified as Medium.

5.2 CASE WHEN with aggregate functions

You can use conditional logic inside aggregate functions to calculate multiple metrics in a single query.

SELECT
    COUNT(CASE WHEN status = 'Completed' THEN 1 END) AS completed_orders,
    COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS pending_orders
FROM orders;

completed_orders	pending_orders
3	2

CASE returns 1 for matching rows and NULL otherwise. Since COUNT(expression) ignores NULL values, only matching rows are counted.

You can also calculate completed-order revenue:

SELECT
    SUM(
        CASE
            WHEN status = 'Completed' THEN amount
            ELSE 0
        END
    ) AS completed_revenue
FROM orders;

Result: 4700.

5.3 CASE WHEN for data quality checks

Imagine that an order should have a positive amount to be considered valid.

SELECT
    order_id,
    CASE
        WHEN amount IS NULL THEN 'Missing Amount'
        WHEN amount <= 0 THEN 'Invalid Amount'
        ELSE 'Valid'
    END AS data_quality_status
FROM orders;

This technique is useful in staging models, validation queries, and analytics pipelines.

Simple CASE:

CASE status
    WHEN 'Completed' THEN 'Closed'
    WHEN 'Pending' THEN 'Open'
    ELSE 'Other'
END

Searched CASE:

CASE
    WHEN amount > 1000 THEN 'High Value'
    WHEN amount <= 1000 THEN 'Low Value'
    ELSE 'Unknown'
END

The simple form compares one expression against specific values. The searched form supports more flexible conditions.

6. Data Types and Type Casting

SQL data types determine what kind of values a column can store and which operations can be performed on those values. Incorrect data types can cause ingestion failures, inaccurate aggregations, failed joins, and downstream reporting issues.

6.1 Common SQL data types

Data type	Purpose	Example
`INT` / `INTEGER`	Whole numbers	`100`
`DECIMAL(p,s)` / `NUMERIC(p,s)`	Exact decimal values	`1250.75`
`FLOAT` / `DOUBLE`	Approximate numeric values	`3.14159`
`VARCHAR` / `STRING`	Text	`'Alice'`
`BOOLEAN` / `BOOL`	True or false	`TRUE`
`DATE`	Calendar date	`'2026-10-11'`
`TIMESTAMP`	Date and time	`'2026-10-11 14:30:00'`

Exact type names and supported precision vary by database. For financial amounts, prefer an appropriate fixed-precision decimal type over floating-point types when exact decimal arithmetic is required.

6.2 CAST() - Convert one data type to another

Suppose an ingestion pipeline loads the order amount as text. You may need to convert it to a numeric type before performing calculations.

SELECT
    order_id,
    CAST(amount AS INTEGER) AS amount_numeric
FROM orders_staging;

For example:

SELECT CAST('123' AS INTEGER) AS numeric_value;

Result: 123.

For monetary amounts:

SELECT CAST('1250.75' AS DECIMAL(10, 2)) AS amount;

Result: 1250.75. DECIMAL(10, 2) allows up to 10 total digits, with 2 digits after the decimal point.

6.3 Implicit vs. explicit casting

Implicit casting occurs when a database automatically converts one type to another in an expression.

SELECT 10 + 2.5 AS result;

Many SQL engines promote the integer to a compatible numeric type and return 12.5.

Explicit casting means you specify the conversion yourself.

SELECT CAST('2026-10-11' AS DATE) AS order_date;

Explicit casting makes transformations easier to understand and helps control the intended output type. Automatic conversion rules vary by database.

6.4 Handling invalid data during casting

Consider these input values:

raw_amount
`'1200'`
`'800'`
`'unknown'`
NULL

A strict cast may fail when it encounters 'unknown'. In Google BigQuery, use SAFE_CAST when invalid values should become NULL instead of causing the conversion to fail.

SELECT
    raw_amount,
    SAFE_CAST(raw_amount AS NUMERIC) AS amount
FROM orders_staging;

raw_amount	amount
`'1200'`	1200
`'800'`	800
`'unknown'`	NULL
NULL	NULL

Data Engineering best practice: Do not silently discard invalid records. Track conversion failures and create data quality metrics so upstream data issues can be investigated.

6.5 Type casting in joins

Suppose one table stores customer_id as an integer and another stores it as a string.

SELECT *
FROM customers c
JOIN orders_staging o
    ON c.customer_id = CAST(o.customer_id AS INTEGER);

This illustrates the idea, but a production query should account for invalid string values and avoid repeatedly casting large join keys when possible. Ideally, normalize key types during ingestion or staging.

Interview tip: Type casting can affect performance, partition pruning, join compatibility, and data correctness. Be prepared to explain not just how to cast, but where in a data pipeline the conversion should occur.

Final Interview Practice

Test your understanding with these six questions before moving to intermediate SQL. Try solving each one.

Question 1: Filtering and sorting

Find all completed orders with amounts greater than 1,000, sorted from highest to lowest.

Question 2: GROUP BY and HAVING

Find customers with more than one order.

Question 3: Aggregate functions

Calculate the average order amount, excluding missing amounts.

Question 4: NULL handling

Replace missing city values with 'Unknown'.

Question 5: CASE WHEN

Classify orders above 1,000 as 'High' and all other known amounts as 'Low'. Missing amounts should be 'Unknown'.

Question 6: Type casting

Convert the string '4500.75' to a decimal with two decimal places.

Chapter Summary

By the end of this chapter, you should be able to:

Retrieve, filter, sort, and deduplicate data.
Aggregate records using GROUP BY and filter groups using HAVING.
Explain the behavior of COUNT, SUM, AVG, MIN, and MAX.
Handle NULL values correctly using IS NULL and COALESCE.
Write conditional business logic with CASE WHEN.
Convert data types safely and explain casting-related data quality risks.

What comes next? Intermediate SQL: joins, subqueries, CTEs, window functions, and deduplication patterns used in real-world Data Engineering interviews.

Data Engineering & AI Interview Playbook