add

About Me

My photo
Oracle Apps - Techno Functional consultant

Friday, July 6

Oracle SQL Sample Queries


--to find maximum number of employees in an department name, depnto
select t.dno, dname
from dept, (select emp.deptno dno, count(*)
             from emp
             group by emp.deptno
             having count(*) >= all (select count(*) from emp group by deptno)) t
where t.dno=dept.deptno


-- to find manager no and number of employees reported
select m.ename, m.empno, count(*)
from emp t, emp m
where  m.empno  = t.mgr
group by m.ename,m.empno

-- to group by salary in thousands        
SELECT DECODE(T.SAL,0,'SALARY LESS THAN THOUSAND',1,'SALARY IN THOUSAND',
2,'SALARY IN TWO THOUSAND',3,'SALARY IN THREE THOUSAND',4,'SALARY IN FOUR THOUSAND',
5,'SALARY IN FIVE THOUSAND',4,'SALARY IN SIX THOUSAND') SAL,
COUNT(*) T1
FROM (select  TRUNC(sal/1000) SAL from emp) T
GROUP BY T.SAL

--palindrome, program
CREATE OR REPLACE PROCEDURE PALIN_CHECK(STRDATA VARCHAR2) AS
  iStrLength NUMBER;
  iRev       NUMBER;
  BFlag      BOOLEAN;
BEGIN
  iStrLength := LENGTH(STRDATA);
  iRev       := iStrLength;
  BFlag      := TRUE;
  FOR iCount in 1 .. iStrLength LOOP

    IF (SUBSTR(STRDATA, iCount, 1)) <> (SUBSTR(STRDATA, iRev, 1)) THEN

      BFlag := FALSE;
    END IF;
    iRev := iRev - 1;
  END LOOP;
  IF BFlag = TRUE THEN
    BEGIN
      RAISE_APPLICATION_ERROR(-20001,'GIVEN STRING IS PALINDORME');
    END;
 
  END IF;

END PALIN_CHECK;


--to select (n)5th row in a table
select * from scott.emp a
where 5= (select count(*) from scott.emp b
          where a.rowid>=b.rowid)

to select row between 2 and 10
select empno, ename, job, mgr, hiredate, sal, comm, deptno from
 (select empno, ename, job, mgr, hiredate, sal, comm, deptno, rownum rn from scott.emp 
          )b
where b.rn between 2 and 10         

merge into m1
using m2
on (m1.deptno = m2.dno)
when matched then
update set sal = sal * m2.com
when not matched then
insert values(m2.dno,m2.com)
select * from (select ename,sal,dense_rank() over (order by sal desc) rank from emp) where rank between 2 and 5

sum of positive and negative number
select sum (case when x >0 then x else 0 end),
 sum (case when x <0 then x else 0 end)
from tpn

select sum(decode(sign(x),1,x,NULL)), sum(decode(sign(x),-1,x,NULL)) from tpn

To get latest added record to employee table
select * from emp where rowid = (select max(rowid) from emp)

Display the emploee records who joins the department before their manager?
select e.ename, m.ename from emp e, emp m
where m.empno = e.mgr
and e.hiredate < m.hiredate
---------------------------------------------------------------------
1          10        CLARK          NEW YORK  1
2          10        KING  NEW YORK  2
3          10        MILLER         NEW YORK  3
4          20        ADAMS         DALLAS        1
5          20        FORD DALLAS        2
6          20        JONES            DALLAS        3

 select deptno,
            max(decode(rn,1,ename))||
            max(decode(rn,2,','||ename))||
            max(decode(rn,3,','||ename))||
           max(decode(rn,4,','||ename))||
            max(decode(rn,5,','||ename))||
            max(decode(rn,6,','||ename)) ename, loc
    from (select emp.deptno, ename, loc,
                  row_number () over (partition by emp.deptno order by ename) rn
           from emp, dept where emp.deptno = dept.deptno)
     group by deptno, loc


--To access th record from first
select * from emp a where 5 = (select count(rowid) from emp b where a.rowid >=b.rowid)


--To access th record from last
select * from emp a where 5 = (select count(rowid) from emp b where a.rowid <=b.rowid)

