SQL Basics — Interview Questions

These cover the concepts from Day 02 Part 1: SELECT, WHERE, ORDER BY, LIMIT, DISTINCT, and NULL handling. Every question has appeared in real data analyst interviews.

---

Fundamentals

[Beginner] What is SQL and why do data analysts use it?

Show answer

SQL (Structured Query Language) is the standard language for querying and manipulating data stored in relational databases. Data analysts use it because:

• Most business data lives in relational databases (MySQL, PostgreSQL, SQL Server, BigQuery, Snowflake)
• SQL lets you filter, aggregate, join, and transform data without downloading it all first
• It's faster than Excel for large datasets — a query on 10 million rows takes seconds; Excel would crash
• It's reproducible — a query is a piece of code, not a series of manual clicks
• It's universal — the core syntax is the same across all major databases

---

[Beginner] What is the difference between SELECT * and selecting specific columns?

Show answer

SELECT * retrieves every column in the table. Selecting specific columns retrieves only the columns you name.

When to use SELECT *: - Exploring a new table you've never seen before (always add LIMIT) - Debugging — you want to see all the data for a specific row

When to use specific columns: - Every production query and dashboard - Any query running more than once - Large tables — SELECT * on a 200-column table wastes memory and bandwidth

-- Exploration (safe with LIMIT)
SELECT * FROM new_table LIMIT 100;

-- Production query
SELECT order_id, customer_id, order_date, order_total
FROM orders
WHERE status = 'completed';

---

[Beginner] What does DISTINCT do and when would you use it?

Show answer

DISTINCT removes duplicate rows from the result set. It applies to the entire combination of selected columns.

Use it when you want to know: - What unique values exist in a column (SELECT DISTINCT status FROM orders) - How many unique customers placed orders (COUNT(DISTINCT customer_id)) - What countries/categories/segments are in the data

-- What statuses are in the orders table?
SELECT DISTINCT status FROM orders ORDER BY status;

-- How many unique customers placed orders this year?
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date >= '2024-01-01';

---

[Beginner] What is NULL in SQL? Is it the same as zero or an empty string?

Show answer

NULL means "unknown" or "no value" — it is not zero and it is not an empty string.

• 0 is a number with value zero
• '' is an empty string — a string with no characters
• NULL is the absence of any value — we don't know what it is

This matters because: - NULL + 5 evaluates to NULL (not 5) - NULL = NULL evaluates to UNKNOWN (not TRUE) - NULL <> NULL also evaluates to UNKNOWN

The only way to check for NULL is IS NULL or IS NOT NULL:

-- WRONG — always returns zero rows
WHERE delivery_date = NULL;

-- CORRECT
WHERE delivery_date IS NULL;
WHERE delivery_date IS NOT NULL;

---

Filtering and Sorting

[Beginner] What operators can you use in a WHERE clause?

Show answer

Operator	Purpose	Example
=	Equal	WHERE country = 'India'
<> / !=	Not equal	WHERE status <> 'cancelled'
>, <, >=, <=	Comparison	WHERE price > 100
BETWEEN x AND y	Range (inclusive)	WHERE price BETWEEN 50 AND 200
IN (a, b, c)	Match list	WHERE status IN ('pending', 'processing')
LIKE 'pattern'	Pattern match	WHERE email LIKE '%@gmail.com'
IS NULL / IS NOT NULL	NULL check	WHERE delivery_date IS NULL
AND, OR, NOT	Logical operators	WHERE a > 5 AND b < 10

---

[Mid-level] Explain the logical execution order of a SQL query. Why does it matter?

Show answer

SQL evaluates clauses in this order — not the order you write them:

1. FROM — identify the table(s)
2. WHERE — filter rows
3. GROUP BY — group rows
4. HAVING — filter groups
5. SELECT — compute columns and aliases
6. ORDER BY — sort results
7. LIMIT — cap result count

Why it matters:

• You can't use a SELECT alias in WHERE because the alias doesn't exist yet when WHERE runs:

  -- WRONG — order_total alias doesn't exist at WHERE time
  SELECT quantity * unit_price AS order_total
  FROM orders
  WHERE order_total > 100;
  
  -- CORRECT
  WHERE quantity * unit_price > 100;
  

• You CAN use an alias in ORDER BY because ORDER BY runs after SELECT

• HAVING filters after grouping; WHERE filters before grouping — this is why you use HAVING COUNT(*) > 5 not WHERE COUNT(*) > 5

---

[Mid-level] What is the difference between WHERE and HAVING?

Show answer

Both filter rows, but at different stages of query execution:

	WHERE	HAVING
