SQL Basics¶

SQL is tested in almost every data science interview because analysts and scientists spend a significant fraction of their work time querying databases. Interviewers use SQL basics to verify you can retrieve, filter, and transform data before trusting you with more complex problems.

Q1: Walk me through the order in which SQL clauses execute.¶

Show answer

SQL is written in a specific order, but the database engine executes it in a different order. Confusing the two is one of the most common sources of errors.

Written order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

Execution order: 1. FROM — identify the source tables and perform joins 2. WHERE — filter rows before any grouping 3. GROUP BY — group the surviving rows 4. HAVING — filter groups after aggregation 5. SELECT — compute the output columns (aliases created here) 6. ORDER BY — sort the output (column aliases are visible here) 7. LIMIT — trim the result set

This explains two common errors: - You cannot reference a SELECT alias in a WHERE clause — WHERE runs before SELECT. - You cannot filter on an aggregate with WHERE — you must use HAVING.

-- Correct: use HAVING to filter on an aggregate
SELECT
    department,
    COUNT(*) AS headcount
FROM employees
WHERE status = 'active'          -- filters rows before grouping
GROUP BY department
HAVING COUNT(*) > 10             -- filters groups after aggregation
ORDER BY headcount DESC
LIMIT 5;

Q2: What is the difference between WHERE and HAVING?¶

Show answer

Both clauses filter rows, but they operate at different stages of query execution.

WHERE filters individual rows before grouping. It cannot reference aggregate functions because aggregation has not happened yet.

HAVING filters groups after GROUP BY and aggregation. It can reference aggregates.

A useful mental model: WHERE is a pre-aggregation filter; HAVING is a post-aggregation filter.

-- Find departments with more than 5 high-salary employees
SELECT
    department,
    COUNT(*) AS high_earners
FROM employees
WHERE salary > 80000            -- row-level filter (before GROUP BY)
GROUP BY department
HAVING COUNT(*) > 5;            -- group-level filter (after GROUP BY)

Common mistake: writing WHERE COUNT(*) > 5 — this raises an error because aggregate functions are not allowed in WHERE.

Interview tip: if an interviewer asks you to filter on a computed aggregate value, reach for HAVING without hesitation.

Q3: How do NULLs behave in SQL — arithmetic, comparisons, and aggregations?¶

Show answer

NULL represents an unknown value. It propagates in arithmetic and comparisons in ways that surprise most beginners.

Arithmetic: any expression involving NULL returns NULL.

SELECT 5 + NULL;    -- NULL
SELECT NULL * 0;    -- NULL (not 0)

Comparisons: comparing anything to NULL returns NULL (not TRUE or FALSE). This means = NULL never matches.

-- Wrong: this returns zero rows even if nulls exist
SELECT * FROM orders WHERE discount = NULL;

-- Correct
SELECT * FROM orders WHERE discount IS NULL;
SELECT * FROM orders WHERE discount IS NOT NULL;

Aggregations: aggregate functions (SUM, AVG, MIN, MAX, COUNT(col)) ignore NULL values. COUNT(*) counts all rows including those with nulls; COUNT(col) counts only non-null values in that column.

SELECT
    COUNT(*)            AS total_rows,
    COUNT(discount)     AS rows_with_discount,
    AVG(discount)       AS avg_nonull_discount
FROM orders;

COALESCE: substitute a default when a value is NULL.

SELECT
    customer_id,
    COALESCE(discount, 0) AS discount_or_zero
FROM orders;

NULLIF: returns NULL if two values are equal — useful to avoid division by zero.

SELECT revenue / NULLIF(units_sold, 0) AS revenue_per_unit
FROM sales;

Q4: What does DISTINCT do, and when should you be cautious with it?¶

Show answer

DISTINCT removes duplicate rows from the result set. It applies to the combination of all selected columns, not to any single column in isolation.

-- Unique combinations of city and country
SELECT DISTINCT city, country
FROM customers
ORDER BY country, city;

When to use it: - Verifying how many unique values exist in a column - Deduplicating a result when you know duplicates can arise from your joins

When to be cautious: - DISTINCT is expensive — it forces a sort or hash to find duplicates. On large tables, this can be slow. - If you're using DISTINCT to hide an incorrect join, you're masking a bug rather than fixing it. Investigate why duplicates are appearing before reaching for DISTINCT. - COUNT(DISTINCT col) counts unique non-null values and is one of the slower aggregate operations.

