Next run the lab0310sql script to create the emphist

This preview shows page 12 - 15 out of 25 pages.

10. Next, run thelab_03_10.sqlscript to create theEMP_HISTtable.a.Increase the size of the e-mail column to 45.b.Merge the data in theEMP_DATAtable created in the last lab into the data in theEMP_HISTtable. Assume that the data in the externalEMP_DATAtable is the mostup-to-date. If a row in theEMP_DATAtable matches the EMP_HIST table, update thee-mail column of theEMP_HISTtable to match theEMP_DATAtable row. If a rowin theEMP_DATAtable does not match, insert it into theEMP_HISTtable. Rows areconsidered 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 10g: SQL Fundamentals IIA-13
Practice 3: Solutions (continued)c.Retrieve the rows fromEMP_HISTafter the merge.
11. Create theEMP3table using thelab_03_11.sqlscript. In theEMP3table, change thedepartment for Kochhar to 60 and commit your change. Next, change the department forKochhar to 50 and commit your change. Track the changes to Kochhar using the RowVersions feature.
Oracle Database 10g: SQL Fundamentals IIA-14
Practice 4: Solutions1.Write a query to display the following for those employees whose manager ID is lessthan 120:-Manager ID-Job ID and total salary for every job ID for employees who report to the samemanager-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 theGROUPINGfunction todetermine whether theNULLvalues in the columns corresponding to theGROUPexpressions are caused by theROLLUPoperation.BY 

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 25 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
Spring
Professor
N/A
Tags
Oracle Database, manager id

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture