Hierarchical queries in Oracle

Hierarchical queries in Oracle – The CONNECT BY clause
The pseudocolumn LEVEL returns the number 1 for the root of the hierarchy, 2 for the
child, 3 for the grandchild, and so on.
Simple Hierarchy Operations
Finding the Root Node
we look for the one node with no parent.
In the EMPLOYEE table we discussed earlier, the value for MANAGER_EMP_ID is
NULL for the uppermost employee,
SELECT EMP_ID, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE
FROM EMPLOYEE
WHERE MANAGER_EMP_ID IS NULL;
EMP_ID LNAME DEPT_ID MANAGER_EMP_ID SALARY HIRE_DATE
--------- ---------- --------- -------------- --------- ---------
7839 KING 10 5000 17-NOV-81
Finding a Node’s Immediate Parent
SELECT E.LNAME "Employee", M.LNAME "Manager"
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER_EMP_ID = M.EMP_ID (+);
Employee Manager
-------------------- --------------------
SMITH FORD
ALLEN BLAKE
WARD BLAKE
JONES KING
MARTIN BLAKE
BLAKE KING
CLARK KING
SCOTT JONES
KING
TURNER BLAKE
ADAMS SCOTT
JAMES BLAKE
FORD JONES
MILLER CLARK
14 rows selected.
Finding Leaf Nodes
SELECT EMP_ID, LNAME, DEPT_ID, MANAGER_EMP_ID, SALARY, HIRE_DATE
FROM EMPLOYEE E
WHERE EMP_ID NOT IN
(SELECT MANAGER_EMP_ID FROM EMPLOYEE
WHERE MANAGER_EMP_ID IS NOT NULL);
EMP_ID LNAME DEPT_ID MANAGER_EMP_ID SALARY HIRE_DATE
--------- ---------- --------- -------------- --------- ---------
7369 SMITH 20 7902 800 17-DEC-80
7499 ALLEN 30 7698 1600 20-FEB-81
7521 WARD 30 7698 1250 22-FEB-81
7654 MARTIN 30 7698 1250 28-SEP-81

7844 TURNER 30 7698 1500 08-SEP-81
7876 ADAMS 20 7788 1100 23-MAY-87
7900 JAMES 30 7698 950 03-DEC-81
7934 MILLER 10 7782 1300 23-JAN-82
8 rows selected.
Oracle provides the following three constructs to effectively and efficiently
perform hierarchical queries:
• The START WITH...CONNECT BY clause
• The PRIOR operator
• The LEVEL pseudocolumn
START WITH...CONNECT BY and PRIOR
We can extract information in hierarchical form from a table containing hierarchical
data by using the SELECT statement’s START WITH...CONNECT BY clause
syntax for this clause is:
[[START WITH condition1] CONNECT BY condition2]
START WITH condition1-
Specifies the root row(s) of the hierarchy. All rows that satisfy condition1 are
considered root rows. If we don’t specify the START WITH clause, all rows are
considered root rows, which is usually not desirable. We can include a subquery
in condition1.
CONNECT BY condition2-
Specifies the relationship between parent rows and child rows in the hierarc where columns from
the current row are compared to corresponding parent columns. condition2 must
contain the PRIOR operator, which is used to identify columns from the parent
row. condition2 cannot contain a subquery.
. PRIOR is a built-in Oracle SQL operator that is used with hierarchical queries only. In
a hierarchical query, the CONNECT BY clause specifies the relationship between parent
and child rows. When we use the PRIOR operator in an expression in the CONNECT
BY condition, the expression following the PRIOR keyword is evaluated for
the parent row of the current row in the query. In the following example, PRIOR is
used to connect each row to its parent by connecting MANAGER_EMP_ID in the
child to EMP_ID in the parent:
SELECT LNAME, EMP_ID, MANAGER_EMP_ID
FROM EMPLOYEE
START WITH MANAGER_EMP_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_EMP_ID;
LNAME EMP_ID MANAGER_EMP_ID
-------------------- ---------- --------------
KING 7839
JONES 7566 7839
SCOTT 7788 7566

ADAMS 7876 7788
FORD 7902 7566
SMITH 7369 7902
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
CLARK 7782 7839
MILLER 7934 7782
14 rows selected LNAME EMP_ID MANAGER_EMP_ID
-------------------- ---------- --------------
KING 7839
JONES 7566 7839
SCOTT 7788 7566
ADAMS 7876 7788
FORD 7902 7566
SMITH 7369 7902
BLAKE 7698 7839
ALLEN 7499 7698
WARD 7521 7698
MARTIN 7654 7698
TURNER 7844 7698
JAMES 7900 7698
CLARK 7782 7839
MILLER 7934 7782
14 rows selected
As usual we’ll work on the EMP table that is already furnished with a hierarchical self relation. On that table,
indeed, there’s a column, MGR, that stores, for each employee, his boss’s EMPNO.
Data contained into the table is as follows:
SQL> select empno, ename, mgr
2 from emp;
EMPNO ENAME MGR
---------- ---------- ----------
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782
KING is the root of the hierarchy since hi has no boss.
The first example of hierarchical query is the following:

SQL> select empno, ename, mgr, prior ename, level
2 from emp
3 connect by prior empno = mgr
4 start with mgr is null;
EMPNO ENAME MGR PRIORENAME LEVEL
---------- ---------- ---------- ---------- ----------
7839 KING 1
7566 JONES 7839 KING 2
7788 SCOTT 7566 JONES 3
7876 ADAMS 7788 SCOTT 4
7902 FORD 7566 JONES 3
7369 SMITH 7902 FORD 4
7698 BLAKE 7839 KING 2
7499 ALLEN 7698 BLAKE 3
7521 WARD 7698 BLAKE 3
7654 MARTIN 7698 BLAKE 3
7844 TURNER 7698 BLAKE 3
7900 JAMES 7698 BLAKE 3
7782 CLARK 7839 KING 2
7934 MILLER 7782 CLARK 3
In a hierarchy tree, the term level refers to one layer of nodes.
Let’s analyze it: the CONNECT BY clause, mandatory to make a hierarchical query, is used to define
how each record is connected to the hierarchical superior.
The father of the record having MGR=x has EMPNO=x.
On the other hand, given a record with EMPNO=x, all the records having MGR=x are his sons.
The unary operator PRIOR indicates “the father of”.
START WITH clause is used to from which records we want to start the hierarchy, in our example we
want to start from the root of the hierarchy, the employee that has no manager.
The root of the hierarchy could be not unique. In this example it is.
The LEVEL pseudocolumn indicates at which level each record stays in the hierarchy, starting from the
root that has level=1.
Once understood the quey, we can read our resulting tree: KING is the root and has level=1.
Under KING there are three employees at level 2(JONES,BLAKE e CLARK). Then the others.
How does Oracle make the hierarchy? First of all it reads the records.
Then it determines the roots applying the START WITH clause.
Then, starting from each root, it determines the first-level sons applying the CONNECT BY clause and
so on…
To enhance the layout of the output we can use the following trick:
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 mgr, prior ename, level
3 from emp
4 connect by prior empno = mgr
5 start with mgr is null;
EMPNO NAME MGR PRIORENAME LEVEL
---------- ------------------ ---------- ---------- ----------
7839 KING 1
7566 JONES 7839 KING 2
7788 SCOTT 7566 JONES 3
7876 ADAMS 7788 SCOTT 4
7902 FORD 7566 JONES 3
7369 SMITH 7902 FORD 4
7698 BLAKE 7839 KING 2

7499 ALLEN 7698 BLAKE 3
7521 WARD 7698 BLAKE 3
7654 MARTIN 7698 BLAKE 3
7844 TURNER 7698 BLAKE 3
7900 JAMES 7698 BLAKE 3
7782 CLARK 7839 KING 2
7934 MILLER 7782 CLARK 3
We just added some white-spaces on the left of the name depending on the level. Now it’s all more
clear…
Records order defines the hierarchy, adding an ORDER BY clause we could completely lose the
hierarchy.
We can decide how to sort siblings, that is to say records on the same level. For example JONES,
BLAKE and CLARK:
SQL> select empno,lpad(' ',level*3,' ')||ename nome,
2 mgr, prior ename, level
3 from emp
4 connect by prior empno = mgr
5 start with mgr is null
6 order siblings by ename;
EMPNO NOME MGR PRIORENAME LEVEL
---------- ------------------ ---------- ---------- ----------
7839 KING 1
7698 BLAKE 7839 KING 2
7499 ALLEN 7698 BLAKE 3
7900 JAMES 7698 BLAKE 3
7654 MARTIN 7698 BLAKE 3
7844 TURNER 7698 BLAKE 3
7521 WARD 7698 BLAKE 3
7782 CLARK 7839 KING 2
7934 MILLER 7782 CLARK 3
7566 JONES 7839 KING 2
7902 FORD 7566 JONES 3
7369 SMITH 7902 FORD 4
7788 SCOTT 7566 JONES 3
7876 ADAMS 7788 SCOTT 4
With no impact on the hierarchy we’ve sorted siblings by name.
If you work on Oracle9i you can stop reading, what follows is available on Oracle10g and 11g…
In any hierarchy infinite loops can raise. For example what happens if KING has a manager himself?
SQL> update emp set mgr=7369 where ename='KING';
KING is one of the SMITH’s sons, SMITH is himself a KING’s great grandson…
What if we run the same query now?
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 mgr, prior ename, level
3 from emp
4 connect by prior empno = mgr
5 start with mgr is null
6 order siblings by ename;

No rows selected
Of course, because there’s no record that has MGR null… So let’s change the START WITH as follows:
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 mgr, prior ename, level
3 from emp
4 connect by prior empno = mgr
5 start with empno=7839;
ERROR:
ORA-01436: CONNECT BY loop in user data
Here’s the loop, Oracle can’t create the hierarchy.
Oracle has managed it introducing the NOCYCLE clause
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 mgr, prior ename, level
3 from emp
4 connect by nocycle prior empno = mgr
5 start with empno=7839;
EMPNO NAME MGR PRIORENAME LEVEL
---------- ------------------ ---------- ---------- ----------
7839 KING 7369 1
7566 JONES 7839 KING 2
7788 SCOTT 7566 JONES 3
7876 ADAMS 7788 SCOTT 4
7902 FORD 7566 JONES 3
7369 SMITH 7902 FORD 4
7698 BLAKE 7839 KING 2
7499 ALLEN 7698 BLAKE 3
7521 WARD 7698 BLAKE 3
7654 MARTIN 7698 BLAKE 3
7844 TURNER 7698 BLAKE 3
7900 JAMES 7698 BLAKE 3
7782 CLARK 7839 KING 2
7934 MILLER 7782 CLARK 3
That clause tells to Oracle to stop when a loop is raised, Oracle goes on on the other branchs of the tree.
After SMITH another instance of KING (and all his descendant) should be extracted, and so on,
generating an infinite output. The NOCYCLE clause prevents that.
CONNECT_BY_ISCYCLE pseudocolumn tells us in which records a loop has been detected:
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 mgr, prior ename, connect_by_iscycle IFLOOP
3 from emp
4 connect by nocycle prior empno = mgr
5 start with empno=7839;
EMPNO NAME MGR PRIORENAME IFLOOP
---------- ------------------ ---------- ---------- ----------
7839 KING 7369 0
7566 JONES 7839 KING 0
7788 SCOTT 7566 JONES 0
7876 ADAMS 7788 SCOTT 0
7902 FORD 7566 JONES 0
7369 SMITH 7902 FORD 1
7698 BLAKE 7839 KING 0
7499 ALLEN 7698 BLAKE 0
7521 WARD 7698 BLAKE 0
7654 MARTIN 7698 BLAKE 0

