Basic SQL Interview Questions for Data Analyst

Basic-level SQL interview questions for Data Analyst positions.

1. What is the difference between WHERE and HAVING clause?

Answer: WHERE filters rows before grouping, while HAVING filters groups after aggregation. WHERE cannot use aggregate functions, but HAVING can. Example: WHERE filters individual sales records, HAVING filters groups of sales by region.

-- WHERE: Filter rows before aggregation
SELECT department, AVG(salary)
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department;

-- HAVING: Filter after aggregation
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

2. Explain the difference between INNER JOIN and LEFT JOIN.

Answer: INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table and matching rows from the right table (NULL for non-matches). Use LEFT JOIN when you need to keep all records from one table regardless of matches.

-- INNER JOIN: Only matched records
SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- LEFT JOIN: All customers, even without orders
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

3. What are aggregate functions? Name five common ones.

Answer: Aggregate functions perform calculations on sets of rows and return a single value. Five common ones: COUNT() - counts rows, SUM() - adds values, AVG() - calculates average, MAX() - finds maximum, MIN() - finds minimum.

SELECT 
  COUNT(*) AS total_orders,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value,
  MAX(amount) AS largest_order,
  MIN(amount) AS smallest_order
FROM orders;