This article is an attempt to answer the most asked essential queries in SQL Server technical interviews, based on my own experience. No theoretical questions are included; this article is meant to be helpful in technical coding interviews.
Sample tables are listed to visualize the data and associate with query answers given.
- ==================
- Consider below tables
- ==================
- EMPLOYEE
- empid empname managerid deptid salary DOB
- 1 emp 1 0 1 6000 1982-08-06 00:00:00.000
- 2 emp 2 0 5 6000 1982-07-11 00:00:00.000
- 3 emp 3 1 1 2000 1983-11-21 00:00:00.000
- 13 emp 13 2 5 2000 1984-03-09 00:00:00.000
- 11 emp 11 2 1 2000 1989-07-23 00:00:00.000
- 9 emp 9 1 5 3000 1990-09-11 00:00:00.000
- 8 emp 8 3 1 3500 1990-05-15 00:00:00.000
- 7 emp 7 2 5 NULL NULL
- 3 emp 3 1 1 2000 1983-11-21 00:00:00.000
- --DEPARTMENT TABLE
- deptid deptname
- 1 IT
- 2 Admin
1. Employee and Manager ID are in the same table; can you get manager names for employees?
Answer:
With the help of Common table expressions, we can achieve this.
- ;with empCTE as
- (
- select e.empid, e.empname, e.managerid,
- CAST('' as varchar(50)) as Mname from employee e
- where managerid = 0
- union all
- select e1.empid, e1.empname, e1.managerid,
- CAST(c.empname as varchar(50)) as Mname from employee e1
- inner join empCTE as C on e1.managerid=c.empid
- where e1.managerid>0
- ) select * from empCTE
2. Can you get employee details whose department id is not valid or department id not present in department table?
Answer
Identifying Department IDs in employee table, which are not available in master.
There are multiple ways to do this.
Using Left JOIN
- SELECT E.EMPID,E.EMPNAME, E.DEPTID FROM EMPLOYEE E
- left outer join DEPARTMENT d
- on E.DEPTID = D.DEPTID
- WHERE D.DEPTID IS NULL
- SELECT E.EMPID,E.EMPNAME, E.DEPTID FROM EMPLOYEE E
- where e.deptid not in (select deptid from department)
Using NOT Exists
- SELECT E.EMPID,E.EMPNAME, E.DEPTID FROM EMPLOYEE E
- where NOT EXISTS (select deptid from department where e.deptid=department.deptid)
Note
"Not In" is the least recommended, considering performance. Outer join and Not Exists are preferred.
Using EXCEPT KEYWORD
if you want to list Department IDs only. INTERSECT and EXCEPT keywords have rules
"Not In" is the least recommended, considering performance. Outer join and Not Exists are preferred.
Using EXCEPT KEYWORD
if you want to list Department IDs only. INTERSECT and EXCEPT keywords have rules
- SELECT deptid FROM EMPLOYEE
- EXCEPT
- SELECT DEPTID FROM DEPARTMENT
3. Can you get the list of employees with same salary?
Answer
With where clause
- Select distinct e.empid,e.empname,e.salary
- from employee e, employee e1
- where e.salary =e1.salary
- and e.empid != e1.empid
4. How can you find duplicate records in Employee table?
Answer
- SELECT EMPID,EMPNAME, SALARY, COUNT(*) AS CNT
- FROM EMPLOYEE
- GROUP BY EMPID,EMPNAME, SALARY
- HAVING COUNT(*)>1
5. How can you DELETE DUPLICATE RECORDS?
Answer
There are multiple options to perform this operation.
Using row count to restrict delete only 1 record
Using row count to restrict delete only 1 record
- set rowcount 1
- DELETE FROM EMPLOYEE WHERE EMPID IN (
- SELECT EMPID
- FROM EMPLOYEE
- GROUP BY EMPID,EMPNAME, SALARY
- HAVING COUNT(*)>1
- )
- set rowcount 0
Use auto increment primary key "add" if not available in the table, as in given example.
- alter table employee
- add empidpk int identity (1,1)
Now, perform query on min of auto pk id, group by duplicate check columns - this will give you latest duplicate records
- select * from employee where
- empidpk not in ( select min(empidpk) from employee
- group by EMPID,EMPNAME, SALARY )
Now, delete.
- Delete from employee where
- empidpk not in ( select min(empidpk) from employee
- group by EMPID,EMPNAME, SALARY )
6. Find the second highest salary.
Answer
- Select max(Salary) from employee
- where Salary not in (Select max(Salary) from employee)
7. Now, can you find 3rd, 5th or 6th i.e. N'th highest Salary?
Answer
Query for 3rd highest salary
- SELECT * FROM EMPLOYEE E
- WHERE 2 = (SELECT COUNT(DISTINCT E1.SALARY)
- FROM EMPLOYEE E1
- WHERE E1.SALARY>E.SALARY)
8. Can you write a query to find employees with age greater than 30?
Answer
- select * from employee
- where datediff(year,dob, getdate()) >30
9. Write an SQL Query to print the name of the distinct employees whose DOB is between 01/01/1960 to 31/12/1987
Answer
- SELECT DISTINCT EmpName FROM Employee
- WHERE DOB BETWEEN '01/01/1960' AND '12/31/1987'
10. Please write a query to get the maximum salary from each department.
Answer
- select DeptId, max(salary) as Salary from employee group by deptid
11. What is wrong with the following query?
- SELECT empName FROM employee WHERE salary <> 6000
Answer
The following query will not fetch record with the salary of 6000 but also will skip the record with NULL.
As per SQL Server logic, it works on 3 values in matching conditions. TRUE or FALSE and UNKNOWN. Here, NULL implies UNKNOWN.
As per SQL Server logic, it works on 3 values in matching conditions. TRUE or FALSE and UNKNOWN. Here, NULL implies UNKNOWN.
to fix this:
- SELECT empName FROM
- employee WHERE salary is NULL or salary <> 6000
12. Can you show one row twice in results from a table?
Answer
Yes. We can use union all or cross join to obtain this.
Yes. We can use union all or cross join to obtain this.
- select deptname from department d where d.deptname='it'
- union all
- select deptname from department d1 where d1.deptname='it'

