Next run the lab0310sql script to create the emphist

10. Next, run the lab_03_10.sql script to create the EMP_HIST table.
a. Increase the size of the e-mail column to 45.
b. Merge the data in the EMP_DATA table created in the last lab into the data in the
EMP_HIST table. Assume that the data in the external EMP_DATA table is the most
up-to-date. If a row in the EMP_DATA table matches the EMP_HIST table, update the
e-mail column of the EMP_HIST table to match the EMP_DATA table row. If a row
in the EMP_DATA table does not match, insert it into the EMP_HIST table. Rows are
considered matching when the employee’s first and last names are identical.
MERGE INTO EMP_HIST f USING EMP_DATA h
ON (f.first_name = h.first_name
AND f.last_name = h.last_name)
WHEN MATCHED THEN
UPDATE SET f.email = h.email
WHEN NOT MATCHED THEN
INSERT (f.first_name
, f.last_name
, f.email)
VALUES (h.first_name
, h.last_name
, h.email);
Oracle Database 10 g : SQL Fundamentals II A-13

Practice 3: Solutions (continued)
c. Retrieve the rows from EMP_HIST after the merge.
11. Create the EMP3 table using the lab_03_11.sql script. In the EMP3 table, change the
department for Kochhar to 60 and commit your change. Next, change the department for
Kochhar to 50 and commit your change. Track the changes to Kochhar using the Row
Versions feature.
Oracle Database 10 g : SQL Fundamentals II A-14

Practice 4: Solutions
1. Write a query to display the following for those employees whose manager ID is less
than 120:
- Manager ID
- Job ID and total salary for every job ID for employees who report to the same
manager
- Total salary of those managers
- Total salary of those managers, irrespective of the job IDs
SELECT manager_id,job_id,sum(salary)
FROM   employees
WHERE manager_id < 120
GROUP BY ROLLUP(manager_id,job_id);
2. Observe the output from question 1. Write a query using the GROUPING function to
determine whether the NULL values in the columns corresponding to the GROUP
expressions are caused by the ROLLUP operation.
BY