Your organization has a lot of duplicated records in their Account Employee table. This is a daily problem in any organization and you should be prepared to fix the problem. You have be assigned to clean all of the duplicated records.

-- Hands-On 04 (Troubleshooting Deleting duplicated records) 

SET ECHO ON
CLEAR SCR
-- Begin by logging in 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
-- First, let's create a table with lots of duplicated 
-- records.


-- Create a table named dup_emp and copy all of the EMP's
-- records into it.

pause

CREATE TABLE dup_emp
AS (SELECT * FROM emp)
/
pause

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

SELECT * FROM dup_emp
/
pause

CLEAR SCR
-- Insert all the accounting department rows into the dup_emp table.
--
pause

INSERT INTO dup_emp
SELECT * FROM emp WHERE deptno = 10
/
commit;
pause

CLEAR SCR
-- Query all the duplicated records from
-- the dup_emp table order by the employee name.
-- 
pause

SELECT * FROM dup_emp
WHERE deptno = 10
ORDER BY ename
/
-- Notice that all of the employees of department 10
-- have been duplicated.
pause

CLEAR SCR
-- Write a procedure to delete all of the 
-- duplicated records.

pause

DECLARE
-- Get the unique empno.
CURSOR c_empno IS
SELECT DISTINCT empno AS empno FROM dup_emp;
-- Get all duplicated records for an employee.
CURSOR c_duprec (p_empno NUMBER) IS
SELECT rowid FROM dup_emp WHERE empno = p_empno;
first_one BOOLEAN;
BEGIN
FOR v_empno IN c_empno LOOP
first_one := TRUE;
FOR v_duprec IN c_duprec (v_empno.empno) LOOP
IF NOT first_one THEN
DELETE FROM dup_emp
WHERE rowid = v_duprec.rowid;
COMMIT;
END IF;
first_one := FALSE;
END LOOP;
END LOOP;
END;
/ 
pause

CLEAR SCR
-- Query again the dup_emp table order by the department 
-- and employee number. 

-- Then check for duplication.
-- 
pause

SELECT * FROM dup_emp
WHERE deptno = 10
ORDER BY ename
/
-- Notice that there are no duplicated records.
pause


CLEAR SCR
-- Or you could write a delete statement to 
-- remove all duplicated records.

-- In this time, you did not have any duplicated 
-- record to be deleted.

pause

DELETE FROM dup_emp
WHERE ROWID IN (SELECT MAX(ROWID) FROM dup_emp
GROUP BY empno 
HAVING COUNT (empno) > 1) 
/ 
commit;

pause

CLEAR SCR
-- Drop the dup_emp table.
--
pause

DROP TABLE dup_emp
/
pause

CLEAR SCR
-- Now, practice this exercise 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
pause