|
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 oracle user.
--
pause
CONNECT oracle/learning
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 |
