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.
Table Name : Employee
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.
SELECT A.employee_id range_start, B.employee_id range_end FROM employee A, employee B WHERE A.employee_id < B.employee_id
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.
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.
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
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
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.
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.