【诗檀学院 Oracle OCP认证专题】Oracle 11g OCP 考题讲解051

ZHAODONG

105. Examine the structure of the PROMOS table:（查看表结构）
name                     Null               Type
PROMO_ID               NOT NULL         NUMBER(3)
PROMO_NAME                              VARCHAR2(30)
PROMO_START_DATE    NOT NULL         DATE
PROMO_END_DATE       NOT NULL        DATE
You want to display the list of promo names with the message 'Same Day' for promos that started and ended on the same day.（你想显示promos names，如果开始和结束为同一天的promos使用'Same Day'显示）
Which query gives the correct output?（哪个给出正确输出？）
A. SELECT promo_name,NVL(NULLIF(promo_start_date,promo_end_date),'Same Day')
FROM promos;
B. SELECT promo_name,NVL(TRUNC(promo_end_date-promo_start_date),'SameDay')
FROM promos;
C. SELECT promo_name,NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)),NULL,'SameDay')
FROM promos;
D. SELECT promo_name,DECODE((NULLIF(promo_start_date,promo_end_date)),NULL,'Same day')
FROM promos;

答案：D
A不正确，因为'Same Day'隐式转换成日期时报错。
B不正确，不仅不满足条件，而且'Same Day'隐式转换成数值时报错。
C不正确，不满足条件

ZHAODONG

106. Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table:（查看表数据）
LIST_PRICE    MIN_PRICE
10000          8000
20000
30000          30000
Which two expressions give the same output? (Choose two.)哪两个表达式结果相同
A. NVL(NULLIF(list_price, min_price), 0)
B. NVL(COALESCE(list_price, min_price), 0)
C. NVL2(COALESCE(list_price, min_price), min_price, 0)
D. COALESCE(NVL2(list_price, list_price, min_price), 0)

答案：BD
A显示：10000   20000   0
B显示：10000   20000   30000
C显示：8000            30000
D显示：10000   20000   30000

ZHAODONG

107. View the Exhibit and examine the structure and data in the INVOICE table.
Which two SQL statements would execute successfully? (Choose two.)
查看表结构和数据，哪两条语句执行成功？

A. SELECT AVG(inv_date )
FROM invoice;
B. SELECT MAX(inv_date),MIN(cust_id)
FROM invoice;
C. SELECT MAX(AVG(SYSDATE - inv_date))
FROM invoice;
D. SELECT AVG( inv_date - SYSDATE), AVG(inv_amt)
FROM invoice;
答案：BD
A不正确，因为AVG的参数为数值型或能隐式转换成数据值的非数据值参数，在隐式转换inv_date时会报错。
C不正确，嵌套组函数需要使用group by子句

ZHAODONG

108. Which two statements are true regarding the COUNT function? (Choose two.)
（关于COUNT函数哪两个句子是正确的？选择两个）
A. The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.（COUNT函数只能用于CHAR,VARCHAR2,NUMBER数据类型）
B. COUNT(*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.（count(*)返回包括重复行和NULL的行）
C. COUNT(cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.
（COUNT(cust_id)返回包括重复行和NULL的行）
D. COUNT(DISTINCT inv_amt)returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column
（COUNT(DISTINCT inv_amt)返回不包括重复值和NULL的行）
E. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.（使用带有DISTINCT关键字的COUNT函数的SELECT语句不能含有WHERE子句）

答案：BD
A不正确，因为还可以为DATE型
C不正确，因为不包括NULL行

ZHAODONG

109. Examine the structure of the MARKS table:（查看表结构）
name                Null             Type
STUDENT_ID         NOT NULL       VARCHAR2(4)
STUDENT_NAME                      VARCHAR2(25)
SUBJECT1                            NUMBER(3)
SUBJECT2                            NUMBER(3)
SUBJECT3                            NUMBER(3)
Which two statements would execute successfully? (Choose two.)
哪两个语句可以执行成功？（选择两个）
A. SELECT student_name,subject1
FROM marks
WHERE subject1 > AVG(subject1);
B. SELECT student_name,SUM(subject1)
FROM marks
WHERE student_name LIKE 'R%';
C. SELECT SUM(subject1+subject2+subject3)
FROM marks
WHERE student_name IS NULL;
D. SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)
FROM marks
WHERE subject1 > subject2;

答案：CD
A组函数不能用于WHERE子句中
B需要使用group by子句

ZHAODONG

110. View the Exhibit and examine the structure of the CUSTOMERS table.（查看表结构）
Using the CUSTOMERS table, you need to generate a report that shows the average credit limit for customers in WASHINGTON and NEW YORK.
（使用CUSTOMERS表，你需要获取一个报表，显示居住在WASHINGTON和NEW YORK的客户的平均credit limit）
Which SQL statement would produce the required result?（哪条SQL语句给出所需结果？）

A. SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_credit_limit, cust_city;
B. SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city,cust_credit_limit;
C. SELECT cust_city, AVG(cust_credit_limit)
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK')
GROUP BY cust_city;
D. SELECT cust_city, AVG(NVL(cust_credit_limit,0))
FROM customers
WHERE cust_city IN ('WASHINGTON','NEW YORK');
答案：C
AB不正确，因为GROUP BY里的分组不满足条件
D不正确，因为需要使用GROUP BY子句

ZHAODONG

111. View the Exhibit and examine the structure of the CUSTOMERS table.（查看表结构）
Which statement would display the highest credit limit available in each income level in each city in the CUSTOMERS table?
（哪条语句显示每个城市中每个收入水平的最高信用额度）

A. SELECT cust_city, cust_income_level, MAX(cust_credit_limit )
FROM customers
GROUP BY cust_city, cust_income_level, cust_credit_limit;
B. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city, cust_income_level;
C. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)
FROM customers
GROUP BY cust_credit_limit, cust_income_level, cust_city ;
D. SELECT cust_city, cust_income_level, MAX(cust_credit_limit)
FROM customers
GROUP BY cust_city, cust_income_level, MAX(cust_credit_limit);

答案：B
AC不正确，因为GROUP BY子句不满足条件
D报错，因为GROUP BY子句中不能使用MAX

ZHAODONG

112. View the Exhibit and examine the structure of the PROMOTIONS table.（查看表结构）
Evaluate the following SQL statement:（评估下面的SQL语句）
SQL>SELECT promo_category, AVG(promo_cost) Avg_Cost, AVG(promo_cost)*.25 Avg_Overhead
FROM promotions
WHERE UPPER(promo_category) IN ('TV', 'INTERNET','POST')
GROUP BY Avg_Cost
ORDER BY Avg_Overhead;
The above query generates an error on execution.（上面的查询会报错）
Which clause in the above SQL statement causes the error?（哪一个子句报的错）

A. WHERE
B. SELECT
C. GROUP BY
D. ORDER BY
答案：C
GROUP BY后面不能使用列别名
ORDER BY可以使用列别名，但要完全匹配列别名，例如有双引号的别名要带双引号，并且ORDER BY子句必须要放在SELECT语句的最后。

ZHAODONG

113. Examine the structure of the ORDERS table:（查看表结构）
Name                Null                Type
ORDER_ID           NOT NULL          NUMBER(12)
ORDER_DATE        NOT NULL          TIMESTAMP(6)
CUSTOMER_ID       NOT NULL           NUMBER(6)
ORDER_STATUS                          NUMBER(2)
ORDER_TOTAL                           NUMBER(8,2)
You want to find the total value of all the orders for each year and issue the following command:（你想查找每年所有orders的total value，你执行下面的命令：）
SQL>SELECT TO_CHAR(order_date,'rr'), SUM(order_total)
FROM orders
GROUP BY TO_CHAR(order_date,'yyyy');
Which statement is true regarding the outcome?（关于结果正确的是？）
A. It executes successfully and gives the correct output.（执行成功给出正确结果）
B. It gives an error because the TO_CHAR function is not valid.（报错因为TO_CHAR函数无效）
C. It executes successfully but does not give the correct output.（执行成功但是不能给出正确结果）
D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.（报错，因为SELECT列表中的数据类型转换与GROUP BY子句中的数据类型转换不匹配）
答案：D

ZHAODONG

114. View the Exhibit and examine the structure of the SALES table.（查看表结构）
The following query is written to retrieve all those product ID s from the SALES table that have more than 55000 sold and have been ordered more than 10 times.
（下面的查询用于检索那些售出了超过55000台,并且已被定购超过10次的所有产品ID）
SQL> SELECT prod_id
FROM sales
WHERE quantity_sold > 55000 AND COUNT(*)>10
GROUP BY prod_id
HAVING COUNT(*)>10;
Which statement is true regarding this SQL statement?（关于查询正确的是？）

