SQL Interview Questions

1. Display the department numbers with more than three employees in each dept.
select deptno, count(*) from emp group by deptno having count(*)>3;
2. Find the 3rd Highest Salary
select min(sal)from emp where sal in (select distinct top 3 sal from emp order by emp
desc)
–OR
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2
where e1.sal <= e2.sal);
–OR for nth highest
select distinct sal from emp a where &n = (select count(distinct sal) from emp b
where a.sal >= b.sal);
3. Find the 3rd MIN salary
select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2
where e1.sal >= e2.sal);
4. Get nth max salaries
select distinct hiredate from emp a where &n = (select count(distinct sal) from emp b
where a.sal >= b.sal);
5. Delete Duplicate records 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
6. Write a sql to get the department name having highest number of employees.
Select DeptName From Employee1 Group by DeptName Having count(EmpName) in(
Select Max(A.COUNTEMP) From (select DeptName, count(EmpName)as COUNTEMP from Employee1 group by DeptName) A)
7. Write a SQL query to get highest salary in each Department.
SELECT DeptName, MAX(salary) as ‘Highest salary’ FROM employee GROUP BY DeptName
8. To fetch ALTERNATE records from a table. (EVEN NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null)from emp);
9. To select ALTERNATE records from a table. (ODD NUMBERED)
select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid)from emp);
10. Select FIRST n records from a table.
select * from emp where rownum <= &n;
11. Select LAST n records from a table
select * from emp minus select * from emp where rownum<=(select count(*)-&n from emp);
12. List dept no., Dept name for all the departments in which there are no employees
in the department.
select * from dept where deptno not in (select deptno from emp);
–OR
select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
13. Get Comma Seperated Value
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+’,’ ,”) + Name FROM Production.Product
SELECT @listStr –-COALESCE: Returns the first nonnull expression among its arguments.
— OR, Get CSV values
SELECT SUBSTRING(
(SELECT ‘,’ + s.Name FROM HumanResources.Shift s ORDER BY s.Name
FOR XML PATH(”)),2,200000) AS CSV
14. Query to get Vacation Hours in Comma Seperated Value for Each manager.
SELECT ManagerID
,SUBSTRING((SELECT ‘,’+ CAST(VacationHours AS VARCHAR)
FROM HumanResources.Employee B
WHERE A.ManagerID = B.ManagerID
FOR XML PATH(”)
),2,100)As Vacation_Hours_In_CSV
FROM HumanResources.Employee A
GROUP BY A.ManagerID