Some tricky SQL queries Part 2 (Interview Questions)

1.Update Employee salary Department wise in single query

update tblEMP
set salary=
case when dept='HR' then
salary + (salary*10)/100 
when dept='IT' then
salary + (salary*20)/100 
when dept='Admin' then
salary + (salary*5)/100 
when dept='SAP' then
salary + (salary*2)/100 
end

2.Get 3 Max salaries ?

select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;

3.Get 3 Min salaries ?

select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal >= b.sal);

4. Count of number of employees in department wise

select count(EMPNO), b.deptno, dname from emp a, dept b where a.deptno(+)=b.deptno group by b.deptno,dname;

5. Delete duplicate rows in a table

delete from emp a where rowid != (select max(rowid) from emp b where a.empno=b.empno);