*

第 1 行出现错误:

ORA-00979: 不是 GROUP BY 表达式

114. View the Exhibitand examine the structure of the SALES table.

The following query iswritten to retrieve all those product ID s from the SALES table that have morethan

55000 sold and have beenordered more than 10 times.

SQL> SELECT prod_id

FROM sales

WHERE quantity_sold >55000 AND COUNT(*)>10

GROUP BY prod_id

HAVING COUNT(*)>10;

Which statement is trueregarding this SQL statement

A. It executessuccessfully and generates the required result.

B. It produces an errorbecause COUNT(*) should be specified in the SELECT clause also.

C. It produces an errorbecause COUNT(*) should be only in the HAVING clause and not in the WHERE

clause.

D. It executessuccessfully but produces no result because COUNT(prod_id) should be usedinstead of

COUNT(*).

Answer: C

解析：

Where后面不能用分组函数

所以C选项正确

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

eva luate the followingSQL statement:

SQL> SELECTcust_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 trueregarding the outcome of the above query

A. It executessuccessfully.

B. It returns an errorbecause the BETWEEN operator cannot be used in the HAVING clause.

C. It returns an error becauseWHERE and HAVING clauses cannot be used in the same SELECT

statement.

D. It returns an errorbecause WHERE and HAVING clauses cannot be used to apply conditions on the

same column.

Answer: A

解析：

Group by 后面可以用分组函数

所以A选项正确

116. Examine the data inthe 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 ifthere is any item in the table for which the average maximum quantity is morethan

50.

You issue the followingquery:

SQL> SELECT AVG(MAX(qty))

FROM ord_items

GROUP BY item_no

HAVINGAVG(MAX(qty))>50;

Which statement is trueregarding the outcome of this query

A. It executessuccessfully and gives the correct output.

B. It gives an errorbecause the HAVING clause is not valid.

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

D. It gives an errorbecause the GROUP BY expression is not valid.

Answer: B

解析：

Having后不能使用嵌套分组函数，测试:

scott@ORCL>selectavg(max(sal)) from emp group by empno having avg(max(sal));

select avg(max(sal))from emp group by empno having avg(max(sal))

*

第 1 行出现错误:

ORA-00935: 分组函数的嵌套太深

117. Which statementsare true regarding the WHERE and HAVING clauses in a SELECT statement

(Choose all that apply.)

A. The HAVING clause canbe used with aggregate functions in subqueries.

B. The WHERE clause canbe used to exclude rows after dividing them into groups.

C. The WHERE clause canbe used to exclude rows before dividing them into groups.

D. The aggregatefunctions and columns used in the HAVING clause must be specified in the SELECTlist

of the query.

E. The WHERE and HAVINGclauses can be used in the same statement only if they are applied to

different columns in thetable.

Answer: AC

解析：

Group by后可以使用聚集函数，而where 后不可以使用

Group by后使用的聚集函数不用首先在select中指定

所以A，C正确

118. View the Exhibitand examine the structure of the PROMOTIONS table.

Examine the followingtwo SQL statements:

Statement 1

SQL>SELECTpromo_category,SUM(promo_cost)

FROM promotions

WHEREpromo_end_date-promo_begin_date > 30

GROUP BY promo_category;

Statement 2