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)