Runs	Before GROUP BY	After GROUP BY
Filters	Individual rows	Groups (aggregated values)
Can use	Column values	Aggregate functions (COUNT, SUM, AVG, etc.)

-- WHERE filters raw rows before grouping
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'       -- filter individual orders first
GROUP BY customer_id
HAVING COUNT(*) > 3;             -- then filter customers with >3 orders

WHERE status = 'completed' — removes cancelled/pending orders before grouping. HAVING COUNT(*) > 3 — removes customers with 3 or fewer completed orders after grouping.

---

[Mid-level] Why is NOT IN with a list that contains NULL dangerous?

Show answer

When the NOT IN list contains a NULL, the entire condition evaluates to UNKNOWN and returns zero rows — even for values that should clearly be excluded.

This happens because NOT IN (a, NULL) expands to: column <> a AND column <> NULL

And column <> NULL is always UNKNOWN.

-- Dangerous: returns zero rows if any value in the subquery is NULL
SELECT * FROM orders
WHERE customer_id NOT IN (SELECT customer_id FROM blacklist);

-- Safe alternatives
WHERE NOT EXISTS (
    SELECT 1 FROM blacklist WHERE blacklist.customer_id = orders.customer_id
);

-- Or filter NULLs from the subquery
WHERE customer_id NOT IN (
    SELECT customer_id FROM blacklist WHERE customer_id IS NOT NULL
);

---

[Mid-level] A query with LIKE '%@gmail.com' on a 50-million-row table takes 30 seconds. Why is it slow, and how would you fix it?

Show answer

Why it's slow: A leading wildcard (LIKE '%...') prevents the database from using an index on the email column. The database must scan every single row to check the pattern — a full table scan on 50 million rows.

Options to fix it:

1. Full-text search index (fastest for text pattern matching)
2. MySQL: FULLTEXT index + MATCH ... AGAINST

3. PostgreSQL: tsvector index + @@ operator

4. Reverse the string — if you only ever search suffixes (like email domains), store a reversed copy of the email and use LIKE 'moc.liamg@%' on the reversed column — now the wildcard is at the end and an index can be used.

5. Add a domain column — store email_domain VARCHAR(100) separately and index it. Query WHERE email_domain = 'gmail.com'.

6. Accept the trade-off — if this query only runs in a batch job, not in a user-facing dashboard, 30 seconds may be acceptable.

---

Practical Scenarios

[Mid-level] A stakeholder asks: "Give me the 10 most recent orders that are still undelivered." Write the query.

Show answer

SELECT
    order_id,
    customer_id,
    order_date,
    status,
    quantity * unit_price   AS order_total
FROM orders
WHERE delivery_date IS NULL
  AND status NOT IN ('cancelled', 'refunded')
ORDER BY order_date DESC
LIMIT 10;

---

[Mid-level] Write a query that returns customers who signed up in 2023 but have placed zero orders. (Hint: use NOT IN or NOT EXISTS.)

Show answer

-- Using NOT IN
SELECT customer_id, first_name, last_name, signup_date
FROM customers
WHERE signup_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND customer_id NOT IN (
      SELECT DISTINCT customer_id FROM orders
  );

-- Using NOT EXISTS (safer — handles NULLs correctly)
SELECT c.customer_id, c.first_name, c.last_name, c.signup_date
FROM customers c
WHERE c.signup_date BETWEEN '2023-01-01' AND '2023-12-31'
  AND NOT EXISTS (
      SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
  );

---

[Senior] You inherit a dashboard query that has been running in production for a year. It uses SELECT * from a join of three tables, has no WHERE clause, and runs every 15 minutes. Table sizes are 5M, 2M, and 800K rows respectively. What do you do?

Show answer

This is a real problem — and a performance/cost incident waiting to happen. Here's the approach:

1. Understand the intent first. Ask: what does this dashboard actually show? What columns does it display? You can't optimise what you don't understand.

2. Add specific column selection. Replace SELECT * with only the columns the dashboard renders. This alone can cut data transfer by 80%+.

3. Add a WHERE clause. Most dashboards show recent data — last 30 days, last quarter. Add WHERE date_column >= CURRENT_DATE - INTERVAL 30 DAY.

4. Add indexes on join and filter columns. Check EXPLAIN / EXPLAIN ANALYZE to see if indexes are being used.

5. Consider materialising results. A query that runs every 15 minutes and returns the same data can be replaced with a scheduled job that writes to a summary table. The dashboard queries the summary table (seconds) instead of the raw tables (minutes).

6. Test in staging before touching production. Document what you changed and why.

---

← SQL Exercises · Next: SQL Advanced →