Analytics

1)Inline view: Inline view to used to make alias work in where clause
2)analytical functions

select ename,sal,sal*12 annsal from emp where annsal>15000;

Flow of sequence:
1)From
2)WHere
3)group by
4)having
5)select
6)order by

select ename,sal,sal*12 annsal from emp where annsal>40000;--->This will not work
*
ERROR at line 1:
ORA-00904: "ANNSAL": invalid identifier

select ename,sal,sal*12 annsal from emp order by annsal;------>This will work

select * from (select ename,sal,sal*12 annsal from emp) where annsal>30000;

SQL> select * from (select ename,sal,sal*12 annsal from emp) where annsal>30000;

ENAME SAL ANNSAL

---------- ---------- ----------
JONES 2975 35700
BLAKE 2850 34200
SCOTT 3000 36000
KING 5000 60000

ROWNUM: It is pseudo column also called as a magic column.It is hidden and belongs
to all the tables in a database.These columns get created whenever
installing oracle server

select ename,empno,rownum from emp;

ENAME EMPNO ROWNUM
---------- ---------- ----------
SMITH 7369 1
ALLEN 7499 2
WARD 7521 3
JONES 7566 4
MARTIN 7654 5
BLAKE 7698 6
CLARK 7782 7
SCOTT 7788 8
KING 7839 9
TURNER 7844 10
ADAMS 7876 11
JAMES 7900 12
FORD 7902 13
MILLER 7934 14

select * from emp where rownum<=5;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

select * from emp where rownum=1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20

select * from emp where rownum=2;

no rows selected

select * from emp where rownum>4;

no rows selected

select....from...where rownum>number
1>number

rownum=1
1=1-->True

rownum<=4
1<=4---->True

rownnum=2
1=2--->False

rownum>4
1>4---->False

Fetching top 5 highest paid employees

SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

select * from emp where rownum<=5 order by sal desc;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
7369 SMITH CLERK 7902 17-DEC-80 800
20

select * from (select * from emp order by sal desc ) where rownum<=5;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7839 KING PRESIDENT 17-NOV-81 5000
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7566 JONES MANAGER 7839 02-APR-81 2975
20
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

To fetch nth row from the emp table

SQL> select * from (select e.*,rownum r from emp e) where r=8;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

DEPTNO R
---------- ---------- --------- ---------- --------- ---------- ----------
---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 8
select * from (select ename,sal,job,deptno,rownum r from emp) where r=8;

select * from (select ename,sal,job,deptno,rownum from emp) where rownum=8;

SQL> select * from (select *,rownum r from emp) where r=8;

select * from (select *,rownum r from emp) where r=8
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select * from (select e.*,rownum r from emp e) where r=8;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

DEPTNO R
---------- ---------- --------- ---------- --------- ---------- ----------
---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 8

analytical functions:
These functions are used to find the rank(nth highest/lowest)

1)row_number()
2)rank()
3)dense_rank()

create table ranktab(id number(4),name varchar(15),sal number(5));

insert into ranktab values(1,'Larry',10000);

insert into ranktab values(2,'Tom',10000);
insert into ranktab values(3,'Mark',20000);
insert into ranktab values(4,'Jeff',20000);
insert into ranktab values(5,'Katy',30000);
commit;

select id,name,sal,row_number() over (order by sal desc) as rank from ranktab;

ID NAME SAL RANK
---------- --------------- ---------- ----------
5 Katy 30000 1
3 Mark 20000 2
4 Jeff 20000 3
1 Larry 10000 4
2 Tom 10000 5

select id,name,sal,rank() over (order by sal desc) rank from ranktab;

ID NAME SAL RANK
---------- --------------- ---------- ----------
5 Katy 30000 1
3 Mark 20000 2
4 Jeff 20000 2
1 Larry 10000 4
2 Tom 10000 4

select id,name,sal,dense_rank() over (order by sal desc) rank from ranktab;

ID NAME SAL RANK
---------- --------------- ---------- ----------
5 Katy 30000 1
3 Mark 20000 2
4 Jeff 20000 2
1 Larry 10000 3
2 Tom 10000 3

To find nth highest and nth lowest rank:

-----------------------------------------------------------------------------------
---------------------------------
SQL> select * from(select id,name,sal,dense_rank() over (order by sal desc) rank
from ranktab) where rank=2;
ID NAME SAL RANK
---------- --------------- ---------- ----------
3 Mark 20000 2
4 Jeff 20000 2

SQL> select * from(select id,name,sal,dense_rank() over (order by sal desc) rank

from ranktab) where rank=1;
ID NAME SAL RANK
---------- --------------- ---------- ----------
5 Katy 30000 1

SQL> select * from(select id,name,sal,dense_rank() over (order by sal desc) rank

from ranktab) where rank=3;
ID NAME SAL RANK
---------- --------------- ---------- ----------
1 Larry 10000 3
2 Tom 10000 3

Nth Highest
select * from (select col1,col2,col3,...,dense_rank() over (order by rank_col desc)
as rank from table_name) where rank=n;

Nth Lowest
select * from (select col1,col2,col3,...,dense_rank() over (order by rank_col asc)
as rank from table_name) where rank=n;

===================================================================================
=======
Correleted subquery
---------------------
Parent query passes data to child query
operator
1)exists
2)not exists

select * from dept where exists(select deptno from emp where

dept.deptno=emp.deptno);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO

select * from dept where not exists(select deptno from emp where
dept.deptno=emp.deptno);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON

dept emp
10 10
20 20
30 30
40