SQL Interview Questions
This page consists of SQL interview questions and answers.
Following are the differences between DELETE and TRUNCATE.
DELETE
TRUNCATE
Following are the different types of INDEX.
The idea of Unique Index is to make a column or group of columns unique i.e., they can't have duplicate values.
Primary key is a unique index as it can't have duplicate values.
For clustered index the search is based on the key values and table is physically reordered.
There can be only one clustered index in a table.
For non clustered index the table is not physically reordered and the data is stored in a logical order.
There can be more than one number of non clustered index in a table.
mysql> SELECT CASE WHEN 1 = 1 THEN 'Foo' ELSE 'BAR' END AS result;
The output for the above code is 'Foo' as the condition 1 = 1 is true.
1 = 1
+--------+ | result | +--------+ | foo | +--------+
mysql> denotes MySQL command line.
mysql>
mysql> SELECT CASE WHEN '' = '' THEN 'Space' ELSE 'Not Space' END AS result;
The result will be 'Space' as the condition '' = '' is true.
'' = ''
+--------+ | result | +--------+ | Space | +--------+
mysql> SELECT CASE WHEN NULL = NULL THEN 'isNULL' ELSE 'isNotNULL' END AS result;
The output for the above query is 'isNotNULL'.
+-----------+ | result | +-----------+ | isNotNULL | +-----------+
The reason for the above result is that NULL represents an unknown value and we can't compare two NULL values for equality using the = operator.
NULL
=
mysql> SELECT CASE WHEN NULL IS NULL THEN 'isNULL' ELSE 'isNotNULL' END AS result;
The result for the above query is 'isNULL'.
+--------+ | result | +--------+ | isNULL | +--------+
We get the above result because the correct way to check NULL value is to use the IS operator.
IS
mysql> SELECT studentid, firstname, lastname FROM student WHERE studentid NOT IN ( SELECT studentid FROM event_winner );
Where student table has the following entries.
student
mysql> SELECT * FROM student; +-----------+-----------+----------+---------------------+---------------------+ | studentid | firstname | lastname | modified_at | created_at | +-----------+-----------+----------+---------------------+---------------------+ | s01 | Jane | Doe | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 | | s02 | John | Doe | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 | | s03 | Tin | Tin | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 | | s04 | Bruce | Wayne | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 | | s05 | Clark | Kent | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 | +-----------+-----------+----------+---------------------+---------------------+
And the event_winner table has the following entries.
event_winner
mysql> SELECT * FROM event_winner; +---------+-----------+ | eventid | studentid | +---------+-----------+ | 1 | s01 | | 2 | s04 | | 3 | s02 | | 4 | s01 | | 5 | NULL | +---------+-----------+
Even though there are 4 winners in the event_winner table but still the given query will give us empty result set.
We get the empty result set because the query is using NOT IN to check the result of the subquery.
NOT IN
When using NOT IN we will always get empty result if any value in the subquery is NULL.
The student table:
The event_winner table:
To get the name of the winners we have to write the following query.
mysql> SELECT studentid, firstname, lastname FROM student WHERE studentid IN ( SELECT studentid FROM event_winner );
Output:
+-----------+-----------+----------+ | studentid | firstname | lastname | +-----------+-----------+----------+ | s01 | Jane | Doe | | s04 | Bruce | Wayne | | s02 | John | Doe | +-----------+-----------+----------+
To list all the losers name we have to write the following query.
mysql> SELECT studentid, firstname, lastname FROM student WHERE studentid NOT IN ( SELECT studentid FROM event_winner WHERE studentid IS NOT NULL );
+-----------+-----------+----------+ | studentid | firstname | lastname | +-----------+-----------+----------+ | s03 | Tin | Tin | | s05 | Clark | Kent | +-----------+-----------+----------+