SELECT
    COUNT(*)                AS total_orders,
    COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE order_date >= '2024-01-01';

Q5: Explain CASE WHEN — how do you use it for computed columns and conditional aggregation?¶

Show answer

CASE WHEN is SQL's conditional expression. It works anywhere an expression is valid: in SELECT, WHERE, ORDER BY, and inside aggregate functions.

Computed column:

SELECT
    order_id,
    total_amount,
    CASE
        WHEN total_amount >= 500 THEN 'high'
        WHEN total_amount >= 100 THEN 'medium'
        ELSE 'low'
    END AS order_tier
FROM orders;

Conditional aggregation — count or sum only rows that meet a condition, without separate subqueries:

SELECT
    product_category,
    COUNT(*)                                          AS total_orders,
    COUNT(CASE WHEN status = 'returned' THEN 1 END)  AS returns,
    SUM(CASE WHEN status = 'returned' THEN total_amount ELSE 0 END) AS returned_revenue
FROM orders
GROUP BY product_category;

This pattern (aggregate + CASE WHEN inside) lets you pivot long data into wide form and compute multiple conditional aggregates in a single pass over the table — far more efficient than multiple separate queries.

Interview tip: if asked "how would you compute the return rate per category in one query", conditional aggregation is the answer.

Q6: What string functions does SQL provide? Give examples of TRIM, CONCAT, SUBSTRING, and LIKE.¶

Show answer

String manipulation is common in data cleaning tasks during interviews.

TRIM / LTRIM / RTRIM — remove whitespace (or specified characters):

SELECT TRIM('  hello world  ');     -- 'hello world'
SELECT LTRIM('   leading');          -- 'leading'

UPPER / LOWER — normalise case for comparison:

SELECT LOWER(email) FROM users;

CONCAT / || — join strings:

SELECT first_name || ' ' || last_name AS full_name FROM employees;
-- or in MySQL/SQL Server:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

SUBSTRING / LEFT / RIGHT:

SELECT SUBSTRING(phone, 1, 3) AS area_code FROM contacts;
SELECT LEFT(postal_code, 4)   AS district    FROM addresses;

LENGTH / CHAR_LENGTH:

SELECT product_code FROM products WHERE LENGTH(product_code) != 8;

LIKE — pattern matching with % (any sequence) and _ (single character):

SELECT * FROM products WHERE name LIKE 'Pro%';       -- starts with "Pro"
SELECT * FROM products WHERE sku  LIKE '___-2024';   -- 3 chars, dash, then 2024
SELECT * FROM emails WHERE address NOT LIKE '%@company.com'; -- external emails

Q7: How do date and time functions work? Cover DATE_TRUNC, EXTRACT, and date arithmetic.¶

Show answer

Date functions are heavily used in time-series analysis and cohort queries.

DATE_TRUNC — round a timestamp down to a time boundary (PostgreSQL / BigQuery):

SELECT
    DATE_TRUNC('month', order_date)  AS month,
    COUNT(*)                         AS orders
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Common intervals: 'year', 'quarter', 'month', 'week', 'day', 'hour'.

EXTRACT / DATE_PART — extract a single component from a date:

SELECT
    EXTRACT(YEAR  FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DOW   FROM order_date) AS day_of_week   -- 0=Sunday
FROM orders;

Date arithmetic — add or subtract intervals:

-- Orders placed in the last 30 days
SELECT * FROM orders
WHERE order_date >= NOW() - INTERVAL '30 days';

-- Days between two dates
SELECT order_id, delivery_date - order_date AS days_to_deliver
FROM orders;

NOW() / CURRENT_DATE / CURRENT_TIMESTAMP:

SELECT NOW();             -- timestamp with timezone
SELECT CURRENT_DATE;      -- date only (no time)

Interview tip: whenever you see a time-series or retention question, reach for DATE_TRUNC to group by period and EXTRACT to isolate components like month-of-year for seasonality analysis.

Q8: What is type casting in SQL and when do you need it?¶

Show answer

Type casting converts a value from one data type to another. You need it when the database stores a value in one type but your query requires another.

CAST syntax (ANSI SQL, portable):

SELECT CAST(price AS NUMERIC(10, 2)) FROM products;
SELECT CAST('2024-01-15' AS DATE)    AS parsed_date;
SELECT CAST(user_id AS VARCHAR)      AS user_id_text;

PostgreSQL shorthand with :::

