SQL tricky questions on job interview

After we showed you 10 main questions asked to a candidate on JavaScript interviews, we found an interesting material with SQL tricky questions. This is a translated summary from the original article by Alexander Chmyr. The questions are related to the basic mechanisms of the language. So, first of all, they will be interesting for the beginners, but perhaps there will be something new for the experienced developers too.

Let’s start:

1. What does 2 <> NULL return? The type of a NULL value in SQL has a slightly different meaning than in applied programming languages. If in C-like languages, NULL means the absence of some value, then in SQL it means only that we don’t know this value. For this reason, any comparison with NULL returns false.
2. What does3 NOT IN (1, 2, NULL) return? As we know from the previous example, 3 <> NULL returns false, and therefore the entire condition (3 <> 1) AND (3 <> 2) AND (3 <> NULL) will also be false.
3. Will this query execute? 

_SELECT 

	order_id,
	order_code,
	SUM(order_value)
FROM 
	orders
GROUP BY
	order_id

It all depends on the database. If this query is executed in MySQL, the order_code column will be added to the GROUP BY clause automatically and the query will execute fine. If this query is executed by MS SQL Server, then an error will be generated by default.

4. Why will this request not execute? 

SELECT 
	user_name,
	YEAR(user_birth_date) AS year_of_birth
FROM 
	users
WHERE
	year_of_birth = 2000

Expressions that are responsible for getting data, such as WHERE, need to use the original field names.

5. Does column order in a composite index matter? Yes. The columns in the search should be placed first in the composite index.

6. What is the difference between TRUNCATE TABLE table_name and DELETE FROM table_name?  Both of these commands will cause the removal of rows from a table called table_name, but this will happen in very different ways.

7. What is the difference between the types CHAR and VARCHAR? CHAR is suitable for storing fixed-length string data and VARCHAR or NVARCHAR are more suitable for other lines.

8. What is the difference between the types VARCHAR and NVARCHAR? In the VARCHAR format, you should store string data that you don’t need to transfer (for example, email addresses). For other cases, you can use NVARCHAR.

9. What is the difference between UNION and UNION ALL?  The difference between them is that if the query contains the same lines, the UNION will remove duplicates, leaving only one of these lines.

10. What is the difference between WHERE and HAVING expressions? WHERE can work independently and filter the data of each row of the result separately, and HAVING expression works only in combination with the GROUP BY expression and filters the already grouped values.

Based on the article by Alexander Chmyr.

If you are interested in the topic and want to understand the SQL queries better, we also recommend you to read this material.