Sql Most asking Query

INTRODUCTION

SQL queries most asked in .NET/SQL Server job interviews. These tricky queries may be required in your day to day database usage.

BACKGROUND

This article demonstrates some commonly asked SQL queries in a job interview. I will be covering some of the common but tricky queries like:- 

(i) Finding the nth highest salary of an employee.
(ii) Finding TOP X records from each group.
(iii) Deleting duplicate rows from a table.

NOTE : All the SQL mentioned in this article has been tested under SQL Server 2005. 

(i) Finding the nth highest salary of an employee.

Create a table named Employee_Test and insert some test data as:-

Hide   Copy Code

CREATE TABLE Employee_Test

(

Emp_ID INT Identity,

Emp_name Varchar(100),

Emp_Sal Decimal (10,2)

)

INSERT INTO Employee_Test VALUES ('Anees',1000);

INSERT INTO Employee_Test VALUES ('Rick',1200);

INSERT INTO Employee_Test VALUES ('John',1100);

INSERT INTO Employee_Test VALUES ('Stephen',1300);

INSERT INTO Employee_Test VALUES ('Maria',1400);

It is very easy to find the highest salary as:-

Hide   Copy Code

--Highest Salary

select max(Emp_Sal) from Employee_Test

Now, if you are asked to find the 3rd highest salary, then the query is as:-

Hide   Copy Code

--3rd Highest Salary

select min(Emp_Sal) from Employee_Test where Emp_Sal in

(select distinct top 3 Emp_Sal from Employee_Test order by Emp_Sal desc)

The result is as :- 1200 
To find the nth highest salary, replace the top 3 with top n (n being an integer 1,2,3 etc.)

Hide   Copy Code

--nth Highest Salary

select min(Emp_Sal) from Employee_Test where Emp_Sal in

(select distinct top n Emp_Sal from Employee_Test order by Emp_Sal desc)

(ii) Finding TOP X records from each group

Create a table named photo_test and insert some test data as :-

Hide   Copy Code

create table photo_test

(

pgm_main_Category_id int,

pgm_sub_category_id int,

file_path varchar(MAX)

)

insert into photo_test values

(17,15,'photo/bb1.jpg');    

                                                

insert into photo_test values(17,16,'photo/cricket1.jpg');                                                   

insert into photo_test values(17,17,'photo/base1.jpg');                                                      

insert into photo_test values(18,18,'photo/forest1.jpg');                                                      

insert into photo_test values(18,19,'photo/tree1.jpg');                                                          

insert into photo_test values(18,20,'photo/flower1.jpg');                                                     

insert into photo_test values(19,21,'photo/laptop1.jpg');                                                      

insert into photo_test values(19,22,'photo/camer1.jpg');                                                

insert into photo_test values(19,23,'photo/cybermbl1.jpg');                                                   

insert into photo_test values

(17,24,'photo/F1.jpg');

There are three groups of pgm_main_category_id 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:-

Hide   Copy Code

select pgm_main_category_id,pgm_sub_category_id,file_path from

(

select pgm_main_category_id,pgm_sub_category_id,file_path,

rank() over (partition by pgm_main_category_id order by pgm_sub_category_id asc) as rankid

from photo_test

) photo_test

where rankid < 3 -- replace 3 by any number 2,3 etc for top2 or top3.

order by pgm_main_category_id,pgm_sub_category_id

The result is as:-

Hide   Copy Code

pgm_main_category_id      pgm_sub_category_id       file_path

17                          15                        photo/bb1.jpg

17                          16                        photo/cricket1.jpg

18                          18                        photo/forest1.jpg

18                          19                        photo/tree1.jpg

19                          21                        photo/laptop1.jpg

19                          22                        photocamer1.jpg

(iii) Deleting duplicate rows from a table

A table with a primary key doesn’t contain duplicates. But if due to some reason, the keys have to be disabled or when importing data from other sources, duplicates come up in the table data, it is often needed to get rid of such duplicates. 
This can be achieved in tow ways :- 
(a) Using a temporary table. 
(b) Without using a temporary table. 

(a) Using a temporary or staging table

Let the table employee_test1 contain some duplicate data like:-

Hide   Copy Code

CREATE TABLE Employee_Test1

(

Emp_ID INT,

Emp_name Varchar(100),

Emp_Sal Decimal (10,2)

)

INSERT INTO Employee_Test1 VALUES (1,'Anees',1000);

INSERT INTO Employee_Test1 VALUES (2,'Rick',1200);

INSERT INTO Employee_Test1 VALUES (3,'John',1100);

INSERT INTO Employee_Test1 VALUES (4,'Stephen',1300);

INSERT INTO Employee_Test1 VALUES (5,'Maria',1400);

INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);

INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);

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

Hide   Copy Code

select top 0* into employee_test1_temp from employee_test1

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

Hide   Copy Code

insert into employee_test1_temp

select Emp_ID,Emp_name,Emp_Sal

from employee_test1

group by Emp_ID,Emp_name,Emp_Sal

Step3: Truncate the original table as:-

Hide   Copy Code

truncate table employee_test1

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

Hide   Copy Code

insert into employee_test1

select * from employee_test1_temp

Now, the duplicate rows from the main table have been removed.

Hide   Copy Code

select * from employee_test1

gives the result as:-

Hide   Copy Code

Emp_ID   Emp_name   Emp_Sal

1        Anees       1000

2        Rick        1200

3        John        1100

4        Stephen     1300

5        Maria       1400

6        Tim         1150

(b) Without using a temporary table

Hide   Copy Code

;with T as

(

         select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank

         from employee_test1

)

delete

from T

where rank > 1

The result is as:-

Hide   Copy Code

Emp_ID   Emp_name   Emp_Sal

1        Anees       1000

2        Rick        1200

3        John        1100

4        Stephen     1300

5        Maria       1400

6        Tim         1150

CONCLUSION

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