OCP 1Z0 051 131

131.  View  the  Exhibit  and  examine  the  structure  of  the  PRODUCT,  COMPONENT,  and  PDT_COMP 
tables. 
In PRODUCT table, PDTNO is the primary key. 
In COMPONENT table, COMPNO is the primary key. 
In  PDT_COMP  table,  (PDTNO,COMPNO)  is  the  primary  key,  PDTNO  is  the  foreign  key  referencing 
PDTNO in PRODUCT table and COMPNO is the foreign key referencing the COMPNO in COMPONENT 
table. 
You want to generate a report listing the product names and their corresponding component names, if the 
component names and product names exist. 
Evaluate the following query: 
SQL>SELECT pdtno,pdtname, compno,compname 
FROM product _____________ pdt_comp 
USING (pdtno) ____________ component USING(compno) 
WHERE compname IS NOT NULL; 
Which combination of joins used in the blanks in the above query gives the correct output?

A. JOIN; JOIN 
B. FULL OUTER JOIN; FULL OUTER JOIN 
C. RIGHT OUTER JOIN; LEFT OUTER JOIN 
D. LEFT OUTER JOIN; RIGHT OUTER JOIN 

测试数据

CREATE TABLE PRODUCT
(
pdtno NUMBER(3) NOT NULL,
pdtname VARCHAR2(25),
qty NUMBER(6,2)
);

CREATE TABLE COMPONENT
(
compno NUMBER(4) NOT NULL,
compname VARCHAR2(25),
qty NUMBER(6,2)
);

CREATE TABLE pdt_comp
(
pdtno NUMBER(2) NOT NULL,
compno NUMBER(3) NOT NULL
);

INSERT INTO product VALUES(1,'p1',1);
INSERT INTO product VALUES(2,'p2',2);
INSERT INTO product VALUES(3,'p3',3);
INSERT INTO product VALUES(4,'p4',4);

INSERT INTO COMPONENT VALUES(1,'c1',1);
INSERT INTO COMPONENT VALUES(2,'c2',2);
INSERT INTO COMPONENT VALUES(3,'c3',3);
INSERT INTO COMPONENT VALUES(4,'c4',4);

INSERT INTO pdt_comp VALUES(2,2);
INSERT INTO pdt_comp VALUES(3,4);

alter table product ADD CONSTRAINTS pk_product PRIMARY KEY (pdtno);
alter table COMPONENT ADD CONSTRAINTS pk_COMPONENT PRIMARY KEY (compno);
alter table pdt_comp ADD CONSTRAINTS pk_pdt_comp PRIMARY KEY (pdtno,compno);
alter table pdt_comp ADD CONSTRAINTS fk_pdtno FOREIGN KEY (pdtno) REFERENCES product (pdtno);
alter table pdt_comp ADD CONSTRAINTS fk_compno FOREIGN KEY (compno) REFERENCES COMPONENT (compno);

pdt_comp(pdtno) 依赖于 product (pdtno)
则product 右联pdt_comp与product 内联pdt_comp等价

SQL> SELECT pdtno, pdtname
  2    FROM product
  3   RIGHT OUTER JOIN pdt_comp
  4   USING (pdtno);
PDTNO PDTNAME
----- -------------------------
    2 p2
    3 p3
2 rows selected

SQL> SELECT pdtno, pdtname FROM product INNER JOIN pdt_comp USING (pdtno);
PDTNO PDTNAME
----- -------------------------
    2 p2
    3 p3
2 rows selected

pdt_comp(compno) 依赖于 COMPONENT (compno)
则pdt_comp左联COMPONENT 与pdt_comp内联COMPONENT 等价

SQL> SELECT compno, compname
  2    FROM pdt_comp
  3    LEFT OUTER JOIN component
  4   USING (compno);
COMPNO COMPNAME
------ -------------------------
     2 c2
     4 c4
2 rows selected

SQL> SELECT compno, compname FROM pdt_comp INNER JOIN component USING (compno);
COMPNO COMPNAME
------ -------------------------
     2 c2
     4 c4
2 rows selected

