SQL QUERIES (Most releated to Oracle)

1. How to find 2 nd Maximum salary?

  we can find by Using SubQueries, Set Operations, RANK etc

  

Subquery: 

select max(partyincome) from party where partyincome not in(select max(partyincome)  from party);

 

Corelated Subquery:

                      Select a.partyincome from party a where 1=(select  count(distinct(b.partyincome)) from party b where          

                      a.partyincome<b.partyincome)

Note:For n th max salary need to place n-1 in place of 1

2.How to Find Duplicate records in a Table?

Select Partyid from party

groupby partyid

Having count(*)>1

3.  How to Eliminate duplicates rows in a table ?

A:DELETE FROM table_name A    WHERE ROWID > (  SELECT min(ROWID) FROM table_name B  WHERE A.col = B.col);

   (OR)   

DELETE FROM table_name A     WHERE ROWID < (  SELECT max(ROWID) FROM table_name B  WHERE A.col = 
             B.col);

4.Query for deleting alternate even rows FROM a table :

DELETE FROM   EMP WHERE  (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,2) FROM   EMP);

5.Query for deleting alternate odd rows FROM a table :

 DELETEFROM   EMP WHERE  (ROWID,1) IN (SELECT ROWID, MOD(ROWNUM,2) FROM   EMP); 

6.Top N rows FROM a table : (Displays top 9 salaried people)

SELECT ename, deptno, sal FROM (SELECT * FROM emp ORDER BY sal DESC)  

    WHERE ROWNUM < 10;