（123）View the Exhibits and examine the structures of the PRODUCTS, SALES, and CUSTOMERS tables.

You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in ' Tokyo' .

Which two queries give the required result? (Choose two.)

A.

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

FROM sales s JOIN products p

USING(prod_id) JOIN customers c USING(cust_id)

WHERE c.cust_city='Tokyo';

B.

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

FROM products p JOIN sales s JOIN customers c ON(p.prod_id=s.prod_id)

ON(s.cust_id=c.cust_id) WHERE c.cust_city='Tokyo';

C.

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

FROM products p JOIN sales s

ON(p.prod_id=s.prod_id) JOIN customers c ON(s.cust_id=c.cust_id)

AND c.cust_city='Tokyo';

D.

SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold

FROM products p JOIN sales s

USING(prod_id) JOIN customers c USING(cust_id)

WHERE c.cust_city='Tokyo';

答案：（A、C）

解析：

ON和WHERE可以同时使用，ON后面还可以接AND增加过滤条件。

D（X），查询的列过多，再有就是出现在USING子句中的列，在SELECT子句中不能使用修饰符。

scott@TESTDB11>select e.empno, e.ename, e.sal, e.deptno, d.deptno, d.dname

2 from emp e join dept d on(e.deptno = d.deptno) and empno = 7788;

ITPub博客

OCP 1Z0-051-123

最新文章