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.

-- Hands-On 03 (Troubleshooting) 

SET ECHO ON
CLEAR SCR
-- Connect to SQLPLUS as the iself user.
-- 
pause

CONNECT iself/schooling
pause

CLEAR SCR
-- Set the linesize to 100 and the pagesize to 55.
--
pause

SET LINESIZE 100 PAGESIZE 55
pause

CLEAR SCR
-- Create a table named accounting and 
-- copy all of the accounting employees into it.
pause

CREATE TABLE accounting
AS (SELECT * FROM emp 
WHERE deptno = 10)
/
pause

CLEAR SCR
-- Query the accounting table.
--
pause

SELECT * FROM accounting
/
pause

CLEAR SCR
-- Now, insert a new record into the accounting table.
--
pause

INSERT INTO accounting VALUES 
(9000,'Dana','Kazerooni',7782,'04-Apr-02',1500,null,10)
/
COMMIT;
pause


CLEAR SCR
-- Insert a new record into the EMP table.
--
pause

INSERT INTO EMP VALUES 
(9999,'Borna','Kazerooni',7782,'04-Apr-02',1500,null,10)
/
COMMIT;
pause

CLEAR SCR
-- Query the accounting table again.
--
pause

SELECT * FROM accounting
/
-- Note the employee that was added to accounting table.
pause

CLEAR SCR 
-- Query the accounting employees from the EMP table.
--
pause

SELECT * FROM emp
WHERE deptno = 10
/
-- Note! 
-- The employee record was added to the EMP table.
--
pause

CLEAR SCR
-- Find all of the records from the EMP table 
-- in the accounting department, that are not in 
-- the accounting table.
--
pause

SELECT * FROM emp 
WHERE deptno = 10 
MINUS SELECT * FROM accounting
/
-- Notice that this is the record that you added into
-- the EMP table.
--
pause

CLEAR SCR
-- Search and list for all of the records that 
-- are common in both tables.
--
pause

SELECT * FROM emp
WHERE deptno = 10
INTERSECT SELECT * FROM accounting
/
-- Notice the common records
pause

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


SELECT * FROM accounting
UNION ALL SELECT * FROM emp WHERE deptno = 10
AND empno NOT IN (SELECT empno FROM accounting)
/
pause

CLEAR SCR
-- Now, drop the accounting table.
--
pause

DROP TABLE accounting
/
pause

CLEAR SCR
-- Delete the record which was added to the EMP table.
--
pause

DELETE FROM emp
WHERE empno = 9999
/
COMMIT;
pause

CLEAR SCR
-- Now, you should practice this Hands-On over and over
-- until you become a master at it.

-- For more information about the subject, you are encouraged
-- to read from a wide selection of available books.

-- Good luck!
pause