A. It executes successfully and generates the required result.（执行成功并给出正确结果）
B. It produces an error because COUNT(*) should be specified in the SELECT clause also.（报错，因为COUNT(*)也应该指定到SELECT子句中）
C. It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.（报错，因为COUNT(*)只能用在HAVING子句中，不能在WHERE子句中）
D. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).（执行成功，但是没有返回结果，因为COUNT(prod_id)应该用于替代COUNT(*)）

答案：C

ZHAODONG

115. View the Exhibit and examine the structure of the CUSTOMERS table.（查看表结构）
Evaluate the following SQL statement:（评估下面的语句）
SQL> SELECT cust_city, COUNT(cust_last_name)
FROM customers
WHERE cust_credit_limit > 1000
GROUP BY cust_city
HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;
Which statement is true regarding the outcome of the above query?（关于结果哪句话正确）

A. It executes successfully.（执行成功）
B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause.（报错，因为BETWEEN操作符不能用在HAVING子句中）
C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.（报错，因为WHERE and HAVING子句不能同时用于SELECT子句中）
D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.（报错，因为WHERE and HAVING子句不能使用同一列作为条件）
答案：A

ZHAODONG

116. Examine the data in the ORD_ITEMS table:（查看表数据）
ORD_NO     ITEM_NO     QTY
1            111           10
1            222           20
1            333           30
2            333           30
2           444           40
3           111           40
You want to find out if there is any item in the table for which the average maximum quantity is more than 50.（你想找到表中任意item的最大值的平均值是否大于50）
You issue the following query:（执行下面的查询）
SQL> SELECT AVG(MAX(qty))
FROM ord_items
GROUP BY item_no
HAVING AVG(MAX(qty))>50;
Which statement is true regarding the outcome of this query?（关于查询结果正确的是）
A. It executes successfully and gives the correct output.（执行成功并给出正确结果）
B. It gives an error because the HAVING clause is not valid.（报错，因为HAVING子句是无效的）
C. It executes successfully but does not give the correct output.（执行成功，但是不能给出正确结果）
D. It gives an error because the GROUP BY expression is not valid.（报错，因为GROUP BY表达式无效）

答案：B
HAVING子句后面不能有好几个组函数嵌套，只能使用一个组函数

wangtong

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

131:
查看下图并检查PRODUCT，COMPONENT和PDT_COMP表的结构。
在PRODUCT表中，PDTNO是主键，
在COMPONENT表中，COMPNO是主键，
在PDT_COMP表中，（PDTNO，COMPNO）是主键，外键PDTNO是参照PRODUCT表的PDTNO，外键COMPNO是参照COMPONENT表的COMPNO。
你想产生一个报告列出产品名称和它们相应组件的名字，如果组件名字和产品名字存在的话。
评估下面的查询：
SQL>SELECT pdtno,pdtname, compno,compname FROM product _____________ pdt_comp
USING (pdtno) ____________ component USING(compno) WHERE compname IS NOT NULL;
哪个使用在空格中的join组合给出正确的输出结果？
A. JOIN; JOIN
B. FULL OUTER JOIN; FULL OUTER JOIN
C. RIGHT OUTER JOIN; LEFT OUTER JOIN
D. LEFT OUTER JOIN; RIGHT OUTER JOIN

答案：C
题目中说如果组件和产品名称都存在就列出，即列出pdt_comp表中的所有组件和产品的名称。第一次关联以pdt_comp表为基准，第二次关联以pdt_comp和product关联的结果为基准，最后去掉compname为空的。
Left outer join表示返回from子句左边的表的所有列
Right outer join表示返回from子句右边的表的所有列

wangtong

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

132：
查看下表并检查SALES和PRODUCTS表的结构。
在SALES表，外键PROD_ID参照PRODUCTS表的PROD_ID，你想列出每个产品ID和物品售卖的数量。
评估下面的查询：
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;
哪两个JOIN操作可以用于上面的查询获得需要的输出结果？（选择两个）
A. JOIN
B. FULL OUTER JOIN
C. LEFT OUTER JOIN
D. RIGHT OUTER JOIN

答案：BC
列出每一个产品ID，应该使用LEFT JOIN，C选项正确，
由于s表的prod_id是products表的外键，不会出现s表中有，而p表中匹配不上的现象，所以在这里，FULL JOIN的效果和LEFT JOIN一样的。Full outer join返回两个连接表的所有列

wangtong

133：
Which two statements are true regarding subqueries? (Choose two.)
A. A subquery can retrieve zero or more rows.
B. Only two subqueries can be placed at one level.
C. A subquery can be used only in SQL query statements.
D. A subquery can appear on either side of a comparison operator.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement

133：
哪两句是正确的关于子查询？（选择两个）
A.        一个子查询可以返回零或者多行
B.        只能有两个子查询发生在同一级上
C.        一个子查询只能用在SQL查询语句中
D.        一个子查询可以出现在比较运算符的任何一边
E.        SELECT语句的WHERE子句没有子查询等级的数量限制

答案：AD
A正确，子查询能查到0行或者多行
B错误，在一个层级上可以使用多个子查询
C错误，子查询只能用于SQL查询语句是错误的，也可以用于DML语句
D正确
E错误，子查询最多嵌套255层

参考官方文档：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries007.htm#SQLRF52357

wangtong

134：
Where can subqueries be used? (Choose all that apply.)
A. field names in the SELECT statement
B. the FROM clause in the SELECT statement
C. the HAVING clause in the SELECT statement
D. the GROUP BY clause in the SELECT statement
E. the WHERE clause in only the SELECT statement
F. the WHERE clause in SELECT as well as all DML statements
134：
子查询可以被用于？（选择所有适用的）
A.        SELECT语句的字段名
B.        SELECT语句的FROM子句
C.        SELECT语句的HAVING子句
D.        SELECT语句的GROUP BY子句
E.        只能在SELECT语句的WHERE子句
F.        SELECT语句的WHERE子句和所有的DML语句

答案：ABCF
D错误，GROUP BY语句根据一个或多个列对结果进行分组，后面不能使用子查询，只能跟列
E错误，FROM后面可以跟子查询

wangtong

135：
Which three statements are true regarding subqueries? (Choose three.)
A. Subqueries can contain GROUP BY and ORDER BY clauses.
B. Main query and subquery can get data from different tables.
C. Main query and subquery must get data from the same tables.
D. Subqueries can contain ORDER BY but not the GROUP BY clause.
E. Only one column or expression can be compared between the main query and subquery.
F. Multiple columns or expressions can be compared between the main query and subquery.

135：
哪三个语句是正确的关于子查询？（选择三个）
A.        子查询可以包含GROUP BY和ORDER BY语句
B.        主查询和子查询可以从不同的表获得数据
C.        主查询和子查询必须从相同的表获得数据
D.        子查询可以包含ORDER BY语句但不能包含GROUP BY语句
E.        只能有一列或一个表达式可以被主查询和子查询比较
F.        多列或多个表达式可以被主查询和子查询比较

答案：ABF

参考官方文档：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries007.htm#SQLRF52357

wangtong

136：
View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks would require subqueries? (Choose two.)

A. Display the minimum list price for each product status.
B. Display all suppliers whose list price is less than 1000.
C. Display the number of products whose list price is more than the average list price.
D. Display the total number of products supplied by supplier 102 and have product status as 'obsolete'.
E. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.

136:
查看下表并检查PRODUCTS表的结构。
哪两个任务需要子查询?(选择两个)
A.        显示每个产品状态的最低标价
B.        显示所有标价低于1000的供应商
C.        显示标价高于平均标价的产品数量
D.        显示供应商102提供的且产品状态为‘obsolete’的产品数量
E.        显示所有产品最低价超过平均价且状态为‘orderable’的产品

答案：CE
A错误，select prod_status,min(prod_min_price) from products group by prod_status
B错误，select supplier_id from products where prod_list_price < 1000
D错误，select count（*） from products where supplier_id=102 and prod_status=’obsolete’
C正确，select count（*） from products where prod_list_price > (select avg(prod_list_price) from products)
E正确，select prod_name from products where prod_status=’orderable’ and prod_min_price > (selectavg(prod_list_price) from products)

