eva luate the followingquery:

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 optionscan 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

Answer: BC

解析：

题意：You want to listeach product ID and the number of times it has been sold，需要列出每个product id 所以需要将 products表中全部列查询

引用官方文档：

■ A left outer joinreturns all the common column values from the left table in the

FROM clause.

■ A right outer joinreturns all the common column values from the right table in the

FROM clause.

■ A full outer joinreturns all the common column values from both joined tables.

133. Which twostatements are true regarding subqueries (Choose two.)

A. A subquery canretrieve zero or more rows.

B. Only two subqueriescan be placed at one level.

C. A subquery can beused only in SQL query statements.

D. A subquery can appearon either side of a comparison operator.

E. There is no limit onthe number of subquery levels in the WHERE clause of a SELECT statement.

Answer: AD

解析：

引用官方文档：

A subquery answers multiple-partquestions 所以A选项正确

B选项，可以有多个子查询在同一级上 所以B选项错误

C选项，create table zbcxy as select * from emp wheresal>(select avg(sal) from emp );

所以C选项错误

D选项 在运算符那一边结果一样 所以正确

E选项，引用官方文档：A subquery can contain another subquery. Oracle Databaseimposes no limit on the number of subquery levels in the FROM clause of the top-levelquery. You can nest up to 255 levels of subqueries in the WHERE clause.

所以E选项错误

134. Where cansubqueries be used (Choose all that apply.)

A. field names in theSELECT statement

B. the FROM clause inthe SELECT statement

C. the HAVING clause inthe SELECT statement

D. the GROUP BY clausein the SELECT statement

E. the WHERE clause inonly the SELECT statement

F. the WHERE clause inSELECT as well as all DML statements

Answer: ABCF

解析：

D选项，group by 后面只能用列名

F选项，很明显不一样

135. Which threestatements are true regarding subqueries (Choose three.)

A. Subqueries cancontain GROUP BY and ORDER BY clauses.

B. Main query andsubquery can get data from different tables.

C. Main query and subquerymust get data from the same tables.

D. Subqueries cancontain ORDER BY but not the GROUP BY clause.

E. Only one column orexpression can be compared between the main query and subquery.

F. Multiple columns orexpressions can be compared between the main query and subquery.

Answer: ABF

解析：

C选项，select * from dept where deptno=(select deptno from empwhere empno=7788);

D选项，select * from emp where deptno in (select deptno from empgroup by deptno);

E选项，select * from emp where deptno in (select deptno from empgroup by deptno);

136. View the Exhibitand examine the structure of the PRODUCTS table.

Which two tasks wouldrequire subqueries (Choose two.)

A. Display the minimumlist price for each product status.

B. Display all supplierswhose list price is less than 1000.

C. Display the number ofproducts whose list price is more than the average list price.

D. Display the totalnumber of products supplied by supplier 102 and have product status as'obsolete'.

E. Display all productswhose minimum list price is more than the average list price of products andhave

the status 'orderable'.

Answer: CE

解析：

C选项，查询平均价格作为子查询

E选项，the average list price of products and have the status'orderable'需要作为子查询

137. View the Exhibitsand examine PRODUCTS and SALES tables.