50 SQL Interview Questions with Answers (2026)

Q1. What is the difference between WHERE and HAVING?

Answer: WHERE filters rows before grouping. It cannot use aggregate functions. HAVING filters after grouping and can use aggregates like COUNT, SUM, AVG.

-- WHERE: filter before grouping
SELECT city, COUNT(*) FROM orders WHERE amount > 100 GROUP BY city;

-- HAVING: filter after grouping
SELECT city, COUNT(*) FROM orders GROUP BY city HAVING COUNT(*) > 1000;

Q2. What is the difference between DELETE, TRUNCATE, and DROP?

| Command | Removes | Can be rolled back? | Table remains? | | -------- | ------------------- | -------------------- | -------------- | | DELETE | Specific rows | Yes (in transaction) | Yes | | TRUNCATE | All rows | No (in most DBs) | Yes | | DROP | Entire table + data | No | No |

Q3. What is a PRIMARY KEY?

A PRIMARY KEY uniquely identifies each row in a table. It cannot be NULL and must be unique. A table can have only one primary key.

Q4. What is a FOREIGN KEY?

A FOREIGN KEY is a column that references the PRIMARY KEY of another table. It enforces referential integrity — you can't insert a customer_id in orders if that customer_id doesn't exist in customers.

Q5. What is the difference between UNION and UNION ALL?

UNION combines results from two queries and removes duplicates. UNION ALL combines results and keeps all rows including duplicates. UNION ALL is faster because it skips the deduplication step.

-- UNION: unique rows only
SELECT city FROM orders_2025
UNION
SELECT city FROM orders_2026;

-- UNION ALL: all rows including duplicates
SELECT city FROM orders_2025
UNION ALL
SELECT city FROM orders_2026;

Q6. How do you find duplicate rows in a table?

SELECT email, COUNT(*) AS count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Q7. What does DISTINCT do?

DISTINCT removes duplicate values from the result set. SELECT DISTINCT city FROM orders returns each city only once.

Q8. How do you find the second-highest salary?

-- Method 1: Subquery
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 2: Window function (more flexible)
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

Q9. What is a NULL value? How do you handle it?

NULL represents the absence of a value — it means "unknown" or "missing." Handle it with:

• IS NULL / IS NOT NULL for filtering
• COALESCE(column, default_value) to replace NULLs with a default
• NULLIF(a, b) which returns NULL if a equals b

Q10. What is the difference between CHAR and VARCHAR?

CHAR(n) stores a fixed-length string — always n characters, padded with spaces. VARCHAR(n) stores variable-length strings up to n characters. Use VARCHAR for names and text; CHAR for fixed codes like state abbreviations.

Q11. How do you count rows that meet a condition without a subquery?

-- Count orders over ₹500 using CASE
SELECT
  COUNT(*) AS total_orders,
  SUM(CASE WHEN amount > 500 THEN 1 ELSE 0 END) AS high_value_orders
FROM orders;

Q12. What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN returns only rows with matches in both tables. OUTER JOIN (LEFT, RIGHT, FULL) also returns unmatched rows, filling missing columns with NULL.

Q13. How do you transpose rows into columns (pivot)?

Most databases use CASE WHEN for manual pivoting:

SELECT
  restaurant,
  SUM(CASE WHEN city = 'Mumbai' THEN amount ELSE 0 END) AS Mumbai_revenue,
  SUM(CASE WHEN city = 'Delhi' THEN amount ELSE 0 END) AS Delhi_revenue
FROM orders
GROUP BY restaurant;

Q14. What is a VIEW?

A VIEW is a saved SQL query that behaves like a virtual table. It doesn't store data itself — it runs the underlying query each time it's accessed. Use views to simplify complex queries and control data access.

Q15. How do you calculate a running total?

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Q16. What is a CTE and when would you use it?

A CTE (Common Table Expression) is defined with WITH and creates a named temporary result set. Use CTEs to break complex queries into readable steps, or when you need to reference the same subquery multiple times.

Q17. Explain the difference between ROW_NUMBER, RANK, and DENSE_RANK.

All three rank rows. ROW_NUMBER assigns unique sequential numbers to all rows. RANK gives tied rows the same rank but skips the next rank. DENSE_RANK gives tied rows the same rank without skipping.

