FROM promotions

GROUP BY promo_category

HAVINGMIN(promo_end_date-promo_begin_date)>30;

Which statement is trueregarding the above two SQL statements

A. statement 1 gives anerror, statement 2 executes successfully

B. statement 2 gives anerror, statement 1 executes successfully

C. statement 1 andstatement 2 execute successfully and give the same output

D. statement 1 andstatement 2 execute successfully and give a different output

Answer: D

解析：

都无语法错误，statement 1先筛选行，再进行分组排列

Statement 2 先分组，再筛选，测试：
scott@ORCL>select job,sum(sal) from emp where sysdate-hiredate>11700group by job;

JOB SUM(SAL)

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

CLERK 968

SALESMAN 5600

PRESIDENT 6300

MANAGER 8749.24

scott@ORCL>selectjob,sum(sal) from emp group by job having min(sysdate-hiredate)>11700;

JOB SUM(SAL)

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

SALESMAN 5600

PRESIDENT 6300

MANAGER 8749.24

119. 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

eva luate the followingquery:

SQL>SELECT item_no,AVG(qty)

FROM ord_items

HAVING AVG(qty) >MIN(qty) * 2

GROUP BY item_no;

Which statement is trueregarding the outcome of the above query

A. It gives an errorbecause the HAVING clause should be specified after the GROUP BY clause.

B. It gives an errorbecause all the aggregate functions used in the HAVING clause must be specifiedin

the SELECT list.

C. It displays the itemnos with their average quantity where the average quantity is more than doublethe

minimum quantity of thatitem in the table.

D. It displays the itemnos with their average quantity where the average quantity is more than doublethe

overall minimum quantityof all the items in the table.

Answer: C

解析：
没有语法错误，得到item_no, AVG(qty)并且AVG(qty) >MIN(qty) * 2

所以C选择正确

120. View the Exhibitsand examine the structures of the PRODUCTS, SALES, and CUSTOMERS

tables.

You issue the followingquery:

SQL>SELECTp.prod_id,prod_name,prod_list_price,

quantity_sold,cust_last_name

FROM products p NATURALJOIN sales s NATURAL JOIN customers c

WHERE prod_id =148;

Which statement is trueregarding the outcome of this query

A. It executessuccessfully.

B. It produces an errorbecause the NATURAL join can be used only with two tables.

C. It produces an errorbecause a column used in the NATURAL join cannot have a qualifier.

D. It produces an errorbecause all columns used in the NATURAL join should have a qualifier.

Answer: C

解析：
引用官方文档：

When specifying columnsthat are involved in the natural join, do not qualify

the column name with atable name or table alias.

所以C选项正确

121. Which twostatements are true regarding the USING clause in table joins (Choose two .)

A. It can be used tojoin a maximum of three tables.

B. It can be used torestrict the number of columns used in a NATURAL join.

C. It can be used toaccess data from tables through equijoins as well as nonequijoins.

D. It can be used tojoin tables that have columns with the same name and compatible data types.

Answer: BD

解析：

引用官方文档：

When you are specifyingan equijoin of columns that have the same

name in both tables, theUSING column clause indicates the columns to be used. You

can use this clause onlyif the join columns in both tables have the same name. Within

this clause, do notqualify the column name with a table name or table alias.

Restriction on the USINGcolumn Clause

■ Within this clause, donot qualify the column name with a table name or table

alias.