Data Analyst Interview Questions with Answers: Part-3

21. What is SELECT used for?
SELECT is used to fetch specific columns or data from a table.
Example:
SELECT customer_name, sales FROM orders;

This query returns customer names and their sales from the orders table.

 

22. Difference between WHERE and HAVING?
WHERE filters rows before aggregation.
HAVING filters results after aggregation.
Example:
SELECT product, SUM(sales) AS total_sales
FROM orders
WHERE region = 'East'
GROUP BY product
HAVING SUM(sales) > 100000;

Here, WHERE filters region first, HAVING filters aggregated sales.

 

23. What is GROUP BY?
GROUP BY groups rows with the same values so aggregate functions can be applied.
Example:
SELECT region, SUM(sales) AS total_sales
FROM orders
GROUP BY region;

This gives total sales per region.

 

24. What are aggregate functions?
Aggregate functions perform calculations on multiple rows.
Common examples:
• COUNT → total rows
• SUM → total value
• AVG → average
• MIN / MAX → smallest or largest value
Example:
SELECT COUNT(order_id), AVG(sales)
FROM orders;

 

25. Difference between INNER JOIN and LEFT JOIN?
INNER JOIN: Returns only matching records.
LEFT JOIN: Returns all rows from left table and matching rows from right table.
Example:
SELECT o.order_id, c.customer_name
FROM orders o
LEFT JOIN customers c
ON o.customer_id = c.customer_id;

All orders appear even if customer info is missing.

 

26. What are subqueries?
A subquery is a query inside another query.
Example:
SELECT *
FROM orders
WHERE sales > (SELECT AVG(sales) FROM orders);

Returns orders with sales above average.

 

27. What is a CTE?
CTE (Common Table Expression) is a temporary named result set that improves readability.
Example:
WITH sales_summary AS (
SELECT region, SUM(sales) AS total_sales
FROM orders
GROUP BY region
)
SELECT *
FROM sales_summary
WHERE total_sales > 500000;

 

28. How do you handle duplicates in SQL?
Identify duplicates:
SELECT customer_id, COUNT(*)
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

Remove duplicates (using ROW_NUMBER):
DELETE FROM orders
WHERE order_id IN (
SELECT order_id
FROM (
SELECT order_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) rn
FROM orders
) t
WHERE rn > 1
);

 

29. How do you handle NULL values?
Check NULL:
SELECT *
FROM orders
WHERE sales IS NULL;

Replace NULL:
SELECT COALESCE(sales, 0) AS sales_amount
FROM orders;

 

30. What are window functions?
Window functions perform calculations across rows without grouping them.
Example:
SELECT customer_id, sales, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY sales DESC) AS rn
FROM orders;

This ranks sales per customer without collapsing rows.

Double Tap ♥️ For Part-4