Friday, August 2, 2013

Important SQL Queries, Important Database Queries

To find the nth row of a table 
In oracle:
Select * from emp where rowid = (select max(rowid) from emp where rownum <= 4)

In Sql Server 2005:
Select * from emp where rowid = (select max(rowid) from emp where row_number() <= 4)

To find duplicate rows
Select * from emp where rowid in (select max(rowid) from emp group by empno, ename, mgr, job, hiredate, comm, deptno, sal)

To delete duplicate rows
Delete emp where rowid in (select max(rowid) from emp group by empno,ename,mgr,job,hiredate,sal,comm,deptno)

To find the count of duplicate rows
Select ename, count(*) from emp group by ename having count(*) >= 1

To display alternative rows in a table 

In oracle:
select * from emp where (rowid,0) in (select rowid,mod(rownum,2) from emp)

Getting employee details of each department who is drawing maximum sal
select * from emp where (deptno,sal) in ( select deptno,max(sal) from emp group by deptno)

To get number of employees in each department, in which department is having more than 2500 employees
Select deptno,count(*) from emp group by deptno having count(*) >2500

To find nth maximum sal 

In oracle:
Select * from emp where sal in (select max(sal) from (select * from emp order by sal) where rownum <= 5)


If you have to give a flat hike to your EMPloyee using the Following CriteriaSalary b/w 2000 and 3000 then Hike is 200Salary b/w 3000 and 4000 then Hike is 300 Salary b/w 4000 and 5000 then Hike is 400 inEMPLOYEE Table
Update EMPLOYEE Set Salary =
Case when Salary between 2000 and 3000 then
Salary = Salary+200
Case when Salary between 3000 and 4000 then
Salary = Salary+300
Case when Salary between 3000 and 4000 then
Salary = Salary+300
End