Advanced SQL Interview Questions and Answers

    8 Votes

In this section, we are going to look into some challenging and advanced SQL interview questions with detailed explanations and answers for experienced programmers and Database administrators. Here goes the first questions.

Write a query to find the range of missing employee id's in the employee table ?
Answers to this question helps you answers similar SQL interview questions like missing sequence number in SQL, find gaps and islands in a sequence and SQL query to find missing numbers in a sequence of numbers.

Table Name : Employee
employee_idEMPLOYEE_NAME
1 Chris
4 Alex
5 Sam
7 Robert

For ease of understanding, we had specified only a few rows. In actual scenario, there will be thousands of rows in the employee table. We need to fetch missing sequence. Following query will fetch the gaps in the employee id sequence of the above table.
SELECT  (a.employee_id + 1) range_start , ( MIN (b.employee_id) - 1 ) range_end FROM employee A, employee B WHERE A.employee_id < B.employee_id GROUP BY A.employee_id HAVING A.employee_id + 1  < MIN(B.employee_id)

Confused about the above SQL query. Don't worry, following explanations will help you understand each and every part of it. To start with, let us crack the above query into parts.

First, we need to use "Self Join" on the EMPLOYEE table. We make two EMPLOYEE tables, A and B. Now join them using employee id field. In where clause of the above query less than operator is used instead of equal to operator. Less than operator is used to eliminate the maximum employee id from the result. Now our query looks like this.
SELECT A.employee_id range_start, B.employee_id range_end FROM employee A, employee B WHERE A.employee_id < B.employee_id
Since less than condition is satisfied for multiple cases, query gives all the possible ranges in the intermediate result as shown below.
RANGE_STARTRANGE_END
1 4
1 5
1 7
4 5
4 7
5 7

In order to avoid duplicate rows and to find the actual ending point of the range, we use "group by" in the query. if we are using group by clause, we need to use at least one aggregate function. So we use the "min aggregate function" to find the nearest value.
SELECT A.employee_id range_start, MIN(B.employee_id) range_end FROM employee A, employee B WHERE A.employee_id < B.employee_id GROUP BY A.employee_id

 The above query returns all the ranges including missing and available sequences in the employee table.

RANGE_STARTRANGE_END
1 4
4 5
5 7


But we need to pick the missing range. For that, we use "having" clause. The condition specified in the having clause should display results where the gap in each sequence is more than 1.

SELECT A.employee_id range_start, MIN(B.employee_id) range_end FROM employee A, employee B WHERE A.employee_id < B.employee_id GROUP BY A.employee_id HAVING A.employee_id + 1  < MIN(B.employee_id)

Now the intermediate result shows the missing ranges.

RANGE_STARTRANGE_END
1 4
5 7


To specify the exact range, we add one to range start and reduce one from range end, as shown in the first query.

SELECT (A.employee_id+1) range_start, (MIN(B.employee_id)-1) range_end FROM employee A, employee B WHERE A.employee_id < B.employee_id GROUP BY A.employee_id HAVING A.employee_id + 1  < MIN(B.employee_id)

Our final results are as shown below

RANGE_STARTRANGE_END
2 3
6 6


Write a SQL query to find missing numbers in employee id sequence ?

This question is not similar to the previous question. Query to this questions in much more complex than the previous query. If the gaps in employee id is only one, we can use the following query.

SELECT A.employee_id +1 missing FROM employee A, employee B WHERE A.employee_id = B.employee_id and A.employee_id + 1 NOT IN (SELECT employee_id FROM employee) AND A.employee_id < (SELECT max(employee_id) FROM employee)

Above query gives us following numbers

Missing
2
6

As you can see that, the query returns the first missing number in the gap. As per our employee table we should get 2,3 and 6. But 3 is missing. So earlier query doesn't seem to work. But if use level and connect by in case of Oracle database, we can find the complete set of missing numbers in the sequence. Here goes our query.

SELECT LEVEL AS missing_sequence FROM ( SELECT MIN(employee_id) id_min , max(employee_id) id_max  FROM employee) CONNECT BY LEVEL <= ID_MAX minus SELECT employee_id FROM employee

Result of the above query. 

Missing_Sequence
2
3
6

For better understanding, we will look into the above query in parts. The first thing we do is to fetch the min and max id's of the employee table.

SELECT MIN(employee_id) id_min , MAX(employee_id) id_max FROM employee

Now let us query to get all the id's which is less than the maximum value in the table using "CONNECT BY" and "LEVEL". Oracle connect by clause helps us to fetch all possible hierarchical permutations from the table when it used with "level" keyword.  What it does is that it will give us all the numbers from the one with the maximum id.

SELECT LEVEL as missing_sequence FROM (SELECT MIN(employee_id) id_min , MAX(employee_id) id_max  from employee) CONNECT BY LEVEL <= id_max

But we need missing values. So we use "minus" keyword  and sub query to fetch the id's which is not present in the above query.

Popular Videos

communication

How to improve your Interview, Salary Negotiation, Communication & Presentation Skills.

Got a tip or Question?
Let us know

Related Articles

SQL Queries Interview Questions and Answers
Database & SQL Interview Questions
Oracle Interview Questions and Answers on SQL Queries and Database Theory
SQL Server Interview Questions and Answers