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

Interview Questions
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.

DMCA.com

31 thoughts on “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)

    1. Hi Avinash
      Really Appreciate your response. Just a modification in your answer and also @Arjan good catch:-)

      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)

  2. 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

    Please comment.

  3. 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

  4. 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

  5. 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. 🙂

  6. Pingback: road cases
  7. Pingback: Throne of Eldraine
  8. Pingback: review
  9. Pingback: scam
  10. Pingback: dang nhap w88top
  11. Pingback: die cuts
  12. Pingback: Nightmare Market
  13. Pingback: cbdque.com

Leave a Reply