"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:
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?
|