--To get prime number row columns in table
select * from (select rownum rn, t.* from smn_bal_whznbn_lot_srl_lvl_bal t)x
where (mod(x.rn,2)<>0  and mod(x.rn,3)<>0)

select rownum,sal from (select * from emp t order by sal desc) x
group by rownum,sal
having rownum=1

select rownum , x.* from emp x order by sal desc

select * from (select ename, sal,dense_rank() over (order by sal desc) rank from emp) where rank between 2 and 5
  
select a.sal from emp a where 5 =(select count(distinct(b.sal)) from emp b where a.sal<=b.sal)

select * from emp a where 5 = (select count(distinct(b.sal)) from emp b where a.sal < = b.sal)

select ename,empno,sal from (select t.*, rownum rn from emp t order by sal desc) x
group by empno,sal,ename,rownum having rownum =5

select * from (select t.*, dense_rank() over (order by sal desc) rank from emp t)
where rank=3

alter table emp rename column empnos to empno

select * from
(select x.* ,dense_rank() over(order by x.sal desc) rank from (select sum(sal) sal,deptno from  emp group by deptno) x)
where rank=1

select * from (select * from ( select sum(sal) sal,deptno from  emp group by deptno order by sal desc) x
               group by x.sal,x.deptno,rownum
               having rownum=1
               )x, dept d
 where d.deptno = x.deptno
              
select deptno,sum(sal)
from EMP group by deptno


-- deptno, deptname
--empno, empname,deptno,desig
 select x.deptno,x.cnt from (select deptno, count (*) cnt
                              from emp
                              group by deptno
                              order by cnt desc
                              )x
group by x.deptno, x.cnt, rownum
having rownum=1

select * from dept where deptno in (select deptno from emp group by deptno
                                    having count(*) >= all (select count(*) from emp group by deptno)
                                    )
                                                           
select * from emp a, (select max(sal) sal,deptno from emp group by deptno) b
where a.deptno =b.deptno             
and a.sal =b.sal

select * from emp a , (select deptno, max(sal) sal from emp group by deptno) x
where a.deptno = x.deptno
and  a.sal =x.sal

select * from (select max(sal),deptno from emp group by deptno) where rownum <=1;

-- how to  group negative and postive value in a table
create table tpn (x number)


insert into tpn values(2)
insert into tpn values(4)
insert into tpn values(-2)
insert into tpn values(-4)

select * from tpn

select sum (case when x >0 then x else 0 end),
 sum (case when x <0 then x else 0 end)
from tpn


select (case when x >0 then x else 0 end),
 (case when x <0 then x else 0 end)
from tpn


select sum(decode(sign(x),1,x,NULL)), sum(decode(sign(x),-1,x,NULL)) from tpn

select sign(x) from tpn

select * from emp where rowid = (select max(rowid) from emp)

select * from (select t.*, rownum rn from emp t) t
where mod(t.rn,2)=0

select e.ename, m.ename from emp e, emp m
where m.empno = e.mgr
and e.hiredate < m.hiredate

select * from ( select deptno from dept a),
              ( select DNAME from dept b)
             
             
 select deptno,
            max(decode(rn,1,ename))||
            max(decode(rn,2,','||ename))||
            max(decode(rn,3,','||ename))||
           max(decode(rn,4,','||ename))||
            max(decode(rn,5,','||ename))||
            max(decode(rn,6,','||ename)) ename, loc
    from (select emp.deptno, ename, loc,
                  row_number () over (partition by emp.deptno order by ename) rn
           from emp, dept where emp.deptno = dept.deptno)
     group by deptno, loc
           
select * from inc (x number, y char)           
,decode(y,'a',2,'b','b') y ;

select decode(X,1,'A',X)  from inc

insert into inc values(1,'a')
insert into inc values(1,'b')

select e.ename, d.loc, row_number() over (partition by e.deptno order by ename) rn
from emp e, dept d where e.deptno=d.deptno

select deptno,loc,ename
 loc from (select e.ename,d.deptno,loc, row_number() over(partition by e.deptno order by e.ename) rn
               from emp e,dept d
               where e.deptno = d.deptno
               )
group by deptno,loc,ename

select ename,sal,rownum from (select ename,sal from emp order by sal desc)
group by ename,sal,rownum
having rownum=10              
order by rownum

select * from emp

