# Question of the Week -25/12/2013- Question1

Suppose we have a table says tbl_students whose structure is given below:-

Create table tbl_students(Studentid int identity(1,1) , Studentname nvarchar(150))

Suppose it contains the following data:-

Studentid   Studentname
1               Vivek Johari
2              Chandra Singh
4              Avinash Dubey
6              Akhil
7              Sunny Rana
10            Saurabh

As one can see from the data that some rows are deleted from the table as some studentids are missing.

So write a query which gives the studentid which are deleted from this table.

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Interview Questions and tagged . Bookmark the permalink.

### 31 Responses to Question of the Week -25/12/2013- Question1

1. WITH DeletedRows (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(id) from tbl_Students) UNION ALL SELECT missnum + 1, maxid FROM DeletedRows
WHERE missnum < maxid
)
SELECT missnum FROM DeletedRows
LEFT OUTER JOIN EMPLOYEE e on e.id = DeletedRows.missnum
WHERE e.id is NULL OPTION (MAXRECURSION 0)

Hi Avinash

WITH DeletedRows (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(Studentid) from tbl_Students) UNION ALL SELECT missnum + 1, maxid FROM DeletedRows
WHERE missnum < maxid
)
SELECT missnum FROM DeletedRows
LEFT OUTER JOIN tbl_Students e on e.Studentid = DeletedRows.missnum
WHERE e.Studentid is NULL OPTION (MAXRECURSION 0)

Just changing the table name from employee to tbl_students.:-)
very good Keep Going………

2. I really appreciate your effort…great work….keep it up… 🙂

3. The solution Avinash came up with is missing one thing… what if student 11 was removed?
I checked for the current identity in my solution to make sure there are no "hidden" removed students:

WITH Numbers AS (
SELECT 1 AS Number
UNION ALL
SELECT Number + 1
FROM Numbers
WHERE Number < IDENT_CURRENT('tbl_students')
)
SELECT Number
FROM Numbers N
LEFT JOIN tbl_students S ON N.Number = S.StudentId
WHERE S.StudentId IS NULL

• @Arjan Dingemans- Good catch 🙂

Thanks Arjan for the correct answer. Good going………..

• Mohsin Afzal says:

4. Aviral Lamba says:

select number from master..spt_values s
left join tbl_students on s.number = tbl_students.studentid
where s.type='P'
and s.number < (select MAX(studentid) from tbl_students)
and tbl_students.studentid is null order by number asc

5. Aviral Lamba says:

@arjan Dingemans Good solution

6. Aviral Lamba says:

select number from master..spt_values s
left join tbl_students on s.number = tbl_students.studentid
where s.type='P'
and s.number < (select MAX(studentid) from tbl_students)
and tbl_students.studentid is null and number > 0

This query will also run when the studentid column is not an identity column

Good Solution Aviral…… Keep going …:-)

7. select number from master..spt_values s
left join tbl_students on s.number = tbl_students.studentid
where s.type='P'
and s.number <= IDENT_CURRENT('tbl_students')
and tbl_students.studentid is null and number > 0

This is the working solution by using master table @aviral lamba as in your solution too if the last rows are deleted by the user then your solution will be incorrect. 🙂

• Aviral Lamba says:

@chandra prakash singh Good catch :-p

8. Pingback: เงินด่วน ขอนแก่น

10. Pingback: Accountant in Tallahassee FL

11. Pingback: Throne of Eldraine

12. Pingback: review

13. Pingback: Acquistare Sibutramina

14. Pingback: scam

15. Pingback: icdatamaster.com/fr/extension/dwg

16. Pingback: dang nhap w88top

17. Pingback: die cuts

18. Pingback: Nightmare Market

19. Pingback: cbdque.com