HIRE A DEVELOPER

APPLY AS A DEVELOPER

WHY HOW WHAT CLIENTS TEAM COMMUNITY BLOG

CALL US: 888.604.3188

RESOURCES CONTACT FAQ

7 Great SQL
Interview
Questions*
Like

2.4k

Share

Tweet

17

Share

71

SUBMIT AN INTERVIEW QUESTION

Looking for SQL experts? Check out Toptal's

SQL developers.

What does UNION do? What

is the difference between
UNION and UNION ALL ?
Hide answer

LOGIN

merges the contents of two

structurally-compatible tables into a
single combined table. The difference
between UNION and UNION ALL is that
UNION will omit duplicate records
whereas UNION ALL will include
duplicate records.
UNION

Comment

List and explain the

different types of JOIN
clauses supported in ANSIstandard SQL.
Hide answer

ANSI-standard SQL specifies five types

of JOIN clauses as follows:
(a.k.a. simple join):
Returns all rows for which there is
at least one match in BOTH tables.
This is the default type of join if no
specific JOIN type is specified.
INNER JOIN

(or LEFT OUTER JOIN ):

Returns all rows from the left table,
and the matched rows from the
right table; i.e., the results will
contain all records from the left
table, even if the JOIN condition
doesnt find any matching records
in the right table. This means that if
LEFT JOIN

the ON clause doesnt match any

records in the right table, the
JOIN will still return a row in the
result for that record in the left
table, but with NULL in each
column from the right table.
(or RIGHT OUTER JOIN ):
Returns all rows from the right
table, and the matched rows from
the left table. This is the exact
opposite of a LEFT JOIN ; i.e., the
results will contain all records from
the right table, even if the JOIN
condition doesnt find any
matching records in the left table.
This means that if the ON clause
doesnt match any records in the
left table, the JOIN will still return
a row in the result for that record
in the right table, but with NULL in
each column from the left table.
RIGHT JOIN

(or FULL OUTER JOIN ):

Returns all rows for which there is
a match in EITHER of the tables.
Conceptually, a FULL JOIN
combines the effect of applying
both a LEFT JOIN and a
RIGHT JOIN ; i.e., its result set is
equivalent to performing a UNION
of the results of left and right outer
queries.
FULL JOIN

: Returns all records

where each row from the first table
is combined with each row from
the second table (i.e., returns the
Cartesian product of the sets of
rows from the joined tables). Note
that a CROSS JOIN can either be
specified using the CROSS JOIN
syntax (explicit join notation) or
CROSS JOIN

(b) listing the tables in the FROM

clause separated by commas
without using a WHERE clause to
supply join criteria (implicit join
notation).

Comment

Given the following tables:

sql> SELECT * FROM runners;
+----+--------------+
| id | name
|
+----+--------------+
| 1 | John Doe
|
| 2 | Jane Doe
|
| 3 | Alice Jones |
| 4 | Bobby Louis |
| 5 | Lisa Romero |
+----+--------------+
sql> SELECT * FROM races;
+----+----------------+-----------+
| id | event
| winner_id |
+----+----------------+-----------+
| 1 | 100 meter dash | 2
|
| 2 | 500 meter dash | 3
|
| 3 | cross-country | 2
|
| 4 | triathalon
| NULL
|
+----+----------------+-----------+

What will be the result of

the query below?
select * from runners where id not in (select winner_id from races)

Explain your answer and

also provide an alternative

version of this query that

will avoid the issue that it
exposes.
Hide answer

Surprisingly, given the sample data

