alt
Advertisement
Online Training
Career Series
Exforsys
Exforsys arrow Tutorials arrow Oracle 9i arrow Tutorial 5: Working with Multiple Tables
Site Search


Tutorial 5: Working with Multiple Tables
Article Index
Tutorial 5: Working with Multiple Tables
Page 2

SQL> SELECT D.DEPTNO , NVL(ENAME, ' No employee') , DNAME
FROM EMP E,DEPT D
WHERE D.DEPTNO= E.DEPTNO(+);

DEPTNO NVL(ENAME,'N DNAME
------- ------------ --------------
10 CLARK ACCOUNTING
10 KING ACCOUNTING
10 MILLER ACCOUNTING
20 SMITH RESEARCH
20 ADAMS RESEARCH
20 FORD RESEARCH
20 SCOTT RESEARCH
20 JONES RESEARCH
30 ALLEN SALES
30 BLAKE SALES
30 MARTIN SALES
30 JAMES SALES
30 TURNER SALES
30 WARD SALES
40 No employee OPERATIONS

4) SELF JOIN:
When we join a table to itself it is called self join.To join a
table itself means that each row of the table is combined with itself
and with every other row of the table. The self join can be seen as join
of two copies of the same table.

SQL> SELECT E.ENAME,M.ENAME FROM EMP E,EMP
WHERE E.MGR=M.EMPNO;

ENAME ENAME
---------- ----------
SCOTT JONES
FORD JONES
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
MILLER CLARK
ADAMS SCOTT
JONES KING
CLARK KING
BLAKE KING
SMITH FORD

Above query uses EMP table to display names of the employees and their managers

SQL> SELECT E.ENAME,NVL(M.ENAME,'He is managed by none') FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO(+)

ENAME NVL(M.ENAME,'HEISMANA
---------- ---------------------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
KING He is managed by none

SUBQUERY :
A query within another quey. A select statement whose output is substituted in the condition of another select statement .(A query is a statement written for returning specific data). The subquery is executed only once. A subquery is enclosed in parenthesis

Conside the following queries.

1) SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH';
2) SELECT ENAME FROM EMP WHERE DEPTNO= 20

These two queries can be combined as follows.

SQL> SELECT ENAME FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP
WHERE ENAME = 'SMITH');

The SELECT statement inside parenthesis is called subquery and the one which
uses the values returned by it as main query.


SQL> SELECT ENAME FROM EMP
WHERE JOB=( SELECT JOB FROM EMP
WHERE ENAME = 'SCOTT');

ENAME
----------
SCOTT
FORD

CORRELATED QUERY:
In a correlated subquery the table used in outer query refers to the table used in the inner query. The correlated subquery is executed repeatedly once
for each row of the main query table.

Query to diplay name of highest salary taker.

SQL> SELECT EMPNO, ENAME FROM EMP A
WHERE 1 > ( SELECT COUNT(*) FROM EMP B
WHERE A.SAL < B.SAL)

EMPNO ENAME
-------- ----------
7839 KING


Query to diplay name of lowest salary taker.

SQL> SELECT EMPNO, ENAME,SAL FROM EMP A
WHERE 1 > ( SELECT COUNT(*) FROM EMP B
WHERE A.SAL > B.SAL)

EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800

SET OPERATORS
They combine results from two or more queries into one result.Data type
of all selected colums must be of same type.

 

UNION: It returns rows of first query plus rows of second query but avoids duplicates.(UNION ALL will give duplicates also).

To display designations in department 10 and 20

SQL> SELECT JOB FROM EMP WHERE DEPTNO=10
UNION
SELECT JOB FROM EMP WHERE DEPTNO=20;

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT

To display designations in department 10,20 and 30

SQL> SELECT JOB FROM EMP WHERE DEPTNO=10
UNION
SELECT JOB FROM EMP WHERE DEPTNO=20
UNION
SELECT JOB FROM EMP WHERE DEPTNO=30;

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN

Similar output can be produced by writing

SQL> SELECT DISTINCT JOB FROM EMP;

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN

INTERSECT: It returns rows that are common to all queries.

To diplay the designations that are common in DEPTNO 10 and 20 write.

SQL> SELECT JOB FROM EMP WHERE DEPTNO=10
INTERSECT
SELECT JOB FROM EMP WHERE DEPTNO=20;

JOB
---------
CLERK
MANAGER

MINUS : It returns rows unique to the first query.
To diplay designations unique in DEPTNO 10 we can write.

SQL> SELECT JOB FROM EMP WHERE DEPTNO=10
MINUS
SELECT JOB FROM EMP WHERE DEPTNO=20;

JOB
---------
PRESIDENT

SQL> SELECT JOB FROM EMP WHERE DEPTNO=10
MINUS
SELECT JOB FROM EMP WHERE DEPTNO=20
MINUS
SELECT JOB FROM EMP WHERE DEPTNO=30;

JOB
---------
PRESIDENT


Trackback(0)
Comments (6)add comment

amit_tgi said:

  your examples r good but if we get analysis of query how it be execute. This will be benificial
August 18, 2005

Ravinder Singh banga said:

  your tutorials are really good and helpfull. I suggest you one thing please also put one small section of qiuz or test at the end of each tutorials, so that a person judge his knowlegde regarding that tutorials.

Thanks,
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
June 27, 2007

Bhavana said:

  Really your tutorials are really excellent and helpful for everybody.As per my opinion it's perfect tutorial.


Thanks,
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
July 17, 2007

chidambar said:

  Thanks. Today is my oracle 9i exam it helped me clarify certain doubts
Thanks once again..
This e-mail address is being protected from spam bots, you need JavaScript enabled to view it
December 24, 2007

sansaar said:

  This Tutorial was very usefull one and very easy to understand. thank u
January 09, 2008

Apparao Akki said:

 
Yeah, Its really Good and simply understandable manner for updations for whoever lost their practice since long time.
my suggestion is : Its good to have some explanation after the example for some of the critical queries .

Thanks!!!

Appa
March 05, 2008

Write comment

busy

 
< Prev   Next >
Exforsys Offers
© 2008 Exforsys.com
Joomla! is Free Software released under the GNU/GPL License.
Page copy protected against web site content infringement by Copyscape