wangtong

137：
View the Exhibits and examine PRODUCTS and SALES tables.
You issue the following query to display product name and the number of times the product has been sold:
SQL>SELECT p.prod_name, i.item_cnt
FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i
RIGHT OUTER JOIN products p
ON i.prod_id = p.prod_id;
What happens when the above statement is executed?

A. The statement executes successfully and produces the required output.
B. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.
C. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
D. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.

137：
查看下表并检查PRODUCTS和SALES表。
你执行下面的查询显示产品名称和产品卖出的次数：
SQL>SELECT p.prod_name, i.item_cnt
FROM (SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id) i
RIGHT OUTER JOIN products p
ON i.prod_id = p.prod_id;
当上面的语句执行会发生什么？
A．        语句执行成功并产生需要的输出结果
B．        语句产生一个错误因为ITEM_CNT不能显示在外层查询
C．        语句产生一个错误因为FROM语句的一个子查询和outer-join不能一起使用
D．        语句产生一个错误因为GROUP BY语句不能用于FROM语句的子查询

答案：A
子查询SELECT prod_id, COUNT(*) item_cnt FROM sales GROUP BY prod_id的结果是产品卖出的次数，然后与表products右外连接得到产品名称和卖出的次数。

wangtong

138：
Which statement is true regarding subqueries?
A. The LIKE operator cannot be used with single- row subqueries.
B. The NOT IN operator is equivalent to IS NULL with single- row subqueries.
C. =ANY and =ALL operators have the same functionality in multiple- row subqueries.
D. The NOT operator can be used with IN, ANY, and ALL operators in multiple- row subqueries.

138：
那个语句是正确的关于子查询？
A.        LIKE操作符不能用于单行子查询
B.        NOT IN操作符等同于IS NULL在单行子查询中
C.        =ANY和=ALL 有相同的功能在多行子查询
D.        NOT操作符可以和IN，ANY，ALL操作符一起用在多行子查询中。

答案：D
A错误，like可以用于单行子查询
B错误，NOT IN等同于<>ALL
C错误，=ANY表示等译任意一个，=ALL是等于所有
D正确，实就是把NOT放到整个表达式之前：
WHERE NOT col IN (SELECT ...)  （也可以是WHERE col not IN (SELECT ...)）
WHERE NOT col = ANY (SELECT ...)  （大于、小于都可以）
WHERE NOT col = ALL (SELECT ...)  （大于、小于都可以

wangtong

139：
Which three statements are true about multiple-row subqueries? (Choose three.)
A. They can contain a subquery within a subquery.
B. They can return multiple columns as well as rows.
C. They cannot contain a subquery within a subquery.
D. They can return only one column but multiple rows.
E. They can contain group functions and GROUP BY and HAVING clauses.
F. They can contain group functions and the GROUP BY clause, but not the HAVING clause.

139：
哪三句是正确的关于多行子查询？（选择三个）
A.        它们可以在子查询中包含子查询
B.        它们可以返回多列和多行
C.        它们不能在子查询中包含子查询
D.        它们只能返回一列和多行
E.        它们可以包含分组函数和GROUP BY和HAVING语句
F.        它们可以包含分组函数和GROUP BY语句，但不包括HAVING语句

答案：ABE
A正确，子查询可以嵌套子查询，最多255层
B正确，能返回多行和多列
E正确，可以包含GROUP BY和HAVING
C,D,F错误

wangtong

140：
Examine the structure of the PRODUCTS table:
Name Null Type
PROD_ID NOT NULL NUMBER(4)
PROD_NAME VARCHAR2(20)
PROD_STATUS VARCHAR2(6)
QTY_IN_HAND NUMBER(8,2)
UNIT_PRICE NUMBER(10,2)
You want to display the names of the products that have the highest total value for UNIT_PRICE *
QTY_IN_HAND.
Which SQL statement gives the required output?
A. SELECT prod_name
  FROM products
WHERE (unit_price * qty_in_hand) =
       (SELECT MAX(unit_price * qty_in_hand) FROM products);

B. SELECT prod_name
  FROM products
WHERE (unit_price * qty_in_hand) =
       (SELECT MAX(unit_price * qty_in_hand)
          FROM products
         GROUP BY prod_name);

C. SELECT prod_name
  FROM products
GROUP BY prod_name
HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand)
                                          FROM products
                                         GROUP BY prod_name);

D. SELECT prod_name
  FROM products
WHERE (unit_price * qty_in_hand) =
       (SELECT MAX(SUM(unit_price * qty_in_hand)) FROM products)
GROUP BY prod_name;

140：
检查PRODUCTS表的结构：
Name Null Type
PROD_ID NOT NULL NUMBER(4)
PROD_NAME VARCHAR2(20)
PROD_STATUS VARCHAR2(6)
QTY_IN_HAND NUMBER(8,2)
UNIT_PRICE NUMBER(10,2)
你想显示拥有最高总价UNIT_PRICE*QTY_IN_HAND的产品名称。
哪个SQL语句给出需要的输出结果？
A. SELECT prod_name
  FROM products
WHERE (unit_price * qty_in_hand) =
       (SELECT MAX(unit_price * qty_in_hand) FROM products);

B. SELECT prod_name
  FROM products
WHERE (unit_price * qty_in_hand) =
       (SELECT MAX(unit_price * qty_in_hand)
          FROM products
         GROUP BY prod_name);

C. SELECT prod_name
  FROM products
GROUP BY prod_name
HAVING MAX(unit_price * qty_in_hand) = (SELECT MAX(unit_price * qty_in_hand)
                                          FROM products
                                         GROUP BY prod_name);

D. SELECT prod_name
  FROM products
WHERE (unit_price * qty_in_hand) =
       (SELECT MAX(SUM(unit_price * qty_in_hand)) FROM products)
GROUP BY prod_name;

答案：A
A正确，SELECT MAX(unit_price * qty_in_hand) FROM products查询出最高价格，然后查出价格等于最大价格的产品名称。
B,C有group by可能返回不止一行，会报错。
D选项先SUM和题意就不一样了。

wangtong

141：
View the Exhibit and examine the structure of CUSTOMERS and GRADES tables.

You need to display names and grades of customers who have the highest credit limit.
Which two SQL statements would accomplish the task? (Choose two.)

A. SELECT custname, grade
  FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and
       endval;

B. SELECT custname, grade
  FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and
       endval
   AND cust_credit_limit BETWEEN startval AND endval;

C. SELECT custname, grade
  FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit) FROM customers)
   AND cust_credit_limit BETWEEN startval AND endval;

D. SELECT custname, grade
  FROM customers, grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit) FROM customers)
   AND MAX(cust_credit_limit) BETWEEN startval AND endval;

141：
查看下图并检查CUSTOMERS和GRADES表。你想显示信用最高的客户的名字和等级。

哪两个SQL语句能完成这个任务？（选择两个）
A. SELECT custname, grade
  FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and
       endval;

B. SELECT custname, grade
  FROM customers, grades
WHERE (SELECT MAX(cust_credit_limit) FROM customers) BETWEEN startval and
       endval
   AND cust_credit_limit BETWEEN startval AND endval;

C. SELECT custname, grade
  FROM customers, grades
WHERE cust_credit_limit = (SELECT MAX(cust_credit_limit) FROM customers)
   AND cust_credit_limit BETWEEN startval AND endval;

D. SELECT custname, grade
  FROM customers, grades
WHERE cust_credit_limit IN (SELECT MAX(cust_credit_limit) FROM customers)
   AND MAX(cust_credit_limit) BETWEEN startval AND endval;

答案：BC
根据题意，找出最高CUST_CREDIT_LIMIT的CUSTNAME和GRADE，
B选项，选出最大CUST_CREDIT_LIMIT所在的区间，然后得出GRADE，之后根据CUST_CREDIT_LIMIT选出CUSTNAME。
C选项，选出最大的CUST_CREDIT_LIMIT，得出CUSTNAME，在根据CUST_CREDIT_LIMIT所在的区间得出GRADE。

wangtong

142：
View the Exhibit and examine the structure of the PRODUCTS table.

Evaluate the following query:
SQL> SELECT prod_name
  FROM products
WHERE prod_id IN
       (SELECT prod_id
          FROM products
         WHERE prod_list_price =
               (SELECT MAX(prod_list_price)
                  FROM products
                 WHERE prod_list_price <
                       (SELECT MAX(prod_list_price) FROM products)));

