一、原题
二、题目翻译
三、题目解析
四、测试
SQL> select d.deptno,e.ename
2 from emp e join dept d
3 using(deptno);
select d.deptno,e.ename
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier
SQL> select deptno,ename
2 from emp e join dept d
3 using(deptno);
DEPTNO ENAME
---------- ----------
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER
14 rows selected.
表连接的用法,详见:
http://blog.csdn.net/holly2008/article/details/25704471
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';
答案:AC
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';
答案:AC
二、题目翻译
查看PRODUCTS、SALES和CUSTOMERS表的结构
要生成一个报表,显示在Tokyo的客户的last name,product name,和the quantity sold(销售数量)的详细信息。
哪两个给出所需结果?(选择2个)
要生成一个报表,显示在Tokyo的客户的last name,product name,和the quantity sold(销售数量)的详细信息。
哪两个给出所需结果?(选择2个)
三、题目解析
B选项不正确,语法错误。
D选项不正确,因为有USING子句,不能使用限定词。
D选项不正确,因为有USING子句,不能使用限定词。
四、测试
SQL> select d.deptno,e.ename
2 from emp e join dept d
3 using(deptno);
select d.deptno,e.ename
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier
SQL> select deptno,ename
2 from emp e join dept d
3 using(deptno);
DEPTNO ENAME
---------- ----------
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER
14 rows selected.
表连接的用法,详见:
http://blog.csdn.net/holly2008/article/details/25704471