Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) DEFAULT ‘SA_REP’
SAL NUMBER
COMM_PCT NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER
You need to update the records of employees 103 and 115. The UPDATE statement you specify
should update the rows with the values specified below:
JOB_ID: Default value specified for this column definition.
SAL: Maximum salary earned for the job ID SA_REP.
COMM_PCT: Default value specified for this commission percentage
column, if any.
If no default value is specified for the column,
the value should be NULL.
DEPARTMENT_ID: Supplied by the user during run time through
substitution variable.
Which UPDATE statement meets the requirements?
A.
UPDATE employeesC.UPDATE employees
SET job_id = DEFAULT,
Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = ‘SA_REP’),
comm_pct = DEFAULT,
department_id = &did
WHERE employee_id IN (103,115);
B.
UPDATE employees
SET job_id = DEFAULT,
Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = ‘SA_REP’),
comm_pct = DEFAULT OR NULL,
department_id = &did
WHERE employee_id IN (103,115);
C.
UPDATE employees
SET job_id = DEFAULT
AND Sal = MAX(sal)
AND comm_pct = DEFAULT OR NULL
AND department_id = &did
WHERE employee_id IN (103,115)
AND job_id = ‘SA_REP’;
D.
UPDATE employeesD.UPDATE employees
SET job_id = DEFAULT,
Sal = MAX(sal),
comm_pct = DEFAULT,
department_id = &did
WHERE employee_id IN (103,115)
AND job_id = ‘SA_REP’;
E.
UPDATE employees
SET job_id = DEFAULT
AND Sal = (SELECT MAX(sal)
FROM employees
WHERE job_id = ‘SA_REP’)
AND comm_pct = DEFAULT
AND department_id = &did
WHERE employee_id IN (103,115);
Explanation:
This UPDATE statement is correct to receive desired results. Correct syntax is UPDATE
table_name SET column_name1 = value, column_name2 = value2. You can also use the
DEFAULT keyword to set a column value to its specified default value in update statements as
well.