Q18. How do you find the latest record per customer?

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

Q19. What is a self-join and when would you use it?

A self-join joins a table to itself. Common use: finding employees and their managers from the same employees table.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Q20. How do you calculate month-over-month growth?

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT
  month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
FROM monthly;

Q21. What is the difference between a subquery and a join?

A join combines rows from two tables based on a condition — you can use the joined table's columns freely. A subquery runs a separate query inside the main query — useful when you need an aggregate value or a derived table. Joins are generally faster for large datasets.

Q22. How do you find the median value in SQL?

SQL has no built-in MEDIAN in most databases. Use PERCENTILE_CONT:

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;

Q23. How do you identify gaps in sequential IDs?

SELECT id + 1 AS gap_start
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM orders WHERE id = o.id + 1
)
AND id < (SELECT MAX(id) FROM orders);

Q24. What is an index and why does it matter for analysts?

An index is a data structure that makes queries faster by allowing the database to find rows without scanning the entire table. As an analyst, understanding indexes helps you write efficient queries — filtering on indexed columns is dramatically faster.

Q25. Explain the COALESCE function.

COALESCE(a, b, c) returns the first non-NULL value. Useful for replacing NULLs with defaults:

SELECT COALESCE(delivery_time, 0) AS delivery_time  -- replace NULL with 0
SELECT COALESCE(first_name || ' ' || last_name, email, 'Unknown') AS display_name

Questions 26–35 (continue in same format)

Q26. How do you find records that exist in Table A but not in Table B? → LEFT JOIN + IS NULL or EXCEPT Q27. What is a cartesian product and when does it happen? → Every row × every row; happens with CROSS JOIN or missing ON clause Q28. How do you handle case-insensitive string comparisons? → LOWER(column) = LOWER('value') or ILIKE in PostgreSQL Q29. What is the difference between NOW() and CURRENT_DATE? → NOW() returns datetime with time; CURRENT_DATE returns date only Q30. How do you extract year/month from a date? → EXTRACT(YEAR FROM date_col), DATE_TRUNC('month', date_col) Q31. What does GROUP BY ROLLUP do? → Creates subtotals and grand totals automatically Q32. How do you calculate a 7-day rolling average? → AVG(col) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) Q33. What is the difference between IN and EXISTS? → IN checks a list of values; EXISTS checks if a subquery returns any rows (EXISTS stops at first match, often faster) Q34. How do you concatenate strings in SQL? → CONCAT(a, b) or a || b (varies by database) Q35. What is a stored procedure? → A named, pre-compiled SQL block stored in the database; called with EXEC or CALL

Q36. Write a query to find the nth highest salary.

SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = :n;  -- replace :n with the desired rank

Q37. How do you detect slowly changing dimensions?

Track changes using effective dates and current flags:

SELECT * FROM dim_customer
WHERE customer_id = 123
ORDER BY effective_start_date DESC;

Q38. Explain query optimization techniques.

1. Filter early with WHERE before JOINs where possible
2. Avoid SELECT * — specify only needed columns
3. Use indexed columns in JOIN and WHERE conditions
4. Avoid functions on indexed columns in WHERE (kills index usage)
5. Use EXPLAIN/EXPLAIN ANALYZE to understand query plans
6. Partition large tables and filter on partition keys

Q39. What is the difference between OLAP and OLTP?

OLTP (Online Transaction Processing) — many small, fast read/write transactions. Examples: order placement, payment processing. OLAP (Online Analytical Processing) — few, complex, read-heavy analytical queries across large datasets. Examples: data warehouses, BI tools.

Q40–50 (Practice prompts for self-study)

Q40. Write a query to find duplicate orders by customer within a 5-minute window Q41. Calculate the customer retention rate month over month Q42. Find the most recent purchase for each customer before a specific date Q43. Write a query to pivot monthly sales data without a PIVOT function Q44. Detect sessions from event logs where gaps > 30 minutes start a new session Q45. Calculate percentile rankings for order amounts within each city Q46. Find customers whose order frequency is increasing (trending up) Q47. Calculate the average time between orders per customer Q48. Write a query to find the longest streak of consecutive daily orders Q49. Identify customers who bought product A but never bought product B Q50. Calculate market share by restaurant within each city using window functions