What would be the outcome of executing the above SQL statement?

A. It produces an error.
B. It shows the names of all products in the table.
C. It shows the names of products whose list price is the second highest in the table.
D. It shows the names of all products whose list price is less than the maximum list price.

142：
查看下表并检查PRODUCTS表的结构。
评估下面的查询语句：
SQL> SELECT prod_name
  FROM products
WHERE prod_id IN
       (SELECT prod_id
          FROM products
         WHERE prod_list_price =
               (SELECT MAX(prod_list_price)
                  FROM products
                 WHERE prod_list_price <
                       (SELECT MAX(prod_list_price) FROM products)));

执行上面的语句会产生什么结果？
A.        它产生一个错误
B.        显示表中所有产品的名称
C.        显示标价第二高的产品名称
D.        显示标价低于最高标价的产品名称

答案：C
从里往外分析，
最内层子查询SELECT MAX(prod_list_price)FROM products结果是输出产品的最高标价，然后SELECT MAX(prod_list_price) FROM products WHERE prod_list_price < (SELECT MAX(prod_list_price) FROM products)的结果是输出低于最高价格的最高价格，也就是第二高的价格，最后选出价格第二高的产品的名称。所以答案选择C

wangtong

143：
View the Exhibit and examine the structure of the PROMOTIONS table.
You have to generate a report that displays the promo name and start date for all promos that started after
the last promo in the 'INTERNET' category.
Which query would give you the required output?

A. SELECT promo_name, promo_begin_date
  FROM promotions
WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions)
   AND promo_category = 'INTERNET';

B. SELECT promo_name, promo_begin_date
  FROM promotions
WHERE promo_begin_date IN
       (SELECT promo_begin_date
          FROM promotions
         WHERE promo_category = 'INTERNET');

C. SELECT promo_name, promo_begin_date
  FROM promotions
WHERE promo_begin_date > ALL (SELECT promo_begin_date
          FROM promotions
         WHERE promo_category = 'INTERNET');

D. SELECT promo_name, promo_begin_date
  FROM promotions
WHERE promo_begin_date > ANY (SELECT promo_begin_date
          FROM promotions
         WHERE promo_category = 'INTERNET');

143：
查看下表并检查PROMOTIONS表的结构。你不得不产生一个报告显示促销名称和所有上次‘INTERNET’类别促销之后开始的促销的开始时间。
哪个查询语句会获得需要的结果？
A. SELECT promo_name, promo_begin_date
  FROM promotions
WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions)
   AND promo_category = 'INTERNET';

B. SELECT promo_name, promo_begin_date
  FROM promotions
WHERE promo_begin_date IN
       (SELECT promo_begin_date
          FROM promotions
         WHERE promo_category = 'INTERNET');

C. SELECT promo_name, promo_begin_date
  FROM promotions
WHERE promo_begin_date > ALL (SELECT promo_begin_date
          FROM promotions
         WHERE promo_category = 'INTERNET');

D. SELECT promo_name, promo_begin_date
  FROM promotions
WHERE promo_begin_date > ANY (SELECT promo_begin_date
          FROM promotions
         WHERE promo_category = 'INTERNET');

答案：C
C选项，先选出INTERENT目录下的开始时间，然后选出开始时间大于所有INTERENT目录下促销的开始时间，故选择C

wangtong

144：
View the Exhibit and examine the structure of the PRODUCTS table.
You want to display the category with the maximum number of items.
You issue the following query:
SQL> SELECT COUNT(*), prod_category_id
  FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);
What is the outcome?

A. It executes successfully and gives the correct output.
B. It executes successfully but does not give the correct output.
C. It generates an error because the subquery does not have a GROUP BY clause.
D. It generates an error because = is not valid and should be replaced by the IN operator.

144：
查看下表并检查PRODUCTS表的结构。
你想显示拥有最大数量项目的类别。你执行下面的查询：
SQL> SELECT COUNT(*), prod_category_id
  FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products);
输出结果是什么？
A.        执行成功且给出正确的输出结果
B.        执行成功但是不能给出正确的输出结果
C.        产生一个错误因为子查询没有GROUP BY
D.        产生一个错误因为=无效应该使用IN代替

答案：C
C正确，因为子查询使用嵌套组函数，没有使用group by。

wangtong

145：
View the Exhibit and examine the structure of the CUSTOMERS table.
You issue the following SQL statement on the CUSTOMERS table to display the customers who are in the
same country as customers with the last name 'KING' and whose credit limit is less than the maximum
credit limit in countries that have customers with the last name 'KING':
SQL> SELECT cust_id, cust_last_name
  FROM customers
WHERE country_id IN
       (SELECT country_id FROM customers WHERE cust_last_name = 'King')
   AND cust_credit_limit <
       (SELECT MAX(cust_credit_limit)
          FROM customers
         WHERE country_id IN (SELECT country_id
                                FROM customers
                               WHERE cust_last_name = 'King'));

Which statement is true regarding the outcome of the above query?

A. It executes and shows the required result.
B. It produces an error and the < operator should be replaced by < ALL to get the required output.
C. It produces an error and the < operator should be replaced by < ANY to get the required output.
D. It produces an error and the IN operator should be replaced by = in the WHERE clause of the main query to get the required output

145：
查看下表并检查CUSTOMERS表的结构
你执行下面的SQL语句在CUSTOMERS表显示在同一个国家且last name为‘KING’的客户信用额度小于国家中信用额度最高的last name为’KING’的客户：
SQL> SELECT cust_id, cust_last_name
  FROM customers
WHERE country_id IN
       (SELECT country_id FROM customers WHERE cust_last_name = 'King')
   AND cust_credit_limit <
       (SELECT MAX(cust_credit_limit)
          FROM customers
         WHERE country_id IN (SELECT country_id
                                FROM customers
                               WHERE cust_last_name = 'King'));

哪个语句是正确的关于上面查询的输出结果？
A.        执行并显示需要的结果
B.        产生一个错误并且<操作符应该被<ALL代替来获得需要的结果
C.        产生一个错误并且<操作符应该被<ANY代替来获得需要的结果
D.        产生一个错误并且IN操作符应该被=代替在主查询的WHERE子句中以获得需要的结果

答案：A
首先子查询查出KING所在的国家ID，然后再求出和KING相同国家ID的最大的CUST_CREDIT_LIMIT，最后得出要的答案。

wangtong

146：
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name
  FROM customers
WHERE cust_credit_limit IN
       (select cust_credit_limit
          FROM customers
         WHERE cust_city = 'Singapore');
Which statement is true regarding the above query if one of the values generated by the subquery is NULL?
A. It produces an error.
B. It executes but returns no rows.
C. It generates output for NULL as well as the other values produced by the subquery.
D. It ignores the NULL value and generates output for the other values produced by the subquery.

146：
评估下面的SQL语句：
SQL> SELECT cust_id, cust_last_name
  FROM customers
WHERE cust_credit_limit IN
       (select cust_credit_limit
          FROM customers
         WHERE cust_city = 'Singapore');
哪些语句是正确关于上面的查询如果子查询产生的结果之一为NULL？
A.        产生一个错误
B.        执行但是不返回一行结果
C.        产生输出结果无论子查询是NULL还是其他结果
D.        忽略NULL值并且产生输出结果如果子查询为其他值结果

答案：C
IN可以改写成or，对于NULL值，相当于一个false or其余的，所以不影响其它的结果，NULL也不会被忽略，选择C

wangtong

147：
View the Exhibit and examine the structure of the PROMOTIONS table.
Evaluate the following SQL statement:
SQL> SELECT promo_name,
       CASE
         WHEN promo_cost >= (SELECT AVG(promo_cost)
                               FROM promotions
                              WHERE promo_category = 'TV') then
          'HIGH'
         else
          'LOW'
       END COST_REMARK
  FROM promotions;Which statement is true regarding the outcome of the above query?

A. It shows COST_REMARK for all the promos in the table.
B. It produces an error because the subquery gives an error.
C. It shows COST_REMARK for all the promos in the promo category 'TV'.
D. It produces an error because subqueries cannot be used with the CASE expression.

147：
查看下表并检查PROMOTIONS表的结构。
评估下面的SQL语句：
SQL> SELECT promo_name,
       CASE
         WHEN promo_cost >= (SELECT AVG(promo_cost)
                               FROM promotions
                              WHERE promo_category = 'TV') then
          'HIGH'
         else
          'LOW'
       END COST_REMARK
  FROM promotions;
