OCP-1Z0-051 第154题集合操作MINUS,INTERSECT

本文链接：https://blog.youkuaiyun.com/holly2008/article/details/26411851

本文通过一个具体的SQL查询实例，详细解析了如何使用INTERSECT与MINUS操作符来筛选已销售但未记录成本的产品ID。通过理解这些操作符的功能，读者可以更好地掌握复杂的SQL查询技巧。

一、原题

View the Exhibit and evaluate structures of the SALES, PRODUCTS, and COSTS tables.

Evaluate the following SQL statement:
SQL>SELECT prod_id FROM products
          INTERSECT
         SELECT prod_id FROM sales
          MINUS
         SELECT prod_id FROM costs;
Which statement is true regarding the above compound query?
A. It produces an error.
B. It shows products that were sold and have a cost recorded.
C. It shows products that were sold but have no cost recorded.
D. It shows products that have a cost recorded irrespective of sales.

答案：C

二、题目翻译

查看SALES, PRODUCTS, and COSTS表的结构:
评估下面的SQL语句:
关于上面的组合查询哪句话是正确的:
A.报错。
B.显示已销售并且有cost记录的产品。
C.显示已销售但是没有cost记录的产品。
D.显示有cost recorded不管sales的产品。

三、题目解析

SELECT prod_id FROM products
INTERSECT
SELECT prod_id FROM sales
首先，交集，求出的是已经销售的产品ID
然后再和costs表和差集，表示已销售但是没有cost记录的产品。