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

You want to display thecategory with the maximum number of items.

You issue the followingquery:

SQL>SELECTCOUNT(*),prod_category_id

FROM products

GROUP BYprod_category_id

HAVING COUNT(*) =(SELECT MAX(COUNT(*)) FROM products);

What is the outcome

A. It executessuccessfully and gives the correct output.

B. It executessuccessfully but does not give the correct output.

C. It generates an errorbecause the subquery does not have a GROUP BY clause.

D. It generates an errorbecause = is not valid and should be replaced by the IN operator.

Answer: C

解析：

子查询用在group by 后面是错误的

145. View the Exhibitand examine the structure of the CUSTOMERS table.

You issue the followingSQL statement on the CUSTOMERS table to display the customers who are in the

same country ascustomers with the last name 'KING' and whose credit limit is less than themaximum

credit limit incountries that have customers with the last name 'KING':

SQL> SELECTcust_id,cust_last_name

FROM customers

WHERE country_idIN(SELECT country_id

FROM customers

WHERE cust_last_name='King')

AND cust_credit_limit< (SELECT MAX(cust_credit_limit)

FROM customers

WHERE country_idIN(SELECT country_id

FROM customers

WHEREcust_last_name='King'));

Which statement is trueregarding the outcome of the above query

A. It executes and showsthe required result.

B. It produces an errorand the < operator should be replaced by < ALL to get the requiredoutput.

C. It produces an errorand the < operator should be replaced by < ANY to get the requiredoutput.

D. It produces an errorand the IN operator should be replaced by = in the WHERE clause of the main

query to get therequired output.

Answer: A

解析：

题意：

display the customerswho are in the

same country as customerswith the last name 'KING' and whose credit limit isless than the maximum

credit limit incountries that have customers with the last name 'KING':

customers withthe last name 'KING'

WHERE country_idIN(SELECT country_id

FROM customers

WHERE cust_last_name='King')

找到所有cust_last_name为king的

whose creditlimit is less than the maximum

credit limitin countries that have customers with the last name 'KING':

cust_credit_limit小于last name 为king中最大的cust _credit_limit

cust_credit_limit <(SELECT MAX(cust_credit_limit)

FROM customers

WHERE country_idIN(SELECT country_id

FROM customers

WHERE cust_last_name='King'));

146. eva luate thefollowing SQL statement:

SQL> SELECT cust_id,cust_last_name

FROM customers

WHERE cust_credit_limitIN

(selectcust_credit_limit

FROM customers

WHERE cust_city='Singapore');

Which statement is trueregarding the above query if one of the values generated by the subquery is

NULL

A. It produces an error.

B. It executes butreturns no rows.

C. It generates outputfor NULL as well as the other values produced by the subquery.

D. It ignores the NULLvalue and generates output for the other values produced by the subquery.

Answer: C

解析：

In 如果子查询中得到部分行为空，则只会返回不为空的行，测试：

scott@ORCL>select *from emp where sal<2000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- ------------------- -------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-12-80 968 20

7499 ALLEN SALESMAN 7698 20-02-81 1600 160 30

7521 WARD SALESMAN 7698 22-02-81 1250 500 30

7654 MARTIN SALESMAN 7698 28-09-81 1250 1400 30

7788 SCOTT ANALYST 7566 19-04-87 1850 20

7844 TURNER SALESMAN 7698 08-09-81 1500 0 30

7876 ADAMS CLERK 7788 23-05-87 1100 20

7900 JAMES CLERK 7698 03-12-81 950 30

7934 MILLER CLERK 7782 23-01-82 1430 10

已选择9行。