哪个语句是正确的关于上面查询的输出结果？
A.        显示表中所有促销的COST_REMARK
B.        产生一个错误因为子查询给出一个错误
C.        显示促销目录’TV‘中所有促销的COST_REMARK
D.        产生一个错误因为子查询不能用于CASE表达式

答案：A
子查询SELECT AVG(promo_cost)  FROM promotions  WHERE promo_category = 'TV'显示了TV的平均促销花费，高于这个的标记为HIGH，低于这个的标记为LOW，故选择A

wangtong

148：
View the Exhibit and examine the structure of the PRODUCTS tables.
You want to generate a report that displays the average list price of product categories where the average
list price is less than half the maximum in each category.
Which query would give the correct output?

A. SELECT prod_category, avg(prod_list_price)
  FROM products
GROUP BY prod_category
HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price) / 2
                                     FROM products
                                    GROUP BY prod_category);

B. SELECT prod_category, avg(prod_list_price)
  FROM products
GROUP BY prod_category
HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price) / 2
                                     FROM products
                                    GROUP BY prod_category);

C. SELECT prod_category, avg(prod_list_price)
  FROM products
HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price) / 2
                                     FROM products
                                    GROUP BY prod_category);

D. SELECT prod_category, avg(prod_list_price)
  FROM products
GROUP BY prod_category
HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price) / 2
                                     FROM products);

148：
查看下表并检查PRODUCTS表的结构。
你想产生一个报告显示产品种类的平均标价且平均标价低于每个种类最高价的一半。
哪个查询将给出正确的输出结果？
A. SELECT prod_category, avg(prod_list_price)
  FROM products
GROUP BY prod_category
HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price) / 2
                                     FROM products
                                    GROUP BY prod_category);

B. SELECT prod_category, avg(prod_list_price)
  FROM products
GROUP BY prod_category
HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price) / 2
                                     FROM products
                                    GROUP BY prod_category);

C. SELECT prod_category, avg(prod_list_price)
  FROM products
HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price) / 2
                                     FROM products
                                    GROUP BY prod_category);

D. SELECT prod_category, avg(prod_list_price)
  FROM products
GROUP BY prod_category
HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price) / 2
                                     FROM products);

答案：A
题意是显示平均价格小于每一个category最大值的一半的product categories的平均价格。
A正确，按prod_category分组选出，prod_category和平均价，然后使用HAVING子句筛选出平均价小于最高价一半的行。
B错误，选出的是大于任意一个最高价的产品一半价格的prod_category和平均价。
C错误，子查询后面没有使用group by，语法错误
D错误，HAVING子句里的子查询，选出的是所有产品最高价格的一半，不是每个pord_category的最高价的一半。

wangtong

149：
View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables.

Evaluate the following SQL statement:
SQL> SELECT prod_id
  FROM costs
WHERE promo_id IN
       (SELECT promo_id
          FROM promotions
         WHERE promo_cost < ALL
         (SELECT MAX(promo_cost)
                  FROM promotions
                 GROUP BY (promo_end_date - promo_begin_date)));
What would be the outcome of the above SQL statement?
A. It displays prod IDs in the promo with the lowest cost.
B. It displays prod IDs in the promos with the lowest cost in the same time interval.
C. It displays prod IDs in the promos with the highest cost in the same time interval.
D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.

149：
查看下表并检查COSTS和PROMOTIONS表的结构。
评估下面SQL语句：
SQL> SELECT prod_id
  FROM costs
WHERE promo_id IN
       (SELECT promo_id
          FROM promotions
         WHERE promo_cost < ALL
         (SELECT MAX(promo_cost)
                  FROM promotions
                 GROUP BY (promo_end_date - promo_begin_date)));
上面的SQL语句将产生什么输出结果？
A.        显示促销中花费最低的prod IDs
B.        显示相同时间间隔的促销花费最低的prod IDs
C.        显示相同时间间隔的促销花费最高的prod IDs
D.        显示相同时间间隔内促销花费不是最高的prod IDs

答案：D
子查询SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date   -   promo_begin_date)的结果是相同时间间隔促销花费的最大值。然后选择花费少于这个的prod_id，故选择D

wangtong

150：
View the Exhibit and examine the data in the PROMOTIONS table.
You need to display all promo categories that do not have 'discount' in their subcategory.
Which two SQL statements give the required result? (Choose two.)

A. SELECT promo_category
  FROM promotions
MINUS
SELECT promo_category
  FROM promotions
WHERE promo_subcategory = 'discount';

B. SELECT promo_category
  FROM promotions
INTERSECT
SELECT promo_category
  FROM promotions
WHERE promo_subcategory = 'discount';

C. SELECT promo_category
  FROM promotions
MINUS
SELECT promo_category
  FROM promotions
WHERE promo_subcategory <> 'discount';

D. SELECT promo_category
  FROM promotions
INTERSECT
SELECT promo_category
  FROM promotions
WHERE promo_subcategory <> 'discount';

150：
查看下表并检查PROMOTIONS表的数据。
你需要显示所有子目录不包含’discount’的促销种类。
哪两个SQL给出需要的结果？（选择两个）
A. SELECT promo_category
  FROM promotions
MINUS
SELECT promo_category
  FROM promotions
WHERE promo_subcategory = 'discount';

B. SELECT promo_category
  FROM promotions
INTERSECT
SELECT promo_category
  FROM promotions
WHERE promo_subcategory = 'discount';

C. SELECT promo_category
  FROM promotions
MINUS
SELECT promo_category
  FROM promotions
WHERE promo_subcategory <> 'discount';

D. SELECT promo_category
  FROM promotions
INTERSECT
SELECT promo_category
  FROM promotions
WHERE promo_subcategory <> 'discount';

答案：AD
根据题意是选出subcategory不是discount的promo_category
MINUS函数，意思是选出第一个结果集减去第二个结果集的部分
INTERSECT函数，意思是选出第一个结果集和第二个结果集重复的部分
故选择A和D

wangtong

151:
View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.
The CUSTOMERS table contains the current location of all currently active customers. The
CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.
You need to find those customers who have never changed their address.
Which SET operator would you use to get the required output?

A. MINUS
B. UNION
C. INTERSECT
D. UNION ALL

151:
查看下图并检查CUSTOMERS和CUST_HISTORY表的结构。
CUSTOMERS表包含当前活跃客户的当前位置。CUST_HISTORY表存储所有当前客户和之前的不再活跃客户相关的位置的历史详细的变化。
你需要找出那些从来没改变过地址的客户，哪种操作你需要使用获得想要的结果？
A. MINUS
B. UNION
C. INTERSECT
D. UNION ALL

答案：A
一个表中是活动客户的地址信息，一个表中是客户以前地址的历史信息，两张表的差集，就是从来没有改变过地址的客户。

wangtong

152：
Which statement is true regarding the UNION operator?
A. By default, the output is not sorted.
B. NULL values are not ignored during duplicate checking.
C. Names of all columns must be identical across all SELECT statements.
D. The number of columns selected in all SELECT statements need not be the same.

152：
哪些语句是正确的关于UNION操作？
A.        默认情况，输出结果没有排序
B.        NULL值不会被忽略在重复检查期间
C.        所有列的名称必须统一在SELECT语句中
D.        SELECT语句中选择的列的数量不必相同

答案：B
A错误，UNION默认排序
B正确，不会忽略NULL值，在去重的时候认为NULL是相等的。
C错误，列名不必相同，数据类型必须相同
D错误，列的数量需要相同

wangtong

153：
View the Exhibits and examine the structures of the PRODUCTS and SALES tables.
Which two SQL statements would give the same output? (Choose two.)

A. SELECT prod_id FROM products
INTERSECT
SELECT prod_id FROM sales;

B. SELECT prod_id FROM products
MINUS
SELECT prod_id FROM sales;

C. SELECT DISTINCT p.prod_id
FROM products p JOIN sales s
ON p.prod_id=s.prod_id;

D. SELECT DISTINCT p.prod_id
FROM products p JOIN sales s
ON p.prod_id <> s.prod_id;

153：
查看下表并检查PRODUCTS和SALES表的结构
哪两个SQL语句会给出相同的输出结果？（选择两个）

A. SELECT prod_id FROM products
INTERSECT
SELECT prod_id FROM sales;

