Tuesday, June 14, 2011

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; 










No comments:

Post a Comment

Note: Only a member of this blog may post a comment.