An interesting database interview question:

Consider an interview question of finding immediate previous exam date and marks along with current exam date & marks with reference to following table schema

create table #tmp
(
    id VARCHAR (20),
    Name varchar(20),
    Dt datetime,
    Marks varchar(20)
)

Here Name signifies Person, DT is exam date & Marks is mark obtained in that exam.

To build a required query we will first add dummy data into our table #tmp

insert into #tmp values('5','abc','12/01/2011','580')
insert into #tmp values('6','abc','10/01/2011','500')
insert into #tmp values('7','abc','08/01/2011','480')

insert into #tmp values('8','xyz','12/01/2011','580')
insert into #tmp values('9','xyz','10/01/2011','500')

insert into #tmp values('10','lmn','08/01/2011','480')

Now time to check inserted data by simple select statement.

select * from #tmp

Let’s approach to solution of problem what we need is all the rows of our table along with it’s immediate previous exam date and result,

     It is clear that we need to find maximuim exam date(dt) which is less than current exam date(dt) for given person(name)

Step 1:

select
    a.Name,
    a.dt,
    a.Marks,
    (select max(b.dt) from #tmp b where b.dt < a.dt and a.Name=b.Name) as LastExDate
from
    #tmp a

This will give us current record along with immediately previous exam date

Step 2:

Now we need to have one more column that will contain marks obtained by given person

On immediately previous exam i.e. exam with earlier calculated date of exam i.e. LastExDate.

What we are going to do is add self join on #tmp table and take mark from it if exam date match immediately previous exam date.

  So join condition need to have common name but exam date should be immediately previous exam date.

   Immediately previous exam date is calculated by using a inner query  

select
    a.Name,
    a.dt,
    a.Marks,
    (select max(b.dt) from #tmp b where b.dt < a.dt and a.Name=b.Name) as LastExDate,
    j.marks
from
    #tmp a
    left outer join #tmp j on a.name= j.name and j.dt = (select max(d.dt) from #tmp d where d.dt < a.dt and a.Name=d.Name)

If anyone of you have any interesting interview question on database kindly let me know.