B. SELECT prod_id FROM products
MINUS
SELECT prod_id FROM sales;

C. SELECT DISTINCT p.prod_id
FROM products p JOIN sales s
ON p.prod_id=s.prod_id;

D. SELECT DISTINCT p.prod_id
FROM products p JOIN sales s
ON p.prod_id <> s.prod_id;

答案：AC
AC选项，结果是所有销售过的产品的prod_id。
B是显示没有销售过的产品的prod_id
D使用的非等值连接，会输出所有的产品的prod_id

wangtong

154：
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.

154：
检查下表并评估SALES,PRODUCTS和COST表的结构。
评估下面的SQL语句：
SQL>SELECT prod_id FROM products
INTERSECT
SELECT prod_id FROM sales
MINUS
SELECT prod_id FROM costs;
哪个语句是正确的关于上面的查询？
A.        它产生一个错误
B.        显示已销售有消费记录的产品
C.        显示已销售没有消费记录的产品
D.        显示有消费记录不管有没有销售的产品

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

wangtong

155：
Evaluate the following SQL statement:
SQL> SELECT promo_id, promo_category
FROM promotions
WHERE promo_category = 'Internet' ORDER BY 2 DESC
UNION
SELECT promo_id, promo_category
FROM promotions
WHERE promo_category = 'TV'
UNION
SELECT promo_id, promo_category
FROM promotions
WHERE promo_category ='Radio';
Which statement is true regarding the outcome of the above query?
A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.
B. It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators.
C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.
D. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SE

155：
评估下面的SQL语句：
SQL> SELECT promo_id, promo_category
FROM promotions
WHERE promo_category = 'Internet' ORDER BY 2 DESC
UNION
SELECT promo_id, promo_category
FROM promotions
WHERE promo_category = 'TV'
UNION
SELECT promo_id, promo_category
FROM promotions
WHERE promo_category ='Radio';
关于上面的查询结果，下面说法正确的是哪一个？
A.        它执行成功，降序显示所有PROMO_CATEGORY的行
B.        它执行报错，因为位置记号不能用在带有SET操作的ORDER BY子句中
C.        它执行成功，但是会忽略ORDER BY子句，因为它没有位于复合语句的最后。
D.        它执行报错，因为ORDER BY 子句只能出现在复合查询的最后一个SELECT语句中。

答案：D
集合操作中，ORDER BY只能放在最后，前面的结果集进行集合操作之后，可以看作一个整体，然后在最后再进行排序。

wangtong

156：
Evaluate the following SQL statement:
SQL> SELECT cust_id, cust_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30;
Which ORDER BY clauses are valid for the above query? (Choose all that apply.)
A. ORDER BY 2,1
B. ORDER BY CUST_NO
C. ORDER BY 2,cust_id
D. ORDER BY "CUST_NO"
E. ORDER BY "Last Name"

156：
评估下面的SQL语句：
SQL> SELECT cust_id, cust_last_name "Last Name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30;
哪个ORDER BY语句是有效的对于上面的查询？（选择所有合适的）
A. ORDER BY 2,1
B. ORDER BY CUST_NO
C. ORDER BY 2,cust_id
D. ORDER BY "CUST_NO"
E. ORDER BY "Last Name"

答案：ACE
ORDER BY后可以使用数字表示select语句后的第几个字段，也可以使用列名。
B和D选项不正确，因为只能使用第一个查询的列进行排序，包括别名、列名或表达式。

wangtong

157：
View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables.
Evaluate the following SQL command:
SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit
FROM orders o JOIN customers c
USING (customer_id)
WHERE o.order_total > c.credit_limit
FOR UPDATE
ORDER BY o.order_id;
Which two statements are true regarding the outcome of the above query? (Choose two.)

A. It locks all the rows that satisfy the condition in the statement.
B. It locks only the columns that satisfy the condition in both the tables.
C. The locks are released only when a COMMIT or ROLLBACK is issued.
D. The locks are released after a DML statement is executed on the locked rows.

157：
查看下表并检查ORDERS和CUSTOMERS表的结构。
评估下面的SQL命令：
SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit
FROM orders o JOIN customers c
USING (customer_id)
WHERE o.order_total > c.credit_limit
FOR UPDATE
ORDER BY o.order_id;
哪两个语句是正确的关于上面查询的结果？（选择两个）
A.        它会锁定所有的满足条件的行
B.        它只会锁定两个表中满足条件的行
C.        只有执行COMMIT和ROLLBACK后，锁才会释放
D.        在锁定的行上执行DML语句后，锁被释放

答案：AC
A正确，select…for update语句，会锁定所有行，房子其它会话变更。
B错误，oracle中并没有列锁
C正确，commit和rollback后，事务提交会释放锁
D错误，在当前会话中，被锁定的行上可以做DML操作，但不会释放锁，如果在其他会话中被锁定的行做DML，会被阻塞

wangtong

158：
Which statements are true regarding the FOR UPDATE clause in a SELECT statement? (Choose all
that apply.)
A. It locks only the columns specified in the SELECT list.
B. It locks the rows that satisfy the condition in the SELECT statement.
C. It can be used only in SELECT statements that are based on a single table.
D. It can be used in SELECT statements that are based on a single or multiple tables.
E. After it is enforced by a SELECT statement, no other query can access the same rows until a COMMIT or ROLLBACK is issued.

158：
哪些语句是正确的关于SELECT语句的FOR UPDATE语句？（选择所有合适的）
A.        它只锁定SELECT列表指定的列
B.        它锁定符合SELECT语句条件的行
C.        它只能被用在基于单个表的SELECT语句中
D.        它可以被用在基于单个或多个表的SELECT语句中
E.        在被一个SELECT语句强制执行后，没有其它查询可以访问相同的行，直到它提交或者回滚

答案：BD
同上一题类似
A不正确，oracle没有列锁
B正确，会锁定满足条件的行
C不正确，可以用于基于一张表，也可以基于多张表
D正确
E不正确，for update之后，不能变更相关的行，但可以查询

wangtong

159：
View the Exhibit and examine the structure of the CUSTOMERS table.
NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have
the same data types and size as the corresponding columns in the CUSTOMERS table.
Evaluate the following INSERT statement:
INSERT INTO new_customers
  (cust_id, cust_name, cust_city)
VALUES
  (SELECT cust_id, cust_first_name ' ' cust_last_name, cust_city
     FROM customers
WHERE cust_id > 23004);
The INSERT statement fails when executed. What could be the reason?



A. The VALUES clause cannot be used in an INSERT with a subquery.
B. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match.
C. The WHERE clause cannot be used in a subquery embedded in an INSERT statement.
D. The total number of columns in the NEW_CUSTOMERS table does not match the total number of columns in the CUSTOMERS

159：
查看下表并检查CUSTOMERS表的结构
NEW_CUSTOMERS是一个新的表，有列CUST_ID，CUST_NAME和CUST_CITY并且具有相同的数据类型和大小和CUSTOMERS表中对应的列。
评估下面的ISNERT语句：
INSERT INTO new_customers
  (cust_id, cust_name, cust_city)
VALUES
  (SELECT cust_id, cust_first_name ' ' cust_last_name, cust_city
     FROM customers
WHERE cust_id > 23004);
执行的时候INSERT语句失败，原因可能是什么？
A.        VALUES语句不能被用于带子查询的INSERT语句
B.        NEW_CUSTOMERS的列名和CUSTOMERS表不匹配
C.        嵌入INSERT语句的子查询不能使用WHERE子句
D.        NEW_CUSTOMERS表中列的数量和CUSTOMERS表不匹配

答案：A
values后面只能跟确定的值，如果需要使用子查询的结果集插入到表中，就不用带values关键字。

wangtong

160：
View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
There is only one customer with the cust_last_name column having value Roberts. Which INSERT
statement should be used to add a row into the ORDERS table for the customer whose
CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600?

A. INSERT INTO orders
VALUES
  (1,
   '10-mar-2007',
   'direct',
   (SELECT customer_id
      FROM customers
     WHERE cust_last_name = 'Roberts'
       AND credit_limit = 600),
   1000);

B. INSERT INTO orders
  (order_id,
   order_date,
   order_mode,
   (SELECT customer_id
      FROM customers
     WHERE cust_last_name = 'Roberts'
       AND credit_limit = 600),
   order_total)
VALUES
  (1, '10-mar-2007', 'direct', &&customer_id, 1000);

