sql interview questions

Hello Every one, This post demonstrates some commonly asked SQL queries in a job interview. I will be covering some of the common but tricky queries like:-
*) Finding the nth highest salary of an employee.
Create a table named SqlTest and insert some test data as:-

CREATE TABLE SqlTest
(
ID INT Identity,
Name Varchar(100),
Sal Decimal (10,2)
)

INSERT INTO SqlTest VALUES ('Chetan',1000);
INSERT INTO SqlTest VALUES ('Shilpa',1200);
INSERT INTO SqlTest VALUES ('Surya',1100);
INSERT INTO SqlTest  VALUES ('Kanchan',1300);
INSERT INTO SqlTest  VALUES ('Amit',1400);

First We Find Highest Salary To find the highest salary as

--Find  Highest Salary
select max(Sal) from SqlTest 

the above one is so esay but
if you are asked to find the 3rd highest salary, then the query is as:-

--3rd Highest Salary
select min(Sal) from SqlTest  where Sal in
(select distinct top 3 Sal from SqlTest order by Sal desc)

The result is as :- 1200
now if any one tell to calculate nth highset salary then query is as

--nth Highest Salary
select min(Sal) from SqlTest  where Sal in
(select distinct top n Sal from SqlTest  order by Sal desc)

*) Finding TOP X records from each group.
Create a table named RecordTest and insert some test data as :-

create table test12
(
Itemid int,
subItemId int,
Item varchar(MAX)
)

insert into test12 values(1,-1,'bb1');     
                                                 
insert into test12 values(1,0,'cricket');                                                    
insert into test12 values(1,1,'base1');                                                       
insert into test12 values(2,2,'forest');                                                       
insert into test12 values(2,3,'tree');                                                           
insert into test12 values(2,4,'flower');                                                     
insert into test12 values(3,5,'laptop');                                                       
insert into test12 values(3,6,'camera');                                                 

insert into test12 values(3,7,'cybermbl');                                                    
insert into test12 values(1,8,'F1');

There are three groups of CategoryId each with a value of 17
(group 17 has four records),
18 (group 18 has three records) and
19 (group 19 has three records).

Now,if you want to select top 2 records from each group, the query is as follows:-

select Itemid,subItemId,Item from
(
select Itemid,subItemId,Item,
rank() over (partition by Itemid order by subItemId asc) as rankid
from test12
) photo_test
where rankid < 3 -- replace 3 by any number 2,3 etc for top2 or top3.
order by Itemid,subItemId

The result is as:-

Itemid      subItemId       Item 
1        -1      bb1
1         0            cricket
2         2      forest
2         3      tree
3         5      laptop
3         6      camera

*) Deleting duplicate rows from a table. If you want to delete duplicate data then used following Query :
OR
This can be achieved in tow ways :-
(a) Using a temporary table.
(b) Without using a temporary table.

1 ) Using a temporary or staging table

CREATE TABLE Employee
(
EmpID INT,
EmpName Varchar(100),
EmpSal Decimal (10,2)
)

INSERT INTO Employee VALUES (1,'chetan',1000);
INSERT INTO Employee VALUES (2,'shilpa',1200);
INSERT INTO Employee VALUES (3,'kanchan',1100);
INSERT INTO Employee VALUES (4,'amit',1300);
INSERT INTO Employee VALUES (5,'muthu',1400);
INSERT INTO Employee VALUES (6,'amit',1150);
INSERT INTO Employee VALUES (6,'amit',1150);

Step 1: Create a temporary table from the main table as follow:-

select * into employeeTest1 from Employee 

Step2 : Insert the result of the GROUP BY query into the temporary table as follow:-

insert into employeeTest1 
select EmpID ,EmpName,EmpSal
from Employee 
group by EmpID ,EmpName,EmpSal

Step3: Truncate the original table
Step4: Fill the original table with the rows of the temporary table as:-

insert into Employee 
select * from employeeTest1 

Now Without using a temporary table

;with T as
(
 select * , row_number() over (partition by EmpID order by EmpID) as rank
 from Employee 
)

delete
from T
where rank > 1

I hope that these queries will help you for Interviews as well as in your day database activities. Thank You ChetanV.