everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

SQL

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19
<< Previous

Chapter # 12

Next >>


 

UNION, INTERSET, and MINUS statements

 

Introduction

Your organization has another table that contains employee data for the accounting department only. You have been assigned to check the contents of the accounting table with the base EMP table.

 

You need to:

1- List all of the records from the EMP table in the accounting department that are not in the accounting table.

2- List all of the records that are common in both tables.

3- Merge the two tables so that you can query all of the records with no duplicated records.

 

Topics:

UNION

INTERSECT

MINUS

 

Connect to SQLPLUS as the oracle user.
SQL> CONNECT oracle/learning
 

Set the linesize to 100 and the pagesize to 55.
SQL> SET LINESIZE 100 PAGESIZE 55
 

Create a table named accounting and copy all of the accounting employees into it.
SQL> CREATE TABLE accounting
AS (SELECT * FROM emp
WHERE deptno = 10)
SQL> /
 

Query the accounting table.
SQL> SELECT * FROM accounting
SQL> /
 

Now, insert a new record into the accounting table.
SQL> INSERT INTO accounting VALUES
(9000,'Dana','Kazerooni',7782,'04-Apr-02',1500,null,10)
SQL> /
SQL> COMMIT;
 

Insert a new record into the EMP table.
SQL> INSERT INTO EMP VALUES
(9999,'Borna','Kazerooni',7782,'04-Apr-02',1500,null,10)
SQL> /
SQL> COMMIT;
 

Query the accounting table again.
SQL> SELECT * FROM accounting
SQL> /
Note the employee that was added to accounting table.
 

Query the accounting employees from the EMP table.
SQL> SELECT * FROM emp
WHERE deptno = 10
SQL> /
Note! The employee record was added to the EMP table.
 

MINUS statement

It will query all the records that are not matching against your base table.

Find all of the records from the EMP table in the accounting department, that are not in the accounting table.
SQL> SELECT * FROM emp
WHERE deptno = 10
MINUS SELECT * FROM accounting
SQL> /
Notice that this is the record that you added into the EMP table.
 

INTERSECT statement

It will query all the records that match with the base table. It is the same as joining two tables.

Search and list for all of the records that are common in both tables.
SQL> SELECT * FROM emp
WHERE deptno = 10
INTERSECT SELECT * FROM accounting
SQL> /
Notice the common records.

UNION statement

It will query all the records that match or not match with the base table.
 

Merge the two tables so that you can query all of the records with no duplicated records.
SQL> SELECT * FROM accounting
UNION ALL SELECT * FROM emp

WHERE deptno = 10
AND empno NOT IN (SELECT empno FROM accounting)
SQL> /
 

Now, drop the accounting table.
SQL> DROP TABLE accounting
SQL> /
 

Delete the record which was added to the EMP table.
SQL> DELETE FROM emp
WHERE empno = 9999
SQL> /
SQL> COMMIT;

 

Questions:

Q: What does the UNION statement in the SQL statement?

Q: What does the INTERSET statement in the SQL statement?

Q: What does the MINUS statement in the SQL statement?

     Reviews and Templates for FrontPage
     

Copyright everythingOracle.bizhat.com 2006 All Rights Reserved.