OCP 1Z0 051 126

126. View the Exhibit and examine the data in the PROJ_TASK_DETAILS table. 
The PROJ_TASK_DETAILS  table  stores  information about  tasks  involved  in a project and  the  relation 
between them. 
The  BASED_ON  column  indicates  dependencies  between  tasks.  Some  tasks  do  not  depend  on  the 
completion of any other tasks. 
You need to generate a report showing all task IDs, the corresponding task ID they are dependent on, and 
the name of the employee in charge of the task it depends on. 
Which query would give the required result?  

A. SELECT p.task_id, p.based_on, d.task_in_charge 
FROM proj_task_details p JOIN proj_task_details d 
ON (p.based_on = d.task_id); 
B. SELECT p.task_id, p.based_on, d.task_in_charge 
FROM proj_task_details p LEFT OUTER JOIN proj_task_details d 
ON (p.based_on = d.task_id); 
C. SELECT p.task_id, p.based_on, d.task_in_charge 
FROM proj_task_details p FULL OUTER JOIN proj_task_details d 
ON (p.based_on = d.task_id); 
D. SELECT p.task_id, p.based_on, d.task_in_charge 
FROM proj_task_details p JOIN proj_task_details d 
ON (p.task_id = d.task_id); 

CREATE OR REPLACE VIEW proj_task_details(task_id, based_on,task_in_charge) ASSELECT 'P01',NULL,'KING' FROM dual UNION ALLSELECT 'P02','P01','KOCHAR' FROM dual UNION ALLSELECT 'P03',NULL,'GREEN' FROM dual UNION ALLSELECT 'P04','P03','SCOTT' FROM dual;

A 内联不能显示based_on为空的数据

SQL> SELECT p.task_id, p.based_on, d.task_in_charge  2    FROM proj_task_details p  3    JOIN proj_task_details d  4      ON (p.based_on = d.task_id);TASK_ID BASED_ON TASK_IN_CHARGE------- -------- --------------P02     P01      KINGP04     P03      GREEN2 rows selected

C FULL JOIN 匹配的数据显示在同行，并把所有不匹配的数据分行显示
为了便于观察，我们增加一列显示，可以看到下面 多了两列上级数据P02 P04

SQL> SELECT p.task_id, p.based_on, d.task_id, d.task_in_charge  2    FROM proj_task_details p  3    FULL OUTER JOIN proj_task_details d  4      ON (p.based_on = d.task_id)  5   ORDER BY 1, 3;TASK_ID BASED_ON TASK_ID TASK_IN_CHARGE------- -------- ------- --------------P01                      P02     P01      P01     KINGP03                      P04     P03      P03     GREEN                 P02     KOCHAR                 P04     SCOTT6 rows selected

D 关联条件不对，相当于直接取单表数据

SQL> SELECT p.task_id, p.based_on, d.task_in_charge  2    FROM proj_task_details p  3    JOIN proj_task_details d  4      ON (p.task_id = d.task_id);TASK_ID BASED_ON TASK_IN_CHARGE------- -------- --------------P01              KINGP02     P01      KOCHARP03              GREENP04     P03      SCOTT4 rows selected

SQL> SELECT p.task_id, p.based_on, d.task_in_charge  2    FROM proj_task_details p  3    LEFT OUTER JOIN proj_task_details d  4      ON (p.based_on = d.task_id)  5   ORDER BY 1;TASK_ID BASED_ON TASK_IN_CHARGE------- -------- --------------P01              P02     P01      KINGP03              P04     P03      GREEN4 rows selected

Answer: B