gingy :)Posted May 8, 2019, 9:13 AM
Thanks for the article. Very useful, I'm in a process of preparing for the meeting. Recommend this book Matthew Urban "TOP 30 SQL Interview Coding Tasks", most common questions. Thanks again.
Shivangi AgarwalPosted May 5, 2019, 1:32 AM
I'm getting error in #27.How can we get our result 0.00?
mhamd abdPosted Dec 16, 2018, 8:10 AM
Thanks a lot for the qThanks a lot for the questions, really helpful for interviews and in real-time too.uestions, really helpful for interviews and in real-time too.
Ravindar KumarPosted Oct 22, 2018, 6:53 AM
Very helpful
Sanjay Singh ChouhanPosted Sep 6, 2018, 5:01 AM
Thanks a lot for the questions, really helpful for interviews and in real-time too.
sas fdsPosted Aug 30, 2018, 1:50 AM
For Question 7 we can write like select * from employee where salary not in (select top 2 * from employee order by salary asc)
sas fdsPosted Aug 30, 2018, 12:50 AM
Will SELF JOIN wont work for your Question 1
Tushar DikshitPosted Aug 13, 2018, 11:58 AM
Thank you all for keeping this post active with reads and comments.!!!
Ashok ChawdryPosted Aug 13, 2018, 7:01 AM
I'm just commenting alternate queries. --To find second highest salary SELECT MIN (SALARY) FROM EMPLOYEE WHERE SALARY IN (SELECT DISTINCT TOP 2 salary FROM employee ORDER BY SALARY DESC) ---To find third highest salary SELECT TOP 1 SALARY FROM EMPLOYEE WHERE SALARY IN (SELECT DISTINCT TOP 3 salary FROM employee ORDER BY SALARY DESC) ORDER BY SALARY ASC --To find second highest salary using CTE WITH SecondHighestSalaryCTE AS (SELECT DISTINCT TOP 2 SALARY FROM employee ORDER BY salary DESC) SELECT MIN(SALARY) AS [Second Highest Salary] FROM SecondHighestSalaryCTE
Ashok ChawdryPosted Aug 10, 2018, 4:01 AM
Q1 Employee and Manager ID are in the same table; can you get manager names for employees? Answer: Self Join select e1.empname as EmployeeName,e2.empname as ManagerName from employee e1 join employee e2 on e1.managerid = e2.empid order by e1.empid
Sasi RekhaPosted May 21, 2018, 1:37 AM
Always contributors of C# corner rockz
Aayush AwasthiPosted May 14, 2018, 10:49 PM
Hi... Thanks alot for these Question... In question one where we will not get detail of emp 8 in output. I used the same data and written script for ques 1 and i am getting emp 8 as per my script and with ur emp 8 details not coming...
Morgan leePosted Apr 22, 2018, 11:11 PM
Hi There, Great info! I recently came across your blog and have been reading along. I thought I would leave my first comment. I don’t know what to say except that I have I wanted to know if we should go for SQL Always-on AG or FCI for SharePoint environment. We are planning to host the whole SharePoint environment in Azure IaaS. what are the pros and cons of SQL Always-on AG and FCI in Azure Iaas for SharePoint. I read multiple articles and watched many videos about how to use this tool - and was still confused! Your instructions were easy to understand and made the process simple. Thanks, Morgan
Sagor AliPosted Apr 22, 2018, 5:32 AM
This is very much helpful & will be more useful in real time application
Tushar DikshitPosted Apr 5, 2018, 3:13 PM
A big thank you all readers of this article. Glad it is helpful.
Rahul shindePosted Mar 26, 2018, 9:05 PM
Thank you so much.........................
Guest UserPosted Mar 4, 2018, 12:53 PM
Very help full...can you post more like this if it possible to you...
Manoj KallaPosted Nov 16, 2017, 6:35 AM
Good article very good... Thanks for sharing.
Kapil BhatiPosted Sep 28, 2017, 4:07 AM
Very useful for who is looking for change. Very nice article.
Hamid KhanPosted Sep 26, 2017, 11:41 PM
Good collection.........................
Prabu ElavarasanPosted Sep 25, 2017, 5:22 AM
Quite interesting. Appreciate your hard work on creating this article.
Upendra Pratap ShahiPosted Sep 21, 2017, 1:42 PM
Nice one Tushar Dikshit sir............
Vishal PrajapatiPosted Sep 19, 2017, 6:50 AM
Good One.................