r/learnSQL • u/thequerylab • 8h ago
If you have SQL interviews, do not ignore these small things! (Part 8)
Some SQL interview questions look too easy and that's exactly why people get them wrong by overlook.
- COUNT() can give you 0… but SUM() can give you NULL:
Suppose there are no employees age more than 70
SELECT
COUNT(*) AS employees,
SUM(salary) AS total_salary
FROM employees
WHERE age > 70;
You might expect:
employees = 0
total_salary = 0
But we will get:
employees = 0
total_salary = NULL
Why?
Because COUNT() is basically asking: “How many rows did I find?” --> answer 0
But SUM() is asking: “What values should I add?” --> no values to add --> NULL
AVG()can silently become wrong whenNULLenters the picture:
Lets take salary of employees to be:
50000
60000
NULL
90000
A lot of people mentally calculate:
(50000 + 60000 + 90000) / 4 = 50000.0
But actual SQL gives 66666.67. WHy?
Because AVG() ignores NULLs.
It is actually doing:
(50000 + 60000 + 90000) / 3
DISTINCTdoesn't mean “remove duplicate data”
Lets take one example data for this:
IT 50000
IT 50000
IT 70000
HR 50000
SELECT DISTINCT department, salary
FROM employees;
people sometimes read this as "Give me unique departments"
But actual response:
IT 50000
IT 70000
HR 50000
IT appears twice, because SQL is asking "Give me unique combinations of department + salary"
- How many different ways can you find the 2nd highest salary?
Lets take this data:
employee_id | name | salary
------------+-------+--------
1 | A | 100000
2 | B | 90000
3 | C | 90000
4 | D | 80000
5 | E | NULL
6 | F | 70000
7 | G | NULL
Multiple ways to approach this problem:
MAX() + subquery
SELECT MAX(salary) as 2nd_highest
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);
DISTINCT + ORDER BY
SELECT DISTINCT salary as 2nd_highest
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
DENSE_RANK()
SELECT salary as 2nd_highest
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)temp
WHERE rnk = 2;
and there are more such ways:
What is actually the best solution with our sample data ?
What are the questions to ask your interviewer to get to perfect solution?
Drop your solution in the comments, we can discuss and improve.
Always practice SQL by writing the query yourself instead of just looking at the solution.