7844 TURNER 7698 BLAKE 0
7900 JAMES 7698 BLAKE 0
7782 CLARK 7839 KING 0
7934 MILLER 7782 CLARK 0
In addition to PRIOR another unary operator exists that’s really useful: CONNECT_BY_ROOT.
It allows us to display the root of a given record:
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 connect_by_root ename boss
3 from emp
4 connect by prior empno = mgr
5 start with mgr is null;
EMPNO NAME BOSS
---------- ------------------ ----------
7839 KING KING
7566 JONES KING
7788 SCOTT KING
7876 ADAMS KING
7902 FORD KING
7369 SMITH KING
7698 BLAKE KING
7499 ALLEN KING
7521 WARD KING
7654 MARTIN KING
7844 TURNER KING
7900 JAMES KING
7782 CLARK KING
7934 MILLER KING
In our example there’s a single root, KING, but if we modify another record:
SQL> update emp set mgr=null where ename='BLAKE';
We get:
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 connect_by_root ename boss
3 from emp
4 connect by prior empno = mgr
5 start with mgr is null;
EMPNO NAME BOSS
---------- ------------------ ----------
7698 BLAKE BLAKE
7499 ALLEN BLAKE
7521 WARD BLAKE
7654 MARTIN BLAKE
7844 TURNER BLAKE
7900 JAMES BLAKE
7839 KING KING
7566 JONES KING
7788 SCOTT KING
7876 ADAMS KING
7902 FORD KING
7369 SMITH KING
7782 CLARK KING
7934 MILLER KING
Where, for each record, we display who is the “big chief”…
Another really useful function is SYS_CONNECT_BY_PATH, it gets as input a field and a character
and builds the full path from the root to the current record using the character as a separator
SQL> select empno,lpad(' ',level*3,' ')||ename name,

2 sys_connect_by_path(ename,'/') bosses
3 from emp
4 connect by prior empno = mgr
5* start with mgr is null
EMPNO NAME BOSSES
---------- ------------------ --------------------------------
7839 KING /KING
7566 JONES /KING/JONES
7788 SCOTT /KING/JONES/SCOTT
7876 ADAMS /KING/JONES/SCOTT/ADAMS
7902 FORD /KING/JONES/FORD
7369 SMITH /KING/JONES/FORD/SMITH
7698 BLAKE /KING/BLAKE
7499 ALLEN /KING/BLAKE/ALLEN
7521 WARD /KING/BLAKE/WARD
7654 MARTIN /KING/BLAKE/MARTIN
7844 TURNER /KING/BLAKE/TURNER
7900 JAMES /KING/BLAKE/JAMES
7782 CLARK /KING/CLARK
7934 MILLER /KING/CLARK/MILLER
There’s another useful pseudocolumn to show, CONNECT_BY_ISLEAF.
It tells us whether a record is a leaf of the tree or not:
SQL> select empno,lpad(' ',level*3,' ')||ename name,
2 connect_by_isleaf ifleaf
3 from emp
4 connect by prior empno = mgr
5 start with mgr is null;
EMPNO NAME IFLEAF
---------- ------------------ ----------
7839 KING 0
7566 JONES 0
7788 SCOTT 0
7876 ADAMS 1
7902 FORD 0
7369 SMITH 1
7698 BLAKE 0
7499 ALLEN 1
7521 WARD 1
7654 MARTIN 1
7844 TURNER 1
7900 JAMES 1
7782 CLARK 0
7934 MILLER 1
Really interesting is the ability of CONNECT BY to generate more records from a table that contains only one
row:
SQL> select level from dual
2 connect by level<=10;
LEVEL
----------
1
2
3
4
5
6

7
8
9
10
SQL> cl scr
SQL> SET VERIFY OFF
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job
2 FROM Emp
3 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
MARTIN 7654 7698 SALESMAN
ALLEN 7499 7698 SALESMAN
JAMES 7900 7698 CLERK
WARD 7521 7698 SALESMAN
TURNER 7844 7698 SALESMAN
MILLER 7934 7782 CLERK
ADAMS 7876 7788 CLERK
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
BLAKE 7698 7839 MANAGER
MARTIN 7654 7698 SALESMAN
ALLEN 7499 7698 SALESMAN
JAMES 7900 7698 CLERK
WARD 7521 7698 SALESMAN
TURNER 7844 7698 SALESMAN
CLARK 7782 7839 MANAGER
MILLER 7934 7782 CLERK
JONES 7566 7839 MANAGER
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
SMITH 7369 7902 CLERK
KING 7839 PRESIDENT
BLAKE 7698 7839 MANAGER
MARTIN 7654 7698 SALESMAN
ALLEN 7499 7698 SALESMAN


JAMES 7900 7698 CLERK
WARD 7521 7698 SALESMAN
TURNER 7844 7698 SALESMAN
CLARK 7782 7839 MANAGER
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
MILLER 7934 7782 CLERK
JONES 7566 7839 MANAGER
FORD 7902 7566 ANALYST


SMITH 7369 7902 CLERK
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
39 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job
2 FROM Emp
3 START WITH Job = 'PRESIDENT'
4 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
KING 7839 PRESIDENT
BLAKE 7698 7839 MANAGER
MARTIN 7654 7698 SALESMAN
ALLEN 7499 7698 SALESMAN
TURNER 7844 7698 SALESMAN
JAMES 7900 7698 CLERK
WARD 7521 7698 SALESMAN
CLARK 7782 7839 MANAGER
MILLER 7934 7782 CLERK
JONES 7566 7839 MANAGER
FORD 7902 7566 ANALYST
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
SMITH 7369 7902 CLERK
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job
2 FROM Emp
3 START WITH Ename = 'KING'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
KING 7839 PRESIDENT
BLAKE 7698 7839 MANAGER
MARTIN 7654 7698 SALESMAN
ALLEN 7499 7698 SALESMAN
TURNER 7844 7698 SALESMAN
JAMES 7900 7698 CLERK
WARD 7521 7698 SALESMAN
CLARK 7782 7839 MANAGER
MILLER 7934 7782 CLERK
JONES 7566 7839 MANAGER


FORD 7902 7566 ANALYST
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
SMITH 7369 7902 CLERK
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
14 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job
2 FROM Emp;
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
KING 7839 PRESIDENT
BLAKE 7698 7839 MANAGER
CLARK 7782 7839 MANAGER
JONES 7566 7839 MANAGER
MARTIN 7654 7698 SALESMAN
ALLEN 7499 7698 SALESMAN
TURNER 7844 7698 SALESMAN
JAMES 7900 7698 CLERK
WARD 7521 7698 SALESMAN
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
MILLER 7934 7782 CLERK
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Sal = 5000
4 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000


ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Sal = (SELECT MAX(Sal)
4 FROM Emp)
5 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
14 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Sal IN (SELECT Sal
4 FROM Emp
5 WHERE Job = 'ANALYST')
6 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
FORD 7902 7566 ANALYST 3000
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
SQL> SPOOL OFF
SQL> cl scr
SQL> SET VERIFY OFF

SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Sal = (SELECT MAX(Sal)
4 FROM Emp
5 WHERE Deptno = (SELECT Deptno
6 FROM
7
WHERE Dname = 'ACCOUNTING')
8 )
9 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Sal = (SELECT MAX(Sal)
4 FROM Emp
5 WHERE Deptno = (SELECT Deptno
6 FROM
7
WHERE Dname = 'RESEARCH')
8 )
9* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
FORD 7902 7566 ANALYST 3000
SMITH 7369 7902 CLERK 800
Dept
Dept


SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Sal = (SELECT MAX(Sal)
4 FROM Emp
5 WHERE Deptno = (SELECT Deptno
6 FROM
7
WHERE Dname = 'SALES')
8 )
9* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
6 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = (SELECT Ename
4 FROM Emp, SalGrade
5 WHERE Emp.Sal BETWEEN SalGrade.LOSAL AND
SalGrade.HiSal
6 AND grade = 5)
7 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
ENAME EMPNO MGR JOB SAL
Dept


---------- ---------- ---------- --------- ----------
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, MGR, Job
2 FROM Emp
3 START WITH Job = 'ANALYST'
4 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, MGR, Job
2 FROM Emp
3 START WITH Ename = 'JONES'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
JONES 7566 7839 MANAGER
FORD 7902 7566 ANALYST
SMITH 7369 7902 CLERK
SCOTT 7788 7566 ANALYST
ADAMS 7876 7788 CLERK
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, MGR, Job
2 FROM Emp
3 START WITH Ename = 'JONES'
4* CONNECT BY Empno = PRIOR MGR
SQL> /
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
JONES 7566 7839 MANAGER
KING 7839 PRESIDENT
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, MGR, Job

2 FROM Emp
3 START WITH Job = 'ANALYST'
4* CONNECT BY Empno = PRIOR MGR
SQL> /
ENAME EMPNO MGR JOB
---------- ---------- ---------- ---------
SCOTT 7788 7566 ANALYST
JONES 7566 7839 MANAGER
KING 7839 PRESIDENT
FORD 7902 7566 ANALYST
JONES 7566 7839 MANAGER
KING 7839 PRESIDENT
6 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5 /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
14 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5 AND Job = 'MANAGER';
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850


CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 WHERE Job = 'MANAGER'
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
BLAKE 7698 7839 MANAGER 2850
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'JONES'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'BLAKE'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
6 rows selected.

SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'CLARK'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'JONES'
4 CONNECT BY PRIOR Empno = MGR AND
5* Job = 'MANAGER'
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
JONES 7566 7839 MANAGER 2975
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5 AND Job = 'MANAGER';
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND Job = 'SALESMAN'
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000

SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND Job = 'MANAGER' OR Job = 'SALESMAN'
SQL> /
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND Job = 'MANAGER' AND Job = 'SALESMAN'
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND (Job = 'MANAGER' OR Job = 'SALESMAN')
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
8 rows selected.
SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND Job IN('MANAGER', 'SALESMAN')
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
8 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5 AND Job <> 'SALESMAN';
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
JAMES 7900 7698 CLERK 950
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
SMITH 7369 7902 CLERK 800
SCOTT 7788 7566 ANALYST 3000
ADAMS 7876 7788 CLERK 1100
10 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND Job <> 'CLERK'
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------


KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
SCOTT 7788 7566 ANALYST 3000
10 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* AND Job <> 'ANALYST'
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
10 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 WHERE Job <> 'ANALYST'
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
Moharana/////sekhar576@gmail.c
om
WARD 7521 7698 SALESMAN 1250
CLARK 7782 7839 MANAGER 2450
MILLER 7934 7782 CLERK 1300
JONES 7566 7839 MANAGER 2975
SMITH 7369 7902 CLERK 800
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
ADAMS 7876 7788 CLERK 1100
12 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5 AND Job <> 'SALESMAN'
6 AND Sal > 1500;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
SCOTT 7788 7566 ANALYST 3000
6 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 WHERE Job <> 'SALESMAN' AND Sal > 1500
4 START WITH Ename = 'KING'
5 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
KING 7839 PRESIDENT 5000
BLAKE 7698 7839 MANAGER 2850
CLARK 7782 7839 MANAGER 2450
JONES 7566 7839 MANAGER 2975
FORD 7902 7566 ANALYST 3000
SCOTT 7788 7566 ANALYST 3000
6 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job, Sal
2 FROM Emp
3 WHERE Deptno = (SELECT Deptno
4 FROM Dept
5 WHERE DName = 'SALES')
6 START WITH Ename = 'KING'
7 CONNECT BY PRIOR Empno = MGR;


