iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |

 

Lesson 12

"The only way to get rid of a temptation is to yield to it." - Oscar Wilde (1854-1900)

 

Read first then play the video:

   SQL-VIDEO -Troubleshooting - UNION, INTERSET, and MINUS statements
 
 

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 iself user.
SQL> CONNECT iself/schooling


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;

 

"Don't stay in bed, unless you can make money in bed." - George Burns (1896-1996)

 

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?