OCP 1Z0 051 132

来源：互联网发布：php调用微信支付接口编辑：程序博客网时间：2024/05/19 03:18

132. View the Exhibit and examine the structure of the SALES and PRODUCTS tables.
In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table,
You want to list each product ID and the number of times it has been sold.
Evaluate the following query:
SQL>SELECT p.prod_id, COUNT(s.prod_id)
FROM products p _____________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN options can be used in the blank in the above query to get the required output? (Choose
two.)

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

因为SALES.PROD_ID 依赖于 PRODUCTS.PROD_ID
所以不存在 SALES.PROD_ID有值而PRODUCTS.PROD_ID没什的数据，相当于可以加条件 WHERE p.prod_id IS NOT NULL
所以full outer join 就相当于 products p left join sales s
我们可以通过plan观察

SQL> EXPLAIN PLAN FOR SELECT *  2    FROM sh.products p  3    FULL OUTER JOIN sh.sales s  4      ON s.prod_id = p.prod_id  5  WHERE p.prod_id IS NOT NULL;ExplainedExecuted in 0.016 secondsSQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Plan hash value: 1313708566--------------------------------------------------------------------------------| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time--------------------------------------------------------------------------------|   0 | SELECT STATEMENT     |          |   918K|   177M|   534   (3)| 00:00:07|*  1 |  HASH JOIN OUTER     |          |   918K|   177M|   534   (3)| 00:00:07|   2 |   TABLE ACCESS FULL  | PRODUCTS |    72 | 12456 |     3   (0)| 00:00:01|   3 |   PARTITION RANGE ALL|          |   918K|    25M|   528   (2)| 00:00:07|   4 |    TABLE ACCESS FULL | SALES    |   918K|    25M|   528   (2)| 00:00:07--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("S"."PROD_ID"(+)="P"."PROD_ID")16 rows selected

注意这儿的条件:access("S"."PROD_ID"(+)="P"."PROD_ID")

Answer: BC

0 0