Showing posts with label IMP QUERIES. Show all posts
Showing posts with label IMP QUERIES. Show all posts

Friday, May 2, 2014

IMPORTANT QUERIES - PART 2


1.     Get duplicate rows from the table:
Select empno, count (*) from EMP group by empno having count (*)>1;
2.     Remove duplicates in the table:
Delete from EMP where rowid not in (select max (rowid) from EMP group by empno);
3.     Below query transpose columns into rows.
Name
No
Add1
Add2
abc
100
Hyd
bang
xyz
200
Mysore
pune

Select name, no, add1 from A
UNION
Select name, no, add2 from A;
4.     Below query transpose rows into columns.
select
emp_id,
max(decode(row_id,0,address))as address1,
max(decode(row_id,1,address)) as address2,
max(decode(row_id,2,address)) as address3
from (select emp_id,address,mod(rownum,3) row_id from temp order by emp_id )
group by emp_id
Other query:
select
emp_id,
max(decode(rank_id,1,address)) as add1,
max(decode(rank_id,2,address)) as add2,
max(decode(rank_id,3,address))as add3
from
(select emp_id,address,rank() over (partition by emp_id order by emp_id,address )rank_id from temp )
group by
emp_id
5.     Rank query:
Select empno, ename, sal, r from (select empno, ename, sal, rank () over (order by sal desc) r from EMP);
6.     Dense rank query:
The DENSE_RANK function works acts like the RANK function except that it assigns consecutive ranks:
Select empno, ename, Sal, from (select empno, ename, sal, dense_rank () over (order by sal desc) r from emp);
7.     Top 5 salaries by using rank:
Select empno, ename, sal,r from (select empno,ename,sal,dense_rank() over (order by sal desc) r from emp) where r<=5;
Or
Select * from (select * from EMP order by sal desc) where rownum<=5;
8.     2 nd highest Sal:
Select empno, ename, sal, r from (select empno, ename, sal, dense_rank () over (order by sal desc) r from EMP) where r=2;
9.     Top sal:
Select * from EMP where sal= (select max (sal) from EMP);
10.                        How to display alternative rows in a table?
          SQL> select *from emp where (rowid, 0) in (select rowid,mod(rownum,2) from emp);

11.                        Hierarchical queries
Starting at the root, walk from the top down, and eliminate employee Higgins in the result, but
process the child rows.
SELECT department_id, employee_id, last_name, job_id, salary
FROM employees
WHERE last_name! = ’Higgins’
START WITH manager_id  IS NULL
CONNECT BY PRIOR employee_id = menagerie;

IMPORTANT QUERIES - PART 1


1)   To find the nth row of a table
SQL> Select *from emp where rowid = (select max(rowid) from emp where rownum <= 4);
Or
   SQL> Select *from emp where rownum <= 4 minus select *from emp where rownum <= 3;

2)   To find duplicate rows
SQL> Select *from emp where rowid in (select max(rowid) from emp group by empno,          
         ename, mgr, job, hiredate, comm, deptno, sal);
Or
 SQL> Select empno,ename,sal,job,hiredate,comm , count(*) from emp group by
         empno,ename,sal,job,hiredate,comm  having count(*) >=1;

3)   To delete duplicate rows
      SQL> Delete emp where rowid in (select max(rowid) from emp group by
              empno,ename,mgr,job,hiredate,sal,comm,deptno);

4)   To find the count of duplicate rows
      SQL> Select ename, count(*) from emp group by ename having count(*) >= 1;

5)   How to display alternative rows in a table?
          SQL> select *from emp where (rowid,0) in (select rowid,mod(rownum,2) from emp);

6)   Getting employee details of each department who is drawing maximum sal?
       SQL> select *from emp where (deptno,sal) in
               ( select deptno,max(sal)  from emp group by deptno);

7)   How to get number of employees in each department  , in which department is having more than 2500 employees?
       SQL> Select deptno,count(*) from emp group by  deptno having count(*) >2500;

           9) To reset the time to the beginning of the day

                  SQL> Select to_char(trunc(sysdate),’dd-mon-yyyy hh:mi:ss am’) from dual;

10) To find nth maximum sal
  SQL> Select *from emp where sal in (select max(sal) from (select *from emp order by sal)

          where rownum <= 5);