.NET/SQL Server job interview questions

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:-


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:-


--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:-
--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.)
--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 :-


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:-


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:-

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:-


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:-
select top 0* into employee_test1_temp from employee_test1
Step2 : Insert the result of the GROUP BY query into the temporary table as:-
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:-
truncate table employee_test1
Step4: Fill the original table with the rows of the temporary table as:-
insert into employee_test1
select * from employee_test1_temp
Now, the duplicate rows from the main table have been removed.
select * from employee_test1
gives the result as:-
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


;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:-

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.




Few Interesting Questions and concepts
There are 3 tables Titles, Authors and Title-Authors (check PUBS db). Write the query to get the author name and the number of books written by that author, the result should start from the author who has written the maximum number of books and end with the author who has written the minimum number of books.


SELECT authors.au_lname, COUNT(*) AS BooksCount FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id INNER JOIN titles ON titles.title_id = titleauthor.title_id GROUP BY authors.au_lname ORDER BY BooksCount DESC
Write a SQL Query to find first day of month?
SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay



There is a table day_temp which has three columns dayid, day and temperature. How do I write a query to get the difference of temperature among each other for seven days of a week?


SELECT a.dayid, a.dday, a.tempe, a.tempe - b.tempe AS Difference FROM day_temp a INNER JOIN day_temp b ON a.dayid = b.dayid + 1
or this query
Select a.day, a.degree-b.degree from temperature a, temperature b where a.id=b.id+1


There is a table which contains the names like this. a1, a2, a3, a3, a4, a1, a1, a2 and their salaries. Write a query to get grand total salary, and total salaries of individual employees in one query.


SELECT empid, SUM(salary) AS salaryFROM employeeGROUP BY empid WITH ROLLUP ORDER BY empid


Update With Case
EmpID EmpName Gender
1 Raja Male
2 Rani Female
In the above table using one query u need to change Gender male to female and who is female need to change male.
UPDATE Emp1 SET Gender=CASE Gender WHEN ‘Male’ THEN ‘Female’WHEN ‘female’ THEN ‘Male’END;
Query to find the maximum salary of an employee
Select * from Employee where salary = (Select max(Salary) from Employee)
Query to Find the Nth Maximum Salary
Select * From Employee E1 Where (3-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary > E1.Salary)
Query to Find the 2nd Maximum Salary
SELECT SALARY FROM EMPLOYEEWHERE SALARY=(SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY <> (SELECT MAX (SALARY) FROM EMPLOYEE))
select max(salary) as Salary from Emplo where salary!=(select max(salary) from Emplo)
SELECT MAX(E1.salary) FROM emplo E1 , emplo E2WHERE E1.salary< E2.salary