SELECT price::NUMERIC(10, 2)   FROM products;
SELECT '2024-01-15'::DATE      AS parsed_date;
SELECT revenue::FLOAT / units  AS revenue_per_unit FROM sales;

Common situations requiring a cast: - A date stored as VARCHAR — you need to cast before date arithmetic - Integer division producing an integer result (e.g., 1/4 = 0) — cast one operand to FLOAT - Joining on columns with mismatched types (e.g., INT vs VARCHAR) — cast to match

-- Integer division trap
SELECT 7 / 2;                    -- returns 3 (integer division)
SELECT 7::FLOAT / 2;             -- returns 3.5

-- Date stored as text
SELECT *
FROM events
WHERE event_date::DATE >= '2024-01-01';

Q9: Explain BETWEEN, IN, and IS NULL — and their NOT variants.¶

Show answer

These are shorthand filter operators that produce cleaner SQL than equivalent AND/OR expressions.

BETWEEN is inclusive on both ends:

-- Orders from Q1 2024 (includes Jan 1 and Mar 31)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- Equivalent but more verbose
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date <= '2024-03-31';

IN — match against a list of values (more readable than many ORs):

SELECT * FROM orders
WHERE status IN ('shipped', 'delivered', 'returned');

-- NOT IN — exclude a list
SELECT * FROM products
WHERE category NOT IN ('discontinued', 'out_of_stock');

IS NULL / IS NOT NULL:

SELECT * FROM leads WHERE assigned_to IS NULL;
SELECT * FROM leads WHERE assigned_to IS NOT NULL;

Critical warning: NOT IN with a subquery is dangerous if the subquery can return NULL. One NULL in the list causes NOT IN to return no rows, because any comparison with NULL is NULL (not TRUE). Prefer NOT EXISTS when working with subqueries that might produce NULLs.

-- Dangerous if subquery returns NULLs
SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM blocked_customers);

-- Safer alternative
SELECT o.* FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM blocked_customers bc WHERE bc.id = o.customer_id
);

Q10: How do you alias tables and columns, and why does it matter?¶

Show answer

Aliases give temporary names to columns or tables within a query. They improve readability and are required in certain situations.

Column aliases — rename output columns:

SELECT
    first_name || ' ' || last_name AS full_name,
    salary * 12                    AS annual_salary,
    EXTRACT(YEAR FROM hire_date)   AS hire_year
FROM employees;

Table aliases — shorten table references (required for self-joins):

SELECT
    o.order_id,
    c.customer_name,
    o.total_amount
FROM orders   o
JOIN customers c ON o.customer_id = c.id;

Rules to remember: - Column aliases defined in SELECT are not visible in WHERE or HAVING (execution order: WHERE runs before SELECT). Use the full expression or a subquery/CTE instead. - Column aliases are visible in ORDER BY (ORDER BY runs after SELECT). - Table aliases, once defined, must be used consistently — you cannot mix the full table name and its alias in the same query.

-- Wrong: alias not visible in WHERE
SELECT salary * 12 AS annual_salary FROM employees WHERE annual_salary > 100000;

-- Correct: wrap in a subquery or CTE
SELECT *
FROM (
    SELECT employee_id, salary * 12 AS annual_salary FROM employees
) sub
WHERE annual_salary > 100000;

Q11: How does ORDER BY work, and what happens with NULLs in sorted results?¶

Show answer

ORDER BY sorts the result set. By default the sort is ascending (ASC); use DESC for descending.

SELECT product_name, price, rating
FROM products
ORDER BY rating DESC, price ASC;   -- sort by rating descending, break ties by price ascending

Sorting by position (less readable, avoid in production):

SELECT name, revenue FROM regions ORDER BY 2 DESC;  -- sorts by revenue

NULL ordering: the SQL standard does not define where NULLs sort. PostgreSQL and most ANSI databases place NULLs last in ascending order and first in descending order. You can override explicitly:

SELECT product_name, discontinued_date
FROM products
ORDER BY discontinued_date ASC NULLS LAST;    -- NULLs at the bottom regardless

LIMIT (PostgreSQL, MySQL) / TOP (SQL Server) / FETCH FIRST n ROWS ONLY (ANSI) truncates the result after sorting:

-- Top 10 customers by total spend
SELECT customer_id, SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC
LIMIT 10;

Q12: What is the difference between COUNT(*), COUNT(col), and COUNT(DISTINCT col)?¶

Show answer