ENAME EMPNO MGR JOB SAL
---------- ---------- ---------- --------- ----------
BLAKE 7698 7839 MANAGER 2850
MARTIN 7654 7698 SALESMAN 1250
ALLEN 7499 7698 SALESMAN 1600
TURNER 7844 7698 SALESMAN 1500
JAMES 7900 7698 CLERK 950
WARD 7521 7698 SALESMAN 1250
6 rows selected.
SQL> cl scr
SQL> SELECT Ename, Empno, Mgr, Job, Sal,
2 ROUND(SalAvg, 2) SalAvg
3 FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg
4 FROM Emp
5 GROUP BY Deptno) E1
6 WHERE E.Deptno = E1.Deptno
7 START WITH Ename = 'KING'
8 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL SALAVG
---------- ---------- ---------- --------- ---------- ----------
KING 7839 PRESIDENT 5000 2916.67
CLARK 7782 7839 MANAGER 2450 2916.67
MILLER 7934 7782 CLERK 1300 2916.67
JONES 7566 7839 MANAGER 2975 2175
SCOTT 7788 7566 ANALYST 3000 2175
ADAMS 7876 7788 CLERK 1100 2175
FORD 7902 7566 ANALYST 3000 2175
SMITH 7369 7902 CLERK 800 2175
BLAKE 7698 7839 MANAGER 2850 1566.67
WARD 7521 7698 SALESMAN 1250 1566.67
JAMES 7900 7698 CLERK 950 1566.67
ENAME EMPNO MGR JOB SAL SALAVG
---------- ---------- ---------- --------- ---------- ----------
TURNER 7844 7698 SALESMAN 1500 1566.67
ALLEN 7499 7698 SALESMAN 1600 1566.67
MARTIN 7654 7698 SALESMAN 1250 1566.67
14 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job, Sal,
2 ROUND(SalAvg, 2) SalAvg
3 FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg
4 FROM Emp
5 GROUP BY Deptno) E1
6 WHERE E.Deptno = E1.Deptno AND
7 E1.Deptno = (SELECT Deptno
8 FROM Dept
9 WHERE Dname = 'SALES')
10 START WITH Ename = 'KING'
11 CONNECT BY PRIOR Empno = MGR;


