Top 10 Sql Interview Questions with Answers (Part 1)

This post attampts to test yourSql skills in the form of a question/answer based sql tutorial .










Q1->How will you find out the second highest sal from a emp table? 
There are many ways to do this. However the easiest way to do this is as below:-

Assume there is a Emp table with the following fields;-

EMP_ID  EMP_NAME  DEPT    SAL
001          X1                   IT          1000
002          X2                   HR        2000
003          X3                   IT          3000

The one approach would we lke this

select max(sal) from schema.emp  where
                                                sal not in ( select max(sal) from emp );

Explanation:-Here in the inner query first we are selecting the max salary from the available dataset and then after excluding that max sal,we are again selecting the max sal from the remaining dataset. Nice and simple though...rite??

Q2->However what if i want to select the Nth highest sal then?

 Above approach is not gonna work In this case we have to use the analytic functions .

select emp_id,sal from(
              select emp_id,sal,row_number() over(order by sal desc) as rowno
              from emp)
    where rowno=n

in this way we can select the nth highest sal from the emp table.

Explanation:-Here in the inner query we are arranging all the emp in the emp table in order of their salary from highest to lowest and assigning a sequence number using row_number().once that is done rest is pretty straightforward. u just need to select which one do u want. 1 2 3  or Nth Highest.


Q3->The next thing would we to find out Nth highest sal from each department?


select * from (
          select emp_id,dept,sal,row_number() over(partition by dept order by sal desc) as rownum 
         from emp) 
where rownum=n

in this way we can select the nth highest salary each deparment from the emp table.

Explanation:-Here in the inner query the clause "partition by dept" devides the emp data in partitions based on dept and then arrange emp in that dept in order of their salary from highest to lowest.


Q4->How would u delete the duplicate rows from the emp tables?


       delete from  emp where rowid in
                          (select rowid from 
(select rowid,row_number() over (partition by empid order by empid) dup
                                   from  emp)
                            where dup > 1);

Explanation:-Whenever you insert a row in the database a unique rowid is assigned to it.It represent the physical location of that record on the disk.we can use this feature for removing the duplicates from a table.

Suppose your emp table have duplicate entries of employees records and you have to removes the duplicates from it.Since each employee have a unique emp_id in the system we can use that as a key for arranging records in groups.