SQL Interview Questions - Set 4

This page consists of SQL interview questions and answers.

Q1: What is the difference between DELETE and TRUNCATE?

Following are the differences between DELETE and TRUNCATE.

• We use DELETE to delete one or more rows from the table whereas, TRUNCATE helps in removing all the rows from the table.
• We can roll back DELETE statement but not TRUNCATE.
• DELETE is slower than TRUNCATE.
• DELETE is DML whereas, TRUNCATE is DDL.

Q2: What are the different types of INDEX?

Following are the different types of INDEX.

• Unique Index
• Clustered Index
• Non Clustered Index

Q3: Explain Unique 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.

Q4: Explain Clustered and Non Clustered Index

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.

Q5: What will be the output of the following SQL query?

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.

+--------+
| result |
+--------+
| foo    |
+--------+

mysql> denotes MySQL command line.

Q6: What will be the output of the following SQL query?

mysql> SELECT
CASE
  WHEN '' = ''
  THEN 'Space'
  ELSE 'Not Space'
END
AS result;

The result will be 'Space' as the condition '' = '' is true.

+--------+
| result |
+--------+
| Space  |
+--------+

Q7: What will be the output of the following SQL query?

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.

Q8: What will be the output of the following SQL query?

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.

Q9: What will be the result of the following SQL query?

mysql> SELECT studentid, firstname, lastname
FROM student
WHERE studentid NOT IN (
  SELECT studentid
  FROM event_winner
);

Where student table has the following entries.

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.

mysql> SELECT * FROM event_winner;
+---------+-----------+
| eventid | studentid |
+---------+-----------+
| 1       | s01       |
| 2       | s04       |
| 3       | s02       |
| 4       | s01       |
| 5       | NULL      |
+---------+-----------+

Answer:

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.

When using NOT IN we will always get empty result if any value in the subquery is NULL.

Q10: List all the winners and losers from the given two tables

The student table:

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 |
+-----------+-----------+----------+---------------------+---------------------+

The event_winner table:

mysql> SELECT * FROM event_winner;
+---------+-----------+
| eventid | studentid |
+---------+-----------+
| 1       | s01       |
| 2       | s04       |
| 3       | s02       |
| 4       | s01       |
| 5       | NULL      |
+---------+-----------+

Answer:

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
);

Output:

+-----------+-----------+----------+
| studentid | firstname | lastname |
+-----------+-----------+----------+
| s03       | Tin       | Tin      |
| s05       | Clark     | Kent     |
+-----------+-----------+----------+