Advance SQL Queries

1. How to Query for finding the nth maximum salary ?

Select distinct a.sal from emp a where (&n-1) = (select count (unique sal ) from emp b where b.sal > a.sal)

2. How to Query for finding the nth minimum salary ?

Select a.sal from emp1 a where (&n-1) = (select count (unique sal) from emp1 b where b.sal < a.sal)

3. What is Query for deleting the duplicate rows in table ?

Delete from emp where rowed not in (select max(rowid) from emp group by empno)

4. What is Query for finding the 2nd maximum ?

Select empno from emp where sal = (select max(sal) from emp where sal <> (select max(sal) from emp));

5. What is Query for finding the 2nd minimum ?

Select empno from emp where sal = (select min(sal) from emp where sal <> (select min(sal) from emp));

6. How to Query to find the cumulative total?

Select sum(x.sal) from emp1 x, emp1 y where y.rowid >= x.rowed group by y.row order by sum(x.sal)

7. How to Query to find the alternate rows ?

Select empno, ename from emp where (empno,rownum) in (select empno, mod(rownum,2) from emp);

8. How to Query to find the other alternate rows ?

Select * from emp where rowed not in (select empno, ename from emp where (empno,rownum) in (select empno,mod(rownum,2) from emp));

9. How to Query to delete alternate rows ?

Delete from emp where (empno,rownum) in (select empno, mod(rownum,2) from emp);

10. What is Query to print some text with the column values ?

Select empno,deptno, decode (mod(rownum,5),0,'*****') print from emp;

11. what is Query to get column without specifying the column name ?

Select &n, &q from emp where deptno = 10;

12. Query to delete duplicate rows by leaving one row deleted on specific condition ?

Delete from emp where deptno = 10 and rowid not in (select min(rowid) from emp where deptno = 10);

13. Query to delete duplicate rows but leaving one row undeleted ?

Delete from emp where deptno = 10 and rowid not in (select min(rowid) from emp where deptno = x.deptno);

14. What is Query to select all columns, rowid with out specifying the column name ?

Select rowid, &a from emp;

15. What is Query to print static text?
Select empno, sal, 'Maximum from emp where sal = (select max(sal) from emp)


Ur's
AmarAlam