10 Most Asked SQL Questions

In Indian DA . BA . Analytics Interviews

Q01
What are the types of JOINs in SQL? Explain with an example.
JOINS | Asked in: 95% of analytics interviews

ANSWER

INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table +
matching from right (NULLs for no match). RIGHT JOIN is the reverse. FULL OUTER JOIN returns all rows
from both tables.

SELECT [Link], d.dept_name

FROM employees e

LEFT JOIN departments d ON e.dept_id = [Link];

■ Tip: Always clarify which JOIN you'd use and WHY — interviewers want to hear your reasoning, not just
syntax.

Q02
What is the difference between WHERE and HAVING clause?
FILTERING | Asked in: 90% of analytics interviews

ANSWER

WHERE filters rows BEFORE grouping. HAVING filters groups AFTER GROUP BY. You cannot use
aggregate functions (SUM, COUNT) inside WHERE — use HAVING for that.

SELECT dept_id, COUNT(*) AS total

FROM employees

WHERE status = 'active'

GROUP BY dept_id

HAVING COUNT(*) > 5;

■ Tip: WHERE = row-level filter. HAVING = group-level filter. This distinction trips up most freshers.
Q03
How do you find the second highest salary from a table?
SUBQUERY / WINDOW FUNCTION | Asked in: 90% of Indian DA/BA interviews

ANSWER

Classic way uses a subquery. Modern way uses DENSE_RANK() window function — preferred in interviews
as it handles duplicates cleanly.

-- Using DENSE_RANK (recommended)

SELECT salary FROM (

SELECT salary,

DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk

FROM employees

) ranked

WHERE rnk = 2;

■ Tip: DENSE_RANK handles ties. ROW_NUMBER does not. Always use DENSE_RANK for 'nth highest'
questions.

Q04
How do you find duplicate records in a table?
GROUP BY + HAVING | Asked in: 85% of analytics interviews

ANSWER

Use GROUP BY on the column(s) you suspect have duplicates, then use HAVING COUNT(*) > 1 to filter
groups with more than one occurrence.

SELECT email, COUNT(*) AS count

FROM customers

GROUP BY email

HAVING COUNT(*) > 1;

■ Tip: Extend this by adding multiple columns in GROUP BY to find duplicate combinations — common in data
cleaning tasks.
Q05
What are Window Functions? Explain ROW_NUMBER, RANK, and DENSE_RANK.
WINDOW FUNCTIONS | Asked in: 80% of mid-level analytics interviews

ANSWER

Window functions perform calculations across a set of rows related to the current row WITHOUT collapsing
them. ROW_NUMBER gives unique numbers. RANK skips numbers on ties. DENSE_RANK does NOT skip
numbers on ties.

SELECT name, salary,

ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,

RANK() OVER (ORDER BY salary DESC) AS rnk,

DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk

FROM employees;

■ Tip: If two employees earn 50,000 — RANK gives 1,1,3. DENSE_RANK gives 1,1,2. Know the difference
cold.

Q06
What is a subquery? What is the difference between correlated and non-correlated
subquery?
SUBQUERIES | Asked in: 80% of analytics interviews

ANSWER

A subquery is a query inside another query. Non-correlated subquery runs independently of the outer query.
Correlated subquery references the outer query and runs once per row — slower but more flexible.

-- Non-correlated

SELECT name FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated

SELECT name FROM employees e1

WHERE salary > (

SELECT AVG(salary) FROM employees e2

WHERE e1.dept_id = e2.dept_id

);

■ Tip: Correlated subqueries are slower. If asked to optimise, suggest replacing with a JOIN or CTE.
Q07
What is a CTE (Common Table Expression) and when do you use it?
CTE | Asked in: 75% of analytics interviews

ANSWER

A CTE is a named temporary result set defined using WITH. It improves readability and is reusable within
the same query. Use CTEs when your query has multiple layers of logic or when a subquery would be
repeated.

WITH avg_salary AS (

SELECT dept_id, AVG(salary) AS avg_sal

FROM employees

GROUP BY dept_id

SELECT [Link], [Link], a.avg_sal

FROM employees e

JOIN avg_salary a ON e.dept_id = a.dept_id

WHERE [Link] > a.avg_sal;

■ Tip: CTEs don't improve performance vs subqueries in most DBs — their real value is readability and reuse.

Q08
How do you handle NULL values in SQL?
NULL HANDLING | Asked in: 75% of fresher analytics interviews

ANSWER

NULL represents missing/unknown data. Use IS NULL or IS NOT NULL to check. Use COALESCE() to
replace NULLs with a default value. Use NULLIF() to return NULL when two expressions are equal.

-- Replace NULL with 0

SELECT name, COALESCE(salary, 0) AS salary

FROM employees;

-- Filter rows where city is missing

SELECT * FROM customers

WHERE city IS NULL;

■ Tip: NULL = NULL returns FALSE in SQL. Never use = NULL. This is a common mistake in written tests.
Q09
Write a query to find customers who placed an order in January but NOT in
February.
DATE FILTERING + SUBQUERY | Asked in: 70% of BA/ops analytics interviews

ANSWER

Use a subquery or NOT IN / NOT EXISTS to find customers in Jan who are absent from Feb's data. This
tests your ability to apply date filtering with set-based logic.

SELECT DISTINCT customer_id

FROM orders

WHERE MONTH(order_date) = 1

AND customer_id NOT IN (

SELECT customer_id FROM orders

WHERE MONTH(order_date) = 2

);

■ Tip: For large tables, NOT EXISTS is faster than NOT IN because NOT IN fails if any NULL is present in the
subquery.

Q10
What is the difference between DELETE, TRUNCATE, and DROP?
DDL / DML | Asked in: 70% of fresher analytics interviews

ANSWER

DELETE removes specific rows, can be rolled back, fires triggers. TRUNCATE removes ALL rows faster,
cannot be rolled back in most DBs, does not fire row-level triggers. DROP removes the entire table structure
+ data permanently.

DELETE FROM employees WHERE dept_id = 5; -- removes rows

TRUNCATE TABLE temp_data; -- clears table

DROP TABLE old_reports; -- removes table

■ Tip: In interviews say: 'DELETE is DML, TRUNCATE is DDL in most databases.' That level of detail
impresses interviewers.

@careerbeyond_degree | Career transition resources for non-tech graduates breaking into analytics