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:
- What does
2 <> NULL
return? The type of aNULL
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 withNULL
returns false. - What does
3 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. - 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.