--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