C. INSERT INTO
  (SELECT o.order_id,
          o.order_date,
          o.order_mode,
          c.customer_id,
          o.order_total
     FROM orders o, customers c
    WHERE o.customer_id = c.customer_id
      AND c.cust_last_name = 'Roberts' ANDc.credit_limit = 600)
VALUES
  (1,
   '10-mar-2007',
   'direct',
   (SELECT customer_id
      FROM customers
     WHERE cust_last_name = 'Roberts'
       AND credit_limit = 600),
   1000);

D. INSERT INTO orders
  (order_id,
   order_date,
   order_mode,
   (SELECT customer_id
      FROM customers
     WHERE cust_last_name = 'Roberts'
       AND credit_limit = 600),
   order_total)
VALUES
  (1, '10-mar-2007', 'direct', &customer_id, 1000);

160：
查看下表并检查ORDERS和CUSTOMERS表的结构。
只有一个CUSTOMER的CUST_LAST_NAME的值是Roberts，哪一个INSERT语句能够被用于给ORDERS添加一行，CUSTOMERS的CUST_LAST_NAME为Roberts，且CREDIT_LIMIT为600？
A. INSERT INTO orders
VALUES
  (1,
   '10-mar-2007',
   'direct',
   (SELECT customer_id
      FROM customers
     WHERE cust_last_name = 'Roberts'
       AND credit_limit = 600),
   1000);

B. INSERT INTO orders
  (order_id,
   order_date,
   order_mode,
   (SELECT customer_id
      FROM customers
     WHERE cust_last_name = 'Roberts'
       AND credit_limit = 600),
   order_total)
VALUES
  (1, '10-mar-2007', 'direct', &&customer_id, 1000);

C. INSERT INTO
  (SELECT o.order_id,
          o.order_date,
          o.order_mode,
          c.customer_id,
          o.order_total
     FROM orders o, customers c
    WHERE o.customer_id = c.customer_id
      AND c.cust_last_name = 'Roberts' ANDc.credit_limit = 600)
VALUES
  (1,
   '10-mar-2007',
   'direct',
   (SELECT customer_id
      FROM customers
     WHERE cust_last_name = 'Roberts'
       AND credit_limit = 600),
   1000);

D. INSERT INTO orders
  (order_id,
   order_date,
   order_mode,
   (SELECT customer_id
      FROM customers
     WHERE cust_last_name = 'Roberts'
       AND credit_limit = 600),
   order_total)
VALUES
  (1, '10-mar-2007', 'direct', &customer_id, 1000);

答案：A
题中提到只有一个roberts，所以子查询返回一个值，这里就能插入成功，故选择A

wangtong

161:
View the exhibit and examine the description for the SALES and CHANNELS tables.
You issued the following SQL statement to insert a row in the SALES table:
INSERT INTO sales
VALUES
  (23,
   2300,
   SYSDATE,
   (SELECT channel_id FROM channels WHERE channel_desc = 'Direct Sales'),
   12,
   1,
   500);
Which statement is true regarding the execution of the above statement?

A. The statement will execute and the new row will be inserted in the SALES table.
B. The statement will fail because subquery cannot be used in the VALUES clause.
C. The statement will fail because the VALUES clause is not required with subquery.
D. The statement will fail because subquery in the VALUES clause is not enclosed with in single quotation marks

161:
查看下表并检查SALES和CHANNELS表的结构
你执行下面的SQL语句插入一行到SALES表：
INSERT INTO sales
VALUES
  (23,
   2300,
   SYSDATE,
   (SELECT channel_id FROM channels WHERE channel_desc = 'Direct Sales'),
   12,
   1,
   500);
哪个语句是正确的关于上面执行的语句？
A.        语句将执行且新行将被插入到SALES表
B.        语句失败因为子查询不能用于VALUES子句
C.        语句失败因为VALUSE子句不需要子查询
D.        语句失败因为VALUES子句的子查询不能被包含在单引号内

答案：A
Values可以使用子查询，只要返回的数据为一行。子查询需要用括号括起来，而不是单引号。

wangtong

162：
View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables.

SALE_VW is a view created using the following command :
SQL>CREATE VIEW sale_vw AS
SELECT prod_id, SUM(quantity_sold) QTY_SOLD
FROM sales GROUP BY prod_id;
You issue the following command to add a row to the SALE_SUMMARY table :
SQL> INSERT INTO sale_summary
  SELECT prod_id, prod_name, qty_sold
    FROM sale_vw
    JOIN products
   USING (prod_id)
   WHERE prod_id = 16;
What is the outcome?
A. It executes successfully.
B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table.
C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match.
D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY

162：
查看下表并检查PRODUCTS，SALES和SALE_SUMMARY表的结构。
SALE_VW是用下面语句创建的视图：
SQL>CREATE VIEW sale_vw AS
SELECT prod_id, SUM(quantity_sold) QTY_SOLD
FROM sales GROUP BY prod_id;

你执行下面的命令添加一行给SALE_SUMMARY表：

SQL>INSERT INTO sale_summary
  SELECT prod_id, prod_name, qty_sold
    FROM sale_vw
    JOIN products
   USING (prod_id)
   WHERE prod_id = 16;

输出结果是什么？
A.        执行成功
B.        给出一个错误因为一个复杂视图不能被用于添加数据到SALE_SUMMARY表
C.        给出一个错误因为子查询的列名和SALE_SUMMARY表不匹配
D.        给出一个错误因为被插入的列数和SALE_SUMMARY表的列数不匹配

答案：D
观察SALE_SUMMARY表结构，知道它有4列，isnert into后面没跟列名，表示要插入所有的列，但是后面的select语句只有3列，所以报错。

wangtong

163：
View the Exhibit and examine the description for the CUSTOMERS table.
You want to update the CUST_CREDIT_LIMIT column to NULL for all the customers, where
CUST_INCOME_LEVEL has NULL in the CUSTOMERS table. Which SQL statement will accomplish the task?

A. UPDATE customers
SET cust_credit_limit = NULL
WHERE CUST_INCOME_LEVEL = NULL;

B. UPDATE customers
SET cust_credit_limit = NULL
WHERE cust_income_level IS NULL;

C. UPDATE customers
SET cust_credit_limit = TO_NUMBER(NULL)
WHERE cust_income_level = TO_NUMBER(NULL);

D. UPDATE customers
SET cust_credit_limit = TO_NUMBER(' ',9999)
WHERE cust_income_level IS NULL;

163：
查看下表并检查CUSTOMERS表的描述信息。
你想更新所有客户CUST_INCOME_LEVEL列为NULL的CUST_CREDIT_LIMIT列为NULL，哪个语句能完成这个任务？
A. UPDATE customers
SET cust_credit_limit = NULL
WHERE CUST_INCOME_LEVEL = NULL;

B. UPDATE customers
SET cust_credit_limit = NULL
WHERE cust_income_level IS NULL;

C. UPDATE customers
SET cust_credit_limit = TO_NUMBER(NULL)
WHERE cust_income_level = TO_NUMBER(NULL);

D. UPDATE customers
SET cust_credit_limit = TO_NUMBER(' ',9999)
WHERE cust_income_level IS NULL;

答案：B
在oracle中 ，判断列值是否为null,要用 列名 is null来判断，而设置值的时候，用等号(=)。

wangtong

164：
View the Exhibit and examine the structure of CUSTOMERS and SALES tables.
Evaluate the following SQL statement:
UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id FROM sales)
   SET time_id = '22-MAR-2007'
WHERE cust_id = (SELECT cust_id
                    FROM customers
                   WHERE cust_last_name = 'Roberts'
                     AND credit_limit = 600);
Which statement is true regarding the execution of the above UPDATE statement?

A. It would not execute because two tables cannot be used in a single UPDATE statement.
B. It would not execute because the SELECT statement cannot be used in place of the table name.
C. It would execute and restrict modifications to only the columns specified in the SELECT statement.
D. It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.

164：
查看下表并检查CUSTOMERS和SALES表的结构
评估下面的SQL语句：
UPDATE (SELECT prod_id, cust_id, quantity_sold, time_id FROM sales)
   SET time_id = '22-MAR-2007'
WHERE cust_id = (SELECT cust_id
                    FROM customers
                   WHERE cust_last_name = 'Roberts'
                     AND credit_limit = 600);
