--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:
Post a Comment