provided, the result of this query will be
an empty set. The reason for this is as
follows: If the set being evaluated by
the SQL NOT IN condition contains any
values that are null, then the outer
query here will return an empty set,
even if there are many runner ids that
match winner_ids in the races table.
Knowing this, a query that avoids this
issue would be as follows:
select * from runners where id not in (select winner_id from races where

Comment

Consider the following two

query results:

sql> select Count(*) as Total from Orders;

+-------+
| Total |
+-------+
| 100 |
+-------+

sql> select Count(*) as Cust123Total from Orders where customer_id = '12

+--------------+
| Cust123Total |
+--------------+
|
15
|
+--------------+

Given the above query

results, what will be the
result of the query below?
mysql> select Count(*) from Orders where customer_id <> '123'

Hide answer

Answering that the result will be 85

(i.e., 100 - 15) is not necessarily correct.
Specifically, any records with a
customer_id of NULL will not be
included in either count (i.e., they wont
be included in the count of records that
do have a customer_id of 123, nor will
they be included in the count of
records that do not have a
customer_id of 123).
Thus, for example, if 5 of the records in
the Orders table had a customer_id of
NULL , then the query for customer_id
values not equal to 123 would return
80 (i.e., 100 - 15 - 5) rather than 85.

Comment

What will be the result of

the query below? Explain
your answer and provide a
version that behaves
correctly.
select case when null = null then 'Yup' else 'Nope' end as Result;

Hide answer

Subscribe to the Toptal Engineering Blog and be en

Win a FRE
Toptal SHIR

This query will actually yield Nope,

seeming to imply that null is not
equal to itself! The reason for this is
that the proper way to compare a value
to null in SQL is with the is
operator, not with = .

Email

Accordingly, the correct version of the

above query that yields the expected
result (i.e., Yup) would be as follows:
select case when null is null then 'Yup' else 'Nope' end as Result;

GET MY FREE T-SHIRT

Comment

We will never spam you. Unsubscribe any time.

[Note: This question is

MySQL-specific]

Consider the following

table definition in a MySQL
database:
CREATE TABLE example (
id INT NOT NULL,
text1 VARCHAR(32) NOT NULL,
text2 VARCHAR(32) NOT NULL DEFAULT 'foo'
);

What will be the result of

the two INSERT statements
below if strict SQL mode is
enabled? How will the
result differ if strict SQL
mode is not enabled?
Explain your answer.
INSERT INTO example (id) VALUES(1);
INSERT INTO example (text1) VALUES('test');

Hide answer

Since the text1 and text2 columns

are marked as NOT NULL and have no
explicit DEFAULT clauses, attempts to
insert implicit NULL values into these
columns should presumably fail (note
that you can use the
SHOW CREATE TABLE statement to see
which columns have an explicit
DEFAULT clause). So accordingly, these
two INSERT statements should fail
since they dont specify a value for one
or more of these columns.

If strict SQL mode is enabled, an error

will indeed occur and the statements

will be rolled back. (Note: In the case of

a multi-row statement on a nontransactional table, an error occurs, but
if this happens for the second or
subsequent row of the statement, the
preceding rows will have been
inserted.)
However, if strict SQL mode is *not
enabled,* MySQL sets the column to
the implicit default value for the
column data type rather than failing.
In MySQL, implicit defaults are
determined as follows:
For numeric types, the default is
typically zero.
For date and time types, the
default is the appropriate zero
value for the type. (This actually a
bit more complicated for fields
with the type TIMESTAMP .)
For string types (other than ENUM ),
the default value is the empty
string. For ENUM , the default is the
first enumeration value.
Therefore, after the above two INSERT
statements are executed, the contents
of the table will be as follows:
mysql> SELECT * FROM example;
+----+-------+-------+
| id | text1 | text2 |
+----+-------+-------+
| 1 |
| foo
|
| 0 | test | foo
|
+----+-------+-------+

As shown, MySQL has inserted an

empty string into column text1 on the
first row, and zero into column id on
the second row, even though each

column is defined as
DEFAULT clause.

NOT NULL

with no

Comment

[Note: This question is

MySQL-specific]
If we create a table and
insert a row as follows:

CREATE TABLE game (team1 VARCHAR(32), team2 VARCHAR(32), start TIMESTAMP

INSERT INTO game VALUES ("argentina", "brazil", now(), now());

The resulting table will be

as follows:
+-----------+--------+---------------------+---------------------+
| team1
| team2 | start
| end
|
+-----------+--------+---------------------+---------------------+
| argentina | brazil | 2014-07-17 20:44:35 | 2014-07-17 20:44:35 |
+-----------+--------+---------------------+---------------------+

Given the above table

definition, what will be the
effect of the following SQL:
UPDATE game SET team1 = "uruguay" WHERE team1 = "argentina";

Hide answer

The updated table will then be as

follows:
+-----------+--------+---------------------+---------------------+
| team1
| team2 | start
| end
|
+-----------+--------+---------------------+---------------------+
| uruguay
| brazil | 2014-07-17 20:50:10 | 2014-07-17 20:44:35 |
+-----------+--------+---------------------+---------------------+

Note that, in addition to the team1

column being modified, the start
column was automatically updated as
well, but the end field remained
unchanged. Why?
The reason the start column was
automatically updated is that its type is
TIMESTAMP . Fields of type TIMESTAMP
have the behavior that when a record
in the table is updated, the TIMESTAMP
field (i.e., the start field in this
example) gets updated to reflect the
then-current time.
But surprisingly, if we have multiple
columns of type TIMESTAMP , only the
first TIMESTAMP column has this
behavior but the others do not. That is
why the end field remains unchanged
in this example.

Comment

* There is more to interviewing than tricky technical

questions, so these are intended merely as a guide. Not
every A candidate worth hiring will be able to answer them
all, nor does answering them all guarantee an A candidate.
At the end of the day, hiring remains an art, a science and
a lot of work.