所以该题中A与C结果是一样的

A  显示pdt_comp中同时有对应的component names 和 product names的数据

SQL> SELECT pdtno, pdtname, compno, compname
  2    FROM product
  3   INNER JOIN pdt_comp
  4   USING (pdtno)
  5    INNER JOIN component
  6   USING (compno)
  7   WHERE compname IS NOT NULL;
PDTNO PDTNAME                   COMPNO COMPNAME
----- ------------------------- ------ -------------------------
    2 p2                             2 c2
    3 p3                             4 c4
2 rows selected

B 因有限制条件WHERE compname IS NOT NULL，所以FULL OUTER JOIN 相当于 RIGHT OUTER JOIN component
    而加上外键约束，此时结果相当于
    SELECT pdtno, pdtname, compno, compname
  FROM product
  INNER JOIN pdt_comp
 USING (pdtno)
  FULL OUTER JOIN component
 USING (compno)
 WHERE compname IS NOT NULL;

TEST >SELECT pdtno, pdtname, compno, compname
  2    FROM product
  3    FULL OUTER JOIN pdt_comp
  4   USING (pdtno)
  5    FULL OUTER JOIN component
  6   USING (compno)
  7   WHERE compname IS NOT NULL;


     PDTNO PDTNAME                       COMPNO COMPNAME
---------- ------------------------- ---------- -------------------------
                                              1 c1
         2 p2                                 2 c2
                                              3 c3
         3 p3                                 4 c4


4 rows selected.


Elapsed: 00:00:00.03


Execution Plan
----------------------------------------------------------
Plan hash value: 1142945215


---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     4 |   268 |     8  (13)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |              |     4 |   268 |     8  (13)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| COMPONENT    |     4 |   108 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_COMPONENT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |              |     4 |   160 |     6  (17)| 00:00:01 |
|   5 |    VIEW                      | VW_FOJ_0     |     4 |   160 |     5   (0)| 00:00:01 |
|*  6 |     HASH JOIN FULL OUTER     |              |     4 |   212 |     5   (0)| 00:00:01 |
|   7 |      INDEX FAST FULL SCAN    | PK_PDT_COMP  |     2 |    52 |     2   (0)| 00:00:01 |
|   8 |      TABLE ACCESS FULL       | PRODUCT      |     4 |   108 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("COMPONENT"."COMPNAME" IS NOT NULL)
   4 - access("from$_subquery$_003"."COMPNO"(+)="COMPONENT"."COMPNO")
       filter("from$_subquery$_003"."COMPNO"(+)="COMPONENT"."COMPNO")
   6 - access("PRODUCT"."PDTNO"="PDT_COMP"."PDTNO")


Note
-----
   - dynamic sampling used for this statement (level=2)

C 意思是显示pdt_comp中所有数据，如果有对应的component names 和 product names，则显示对应名称

SQL> SELECT pdtno, pdtname, compno, compname
  2    FROM product
  3   RIGHT OUTER JOIN pdt_comp
  4   USING (pdtno)
  5    LEFT OUTER JOIN component
  6   USING (compno)
  7   WHERE compname IS NOT NULL;
PDTNO PDTNAME                   COMPNO COMPNAME
----- ------------------------- ------ -------------------------
    2 p2                             2 c2
    3 p3                             4 c4
2 rows selected

D LEFT OUTER JOIN; RIGHT OUTER JOIN  显示PRODUCT与COMPONENT中所有数据及PDT_COMP中匹配的数据
    但加上条件 WHERE compname IS NOT NULL 及外键约束与非空约束
    该处与B相同

SQL> SELECT pdtno, pdtname, compno, compname
  2    FROM product
  3    LEFT OUTER JOIN pdt_comp
  4   USING (pdtno)
  5    RIGHT OUTER JOIN component
  6   USING (compno)
  7   WHERE compname IS NOT NULL;
PDTNO PDTNAME    COMPNO COMPNAME
----- ---------- ------ -------------------------
                      1 c1
    2 p2              2 c2
                      3 c3
    3 p3              4 c4
4 rows selected

Answer: C