clause.

■ You cannot specify thisclause with a NATURAL outer join.

122. View the Exhibit forthe structure of the STUDENT and FACULTY tables.

You need to display thefaculty name followed by the number of students handled by the faculty at the

base location.

Examine the followingtwo SQL statements:

Statement 1

SQL>SELECTfaculty_name,COUNT(student_id)

FROM student JOINfaculty

USING (faculty_id,location_id)

GROUP BY faculty_name;

Statement 2

SQL>SELECTfaculty_name,COUNT(student_id)

FROM student NATURALJOIN faculty

GROUP BY faculty_name;

Which statement is trueregarding the outcome

A. Only s tatement 1executes successfully and gives the required result.

B. Only statement 2executes successfully and gives the required result.

C. Both statements 1 and2 execute successfully and give different results.

D. Both statements 1 and2 execute successfully and give the same required result.

Answer: D

解析：

USING (faculty_id,location_id)和NATURAL JOIN效果一样

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

tables.

You need to generate areport that gives details of the customer's last name, name of the product, and

the quantity sold forall customers in ' Tokyo' .

Which two queries givethe required result (Choose two.)

A. SELECTc.cust_last_name,p.prod_name, s.quantity_sold

FROM sales s JOINproducts p

USING(prod_id)

JOIN customers c

USING(cust_id)

WHEREc.cust_city='Tokyo';

B. SELECTc.cust_last_name, p.prod_name, s.quantity_sold

FROM products p JOINsales s JOIN customers c

ON(p.prod_id=s.prod_id)

ON(s.cust_id=c.cust_id)

WHEREc.cust_city='Tokyo';

C. SELECT c.cust_last_name,p.prod_name, s.quantity_sold

FROM products p JOINsales s

ON(p.prod_id=s.prod_id)

JOIN customers c

ON(s.cust_id=c.cust_id)

AND c.cust_city='Tokyo';

D. SELECTc.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold

FROM products p JOINsales s

USING(prod_id)

JOIN customers c

USING(cust_id)

WHEREc.cust_city='Tokyo';

Answer: AC

解析：

A选项，使用USING(prod_id)连接 sales比表和product表 使用USING(prod_id)连接sales表和customers表

C选项，ON(p.prod_id=s.prod_id)和ON(s.cust_id=c.cust_id)与A选项中的using效果一样

124. View the Exhibitand examine the structure of the PROMOTIONS, SALES, and CUSTOMER tables.

You need to generate areport showing the promo name along with the customer name for all products

that were sold duringtheir promo campaign and before 30th October 2007.

You issue the followingquery:

SQL> SELECTpromo_name,cust_name

FROM promotions p JOINsales s

ON(time_id BETWEENpromo_begin_date AND promo_end_date)

JOIN customer c

ON (s.cust_id =c.cust_id) AND time_id < '30-oct-2007';

Which statement is true regardingthe above query

A. It executessuccessfully and gives the required result.

B. It executessuccessfully but does not give the required result.

C. It produces an errorbecause the join order of the tables is incorrect.

D. It produces an errorbecause equijoin and nonequijoin conditions cannot be used in the same SELECT

statement.

Answer:B

解析：

题目要求：

showing the promo namealong with the customer name for all products

that were sold duringtheir promo campaign and before 30th October 2007

不应该在连接表的时候就加上条件，应该在把条件最后面

125. Examine thestructure of the CUSTOMERS table:

name Null Type

CUSTNO NOT NULLNUMBER(3)

CUSTNAME NOT NULLVARCHAR2(25)