Review the WORK_HISTORY table:
Your task is to create a query that will list—for each ship—all of the EMPLOYEE_ID values for all the employees who have the shortest work history for their ship. In other words, if there are two ships, you want to list all of the employees assigned to the first ship who have the shortest work history, and all of the employees assigned to the second ship who have the shortest work history, etc. Which of the following queries will accomplish this task? ([xx1] Choose two.)
A. SELECT EMPLOYEE_ID FROM WORK_HISTORY W1
WHERE ABS(START_DATE - END_DATE) =
(SELECT MIN(ABS(START_DATE - END_DATE))
FROM WORK_HISTORY
WHERE SHIP_ID = W1.SHIP_ID);
B. SELECT EMPLOYEE_ID FROM WORK_HISTORY W1
WHERE ABS(START_DATE - END_DATE) =
(SELECT MIN(ABS(START_DATE - END_DATE))
FROM WORK_HISTORY);
C. SELECT EMPLOYEE_ID FROM WORK_HISTORY W1
WHERE ABS(START_DATE - END_DATE) <= ALL //小于等于所有人(最少);大于等于所有人(最大)
(SELECT ABS(START_DATE - END_DATE)
FROM WORK_HISTORY
WHERE SHIP_ID = W1.SHIP_ID);
D. SELECT EMPLOYEE_ID FROM WORK_HISTORY W1
WHERE ABS(START_DATE - END_DATE) <
(SELECT MIN(ABS(START_DATE - END_DATE))
FROM WORK_HISTORY
WHERE SHIP_ID = W1.SHIP_ID);
[xx1]CD AC
列出每条船上工作时间最少的员工ID
A and C. Answer A is a classic correlated subquery, connecting the subquery to the parent by way of the W1.SHIP_ID value.
Answer C also works with the “<= ALL” comparison condition.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22327710/viewspace-763476/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22327710/viewspace-763476/