I would like to add key columns to my IMPORT_EMPLOYEE and IMPORT_PURCHASE tables so I can automate primary and foreign keys column values. The assignment is as following:

 IMPORT_EMPLOYEE  table is

 

 MPORT_PURCHASE table is

 

1. Add the columns PRIMARYID, EMPLOYEEID, ADDRESSID, SKILLID, SKILLTYPEID, DEPARTMENTID to IMPORT_EMPLOYEE. Make each column a NUMBER with 10 digits.

 

2. Add the columns PRIMARYID, CUSTOMERID, ADDRESSID, PURCHASEID, PRODUCTID to IMPORT_PURCHASE. Make each column a NUMBER with 10 digits.

 

3. Use the following UPDATE example on each import table to number each row

 

UPDATE import_table SET PrimaryID = ROWNUM;

 

4. Use the following UPDATE example on IMPORT_EMPLOYEE to assign values to update the EmployeeID, DepartmentID, SkillTypeID

 

field_ID	Identifying_attribute
EmployeeID	EmployeeName
DepartmentID	DepartmentName
SkillTypeID	SkillName

 

UPDATE IMPORT_EMPLOYEE

SET field_ID =

    (

    SELECT MIN(PrimaryID) FROM IMPORT_EMPLOYEE IT2

WHERE IT2.identifying_attribute =

IMPORT_EMPLOYEE.identifying_attribute

GROUP BY identifying_attribute

);

 

5. Use the following UPDATE example to assign values to update the CustomerID and ProductID

 

field_ID	identifying_attribute
CustomerID	CustomerName
ProductID	ProductName

 

UPDATE IMPORT_PURCHASE

SET field_ID =

    (

    SELECT MIN(PrimaryID) FROM IMPORT_PURCHASE IT2

WHERE IT2.identifying_attribute =

IMPORT_PURCHASE.identifying_attribute

GROUP BY identifying_attribute

);

 

6. Use the following UPDATE example to update SkillID, PurchaseID, AddressID in the IMPORT_EMPLOYEE and IMPORT_PURCHASE tables

 

UPDATE import_table

SET field_ID = PrimaryID;

 

7. Use the following UPDATE example to avoid conflicting AddressIDs during imports

 

UPDATE IMPORT_PURCHASE

SET AddressID = PrimaryID + 9999;

 

8. Run COMMIT; so all connections can see your updated data

 

9. Confirm all Key columns in both import tables have values.

Transcribed Image Text:

A 1 EmployeeName 2 Francoise Rautenstrauch 3 Loud, Kendra 4 Bauswell, Lourdes B Address 2335 Canton Hwy #6, Windsor, ON N8N 3N2 6 Arch St # 9757, Alcida, NB E8J 2C4 9547 Belmont Rd #21, Belleville, ON K8P 1B3 Salary Position D 36,359 process worker 40,954 process worker 40,926 education E SkillName DepartmentName Operations operations Operations Ops Operations Training A 1 EmployeeName 2 Francoise Rautenstrauch 3 Loud, Kendra 4 Bauswell, Lourdes B Address 2335 Canton Hwy #6, Windsor, ON N8N 3N2 6 Arch St # 9757, Alcida, NB E8J 2C4 9547 Belmont Rd #21, Belleville, ON K8P 1B3 Salary Position D 36,359 process worker 40,954 process worker 40,926 education E SkillName DepartmentName Operations operations Operations Ops Operations Training

Answer rating: 100% (QA)

Answer The following SQL query will calculate the total sales for each product cat... View the full answer