--To access th record from first
select * from emp a where 5 = (select count(rowid) from emp b where a.rowid >=b.rowid)

--To access th record from last
select * from emp a where 5 = (select count(rowid) from emp b where a.rowid <=b.rowid)

select * from (select rownum rn, t.* from emp t)x
where (mod(x.rn,2)<>0  and mod(x.rn,3)<>0)

create or replace package body cur_pack as
cursor c1 return emp%rowtype is select * from emp;
procedure c1_pack as
TYPE emp_row  is table of emp%rowtype index by binary_integer;
emp_row1 emp_row;
cnt number :=0;
begin
open c1;
loop
     fetch c1 into emp_row1(cnt+1);
     exit when c1%notfound;
     cnt:=cnt+1;
--     dbms_output.put_line(emp_row.EMPNO || '||'  || emp_row.ename);
end loop;    
close c1;

for i in 1..cnt loop
dbms_output.put_line(emp_row1(i).ename);
dbms_output.put_line(emp_row1(i).EMPNO);
end loop;

end c1_pack;
end cur_pack;

select * from emp a where 3 = (select count(distinct hiredate) from emp b where a.hiredate <=b.hiredate)

select * from emp order by hiredate desc;

select e.ename, m.ename from emp e, emp m
where m.empno(+) = e.mgr

select e.ename, m.ename from emp e, emp m
where m.empno = e.mgr

select lo.ename "EMP NAMES", hi.ename "MGR NAMES" from
            emp lo, emp hi where lo.mgr = hi.empno(+)

select * from dept
where   deptno not in (select distinct deptno from emp)
 
 select * from emp
select * from dept


select empno,ename,b.deptno,dname from emp a, dept b
            where a.deptno(+) = b.deptno and empno is null;

 
  select a.deptno, a.cnt, b.dname,b.loc from (select deptno, count(*) cnt from emp group by deptno) a, dept b
  where b.deptno = a.deptno
   

            select count(EMPNO), b.deptno, dname from emp a, dept b
            where a.deptno(+)=b.deptno 
            group by b.deptno,b.dname

  select * from emp
  where sal < (select sal from emp where ename= 'ALLEN')
 
  create table emp_tmp as select * from emp
 
 
  create force view  emp_tmp_vw as select * from emp_tmp
 
  select * from user_views;
 
  select * from user_objects where object_type = 'VIEW'
 
  select job from emp where deptno=20 INTERSECT
                        select job from emp where deptno=30
   
select a.deptno, dname, b.avgsal from dept a, (select deptno, avg(sal) avgsal from emp group by deptno )  b
where  a.deptno = b.deptno

select a.deptno,b.dname,avg(sal) sal
from emp a, dept b
where a.deptno = b.deptno
group by a.deptno,b.dname
 
select dept.deptno,dname,loc from emp, dept
where emp.deptno (+) = dept.deptno
and emp.empno IS null


select empno, sal , (case when sal<2000 then sal + (sal*25)/100 else sal end) Revised
from emp
where deptno IN (10,20)


select e.ename, e.sal,m.ename,m.sal
from emp e, emp m
where m.empno = e.mgr
and e.sal > m.sal


select * from emp where sal > all (select sal from emp where deptno =20)
 select ename, deptno, sal from emp where
            sal > (select max(sal) from emp where deptno=20)

 
  select * from emp where HIREDATE < (select hiredate from emp where job='PRESIDENT');
 
 
  select ename, hiredate, TO_CHAR(add_months(hiredate ,58*12),'YYYY') from emp
 
 
             select empno,ename,hiredate,
            to_number(to_char(hiredate,'yy')) + 58 "RETIREMENT DATE" from
            emp  /* not correct */

 
  select sysdate from dual 
  select sysdate + 3/(24*60) from dual
 
 
  select TRUNC(SYSDATE+1) + 12/24 FROM DUAL
 
  SELECT TRUNC(NEXT_DAY(SYSDATE,'FRIDAY')) + 9/24 from dual
 
 
select empno,ename,sal, decode(job,'MANAGER','Manager','Staff')
            from emp ;

 
select * from emp where sal = (select max(sal) from emp  where sal <
            (select max(sal) from emp where sal <
            (select max(sal) from emp where sal <
            (select max(sal) from emp where sal <
            (select max(sal) from emp )))))


 