哪个语句是正确的关于上面的UPDATE语句？
A.        它将不会被执行因为两个表不能呢个用于一个UPDATE语句
B.        它将不会被执行因为SELECT语句不能被用于代替表名
C.        它将执行并限制修改SELECT语句指定的列
D.        它将不会被执行因为一个子查询不能被用于UPDATE语句的WHERE子句

答案：C
UPDATE后面的SELECT语句相当于一个简单视图，也就是对这个视图进行修改，所以，只能修改select后面的的列。

wangtong

165：
View the Exhibit and examine the description for the CUSTOMERS table.
You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with
the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of
the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the
customer with CUST_ID 2566.
Which UPDATE statement will accomplish the task?

A. UPDATE customers
   SET cust_income_level =
       (SELECT cust_income_level FROM customers WHERE cust_id = 2560),
       cust_credit_limit =
       (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566)
WHERE cust_id = 2360;

B. UPDATE customers
   SET (cust_income_level, cust_credit_limit) =
       (SELECT cust_income_level, cust_credit_limit
          FROM customers
         WHERE cust_id = 2560
            OR cust_id = 2566)
WHERE cust_id = 2360;

C. UPDATE customers
SET (cust_income_level,cust_credit_limit) =
(SELECT cust_income_level, cust_credit_limit
FROM customers
WHERE cust_id IN(2560, 2566)
WHERE cust_id=2360;

D. UPDATE customers
   SET (cust_income_level, cust_credit_limit) =
       (SELECT cust_income_level, cust_credit_limit
          FROM customers
         WHERE cust_id = 2560
           AND cust_id = 2566)
WHERE cust_id = 2360;

165：
查看下表并检查CUSTOMERS表的详细信息。
你想更新CUST_ID为2306的客户的CUST_INCOME_LEVEL和CUST_CREDIT_LIMIT列。你想CUST_INCOME_LEVEL的值和CUST_ID为2560的值相同，CUST_CREDIT_LIMIT和CUST_ID为2566的值相同。
哪个UPDATE语句将完成这个任务？
A. UPDATE customers
   SET cust_income_level =
       (SELECT cust_income_level FROM customers WHERE cust_id = 2560),
       cust_credit_limit =
       (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566)
WHERE cust_id = 2360;

B. UPDATE customers
   SET (cust_income_level, cust_credit_limit) =
       (SELECT cust_income_level, cust_credit_limit
          FROM customers
         WHERE cust_id = 2560
            OR cust_id = 2566)
WHERE cust_id = 2360;

C. UPDATE customers
SET (cust_income_level,cust_credit_limit) =
(SELECT cust_income_level, cust_credit_limit
FROM customers
WHERE cust_id IN(2560, 2566)
WHERE cust_id=2360;

D. UPDATE customers
   SET (cust_income_level, cust_credit_limit) =
       (SELECT cust_income_level, cust_credit_limit
          FROM customers
         WHERE cust_id = 2560
           AND cust_id = 2566)
WHERE cust_id = 2360;

答案：A
B和C选项不正确，由于子查询里返回多行记录，而这里又是=,所以报错。
D明显子查询的条件不符合题意，这里不会返回结果。

wangtong

166：
View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
You want to update the EMPLOYEES table as follows:4 ? 4;
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
-Set department_id for these employees to the department_id corresponding to London (location_id 2100).
-Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission in location_id 2100 to 1.5 times the average commission of their
department.
You issue the following command:
UPDATE employees
   SET department_id =
       (SELECT department_id FROM departments WHERE location_id = 2100),
       (salary, commission) =
       (SELECT 1.1 * AVG(salary), 1.5 * AVG(commission)
          FROM employees, departments
         WHERE departments.location_id IN (2900, 2700, 2100))
WHERE department_id IN (SELECT department_id
                           FROM departments
                          WHERE location_id = 2900
                             OR location_id = 2700)
What is the outcome?
A. It executes successfully and gives the correct result.
B. It executes successfully but does not give the correct result.
C. It generates an error because a subquery cannot have a join condition in an UPDATE statement.
D. It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an UPDATE statement.

166：
查看下表并检查EMPLOYEES和DEPTMENTS表的结构
你想更新EMPLOYEES表如下：
- 只更新工作在Boston或Seatle的员工（locations为2900和2700）
- 设置这些员工的department_id与London（Location_id为2100）的department_id一致
- 设置location_id为2100的员工的薪水为他们部门平均薪水的1.1倍
- 设置location_id为2100的员工的佣金为他们部门平均佣金的1.5倍

你执行下面的命令：
UPDATE employees
   SET department_id =
       (SELECT department_id FROM departments WHERE location_id = 2100),
       (salary, commission) =
       (SELECT 1.1 * AVG(salary), 1.5 * AVG(commission)
          FROM employees, departments
         WHERE departments.location_id IN (2900, 2700, 2100))
WHERE department_id IN (SELECT department_id
                           FROM departments
                          WHERE location_id = 2900
                             OR location_id = 2700)
输出结果是什么？

A.        它执行成功并给出正确的结果
B.        它执行成功但是没给出正确的结果
C.        它产生一个错误因为一个子查询不能有join条件在一个UPDATE语句中
D.        它产生一个错误因为组合列（SALARY，COMMISION）不能一起指定在一个UPDATE语句中

答案：B
题目中要求的是location_id为2100的员工薪水为部门平均薪水的1.1倍。
子查询SELECT 1.1 * AVG(salary), 1.5 * AVG(commission)
          FROM employees, departments
         WHERE departments.location_id IN (2900, 2700, 2100)
选出来的不只是2100的员工，还有2700和2900的。

wangtong

167：
Evaluate the following DELETE statement:
DELETE FROM sales;
There are no other uncommitted transactions on the SALES table.
Which statement is true about the DELETE statement?
A. It would not remove the rows if the table has a primary key.
B. It removes all the rows as well as the structure of the table.
C. It removes all the rows in the table and deleted rows can be rolled back.
D. It removes all the rows in the table and deleted rows cannot be rolled back.

167：
评估下面的DELETE语句：
DELETE FROM sales;
没有其它未提交的事务在SALES表
哪个语句是正确的关于DELETE语句？
A.        不能移除行如果表有主键的话
B.        移除所有行，同时移除表结构
C.        移除所有行，删除的行可以回滚
D.        移除所有行，删除的行不能回滚

答案：C
没有where子句，表明删除所有的行。Delete是DML会产生undo，如果不commit的话，可以用rollback进行回滚。

wangtong

168：
View the Exhibit and examine the description of SALES and PROMOTIONS tables.
You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS
table has either blowout sale or everyday low price as values.
Which DELETE statements are valid? (Choose all that apply.)

A. DELETE FROM sales
WHERE promo_id =
       (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale')
   AND promo_id = (SELECT promo_id
                     FROM promotions
                    WHERE promo_name = 'everyday low price');

B. DELETE FROM sales
WHERE promo_id =
       (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale')
    OR promo_id = (SELECT promo_id
                     FROM promotions
                    WHERE promo_name = 'everyday low price');

C. DELETE FROM sales
WHERE promo_id IN (SELECT promo_id
                      FROM promotions
                     WHERE promo_name = 'blowout sale'
                        OR promo_name = 'everyday low price');

D. DELETE FROM sales
WHERE promo_id IN
       (SELECT promo_id
          FROM promotions
         WHERE promo_name IN ('blowout sale', 'everyday low price'));

168：
查看下表并检查SALES和PROMOTIONS表的详细信息
你想删除SALES表的行，条件为PROMOTIONS表中的PROMO_NAME列值为blowout sale或者everyday low price。
哪个delete语句是有效的？（选择正确的选项）
A. DELETE FROM sales
WHERE promo_id =
       (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale')
   AND promo_id = (SELECT promo_id
                     FROM promotions
                    WHERE promo_name = 'everyday low price');

B. DELETE FROM sales
WHERE promo_id =
       (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale')
    OR promo_id = (SELECT promo_id
                     FROM promotions
                    WHERE promo_name = 'everyday low price');

C. DELETE FROM sales
WHERE promo_id IN (SELECT promo_id
                      FROM promotions
                     WHERE promo_name = 'blowout sale'
                        OR promo_name = 'everyday low price');

D. DELETE FROM sales
WHERE promo_id IN
       (SELECT promo_id
          FROM promotions
         WHERE promo_name IN ('blowout sale', 'everyday low price'));

答案：BCD
A不正确，A使用的是and，题意是用or