e followingquery to display product name and the number of times the product has been
sold:
SQL>SELECTp.prod_name, i.item_cnt
FROM (SELECT prod_id,COUNT(*) item_cnt
FROM sales
GROUP BY prod_id) iRIGHT OUTER JOIN products p
ON i.prod_id =p.prod_id;
What happens when theabove statement is executed
A. The statementexecutes successfully and produces the required output.
B. The statementproduces an error because ITEM_CNT cannot be displayed in the outer query.
C. The statement producesan error because a subquery in the FROM clause and outer-joins cannot be
used together.
D. The statementproduces an error because the GROUP BY clause cannot be used in a subquery inthe
FROM clause.
Answer: A
解析：
将 SELECT prod_id, COUNT(*) item_cnt
FROM sales
GROUP BY prod_id
的查询结果作为一张表和product表右外连接 ，可以得到product中每个产品的销售次数
138. Which statement istrue regarding subqueries
A. The LIKE operatorcannot be used with single- row subqueries.
B. The NOT IN operatoris equivalent to IS NULL with single- row subqueries.
C. =ANY and =ALLoperators have the same functionality in multiple- row subqueries.
D. The NOT operator canbe used with IN, ANY, and ALL operators in multiple- row subqueries.
Answer: D
解析：
A选项，测试： scott@ORCL>select * from emp where ename like (selectename from emp where empno=7788);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------- ---------- -------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-04-87 1850 20
B选项，不相等，not in 如果不处理子查询中的空值，就会全部返回为空
C选项，功能不一样，any表示已任意一个，all表示全部
139. Which threestatements are true about multiple-row subqueries (Choose three.)
A. They can contain asubquery within a subquery.
B. They can returnmultiple columns as well as rows.
C. They cannot contain asubquery within a subquery.
D. They can return onlyone column but multiple rows.
E. They can containgroup functions and GROUP BY and HAVING clauses.
F. They can containgroup functions and the GROUP BY clause, but not the HAVING clause.
Answer: ABE
解析：
C选项，子查询可以嵌套
应用官方文档：
A subquery can containanother subquery. Oracle Database imposes no limit on the
number of subquerylevels in the FROM clause of the top-level query. You can nest up
to 255 levels ofsubqueries in the WHERE clause
D选项，可以返回多列多行
F选项，测试：
scott@ORCL>select *from emp where job in(select job from emp group by job having job like 'S%');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------- ---------- -------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-09-81 1500 0 30
7654 MARTIN SALESMAN 7698 28-09-81 1250 1400 30
7521 WARD SALESMAN 7698 22-02-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-02-81 1600 160 30
140. Examine thestructure of the PRODUCTS table:
name Null Type
PROD_ID NOT NULLNUMBER(4)
PROD_NAME VARCHAR2(20)
PROD_STATUS VARCHAR2(6)
QTY_IN_HAND NUMBER(8,2)
UNIT_PRICE NUMBER(10,2)
You want to display thenames of the products that have the highest total value for UNIT_PRICE *
QTY_IN_HAND.
Which SQL statementgives 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 pr