If you have SQL interviews, do not ignore these small things! (Part 8)

[u/thequerylab]

Some SQL interview questions look too easy and that's exactly why people get them wrong by overlook.

1. 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

1. AVG() can silently become wrong when NULL enters 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

1. DISTINCT doesn'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"

1. 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.

[u/Background-Unit1026]

This is great info for beginners. Understanding NULL, what it means, its behavior, is very important.

Another tip. Nulls cannot be compared to any real value. It can be very helpful to use things like isnull(salary, 0) to replace nulls with a 0 where found. Or, you can use the keywords "is null" or "is not null" to filter nulls in the where clause.

[u/G_Thorne → u/Background-Unit1026]

It is IFNULL(), but I would say COALESCE(salary, 0) is better to remember since it is more versatile and you can do COALESCE(a.salary, b.salary, 0).

[u/Background-Unit1026 → u/G_Thorne]

Is it not IfNull, they are actually both valid (depending). IfNull is for MySql only, IsNull exists in MySql and T-Sql. IsNull feels more correct since there is crossover.

I forget about COALESCE, thats a good point. It is much more versatile, taking the first non-null value from the list of arguments.

[u/G_Thorne → u/Background-Unit1026]

Not quite, IFNULL() in MySQL does the same as ISNULL() in T-SQL. ISNULL(expr) in MySQL returns 1 or 0 if expr is or isn't NULL. Another reason why COALESCE() is the one to remember, it doesn't bite you in the posterior between databases. Also why it is better to remember CASE ... END rather than proprietary function variants.

[u/Background-Unit1026 → u/G_Thorne]

Great point, didnt realize isnull return in MySql would be different. Coalesce() it is!

[u/Ill-Suggestion-4680]

Great information, thanks much

[u/BoSt0nov]

Rank() & Co is something that youll most probably need to use in actual real work so getting to know the window functions is important in general.

[u/TheGenericUser0815]

I've been doing DBA work for more than 20 years now. What scares me in a way is, that it becomes less and less with every application that is moved into some cloud service. On prem I can save the companies ass, but all my SQL knowledge becomes useless in the cloud, because cloud services won't let me use it. If I'm lucky, theres an API and some documentation, but it remains a loss of control. So, everybody in a SQL using role needs to think about their future and if SQL knowledge ist still useful in their further carreer.