ENAME EMPNO MGR JOB SAL SALAVG
---------- ---------- ---------- --------- ---------- ----------
BLAKE 7698 7839 MANAGER 2850 1566.67
WARD 7521 7698 SALESMAN 1250 1566.67
JAMES 7900 7698 CLERK 950 1566.67
TURNER 7844 7698 SALESMAN 1500 1566.67
ALLEN 7499 7698 SALESMAN 1600 1566.67
MARTIN 7654 7698 SALESMAN 1250 1566.67
6 rows selected.
SQL> SELECT Ename, Empno, Mgr, Job, Sal, ROUND(SalAvg, 2) SalAvg, ROUND(((SELECT
AVG(Sal) FROM Emp) - SalAvg), 2) AvgDif
2 FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg
3 FROM Emp
4 GROUP BY Deptno) E1
5 WHERE E.Deptno = E1.Deptno AND
6 E1.Deptno = (SELECT Deptno
7 FROM Dept
8 WHERE Dname = 'SALES')
9 START WITH Ename = 'KING'
10 CONNECT BY PRIOR Empno = MGR;
ENAME EMPNO MGR JOB SAL SALAVG AVGDIF
---------- ---------- ---------- --------- ---------- ---------- ----------
BLAKE 7698 7839 MANAGER 2850 1566.67 506.55
WARD 7521 7698 SALESMAN 1250 1566.67 506.55
JAMES 7900 7698 CLERK 950 1566.67 506.55
TURNER 7844 7698 SALESMAN 1500 1566.67 506.55
ALLEN 7499 7698 SALESMAN 1600 1566.67 506.55
MARTIN 7654 7698 SALESMAN 1250 1566.67 506.55
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal, ROUND(SalAvg, 2) SalAvg, ROUND(((SELECT
AVG(Sal) FROM Emp) - SalAvg), 2) AvgDif
2 FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg
3 FROM Emp
4 GROUP BY Deptno) E1
5 WHERE E.Deptno = E1.Deptno AND
6 E1.Deptno = (SELECT Deptno
7 FROM Dept
8 WHERE Dname = 'SALES')
9 START WITH Ename = (
10 SELECT Ename
11 FROM Emp
12 WHERE Sal = (
13 SELECT
14 MAX(Sal)
15 FROM Emp
16 WHERE Deptno = (
17 SELECT Deptno

18 FROM Dept
19 WHERE Dname = 'ACCOUNTING'
20
21
22
23* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME EMPNO MGR JOB SAL SALAVG AVGDIF
---------- ---------- ---------- --------- ---------- ---------- ----------
BLAKE 7698 7839 MANAGER 2850 1566.67 506.55
WARD 7521 7698 SALESMAN 1250 1566.67 506.55
JAMES 7900 7698 CLERK 950 1566.67 506.55
TURNER 7844 7698 SALESMAN 1500 1566.67 506.55
ALLEN 7499 7698 SALESMAN 1600 1566.67 506.55
MARTIN 7654 7698 SALESMAN 1250 1566.67 506.55
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Empno, Mgr, Job, Sal, ROUND(SalAvg, 2) SalAvg, ROUND(((SELECT
AVG(Sal) FROM Emp) - SalAvg), 2) AvgDif
2 FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg
3 FROM Emp
4 GROUP BY Deptno) E1
5 WHERE E.Deptno = E1.Deptno AND
6 E1.Deptno = (SELECT Deptno
7 FROM Dept
8 WHERE Dname = 'SALES')
9 START WITH Ename = (
10 SELECT Ename
11 FROM Emp
12 WHERE Sal = (
13 SELECT
14 MAX(Sal)
15 FROM Emp
16 WHERE Deptno = (
17 SELECT Deptno
18 FROM Dept
19 WHERE Dname = 'ACCOUNTING'
20
21
22
23* CONNECT BY PRIOR Empno = MGR
SQL> SPOOL OFF
SQL> cl scr
SQL> SET VERIFY OFF
SQL> cl scr
SQL> SELECT Ename, Sal, Job,
2 CONNECT_BY_ROOT(Ename) Boss
3 FROM Emp
4 START WITH Ename = 'KING'

5 CONNECT BY PRIOR Empno = MGR;
ENAME SAL JOB BOSS
---------- ---------- --------- ----------
KING 5000 PRESIDENT KING
BLAKE 2850 MANAGER KING
MARTIN 1250 SALESMAN KING
ALLEN 1600 SALESMAN KING
TURNER 1500 SALESMAN KING
JAMES 950 CLERK KING
WARD 1250 SALESMAN KING
CLARK 2450 MANAGER KING
MILLER 1300 CLERK KING
JONES 2975 MANAGER KING
FORD 3000 ANALYST KING
ENAME SAL JOB BOSS
---------- ---------- --------- ----------
SMITH 800 CLERK KING
SCOTT 3000 ANALYST KING
ADAMS 1100 CLERK KING
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job,
2 CONNECT_BY_ROOT(Job) BossJob
3 FROM Emp
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME SAL JOB BOSSJOB
---------- ---------- --------- ---------
KING 5000 PRESIDENT PRESIDENT
BLAKE 2850 MANAGER PRESIDENT
MARTIN 1250 SALESMAN PRESIDENT
ALLEN 1600 SALESMAN PRESIDENT
TURNER 1500 SALESMAN PRESIDENT
JAMES 950 CLERK PRESIDENT
WARD 1250 SALESMAN PRESIDENT
CLARK 2450 MANAGER PRESIDENT
MILLER 1300 CLERK PRESIDENT
JONES 2975 MANAGER PRESIDENT
FORD 3000 ANALYST PRESIDENT
ENAME SAL JOB BOSSJOB
---------- ---------- --------- ---------
SMITH 800 CLERK PRESIDENT
SCOTT 3000 ANALYST PRESIDENT
ADAMS 1100 CLERK PRESIDENT
14 rows selected.

SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job,
2 CONNECT_BY_ROOT(Sal) - Sal DiffBossEmpSal
3 FROM Emp
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME SAL JOB DIFFBOSSEMPSAL
---------- ---------- --------- --------------
KING 5000 PRESIDENT 0
BLAKE 2850 MANAGER 2150
MARTIN 1250 SALESMAN 3750
ALLEN 1600 SALESMAN 3400
TURNER 1500 SALESMAN 3500
JAMES 950 CLERK 4050
WARD 1250 SALESMAN 3750
CLARK 2450 MANAGER 2550
MILLER 1300 CLERK 3700
JONES 2975 MANAGER 2025
FORD 3000 ANALYST 2000
ENAME SAL JOB DIFFBOSSEMPSAL
---------- ---------- --------- --------------
SMITH 800 CLERK 4200
SCOTT 3000 ANALYST 2000
ADAMS 1100 CLERK 3900
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job, Sal EmpSal,
2 CONNECT_BY_ROOT(Sal) BossSal,
3 CONNECT_BY_ROOT(Sal) - Sal DiffBossEmpSal
4 FROM Emp
5 START WITH Ename = 'KING'
6* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME JOB EMPSAL BOSSSAL DIFFBOSSEMPSAL
---------- --------- ---------- ---------- --------------
KING PRESIDENT 5000 5000 0
BLAKE MANAGER 2850 5000 2150
MARTIN SALESMAN 1250 5000 3750
ALLEN SALESMAN 1600 5000 3400
TURNER SALESMAN 1500 5000 3500
JAMES CLERK 950 5000 4050
WARD SALESMAN 1250 5000 3750
CLARK MANAGER 2450 5000 2550
MILLER CLERK 1300 5000 3700
JONES MANAGER 2975 5000 2025
FORD ANALYST 3000 5000 2000


ENAME JOB EMPSAL BOSSSAL DIFFBOSSEMPSAL
---------- --------- ---------- ---------- --------------
SMITH CLERK 800 5000 4200
SCOTT ANALYST 3000 5000 2000
ADAMS CLERK 1100 5000 3900
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job,
2 CONNECT_BY_ROOT(Ename) BossName
3 FROM Emp
4 START WITH Ename = 'KING'
5* CONNECT BY Empno = PRIOR MGR
SQL> /
ENAME JOB BOSSNAME
---------- --------- ----------
KING PRESIDENT KING
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job,
2 CONNECT_BY_ROOT(Ename) BossName
3 FROM Emp
4 START WITH Job = 'ANALYST'
5* CONNECT BY Empno = PRIOR MGR
SQL> /
ENAME JOB BOSSNAME
---------- --------- ----------
SCOTT ANALYST SCOTT
JONES MANAGER SCOTT
KING PRESIDENT SCOTT
FORD ANALYST FORD
JONES MANAGER FORD
KING PRESIDENT FORD
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job,
2 CONNECT_BY_ROOT(Ename) BossName
3 FROM Emp
4 START WITH Empno = 7839
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME JOB BOSSNAME
---------- --------- ----------


KING PRESIDENT KING
BLAKE MANAGER KING
MARTIN SALESMAN KING
ALLEN SALESMAN KING
TURNER SALESMAN KING
JAMES CLERK KING
WARD SALESMAN KING
CLARK MANAGER KING
MILLER CLERK KING
JONES MANAGER KING
FORD ANALYST KING
ENAME JOB BOSSNAME
---------- --------- ----------
SMITH CLERK KING
SCOTT ANALYST KING
ADAMS CLERK KING
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job,
2 CONNECT_BY_ROOT(Ename) BossName
3 FROM Emp
4 START WITH MGR = 7839
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME JOB BOSSNAME
---------- --------- ----------
BLAKE MANAGER BLAKE
MARTIN SALESMAN BLAKE
ALLEN SALESMAN BLAKE
TURNER SALESMAN BLAKE
JAMES CLERK BLAKE
WARD SALESMAN BLAKE
CLARK MANAGER CLARK
MILLER CLERK CLARK
JONES MANAGER JONES
FORD ANALYST JONES
SMITH CLERK JONES
ENAME JOB BOSSNAME
---------- --------- ----------
SCOTT ANALYST JONES
ADAMS CLERK JONES
13 rows selected.
SQL> cl scr
SQL> SELECT ENAME Name, HireDate,
2 CONNECT_BY_ROOT Ename Boss,
3 CONNECT_BY_ROOT HireDate BossHire,

4 ROUND(CONNECT_BY_ROOT HireDate - HireDate) Days
5 FROM EMP
6 START WITH Job = 'PRESIDENT'
7 CONNECT BY PRIOR EMPNO = MGR;
NAME HIREDATE BOSS BOSSHIRE DAYS
---------- --------- ---------- --------- ----------
KING 17-NOV-81 KING 17-NOV-81 0
BLAKE 01-MAY-81 KING 17-NOV-81 200
MARTIN 28-SEP-81 KING 17-NOV-81 50
ALLEN 20-FEB-81 KING 17-NOV-81 270
TURNER 08-SEP-81 KING 17-NOV-81 70
JAMES 03-DEC-81 KING 17-NOV-81 -16
WARD 22-FEB-81 KING 17-NOV-81 268
CLARK 09-JUN-81 KING 17-NOV-81 161
MILLER 23-JAN-82 KING 17-NOV-81 -67
JONES 02-APR-81 KING 17-NOV-81 229
FORD 03-DEC-81 KING 17-NOV-81 -16
NAME HIREDATE BOSS BOSSHIRE DAYS
---------- --------- ---------- --------- ----------
SMITH 17-DEC-80 KING 17-NOV-81 335
SCOTT 09-DEC-82 KING 17-NOV-81 -387
ADAMS 12-JAN-83 KING 17-NOV-81 -421
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME Name, HireDate,
2 CONNECT_BY_ROOT Ename Boss,
3 CONNECT_BY_ROOT HireDate BossHire,
4 ROUND(CONNECT_BY_ROOT HireDate - HireDate) Days
5 FROM EMP
6 WHERE
7 ROUND(CONNECT_BY_ROOT HireDate - HireDate) = &GVal
8 START WITH Job = 'PRESIDENT'
9* CONNECT BY PRIOR EMPNO = MGR
SQL> /
Enter value for gval: 1
no rows selected
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME Name, HireDate,
2 CONNECT_BY_ROOT Ename Boss,
3 CONNECT_BY_ROOT HireDate BossHire,
4 ROUND(CONNECT_BY_ROOT HireDate - HireDate) Days
5 FROM EMP
6 WHERE
7 SIGN(ROUND(CONNECT_BY_ROOT HireDate - HireDate)) = &GVal
8 START WITH Job = 'PRESIDENT'
9* CONNECT BY PRIOR EMPNO = MGR

SQL> /
Enter value for gval: 1
NAME HIREDATE BOSS BOSSHIRE DAYS
---------- --------- ---------- --------- ----------
BLAKE 01-MAY-81 KING 17-NOV-81 200
MARTIN 28-SEP-81 KING 17-NOV-81 50
ALLEN 20-FEB-81 KING 17-NOV-81 270
TURNER 08-SEP-81 KING 17-NOV-81 70
WARD 22-FEB-81 KING 17-NOV-81 268
CLARK 09-JUN-81 KING 17-NOV-81 161
JONES 02-APR-81 KING 17-NOV-81 229
SMITH 17-DEC-80 KING 17-NOV-81 335
8 rows selected.
SQL> /
Enter value for gval: -1
NAME HIREDATE BOSS BOSSHIRE DAYS
---------- --------- ---------- --------- ----------
JAMES 03-DEC-81 KING 17-NOV-81 -16
MILLER 23-JAN-82 KING 17-NOV-81 -67
FORD 03-DEC-81 KING 17-NOV-81 -16
SCOTT 09-DEC-82 KING 17-NOV-81 -387
ADAMS 12-JAN-83 KING 17-NOV-81 -421
SQL> cl cr
SP2-0158: unknown CLEAR option "cr"
SQL> cl scr
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME Name, HireDate
2 FROM EMP
3 WHERE
4 START WITH Job = 'PRESIDENT'
5* CONNECT BY PRIOR EMPNO = MGR
SQL> /
START WITH Job = 'PRESIDENT'
ERROR at line 4:
ORA-00936: missing expression
SQL> ED
Wrote file afiedt.buf
1 SELECT ENAME Name, HireDate
2 FROM EMP
3 START WITH Job = 'PRESIDENT'
4* CONNECT BY PRIOR EMPNO = MGR
SQL> /
NAME HIREDATE
*


---------- ---------
KING 17-NOV-81
BLAKE 01-MAY-81
MARTIN 28-SEP-81
ALLEN 20-FEB-81
TURNER 08-SEP-81
JAMES 03-DEC-81
WARD 22-FEB-81
CLARK 09-JUN-81
MILLER 23-JAN-82
JONES 02-APR-81
FORD 03-DEC-81
NAME HIREDATE
---------- ---------
SMITH 17-DEC-80
SCOTT 09-DEC-82
ADAMS 12-JAN-83
14 rows selected.
SQL> cl scr
SQL> COLUMN Path FORMAT A35
SQL> SELECT Ename,
2 SYS_CONNECT_BY_PATH(Ename, '/') "Path"
3 FROM Emp
4 START WITH Ename = 'KING'
5 CONNECT BY PRIOR Empno = MGR;
ENAME Path
---------- -----------------------------------
KING /KING
BLAKE /KING/BLAKE
MARTIN /KING/BLAKE/MARTIN
ALLEN /KING/BLAKE/ALLEN
TURNER /KING/BLAKE/TURNER
JAMES /KING/BLAKE/JAMES
WARD /KING/BLAKE/WARD
CLARK /KING/CLARK
MILLER /KING/CLARK/MILLER
JONES /KING/JONES
FORD /KING/JONES/FORD
ENAME Path
---------- -----------------------------------
SMITH /KING/JONES/FORD/SMITH
SCOTT /KING/JONES/SCOTT
ADAMS /KING/JONES/SCOTT/ADAMS
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,

2 SYS_CONNECT_BY_PATH(Ename, '=>') "Path"
3 FROM Emp
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME Path
---------- -----------------------------------
KING =>KING
BLAKE =>KING=>BLAKE
MARTIN =>KING=>BLAKE=>MARTIN
ALLEN =>KING=>BLAKE=>ALLEN
TURNER =>KING=>BLAKE=>TURNER
JAMES =>KING=>BLAKE=>JAMES
WARD =>KING=>BLAKE=>WARD
CLARK =>KING=>CLARK
MILLER =>KING=>CLARK=>MILLER
JONES =>KING=>JONES
FORD =>KING=>JONES=>FORD
ENAME Path
---------- -----------------------------------
SMITH =>KING=>JONES=>FORD=>SMITH
SCOTT =>KING=>JONES=>SCOTT
ADAMS =>KING=>JONES=>SCOTT=>ADAMS
14 rows selected.
SQL> cl scr
SQL> SELECT Empno, Ename, MGR
2 FROM Emp;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING
7698 BLAKE 7839
7782 CLARK 7839
7566 JONES 7839
7654 MARTIN 7698
7499 ALLEN 7698
7844 TURNER 7698
7900 JAMES 7698
7521 WARD 7698
7902 FORD 7566
7369 SMITH 7902
EMPNO ENAME MGR
---------- ---------- ----------
7788 SCOTT 7566
7876 ADAMS 7788
7934 MILLER 7782
14 rows selected.
SQL> UPDATE Emp

2 SET MGR = 7566
3 WHERE Empno = 7839;
1 row updated.
SQL> SELECT Empno, Ename, MGR
2 FROM Emp;
EMPNO ENAME MGR
---------- ---------- ----------
7839 KING 7566
7698 BLAKE 7839
7782 CLARK 7839
7566 JONES 7839
7654 MARTIN 7698
7499 ALLEN 7698
7844 TURNER 7698
7900 JAMES 7698
7521 WARD 7698
7902 FORD 7566
7369 SMITH 7902
EMPNO ENAME MGR
---------- ---------- ----------
7788 SCOTT 7566
7876 ADAMS 7788
7934 MILLER 7782
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Empno, Ename, MGR
2 FROM Emp
3 START WITH Ename = 'KING'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
SQL> SELECT Ename,
2 SYS_CONNECT_BY_PATH(Sal, '/') "SalPath"
3 FROM Emp
4 START WITH Ename = 'KING'
5 CONNECT BY NOCYCLE PRIOR Empno = MGR;
ENAME
----------
SalPath
--------------------------------------------------------------------------------
KING


/5000
BLAKE
/5000/2850
MARTIN
/5000/2850/1250
ENAME
----------
SalPath
--------------------------------------------------------------------------------
ALLEN
/5000/2850/1600
TURNER
/5000/2850/1500
JAMES
/5000/2850/950
ENAME
----------
SalPath
--------------------------------------------------------------------------------
WARD
/5000/2850/1250
CLARK
/5000/2450
MILLER
/5000/2450/1300
ENAME
----------
SalPath
--------------------------------------------------------------------------------
JONES
/5000/2975
FORD
/5000/2975/3000
SMITH
/5000/2975/3000/800
ENAME
----------
SalPath
--------------------------------------------------------------------------------
SCOTT


/5000/2975/3000
ADAMS
/5000/2975/3000/1100
14 rows selected.
SQL> COLUMN "SalPath" FORMAT A25
SQL> /
ENAME SalPath
---------- -------------------------
KING /5000
BLAKE /5000/2850
MARTIN /5000/2850/1250
ALLEN /5000/2850/1600
TURNER /5000/2850/1500
JAMES /5000/2850/950
WARD /5000/2850/1250
CLARK /5000/2450
MILLER /5000/2450/1300
JONES /5000/2975
FORD /5000/2975/3000
ENAME SalPath
---------- -------------------------
SMITH /5000/2975/3000/800
SCOTT /5000/2975/3000
ADAMS /5000/2975/3000/1100
14 rows selected.
SQL> cl scr
SQL> ROLLBACK
2 /
Rollback complete.
SQL> cl scr
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,
2 SYS_CONNECT_BY_PATH(Sal, '/') "SalPath"
3 FROM Emp
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR;
SQL> /
CONNECT BY PRIOR Empno = MGR;
*
ERROR at line 5:
ORA-00911: invalid character

SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,
2 SYS_CONNECT_BY_PATH(Sal, '/') "SalPath"
3 FROM Emp
4 START WITH Ename = 'KING'
5* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME SalPath
---------- -------------------------
KING /5000
BLAKE /5000/2850
MARTIN /5000/2850/1250
ALLEN /5000/2850/1600
TURNER /5000/2850/1500
JAMES /5000/2850/950
WARD /5000/2850/1250
CLARK /5000/2450
MILLER /5000/2450/1300
JONES /5000/2975
FORD /5000/2975/3000
ENAME SalPath
---------- -------------------------
SMITH /5000/2975/3000/800
SCOTT /5000/2975/3000
ADAMS /5000/2975/3000/1100
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,
2 SYS_CONNECT_BY_PATH(Sal, '/') "SalPath"
3 FROM Emp
4 START WITH Ename = 'KING'
5 CONNECT BY PRIOR Empno = MGR
6* ORDER BY Sal
SQL> /
ENAME SalPath
---------- -------------------------
SMITH /5000/2975/3000/800
JAMES /5000/2850/950
ADAMS /5000/2975/3000/1100
MARTIN /5000/2850/1250
WARD /5000/2850/1250
MILLER /5000/2450/1300
TURNER /5000/2850/1500
ALLEN /5000/2850/1600
CLARK /5000/2450
BLAKE /5000/2850
Moharana/////sekhar576@gmail.c
om
JONES /5000/2975
ENAME SalPath
---------- -------------------------
FORD /5000/2975/3000
SCOTT /5000/2975/3000
KING /5000
14 rows selected.
SQL> cl scr
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job
2 FROM Emp
3 START WITH Ename = 'KING'
4* CONNECT BY PRIOR Empno = MGR
SQL> /
ENAME SAL JOB
---------- ---------- ---------
KING 5000 PRESIDENT
BLAKE 2850 MANAGER
MARTIN 1250 SALESMAN
ALLEN 1600 SALESMAN
TURNER 1500 SALESMAN
JAMES 950 CLERK
WARD 1250 SALESMAN
CLARK 2450 MANAGER
MILLER 1300 CLERK
JONES 2975 MANAGER
FORD 3000 ANALYST
ENAME SAL JOB
---------- ---------- ---------
SMITH 800 CLERK
SCOTT 3000 ANALYST
ADAMS 1100 CLERK
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* ORDER SIBLINGS BY Sal
SQL> /
ENAME SAL JOB
---------- ---------- ---------
KING 5000 PRESIDENT


CLARK 2450 MANAGER
MILLER 1300 CLERK
BLAKE 2850 MANAGER
JAMES 950 CLERK
MARTIN 1250 SALESMAN
WARD 1250 SALESMAN
TURNER 1500 SALESMAN
ALLEN 1600 SALESMAN
JONES 2975 MANAGER
FORD 3000 ANALYST
ENAME SAL JOB
---------- ---------- ---------
SMITH 800 CLERK
SCOTT 3000 ANALYST
ADAMS 1100 CLERK
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job
2 FROM Emp
3 START WITH Ename = 'KING'
4 CONNECT BY PRIOR Empno = MGR
5* ORDER SIBLINGS BY Ename
SQL> /
ENAME SAL JOB
---------- ---------- ---------
KING 5000 PRESIDENT
BLAKE 2850 MANAGER
ALLEN 1600 SALESMAN
JAMES 950 CLERK
MARTIN 1250 SALESMAN
TURNER 1500 SALESMAN
WARD 1250 SALESMAN
CLARK 2450 MANAGER
MILLER 1300 CLERK
JONES 2975 MANAGER
FORD 3000 ANALYST
ENAME SAL JOB
---------- ---------- ---------
SMITH 800 CLERK
SCOTT 3000 ANALYST
ADAMS 1100 CLERK
14 rows selected.

Functions in oracle sql

Functions
 Functions are a very useful and powerful part of the SQL language. They can
transform data in a way that is different from the way it is stored in a database. A function is atype of
formula whose result is one of two things: either a transformation, such as a change in the name of a
student to uppercase letters, or information, such as the lengthof a word in a column. Most functions
share similar characteristics, including a name, and typically at least one input parameter, also called
an argument, inside a pair ofmatching parentheses.
function_name(input_parameter)
Data Types
Each value in Oracle has a data type associated with it. A data type determines the value’s attributes
and acceptable values. For example, you cannot enter a text value into aNUMBER data type column
or enter an invalid date, such as 32-DEC-2008, into a DATE data type column. In most SQL
operations, you use the NUMBER, VARCHAR2, andDATE data types. These are the commonly
used data types where the vast majority of data is stored. This chapter concentrates on functions
related to character andnumeric data
The DUAL Table
DUAL is a table unique to Oracle. It contains a single row and a single column called DUMMY and
holds no significant data of its own. It can be used in conjunction with functions to select values that
donot exist in tables, such as text literals or today’s date.A single row is always returned in the result
set. Some of the subsequent SQL examples are not concerned with specific rows but instead use
literals to demonstrate the purpose of a function. You might wonder why the DUAL table does not
appear in the list of tables below Student Connection. This table does not belong to the STUDENT
schema. It belongs to the user SYS, which is the owner of all Oracle system tables. This table, like
many other Oracle system tables, is accessible to all the users via a public synonym
Character function—
All character functions require alphanumeric input parameters. The input can be a text literal or
character literal, sometimes referred to as a string, or text, constant, or a column of data type
VARCHAR2, CHAR, or CLOB. Text literals are always surrounded by single quotation marks. This
lab discusses the most frequently used character functions
Common Oracle Text Functions

The LOWER Function
The LOWER function transforms data into lowercase. The following query shows how both a
column and a text constant serve as individual parameters for the LOWER function.
SQL> SELECT
2 'ORACLE' String,
3 LOWER('ORACLE') Lower
4 FROM DUAL;
STRING LOWER
SELECT LOWER(Ename) Ename, LOWER(Job) Job, Sal, HireDate 2*
FROM Emp:
SQL> SELECT
2 LOWER(Ename)||' is Working As '||Job Employee
3FROM Emp
Sql> SELECT
2 LOWER(Ename)||' is Working As '||Job Employee
3FROM Emp
4* WHERE LOWER(Job) = 'manager';
The UPPER and INITCAP Functions

The UPPER function is the exact opposite of the LOWER function: It transforms data into
uppercase. The INITCAP function capitalizes the first letter of a word and lowercases the rest of the
word.
SQL> SELECT
2 'oracle' String,
3 UPPER('oracle') Upper
4 FROM DUAL;
STRING UPPER
oracle ORACLE
SQL> SELECT Ename, UPPER(Ename) Upper
2 FROM Emp;
SQL> SELECT
2 Ename||' is Designated As '||LOWER(Job) 3
FROM Emp
4 WHERE Job = 'MANAGER';
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename||' is Designated As '||LOWER(Job) Ename 3
FROM Emp
4* WHERE Job = UPPER('manager')
SQL> SELECT
2 'oracle corporation' String,
3 INITCAP('oracle corporation') InitCap 4
FROM DUAl;
STRING INITCAP
oracle corporation Oracle Corporation
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'sample text for demonstration purpose' String,
3 INITCAP('sample text for demonstration purpose') InitCap 4*
FROM DUAL
SQL> /
STRING INITCAP
sample text for demonstration purpose Sample Text For Demonstration Purpose
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'sample1text@for4demonstration%purpose' String,
3 INITCAP('sample1text@for4demonstration%purpose') InitCap
4* FROM DUAL
SQL> /
STRING INITCAP
sample1text@for4demonstration%purpose Sample1text@For4demonstration%Purpose
SQL> SELECT
2 Ename, INITCAP(Ename) InitCap
3 FROM Emp;
SQL> SELECT

2 Ename||' is Designated As '||INITCAP(Job) Employee 3
FROM Emp
4 WHERE LOWER(Job) = 'manager';
Concatenation
Concatenation connects strings together to become one. Strings can be concatenated to produce a
single column in the result set. There are two methods of concatenation inOracle: One is with the
CONCAT function, the other is the concatenation operator (||), which is two vertical bars or pipe
symbols. The syntax of the CONCAT function is as follows.
CONCAT(char1, char2)
SQL> SELECT
2 'Oracle' Strimg1,
3 'Corporation' String2,
4 CONCAT('Oracle', 'Corporation') Concat 5
FROM DUAL;
STRIMG STRING2 CONCAT
Oracle Corporation OracleCorporation
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'Oracle' Strimg1,
3 'Corporation' String2,
4 CONCAT('Oracle', CONCAT(' ', 'Corporation')) Concat 5*
FROM DUAL
SQL> /
STRIMG STRING2 CONCAT
Oracle Corporation Oracle Corporation
SQL> SELECT
2 CONCAT(CONCAT(INITCAP(Ename),' is a '), Job) Job
3 FROM Emp
4 WHERE Deptno IN(10, 20);
JOB
King is a PRESIDENT
Clark is a MANAGER
Jones is a MANAGER
Ford is a ANALYST
Smith is a CLERK
Scott is a ANALYST
Adams is a CLERK
Miller is a CLERK
8 rows selected.
The SUBSTR Function
SUBSTR transforms a string, returning a substring or subset of a string, based on its input
parameters.
The SUBSTR function’s first input parameter is a string; the second is the starting position of the
subset; the third is optional, indicating the length of the subset. If thethird parameter is not used, the
default is to display the remainder of the string. Here is the syntax for SUBSTR.
SUBSTR(char1, starting_position [, substring_length])

If starting_position is a negative number, Oracle starts counting from the end of the string;
SQL> SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 0, 5) SubStr
4 FROM DUAL;
STRING SUBST
ABCDEFGHIJ ABCDE
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 4) SubStr
4* FROM DUAL
SQL> /
STRING SUBSTR
ABCDEFGHIJ DEFGHIJ
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 4, 4) SubStr
4* FROM DUAL
SQL> /
STRING SUBS
ABCDEFGHIJ DEFG
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', -5, 4) SubStr
4* FROM DUAL
SQL> /
STRING SUBS
ABCDEFGHIJ FGHI
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 5.25, 4.65) SubStr 4*
FROM DUAL
SQL> /
STRING SUBS
ABCDEFGHIJ EFGH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'ABCDEFGHIJ' String,
3 SUBSTR('ABCDEFGHIJ', 5, -4) SubStr
4* FROM DUAL
SQL> /
STRING S
ABCDEFGHIJ

SQL> SELECT Ename, Job, Deptno
2 FROM Emp
3 WHERE
4 SUBSTR(Job, 4, 3) = 'AGE';
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job, SUBSTR(Job, 4, 3) SubStr, Deptno 2*
FROM Emp
SQL> /
SQL> SELECT
2 CONCAT(
3 CONCAT(
4 CONCAT(
5 CONCAT(INITCAP(Ename), '''s Designation is '), INITCAP(Job)),
' But He is a '),
6 CONCAT(
7 INITCAP( SUBSTR(Job, 1, 3)),' Eater.'))
8 FROM Emp
9 WHERE SUBSTR(Job, 4, 3) = UPPER('Age');
The LENGTH Function
The LENGTH function determines the length of a string, expressed as a number. The following SQL
statement selects a text literal from the DUAL table in conjunction with the LENGTH function.
SELECT LENGTH('Hello there')
FROM dual
LENGTH('HELLOTHERE')
--------------------
11
1 row selected
SQL> SELECT
2 'ORACLE' String,
3 LENGTH('ORACLE') Length
4 FROM DUAL;
STRING LENGTH
ORACLE 6
SQL> SELECT
2 Ename||' Your Name is A Collection of '||LENGTH(Ename)||' Characters.'
EnameLength
3 FROM Emp;
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename||' Your Name is A Collection of '||LENGTH(Ename)||' Characters.'
EnameLength
3 FROM Emp
4* WHERE LENGTH(Ename) = &GLength
SQL> /
Enter value for glength: 4
ENAMELENGTH
KING Your Name is A Collection of 4 Characters.

WARD Your Name is A Collection of 4 Characters.
FORD Your Name is A Collection of 4 Characters.
SQL>
1 SELECT
2 Ename||' Your Name is A Collection of '||LENGTH(Ename)||' Characters.'
EnameLength
3 FROM Emp
4* WHERE LENGTH(Ename) = &GLength AND Ename LIKE '%A%'
SQL> SELECT
2 INITCAP(Ename) Name,
3 Job
4 FROM Emp
5 WHERE LENGTH(Job) = 7;
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 INITCAP(Ename),
3 Job
4 FROM Emp
5* WHERE SUBSTR(Job, 4, LENGTH(SUBSTR(Job, 4, 3))) = 'AGE'
SQL> /
INITCAP(EN JOB
Blake MANAGER
Clark MANAGER
Jones MANAGER
SQL> cl scr
The INSTR Function
INSTR, meaning in string, looks for the occurrence of a string inside another string, returning the
starting position of the search string within the target string. Unlike the other string functions,
INSTR does not return another string; rather, it returns a number
INSTR(char1, char2 [,starting_position [, occurrence]])
INSTR can take two optional input parameters. The third parameter allows you to specify the start
position for the search. The fourth parameter specifies which occurrence of the string to look for.
When these optional parameters are not used, the default value is 1
SQL> SELECT
2 'STRING' Original,
3 INSTR('STRING' , 'R') InString
4 FROM DUAL;
ORIGIN INSTRING
STRING 3
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'STRING' Original,
3 INSTR('STRING' , 'RIN', 2) InString 4*
FROM DUAL
SQL> /
ORIGIN INSTRING

STRING 3
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'STRING' Original,
3 INSTR('STRING' , 'RN', 2) InString
4* FROM DUAL
SQL> /
ORIGIN INSTRING
STRING 0
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 3, 2) InString 4*
FROM DUAL
SQL> /
ORIGINAL INSTRING
CORPORATE FLOOR 14
1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 3, 1) InString
4 FROM DUAL
SQL> /
ORIGINAL INSTRING
CORPORATE FLOOR 5
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 2, 1) InString 4*
FROM DUAL
SQL> /
ORIGINAL INSTRING
CORPORATE FLOOR 2
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 2, 2) InString 4*
FROM DUAL
SQL> /
ORIGINAL INSTRING
CORPORATE FLOOR 5
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', 5, 3) InString
4FROM DUAL;
ORIGINAL INSTRING

CORPORATE FLOOR 0
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'CORPORATE FLOOR' Original,
3 INSTR('CORPORATE FLOOR', 'OR', -7, 1) InString 4*
FROM DUAL
SQL> /
ORIGINAL INSTRING
CORPORATE FLOOR 5
SQL> SELECT
2 Ename, Job, INSTR(Job, 'A', 1, 2) InString 3
FROM Emp
4 WHERE Job = 'MANAGER';
ENAME JOB INSTRING
BLAKE MANAGER 4
CLARK MANAGER 4
JONES MANAGER 4
SQL> SELECT
2 Ename,
3 Job Original,
4 SUBSTR(Job,INSTR(Job, 'A', 1, 2), 3) SubString 5
FROM Emp
6 WHERE Job = 'MANAGER';
ENAME ORIGINAL SUB
BLAKE MANAGER AGE
CLARK MANAGER AGE
JONES MANAGER AGE
SQL> SELECT
2 Ename,
3 Job Original,
4 SUBSTR(Job,INSTR(Job, 'A', 1, 2), 3) SubString 5
FROM Emp
6 WHERE SUBSTR(Job, INSTR(Job, 'A', 1, 2), 1) = 'A';
SQL> SELECT
2 'Sample_Text' String,
3 'String',
4 'Text'
5 FROM DUAL;
STRING 'STRIN 'TEX
Sample_Text String Text
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'Sample_Text' String,
3 SUBSTR('Sample_Text', 1, 6) Sample, 4
SUBSTR('Sample_Text', 8) Text
5* FROM DUAL
SQL> /
STRING SAMPLE TEXT
Sample_Text Sample Text
SQL> SELECT

2 'Sample_Text' OrgString,
3 SUBSTR('Sample_Text', 1, INSTR('Sample_Text', '_', 1, 1) - 1) Sample, 4
SUBSTR('Sample_Text', INSTR('Sample_Text', '_', 1, 1) + 1) Text
5 FROM DUAL;
ORGSTRING SAMPLE TEXT
Sample_Text Sample Text
The LPAD and RPAD Functions
The LPAD and RPAD functions also transform data: They left pad and right pad strings,
respectively. When you pad a string, you add to it. These functions can add characters, symbols, or
even spaces to your result set. Unlike the LOWER, UPPER, or INITCAP functions, these functions
take more than one parameter as their input
LPAD(char1, n [, char2])
RPAD(char1, n [, char2])
SQL> SELECT
2 'Page 1' MyString,
3 LPAD('Page 1', 15, '*.') LPadded
4 FROM DUAL;
MYSTRI LPADDED
Page 1 *.*.*.*.*Page 1
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'Page 1' MyString,
3 LPAD('Page 1', 15) LPadded
4* FROM DUAL
SQL> /
MYSTRI LPADDED
Page 1 Page 1
SQL> SELECT
2 Ename,
3 LPAD(Ename, 10, '-') LPad
4 FROM Emp WHERE Sal >= 2500;
ENAME LPAD
KING ------KING
BLAKE -----BLAKE
JONES -----JONES
FORD ------FORD
SCOTT -----SCOTT
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename,
3 LPAD(Ename, 10) LPad
4* FROM Emp WHERE Sal >= 2500
SQL> /
ENAME LPAD
KING KING
BLAKE BLAKE

JONES JONES
FORD FORD
SCOTT SCOTT
SQL> SELECT
2 'Page 1' String1,
3 RPAD('Page 1' , 15, '*.') RPad
4 FROM DUAL;
STRING RPAD
Page 1 Page 1*.*.*.*.*
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'Page 1' String1,
3 RPAD('Page 1' , 15) RPad
4* FROM DUAL
SQL> /
STRING RPAD
Page 1 Page 1
SQL> SELECT
2 Ename,
3 LPAD(RPAD(Ename, 10, '-'), 15, '-') Center 4
FROM Emp;
ENAME CENTER
KING -----KING------
BLAKE -----BLAKE-----
CLARK -----CLARK-----
JONES -----JONES-----
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename,
3 LPAD(RPAD(Ename, 10), 15) Center
4* FROM Emp
The LTRIM, RTRIM, and TRIM Functions
LTRIM and RTRIM are the opposite of LPAD and RPAD because they trim, or remove, unwanted
characters, symbols, or spaces in strings. In this example, you see the use of the DUAL table to trim
the zero (0) from the left, the right, and both sides. If both the left and right sides of the string are
trimmed, you need to nest the function. The result of one function provides the input for the other
function.
LTRIM(char1 [, char2])
RTRIM(char1 [, char2])
The TRIM function removes leading characters, trailing characters, or both, effectively doing the job
of LTRIM and RTRIM in one function. If you want the function to act like LTRIM, specify
LEADING as the first parameter; for RTRIM, use the TRAILING option; for both, either specify the
BOTH keyword or omit it altogether
TRIM([LEADING|TRAILING|BOTH] char1 FROM char2)

SQL> SELECT
2 'xyzXxyLAST WORD' OrgStr,
3 LTRIM('xyzXxyLAST WORD', 'xy') Ltrim 4
FROM DUAL;
ORGSTR LTRIM
xyzXxyLAST WORD zXxyLAST WORD
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'xyzXxyLAST WORD' OrgStr,
3 LTRIM('xyzXxyLAST WORD', 'xzy') Ltrim 4*
FROM DUAL
SQL> /
ORGSTR LTRIM
xyzXxyLAST WORD XxyLAST WORD
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'xyzXxyLAST WORD' OrgStr,
3 LTRIM('xyzXxyLAST WORD', 'xzyX') Ltrim 4*
FROM DUAL
SQL> /
ORGSTR LTRIM
xyzXxyLAST WORD LAST WORD
sql> SELECT
2 ' LAST WORD' OrgStr,
3 LTRIM(' LAST WORD') Ltrim
4* FROM DUAL
SQL> /
ORGSTR LTRIM
LAST WORD LAST WORD
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'BROWNINGyxXxy' String,
3 RTRIM('BROWNINGyxXxy', 'xy') Rtrim
4* FROM DUAL
SQL> /
STRING RTRIM
BROWNINGyxXxy BROWNINGyxX
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'BROWNINGyxXxy' String,
3 RTRIM('BROWNINGyxXxy', 'xyX') Rtrim 4*
FROM DUAL
SQL> /
STRING RTRIM
BROWNINGyxXxy BROWNING
SQL> ED
Wrote file afiedt.buf

1 SELECT
2 'BROWNING ' String,
3 RTRIM('BROWNING ') Rtrim
4* FROM DUAL
SQL> /
STRING RTRIM
BROWNING BROWNING
SQL> cl scr
SQL> SELECT
2 Ename, Job, Sal
3 FROM Emp
4 WHERE
5 LTRIM(Job, 'MAN') LIKE 'GER';
ENAME JOB SAL
BLAKE MANAGER 2850
CLARK MANAGER 2450
JONES MANAGER 2975
SQL> cl scr
SQL> SELECT
2 'MITHSS' String,
3 TRIM('S' FROM 'MITHSS') Trimmed
4 FROM DUAL;
STRING TRIM
MITHSS MITH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITH' String,
3 TRIM('S' FROM 'SSMITH') Trimmed
4* FROM DUAL
SQL> /
STRING TRIM
SSMITH MITH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITHSS' String,
3 TRIM('S' FROM 'SSMITHSS') Trimmed
4* FROM DUAL
SQL> /
STRING TRIM
SSMITHSS MITH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITHSS' String,
3 TRIM(LEADING 'S' FROM 'SSMITHSS') Trimmed 4*

FROM DUAL
SQL> /
STRING TRIMME
SSMITHSS MITHSS
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITHSS' String,
3 TRIM(TRAILING 'S' FROM 'SSMITHSS') Trimmed 4*
FROM DUAL
SQL> /
STRING TRIMME
SSMITHSS SSMITH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITHSS' String,
3 TRIM(BOTH 'S' FROM 'SSMITHSS') Trimmed 4*
FROM DUAL
SQL> /
STRING TRIM
SSMITHSS MITH
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'SSMITHSS' String,
3 TRIM(BOTH 'SO' FROM 'SOSMITHSOS') Trimmed 4*
FROM DUAL
SQL> /
TRIM(BOTH 'SO' FROM 'SOSMITHSOS') Trimmed
*
ERROR at line 3:
ORA-30001: trim set should have only one character
The REPLACE Function
The REPLACE function replaces one string with another string. In the following example, when the
string ‘hand’ is found within the string ‘My hand is asleep’, it is replaced by the string ‘foot’.
REPLACE(char, if, then)
The second parameter looks to see if a string exists within the first parameter. If so, it displays the
third parameter. If the second parameter is not found, then the original string is displayed
SQL> SELECT
2 'JACK AND JUE' OrgStr,
3 REPLACE('JACK AND JUE' , 'J', 'BL') Replace 4
FROM DUAL;
ORGSTR REPLACE
JACK AND JUE BLACK AND BLUE

SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'JACK AND JUE' OrgStr,
3 REPLACE('JACK AND JUE' , 'J') Replace 4*
FROM DUAL
SQL> /
ORGSTR REPLACE
JACK AND JUE ACK AND UE
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'JACK AND JUE' OrgStr,
3 LENGTH('JACK AND JUE') - LENGTH(REPLACE('JACK AND JUE' , 'J')) "N J's
Found"
4* FROM DUAL
SQL> /
SQL> /
ORGSTR N J's Found
JACK AND JUE 2
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,
2 REPLACE(JOB, 'MAN', 'DAM') Replace
3 FROM Emp
4* WHERE Job = 'MANAGER'
SQL> /
ENAME REPLACE
BLAKE DAMAGER
CLARK DAMAGER
JONES DAMAGER
SQL> SELECT Ename, Job,
2 REPLACE (Job, 'P') Replace
3 FROM Emp
4 WHERE Job = 'PRESIDENT';
ENAME JOB REPLACE
KING PRESIDENT RESIDENT
SQL> SELECT Job,
2 REPLACE (Job, 'MAN', 'EXECUTIVE') Replace 3
FROM Emp
4 WHERE Job = 'SALESMAN';
The TRANSLATE Function
Unlike REPLACE, which replaces an entire string, the TRANSLATE function provides a one-forone
character substitution
TRANSLATE(char, if, then)
SQL> SELECT Ename, Job,
2 TRANSLATE(Job, 'P', ' ') Translate
3 FROM Emp
4 WHERE Job = 'PRESIDENT';
ENAME JOB TRANSLATE
KING PRESIDENT RESIDENT
SQL> ED

Wrote file afiedt.buf
1 SELECT Ename, Job,
2 TRANSLATE(Job, 'P') Translate
3 FROM Emp
4* WHERE Job = 'PRESIDENT'
SQL> /
TRANSLATE(Job, 'P') Translate
*
ERROR at line 2:
ORA-00909: invalid number of arguments
SQL> SELECT Job,
2 TRANSLATE(Job, 'MN', 'DM') Translate 3
FROM Emp
4 WHERE Job = 'MANAGER';
JOB TRANSLATE
MANAGER DAMAGER
MANAGER DAMAGER
MANAGER DAMAGER
The SOUNDEX Function
The SOUNDEX function allows you to compare differently spelled words that phonetically sound
alike. The next query uses the SOUNDEX function to display students whose last name sounds like
Martin.
SELECT student_id, last_name
FROM student
WHERE SOUNDEX(last_name) = SOUNDEX('MARTIN')
STUDENT_ID LAST_NAME
Using the ASCII and CHR Functions
SQL> select ascii('a'), ascii('z')
2 , ascii('A'), ascii('Z')
3 , ascii('ABC'), chr(77)
4 from dual;
ASCII('A') ASCII('Z') ASCII('A') ASCII('Z') ASCII('ABC') CHR(77)
---------- ---------- ---------- ---------- ------------ -------
97 122 65 90 65 M
If you compare the third and the fifth columns of the result, you can see that the ASCII function
considers only the first character of its argument, regardless of the length of the input text
SQL> select ascii('a'), ascii('z')
2 , ascii('A'), ascii('Z')
3 , ascii('ABC'), chr(77)
4 from dual;
ASCII('A') ASCII('Z') ASCII('A') ASCII('Z') ASCII('ABC') CHR(77)
---------- ---------- ---------- ---------- ------------ -------
97 122 65 90 65 M

Arithmetic Functions-----
The SIGN Function
The SIGN function tells you the sign of a value, returning a number 1 for a positive number, -1 for a
negative number, or 0 for zero. The following example compares SIGN with the ABS function.
SELECT -14, SIGN(-14), SIGN(14), SIGN(0), ABS(-14)
FROM dual
-14 SIGN(-14) SIGN(14) SIGN(0) ABS(-14)
-14 -1 1 0 14
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Comm
2 FROM Emp
3* WHERE SIGN(Sal - Comm) = -1
SQL> /
ENAME SAL COMM
MARTIN 1250 1400
SQL> SELECT 'The Current calender in Server is : '||SYSDATE Today FROM DUAL;

ROUND and TRUNC Functions
ROUND and TRUNC are two useful functions that round and truncate (or cut off) values,
respectively, based on a given number of digits of precision. The next SELECT statement illustrates
the use of ROUND and TRUNC, which both take two input parameters. Observe the differences in
the result
>SELECT 222.34501,
ROUND(222.34501, 2),
TRUNC(222.34501, 2)
FROM dual;
222.34501 ROUND(222.34501,2) TRUNC(222.34501,2)
222.34501 222.35 222.34
1 row selected.
Here, ROUND (222.34501, 2) rounds the number 222.34501 to two digits to the right of the
decimal, rounding the result up to 222.35, following the normal convention for rounding. In contrast,
TRUNC cuts off all digits beyond two digits to the right of the decimal, resulting in
222.34. ROUND and TRUNC can be used to affect the left side of the decimal as well by passing a
negative number as a parameter.
>SELECT 222.34501,
ROUND(222.34501, -2),
TRUNC(222.34501, -2)
FROM dual
222.34501 ROUND(222.34501,-2) TRUNC(222.34501,-2)
222.34501 200 200
The following is the syntax for ROUND and TRUNC.
ROUND(value [, precision])
TRUNC(value [, precision])
Numbers with decimal places may be rounded to whole numbers by omitting the second parameter,
or specifying a precision of 0.
>SELECT 2.617, ROUND(2.617), TRUNC(2.617)
FROM dual
2.617 ROUND(2.617) TRUNC(2.617)
2.617 3 2
1 row selected.
You can use the TRUNC and ROUND functions not only on values of the NUMBER data type but
also on the DATE data type,
FLOOR and CEIL Functions

The CEIL function returns the smallest integer greater than or equal to a value; the FLOOR function
returns the largest integer equal to or less than a value. These functions perform much like the
ROUND and TRUNC functions, without the optional precision parameter.
>SELECT FLOOR(22.5), CEIL(22.5), TRUNC(22.5), ROUND(22.5)
FROM dual
FLOOR(22.5) CEIL(22.5) TRUNC(22.5) ROUND(22.5)
22 23 22 23
1 row selected.
The syntax for the FLOOR and CEIL functions is as follows.
FLOOR(value)
CEIL(value)
The MOD Function
MOD is a function that returns the modulus, or the remainder of a value divided by another value. It
takes two input parameters, as in the following SELECT statement.
>SELECT MOD(23, 8)
FROM dual
MOD(23,8)
7
1 row selected.
The MOD function divides 23 by 8 and returns a remainder of 7. The following is the syntax for
MOD.
MOD(value, divisor)
The MOD function is particularly useful if you want to determine whether a value is odd or even. If
you divide by 2 and the remainder is a zero, this indicates that the value is even; if the remainder is
1, it means that the value is odd.
The REMAINDER Function
The REMAINDER function calculates the remainder, according to the IEEE specification. The
syntax is as follows.
REMAINDER(value, divisor)
The difference between REMAINDER and the MOD function is that MOD uses FLOOR in its
computations, whereas REMAINDER uses ROUND. The next example shows that the results
between the MOD and REMAINDER functions can be different.
>SELECT MOD(23,8), REMAINDER(23,8)

FROM DUAL
MOD(23,8) REMAINDER(23,8)
7 -1
1 row selected.
Date Functions
let’s first review the syntax to specifydate/time-related constants (or literals), using predefined
ANSI/ISO SQL standard formats.
SQL> select * from nls_session_parameters;
If you execute this query, you will see the current values for NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT.

1 SELECT Ename, SYSDATE Today, HireDate,
2TRUNC(SYSDATE - HireDate) DateDiff
3* FROM Emp
SQL> /
ENAME TODAY HIREDATE DATEDIFF
KING 07-JUL-10 17-NOV-81 10459
BLAKE 07-JUL-10 01-MAY-81 10659
CLARK 07-JUL-10 09-JUN-81 10620
…..
….
Sql> SELECT Ename, SYSDATE Today, HireDate,
2TRUNC(SYSDATE - HireDate) Days,
3 TRUNC((SYSDATE - HireDate) / 365) Years
4FROM Emp
5 WHERE
6* TRUNC((SYSDATE - HireDate) / 365) > 28
SQL> SELECT Ename, HireDate, SYSDATE, SYSDATE + HireDate Added
2 FROM Emp;
SELECT Ename, HireDate, SYSDATE, SYSDATE + HireDate Added
*
ERROR at line 1:
ORA-00975: date + date not allowed
SQL> SELECT
2 '07-JUL-10' Today,
3 '07-JUL-10' + 3 "3 Days"
4 FROM DUAL;
'07-JUL-10' + 3 "3 Days"
*
ERROR at line 3:
ORA-01722: invalid number
SQL> SELECT
2 SYSDATE Today,
3 SYSDATE - '01-MAY-10' Diff
4 FROM DUAL;
SYSDATE - '01-MAY-10' Diff
*
ERROR at line 3:
ORA-01722: invalid number
SQL> SELECT
2 SYSDATE Today,

3 SYSDATE + 90 "3 Months"
4 FROM DUAL;
TODAY 3 Months
07-JUL-10 05-OCT-10
SQL> select sysdate from dual;
SYSDATE
---------
05-NOV-13
SQL> select sysdate+4 from dual;
SYSDATE+4
---------
09-NOV-13
SQL> select '09-nov-13'+3 from dual;
select '09-nov-13'+3 from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> SELECT
2 SYSDATE ToDay,
3 ADD_MONTHS(SYSDATE, 3) "3 Months"
4 FROM DUAL;
TODAY 3 Months
07-JUL-10 07-OCT-10
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 SYSDATE ToDay,
3 ADD_MONTHS(SYSDATE, -3) "3 Months"
4* FROM DUAL
SQL> /
TODAY 3 Months
07-JUL-10 07-APR-10
SQL> SELECT
2 Ename, Sal, SYSDATE Today, HireDate,
3 MONTHS_BETWEEN(SYSDATE, HireDate) "Months" 4
FROM Emp;
ENAME SAL TODAY HIREDATE Months
KING 5000 07-JUL-10 17-NOV-81 343.693194
BLAKE 2850 07-JUL-10 01-MAY-81 350.209323
‘’’’’’’
‘’’’’’’’
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename, Sal, SYSDATE Today, HireDate,
3 TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate)) "Months"
4* FROM Emp

SQL> /
ENAME SAL TODAY HIREDATE Months
KING 5000 07-JUL-10 17-NOV-81 343
BLAKE 2850 07-JUL-10 01-MAY-81 350
CLARK 2450 07-JUL-10 09-JUN-81 348
;;;;;;;;
;;;;;
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename, Sal, SYSDATE Today, HireDate,
3 TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate)) "Months",
4 TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12) "Years"
5FROM Emp
6 WHERE
7* TRUNC(MONTHS_BETWEEN(SYSDATE, HireDate) / 12) > 28
SQL> /
ENAME SAL TODAY HIREDATE Months Years
BLAKE 2850 07-JUL-10 01-MAY-81 350 29
CLARK 2450 07-JUL-10 09-JUN-81 348 29
JONES 2975 07-JUL-10 02-APR-81 351 29
ALLEN 1600 07-JUL-10 20-FEB-81 352 29
WARD 1250 07-JUL-10 22-FEB-81 352 29
SMITH 800 07-JUL-10 17-DEC-80 354 29
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 '07-JUL-10' Today,
3 '07-FEB-10' Past,
4 MONTHS_BETWEEN('07-JUL-10', '10-FEB-10') Months
5* FROM DUAL
SQL> /
TODAY PAST MONTHS
07-JUL-10 07-FEB-10 4.90322581
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 '07-JUL-10' Today,
3 '07-FEB-10' Past,
4 MONTHS_BETWEEN('07-FEB-10', '07-JUL-10') Months
5* FROM DUAL
SQL> /
TODAY PAST MONTHS
07-JUL-10 07-FEB-10 -5
SQL> SELECT
2 SYSDATE Today,
3 NEXT_DAY(SYSDATE, 'FRI') "Friday"
4 FROM DUAL;
TODAY Friday
07-JUL-10 09-JUL-10
SQL> ED
Wrote file afiedt.buf

1 SELECT
2 SYSDATE Today,
3 NEXT_DAY(SYSDATE, 6) "Friday"
4* FROM DUAL
SQL> /
TODAY Friday
07-JUL-10 09-JUL-10
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 SYSDATE Today,
3 NEXT_DAY(SYSDATE, 'FRIDAY') "Friday"
4*FROM DUAL
SQL> /
TODAY Friday
07-JUL-10 09-JUL-10
SQL> SELECT
2 SYSDATE Today,
3 LAST_DAY(SYSDATE) LastDay
4 FROM DUAL;
TODAY LASTDAY
07-JUL-10 31-JUL-10
SQL> SELECT
2 'The Days Left in The Current Month Are : '||
3 (
4 LAST_DAY(SYSDATE) - SYSDATE
5 ) "Days Left"
6 FROM DUAL;
Days Left
The Days Left in The Current Month Are : 24
EXTRACT
You can extract various components of a date or timestamp expression with the ANSI/ISO
standard EXTRACT function. Depending on the datatype of the argument d (DATE,
TIMESTAMP,or INTERVAL) the following values for c are supported: YEAR, MONTH, DAY,
HOUR, MINUTE, SECOND,TIMEZONE_ABBR, and so on
SQL> select bdate
2 , extract(year from hiredate) as year_of_join
3 , extract(month from hiredate) as month_of_join
4 , extract(day from hiredate) as day_of_join
5 from emp
6 where ename = 'KING';
BDATE YEAR_OF_BIRTH MONTH_OF_BIRTH DAY_OF_BIRTH
----------- ------------- -------------- ------------

17-NOV-1952 1952 11 17
ROUND and TRUNC

The default format is 'DD', resulting in rounding or truncating to midnight. For example,
TRUNC(SYSDATE) truncates the current system date and time to midnight.
SQL> SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE) Round,
4 TRUNC(SYSDATE) Trunc
5 FROM DUAL;
TODAY ROUND TRUNC
07-JUL-10 08-JUL-10 07-JUL-10
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE, 'DAY') Round,
4 TRUNC(SYSDATE, 'DAY') Trunc
5* FROM DUAL
SQL> /
TODAY ROUND TRUNC
07-JUL-10 11-JUL-10 04-JUL-10
SQL> SELECT /* AFIER MAKING SYSTEM DATE 11.40 AM */
2 SYSDATE Today,
3 ROUND(SYSDATE, 'DAY') Round,
4 TRUNC(SYSDATE, 'DAY') Trunc
5* FROM DUAL
TODAY ROUND TRUNC
07-JUL-10 04-JUL-10 04-JUL-10

SQL> ED
Wrote file afiedt.buf
1 SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE, 'MONTH') Round,
4 TRUNC(SYSDATE, 'MONTH') Trunc
5* FROM DUAL
SQL> /
TODAY ROUND TRUNC
07-JUL-10 01-JUL-10 01-JUL-10
/* CHNGE SYSTEM DATE TO 16-JUL-10 */
SQL> /
TODAY ROUND TRUNC
16-JUL-10 01-AUG-10 01-JUL-10
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 SYSDATE Today,
3 ROUND(SYSDATE, 'YEAR') Round,
4 TRUNC(SYSDATE, 'YEAR') Trunc
5* FROM DUAL
TODAY ROUND TRUNC
07-JUL-10 01-JAN-11 01-JAN-10

SQL> SELECT Ename, Sal, Comm, Sal - Comm Diff
2 FROM Emp
3 WHERE Comm IS NOT NULL;
ENAME SAL COMM DIFF
MARTIN 1250 1400 -150
ALLEN 1600 300 1300
TURNER 1500 0 1500
WARD 1250 500 750
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Comm,
2 TO_CHAR(Sal - Comm, '9999MI') Diff
3 FROM Emp
4* WHERE Comm IS NOT NULL
SQL> /
ENAME SAL COMM DIFF
MARTIN 1250 1400 150-
ALLEN 1600 300 1300
TURNER 1500 0 1500
WARD 1250 500 750
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Comm,
2 TO_CHAR(Sal - Comm, '9999PR') Diff
3 FROM Emp

4* WHERE Comm IS NOT NULL
SQL> /
ENAME SAL COMM DIFF
MARTIN 1250 1400 <150>
ALLEN 1600 300 1300
TURNER 1500 0 1500
WARD 1250 500 750
Wrote file afiedt.buf
1 SELECT Ename, Sal, Comm,
2 TO_CHAR(Sal - Comm, '9999PT') Diff
3 FROM Emp
4* WHERE Comm IS NOT NULL
SQL> /
ENAME SAL COMM DIFF
MARTIN 1250 1400 (150)
ALLEN 1600 300 1300
TURNER 1500 0 1500
WARD 1250 500 750
SQL> SELECT
2 'The Roman Number of Decimal '||&GVal1||' is '|| 3
TO_CHAR(&GVal2, 'RN') "Deci 2 Roman"
4 FROM DUAL;
Enter value for gval1: 1
Enter value for gval2: 1
Deci 2 Roman
The Roman Number of Decimal 1 is I
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'The Roman Number of Decimal '||&GVal1||' is '|| 3
LTRIM(TO_CHAR(&GVal2, 'RN')) "Deci 2 Roman"
4* FROM DUAL
SQL> /
Enter value for gval1: 8
Enter value for gval2: 8
Deci 2 Roman
The Roman Number of Decimal 8 is VIII
SQL> SELECT
2 Ename, Sal, Comm, TO_CHAR(Sal - Comm, '9G999D99S') Diff 3
FROM Emp
4 WHERE Comm IS NOT NULL;
ENAME SAL COMM DIFF
MARTIN 1250 1400 150.00-
ALLEN 1600 300 1,300.00+
TURNER 1500 0 1,500.00+
WARD 1250 500 750.00+
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'The Hexadecimal Value of Decimal '||&GVal1||' is '|| 3
TO_CHAR(&GVal2, 'x') "Deci 2 Hexa"
4* FROM DUAL
SQL> /
Enter value for gval1: 10

Enter value for gval2: 10
Deci 2 Hexa
The Hexadecimal Value of Decimal 10 is a
1 SELECT Ename,
2 TO_CHAR(Sal, '0999') Sal,
3 TO_CHAR(Comm, '0999') Comm
4* FROM Emp
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,
2 TO_CHAR(Sal, '0G999D99') Sal,
3 TO_CHAR(Comm, '0G999D99') Comm
4* FROM Emp
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename,
2 TO_CHAR(Sal, '0G999D99') Sal,
3 TO_CHAR(NVL(Comm, 0), '0G999D99') Comm,
4 TO_CHAR(Sal + NVL(Comm, 0), '0G999D99') TotSal
5*FROM Emp
SQL> /
ENAME SAL COMM TOTSAL
KING 5,000.00 0,000.00 5,000.00
BLAKE 2,850.00 0,000.00 2,850.00
CLARK 2,450.00 0,000.00 2,450.00
1 SELECT Ename,
2 TO_CHAR(Sal, '0G999D99C') Sal,
3 TO_CHAR(NVL(Comm, 0), '0G999D99C') Comm,
4 TO_CHAR(Sal + NVL(Comm, 0), '0G999D99C') TotSal 5*
FROM Emp
Changing the Date Display Format
When you query a DATE data type column, Oracle displays it in the default format determined by
the database NLS_DATE_FORMAT parameter. The most frequent setup values you will see are
DD-MON-YYYY and DD-MON-RR. The RR represents a two-digit year based on the century; if
the two-digit year is between 50 and 99, then it’s the previous century; if the two-digit year is
between 00 and 49, it’s the current century The TO_CHAR conversion function changes the DATE
data type into text and applies a format mask. As you see from the syntax listed in Table 5.3, the
function takes a DATE data type as the first parameter; the second optional parameter is for the
format mask.
Date-Related Conversion Functions



Implicit Conversion and Default Date Format
Without a format mask, Oracle can implicitly perform a conversion of the text literal
to The DATE data type when the text literal is in the default date format. This default
format is determined by the NLS_DATE_FORMAT, an Oracle instance parameter.
The SYSDATE Function
The SYSDATE function returns the computer operating system’s current date and
time and does not take any parameters. If you connect to the database server via a
client machine, it returns the date and time of the machine hosting the database, not
the date and time of your client machine. For example, if your client workstation is
located in New York, your local time zone is Eastern Standard Time (EST); if you
connect to a server in California, you receive the server’s Pacific Standard Time (PST)
date and time. To include the time in the result, you use the TO_CHAR function
together with the appropriate format mask.
SELECT SYSDATE, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI')
FROM dual
SYSDATE TO_CHAR(SYSDATE,'
26-OCT-08 26-OCT-2008 19:49
1 row selected.
SQL> SELECT
2 'Today is : '||SYSDATE||TO_CHAR(SYSDATE, 'B.C.') Today 3
FROM DUAL;
TODAY
Today is : 09-JUL-10A.D.
SQL> SELECT Ename, Sal, HireDate||TO_CHAR(HireDate, ' B.C.') HireDate

2 FROM Emp;
SQL> SELECT
2 'The Meridian in My Clock is : '|| 3
TO_CHAR(SYSDATE, 'P.M.') Meridian 4 FROM
DUAL;
MERIDIAN
The Meridian in My Clock is : A.M.
SQL> SELECT
2 'The Current Date is '||
3 SYSDATE||' '||TO_CHAR(SYSDATE, 'CC, B.C.') Calendar 4
FROM DUAL;
CALENDAR
The Current Date is 09-JUL-10 21, A.D.
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'The Current Date is '||
3 SYSDATE||' '||TO_CHAR(SYSDATE, 'SCC, B.C.') Calendar 4*
FROM DUAL
SQL> /
CALENDAR
The Current Date is 09-JUL-10 21, A.D.
SQL> cl scr
SQL> SELECT
2 Ename,
3 HireDate||' '||TO_CHAR(HireDate, 'CC, B.C.') HireDate 4
FROM Emp;
SQL> SELECT
2 'The Weekday Number of Today : '|| 3
TO_CHAR(SYSDATE, 'D') WeekDay
4 FROM DUAL;
WEEKDAY
The Weekday Number of Today : 6
SQL> SELECT
2 Ename||' You Joined on The Weekday of '|| 3
TO_CHAR(HireDate, 'D') WeekDay
4 FROM Emp;
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename||' You Joined on The Weekday of '|| 3
TO_CHAR(HireDate, 'D') WeekDay
4 FROM Emp
5* WHERE TO_CHAR(HireDate, 'D') = &GWeekday
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'Today is '||SYSDATE||TO_CHAR(SYSDATE, ' Day') Today 3*
FROM DUAL
SQL> /
TODAY

Today is 09-JUL-10 Friday
SQL> SELECT
2 Ename||' You Are Recruited on '||HireDate|| 3
TO_CHAR(HireDate, ' Day') Hiredate
4 FROM Emp;
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename||' You Are Recruited on '||HireDate|| 3
TO_CHAR(HireDate, ' Day') Hiredate
4 FROM Emp
5* WHERE RTRIM(TO_CHAR(HireDate, 'Day')) = 'Friday'
SQL> /
HIREDATE
BLAKE You Are Recruited on 01-MAY-81 Friday
ALLEN You Are Recruited on 20-FEB-81 Friday
SQL> SELECT
2 'We Are on the Day '||TO_CHAR(SYSDATE, 'DD')|| 3
' of This Month.' MonthDay
4 FROM DUAL;
MONTHDAY
We Are on the Day 09 of This Month.
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'The Current day Running in This Year : '|| 3
TO_CHAR(SYSDATE, 'DDD') Yearday
4* FROM DUAL
SQL> /
YEARDAY
The Current day Running in This Year : 190
SQL> SELECT Ename, Sal, HireDate
2 FROM Emp
3 WHERE
4 TO_CHAR(HireDate, 'DDD') > 245;
ENAME SAL HIREDATE
KING 5000 17-NOV-81
MARTIN 1250 28-SEP-81
;;;
7 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, HireDate
2 FROM Emp
3 WHERE
4* TO_CHAR(HireDate, 'DDD') BETWEEN 245 AND 300
SQL> /
ENAME SAL HIREDATE
MARTIN 1250 28-SEP-81
TURNER 1500 08-SEP-81
SQL> SELECT
2 'Today is : '||TO_CHAr(SYSDATE, 'DY') Today 3
FROM DUAL;

TODAY
Today is : FRI
SQL> cl scr
SQL> SELECT
2 'The Current Week of This Year is : ' 3
||TO_CHAR(SYSDATE, 'IW') YearWeek
4 FROM DUAL;
YEARWEEK
The Current Week of This Year is : 27
SQL> SELECT
2 Ename, Sal, HireDate, TO_CHAR(HireDate, 'IW') YearWeek 3
FROM Emp
4 WHERE TO_CHAR(HireDate, 'IW') > 25;
ENAME SAL HIREDATE YE
KING 5000 17-NOV-81 47
MARTIN 1250 28-SEP-81 40
;;;;
SQL> SELECT
2 'The Current Year is : '||TO_CHAR(SYSDATE, 'IYYY, B.C.') ThisYear 3
FROM DUAL;
THISYEAR
The Current Year is : 2010, A.D.
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 'The Current Year is : '||TO_CHAR(SYSDATE, 'IYYY, B.C. "Century : "CC')
ThisYear
3* FROM DUAL
SQL> /
THISYEAR
The Current Year is : 2010, A.D. Century : 21
SQL> SELECT
2 Ename||'''s Recruited Year is '||TO_CHAR(HireDate, 'IYYY, B.C.') EmpHire 3
FROM Emp;
SQL> ED
Wrote file afiedt.buf
1 SELECT
2 Ename||'''s Recruited Year is '||
3 TO_CHAR(HireDate, 'YYYY "=>" Year, B.C.') EmpHire 4*
FROM Emp
SQL> /
EMPHIRE
KING's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
BLAKE's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
CLARK's Recruited Year is 1981 => Nineteen Eighty-One, A.D.
;;;;;;;;;;;;;;;;;;;;;
;;;;;
SQL> SELECT
2 'The Current Year is : '||
3 TO_CHAR(SYSDATE, 'YYYY "=>" Year, B.C.') Year 4
FROM DUAl;
YEAR
The Current Year is : 2010 => Twenty Ten, A.D.
SQL> cl scr

SQL> SELECT
2 'The Current Week in This Month is : '|| 3
TO_CHAR(SYSDATE, 'W') MonthWeek
4 FROM DUAl;
MONTHWEEK
The Current Week in This Month is : 2