SQL

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.
 

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.
 

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.

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

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;
 

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?