select * from ( select t.*, dense_rank() over(order by sal desc) rank from emp t )
where rank between 2 and 5


select max(sal) from emp  where sal <
            (select max(sal) from emp ) <
            (select max(sal) from emp where sal <
            (select max(sal) from emp where sal <
            (select max(sal) from emp ))))

 
  select * from emp
  where sal = (select max(sal) from emp
               where sal < (select max(sal) from emp
                            where sal < (select max(sal) from emp
                                         where sal < (select max(sal) from emp
                                                      where sal < (select max(sal) from emp
                                                      )                                        
                                                 )       
                                         )
                            )
               )                                     
  
  
select t.ename, t.sal, (case when t.sal >=1000 THEN 'VALID' ELSE 'WITH IN RANGE' END) status from emp t               
where t.job ='CLERK'


select empno,ename,sal,decode(sign(sal), sign(1000-sal),'Within Range','Not Valid')
            from emp where job = 'CLERK';

  select * from dept where DEPTNO = (select DEPTNO from emp group by DEPTNO having count(*)=3)
 
  select add_months(sysdate,12) from dual
 
             select level, ename EMPNAME, job
            from emp start with job = 'PRESIDENT'  connect by prior empno=mgr;
 
 
  select * from emp
  where rownum <=2
  union

 select * from emp where rownum <= 2 union
            (select * from emp minus
            (select * from emp where rownum <=
            (select count(*)  from emp)))

 
 
  select * from  (select rownum rn, t.* from emp t) x
  where x.rn < =3
  union
 
  select * from  (select rownum rn, t.* from emp t) x
  where rn between (select count(*) -2 from emp) and (select count(*) from emp)
 
 

  select * from cat

queue
xml inside oracle
trigger
performance tuning
code reveiw
indexes
clob
packages
sdlc
audit trigger
scheduler 3 types
cluster, non-cluster index


In order to understand wht is a clustered index in oracle it is imp. to know what is a cluster. A cluster is a method to store data for more than 1 table in the same block.This can be compared to a condition where we have a join condtion of 2 tables(primary key of one is the foreign key in other). Under such conditions, we need to index the cluster i.e. which data segment belongs to which table. Such type of clustering is Indexed clusetering in oracle.Plz make a note that clustered index has some other meaning in other data base esp. Sybase.

CREATE OR REPLACE PROCEDURE BULK_BIND AS
TYPE EMP_NO_TMP IS TABLE OF EMP.EMPNO%TYPE;

EMP_NO EMP_NO_TMP;

TYPE RC IS REF CURSOR RETURN EMP%ROWTYPE;
RC1 RC;
EMP_REC EMP%ROWTYPE;

BEGIN

SELECT EMPNO BULK COLLECT INTO EMP_NO FROM EMP;

FOR I IN 1..EMP_NO.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('EMPNO: ' || EMP_NO(I));
END LOOP;

FORALL I IN EMP_NO.FIRST..EMP_NO.LAST
UPDATE EMP SET EMPNO=EMPNO + 1 WHERE EMPNO=EMP_NO(I);

OPEN RC1 FOR SELECT * FROM EMP;

LOOP
    FETCH RC1 INTO EMP_REC;
    EXIT WHEN RC1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPNO||','||EMP_REC.ENAME);
END LOOP;

END BULK_BIND;


create or replace type complex as  object (
ix       number,
iy       number,
constructor function complex(x integer, y integer)return self as result,
member function comput_complex(ix complex) return complex
);


create or replace type body complex is
      
constructor function complex(m integer, n integer) return self as result is
begin
ix:=m;
iy:=n;
end;

member function comput_complex(ix integer, iy integer) return integer is
begin
return ix+iy;
end;


end;


create or replace procedure complex_prc(ix number, iy number) as
comp complex:=complex(null,null);
x number;
begin
x:=comp.comput_complex(ix,iy);
dbms_output.put_line(x);
end complex_prc;


select deptno,max(decode(rank,1,ename)) First,
max(decode(rank,2,ename)) Second,
max(decode(rank,3,ename)) Third
 from 
(select ename, sal,deptno,row_number() over(partition by deptno order by sal desc) rank from emp)x
where rank<=3
group by deptno

No comments: