Practical SQL Interview Questions & Answers
1. How do you handle NULL values in SQL?
I check for NULLs using IS NULL or IS NOT NULL. If I need to replace them, I use COALESCE() or IFNULL().
If NULLs affect calculations, I make sure to handle them properly to avoid incorrect results.
2. Difference between WHERE and HAVING?
WHERE filters rows before aggregation, while HAVING filters after aggregation. If I need to filter raw
data, I use WHERE; if I need to filter results based on aggregate functions, I use HAVING.
3. How do you remove duplicates from a table?
If I just need to query unique values, I use DISTINCT. If I need to permanently remove duplicates, I use
ROW_NUMBER() or CTE with DELETE to keep only one version of each duplicate row.
4. How do you optimize a slow-running query?
First, I check the execution plan to find bottlenecks. Then, I optimize indexes, avoid unnecessary SELECT
*, use proper joins, and break down complex queries. If needed, I analyze partitions or materialized
views.
5. What is a window function?
A window function performs calculations across a set of rows related to the current row without
collapsing them into a single result. I use them for running totals, ranking, and moving averages.
6. How do you calculate a running total in SQL?
I use SUM(column) OVER (ORDER BY something), which keeps a cumulative sum without grouping the
data.
7. How do you find the second highest salary?
One way is SELECT MAX(salary) FROM table WHERE salary < (SELECT MAX(salary) FROM table). Another
is using DENSE_RANK() and filtering for RANK = 2.
8. How do you pivot data in SQL?
If the database supports PIVOT, I use it. Otherwise, I use CASE WHEN with GROUP BY.
9. How do you handle large datasets in SQL?
I optimize indexes, use partitioning, limit results with TOP or LIMIT, and avoid subqueries when possible.
If it's a recurring query, I consider materialized views.
10. Difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table
and matches from the right, filling unmatched rows with NULLs.
11. How do you calculate the percentage of a total in SQL?
I divide each value by the total using SUM() with a window function:
SELECT column, value, value * 100.0 / SUM(value) OVER () AS percentage FROM table;
12. How do you find the top N rows per group?
I use ROW_NUMBER() or DENSE_RANK() with PARTITION BY group_column and filter for row_number <=
N.
13. How do you handle time-series data in SQL?
I use date functions, LAG(), LEAD(), and window functions for trends. Indexing by date helps with
performance.
14. How do you calculate a moving average?
I use AVG(column) OVER (ORDER BY date ROWS BETWEEN N PRECEDING AND CURRENT ROW).
15. How do you calculate year-over-year growth?
I use LAG() to get last year’s value and then (current - previous) / previous * 100.
16. How do you calculate the difference between two dates?
I use DATEDIFF() in SQL Server, TIMESTAMPDIFF() in MySQL, or just subtract dates in PostgreSQL.
17. How do you handle case sensitivity in SQL?
It depends on the database. In some, I use LOWER() or UPPER() for comparisons. In others, I adjust
collation settings.
18. How do you calculate the correlation between two columns?
I use CORR(column1, column2) if supported, or calculate covariance and standard deviations manually.
19. How do you find the most frequent value in a column?
I use GROUP BY column ORDER BY COUNT(*) DESC LIMIT 1.
20. How do you calculate cumulative distribution?
I use CUME_DIST() OVER (ORDER BY column), which gives the relative rank of each row.
21. How do you handle slow joins?
I make sure both tables have proper indexes, avoid joining large tables unnecessarily, and use EXPLAIN to
check execution plans.
22. How do you find the median in SQL?
I use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY column) if supported. Otherwise, I use
ROW_NUMBER() to find the middle value.
23. How do you find the number of unique values in a column?
I use COUNT(DISTINCT column).
24. How do you handle large text fields in SQL?
I avoid unnecessary SELECT *, store them in separate tables if performance is an issue, and use full-text
indexing for searches.
25. How do you calculate the standard deviation in SQL?
I use STDDEV(column) or STDDEV_SAMP(column) depending on the database.
26. How do you handle incremental data loads?
I track changes using timestamps, an ID column, or a change-data capture approach. I only insert or
update the necessary records.
27. How do you find the number of weekends between two dates?
I generate all dates in the range and count those where WEEKDAY(date) IN (5,6). In SQL Server, I use
DATEPART(WEEKDAY, date).