Question

3. How many people have the last name King Use PL/SQL? Code: Output:

4. Change the first name to Stephen for the person with the last name King Use PL/SQL. Code: Output:

Copy the record showing name change: Code: Output:

5. Delete all employees in Department 20. Use %TYPE for the variable. Use SQL%ROWCOUNT to see how many records were deleted. Code: Copy output showing ROWCOUNT:

6. Use a merge to merge the original employees table into emp_test. Do UPDATE if matched and INSERT if NOT MATCHED. Include SQL%ROWCOUNT to indicate the number of rows affected. Code: Output:

I ONLY NEED THE CODE FOR THESE. THIS IS FOR ORACLE APEX

Answer 1

Ans: 3

SELECT count(*) FROM Department WHERE last_name = 'King';

Ans: 4:

UPDATE Department SET first_name = 'Stephen' WHERE last_name = 'King';

Ans: 5:

BEGIN
  DELETE FROM Department;
  DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT));
END;

Ans:6:

MERGE emp_test ET

USING Department D

ON ET.eid = D.eid

WHEN MATCHED THEN

UPDATE SET ET.first_name = D.first_name, ET.last_name = D.last_name

WHEN NOT MATCHED BY TARGET

THEN

INSERT (eid, first_name, last_name)

VALUES (D.eid, D.first_name, D.last_name);