View the Exhibit and examine the structure of EMPLOYEES and JOB_HISTORY tables.
The EMPLOYEES table maintains the most recent information regarding salary, department, and job for all the employees. The JOB_HISTORY table maintains the record for all the job changes for the employees. You want to delete all the records from the JOB_HISTORY table that are repeated in the EMPLOYEES table.
Which two SQL statements can you execute to accomplish the task? (Choose two.)
A.
DELETE
FROM job_history j
WHERE employee_id =
(SELECT employee_id
FROM employees e
WHERE j.employee_id = e.employee_id) AND job_id = (SELECT job_id FROM employees e
WHERE j.job_id = e.job_id);
B.
DELETE
FROM job_history j
WHERE (employee_id, job_id) = ALL
(SELECT employee_id, job_id
FROM employees e
WHERE j.employee_id = e.employee_id and j.job_id = e.job_id )
C.
DELETE
FROM job_history j
WHERE employee_id =
(SELECT employee_id
FROM employees e
WHERE j.employee_id = e.employee_id and j.job_id = e.job_id )
D.
DELETE
FROM job_history j
WHERE (employee_id, job_id) =
(SELECT employee_id, job_id
FROM employees e
WHERE j.employee_id = e.employee_id and j.job_id = e.job_id )
给的答案是cd？但是多行函数怎么能用=号？
Hi , this website use English only . thanks
Maybe because, 2 columns in WHERE clause will return 1 row, in the SubQuery.
select sal from emp e where empno = (select empno from empu u where u.empno = e.empno);
select sal from emp e where empno = ALL (select empno from empu u where u.empno = e.empno);
Also, the JOB_HISTORY table maintains the record for all the job changes for the employees
Hi, In my opinion option B is correct too, but that makes no sense as the question says “Choose two” 🙁
B option says:
DELETE … WHERE (employee_id, job_id) = ALL (SELECT employee_id, job_id … )
–> So delete all records where both columns (employee_id, job_id) in outer query equal to ALL records in inner query, using columns employee_id, job_id.
The inner query has the following condition:
WHERE j.employee_id = e.employee_id and j.job_id = e.job_id
–> So retrieve all employees that have the employee_id and job_id the same as in outer query.
So I see no error in the option B. I’ve also tried with some sample data and seems to work (both B, C and D options).
Any idea, something I misunderstood?
Thx
Not B.
= ALL evaluates to TRUE as the subquery returns 0 rows.
Therefore, B deletes all rows in job_history.
A subquery would return 0 rows. However, in this case with correlated subquery, it seems to return all the matching rows. Therefore, it removes all the rows.
Is C really correct? It removes records with JOB_ID other than found in EMPLOYEES table.