These three forms behave differently around NULLs and duplicates:

COUNT(*) — counts every row, including rows where all columns are NULL
COUNT(col) — counts non-NULL values in the specified column
COUNT(DISTINCT col) — counts unique non-NULL values in the column

SELECT
    COUNT(*)                  AS total_rows,
    COUNT(email)              AS rows_with_email,       -- nulls excluded
    COUNT(DISTINCT email)     AS unique_email_addresses -- nulls and dupes excluded
FROM users;

Concrete example: if a table has 100 rows, 10 rows have email = NULL, and 15 rows share the same email address: - COUNT(*) = 100 - COUNT(email) = 90 (10 NULLs excluded) - COUNT(DISTINCT email) = 75 (10 NULLs + 15 duplicates excluded)

Interview tip: interviewers frequently use a scenario like this to test whether you know that COUNT(*) and COUNT(col) can return different numbers. Always clarify which you mean.

Q13: How do you handle conditional logic with COALESCE vs CASE WHEN?¶

Show answer

Both COALESCE and CASE WHEN can express conditional logic, but they serve different purposes.

COALESCE(val1, val2, ...) returns the first non-NULL value in the list. It is the idiomatic, concise tool for null substitution:

-- Use the shipping address; fall back to billing address if shipping is null
SELECT
    order_id,
    COALESCE(shipping_address, billing_address, 'No address on file') AS delivery_address
FROM orders;

CASE WHEN handles arbitrary conditions — not just nulls:

SELECT
    customer_id,
    CASE
        WHEN total_orders > 100 THEN 'vip'
        WHEN total_orders > 20  THEN 'loyal'
        WHEN total_orders > 0   THEN 'regular'
        ELSE 'inactive'
    END AS customer_segment
FROM customer_stats;

Use COALESCE for null fallback — it's shorter and signals intent clearly. Use CASE WHEN for multi-branch logic based on conditions. Avoid nesting multiple CASE WHEN blocks for null handling when COALESCE can do the same job.

-- Verbose null handling with CASE WHEN (avoid this)
SELECT CASE WHEN discount IS NULL THEN 0 ELSE discount END FROM orders;

-- Concise equivalent
SELECT COALESCE(discount, 0) FROM orders;

Q14: What are common filtering mistakes with LIKE and how do wildcards work?¶

Show answer

LIKE supports two wildcards: - % — matches zero or more of any character - _ — matches exactly one character

SELECT * FROM products WHERE name LIKE 'phone%';       -- starts with "phone"
SELECT * FROM products WHERE name LIKE '%phone';       -- ends with "phone"
SELECT * FROM products WHERE name LIKE '%phone%';      -- contains "phone"
SELECT * FROM products WHERE code LIKE 'A_C';          -- A, any one char, C

Common mistakes:

Case sensitivity: LIKE is case-sensitive in PostgreSQL. Use ILIKE for case-insensitive matching, or normalise with LOWER().
```
SELECT * FROM users WHERE LOWER(email) LIKE '%@gmail.com';
```
Leading wildcard disables index: LIKE '%phone' cannot use a B-tree index on name — the database must scan every row. Only patterns anchored at the start (LIKE 'phone%') can use an index.
Escaping the wildcard character: if you need to match a literal % or _, escape it.
```
SELECT * FROM discounts WHERE code LIKE '50\%' ESCAPE '\';
```
SIMILAR TO (PostgreSQL) supports full regex-like syntax if LIKE is insufficient. For heavy pattern matching, consider ~ (regex match operator) in PostgreSQL.

Q15: How do you write a query to find the N most recent records per category?¶

Show answer

This is a classic interview pattern combining ORDER BY, LIMIT, or window functions. The naive version uses a subquery; the scalable version uses ROW_NUMBER().

For a single category — trivial:

SELECT * FROM orders
WHERE category = 'electronics'
ORDER BY order_date DESC
LIMIT 5;

For all categories at once — requires a window function (covered in depth in the window functions file, but the pattern is important here too):

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY order_date DESC
        ) AS rn
    FROM orders
) ranked
WHERE rn <= 5;

Why not just GROUP BY with MAX(order_date)? GROUP BY collapses rows — you lose all columns except the grouped/aggregated ones. The window function approach keeps all columns and lets you select exactly N rows per group.

Interviewers use this problem to see whether you reach for window functions when appropriate. Knowing ROW_NUMBER() OVER (PARTITION BY ...) is essential.