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 in EMPLOYEE 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

Tags: Important SQL Queries, Important Database Queries, Most Common SQL Queries