一、原题
二、题目翻译
三、题目解析
四、测试
测试结果,A,E正确。
SQL> Create table customers (CUSTNO NUMBER,CUSTNAME VARCHAR2(20),CITY VARCHAR2(20));
Table created.
SQL> insert into customers values(1,'KING','SEATTLE');
1 row created.
SQL> insert into customers values(2,'GREEN','BOSTON');
1 row created.
SQL> insert into customers values(3,'KOCHAR','SEATTLE');
1 row created.
SQL> insert into customers values(4,'SMITH','NEY YORK');
1 row created.
SQL> COMMIT;
Commit complete.
--题目要求客户多于一个的城市,先看一下,有哪些城市,查询结果,只有SEAITLE这个城市。
SQL> select CITY from customers
2 group by city
3 having count(CUSTNAME)>1
4 order by city;CITY
---------------------------------
SEATTLE
SQL> select c1.custname, c1.city
2 from Customers c1 JOIN Customers c2
3 ON (c1.city=c2.city AND c1.custname<>c2.custname);
CUSTNAME CITY
---------------------------------------- ----------------------------------------
KOCHAR SEATTLE
KING SEATTLE
SQL> select c1.custname, c1.city
2 from Customers c1 NATURAL JOIN Customers c2
3 ON (c1.city=c2.city AND c1.custname<>c2.custname);
select c1.custname, c1.city
from Customers c1 NATURAL JOIN Customers c2
ON (c1.city=c2.city AND c1.custname<>c2.custname)
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select c1.custname, c1.city
2 from Customers c1 LEFT OUTER JOIN Customers c2
3 ON (c2.city=c1.city AND c2.custname<>c1.custname);
CUSTNAME CITY
---------------------------------------- -----------
KOCHAR SEATTLE
KING SEATTLE
GREEN BOSTON
SQL> select c1.custname, c1.city
2 from Customers c1 FULL OUTER JOIN Customers c2
3 ON (c2.city=c1.city AND c2.custname<>c1.custname);
CUSTNAME CITY
---------------------------------------- -----------
KOCHAR SEATTLE
KING SEATTLE
GREEN BOSTON
Examine the data in the CUSTOMERS table:
CUSTNO CUSTNAME CITY
1 KING S EATTLE
2 GREEN BOSTON
3 KOCHAR SEATTLE
4 SMITH NEW YORK
You want to list all cities that have more than one customer along with the customer details.
Evaluate the following query:
SQL>SELECT c1.custname, c1.city
FROM Customers c1 __________________ Customers c2
ON (c1.city=c2.city AND c1.custname<>c2.custname);
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)
A. JOIN
B. NATURAL JOIN
C. LEFT OUTER JOIN
D. FULL OUTER JOIN
E. RIGHT OUTER JOIN
答案:AE
CUSTNO CUSTNAME CITY
1 KING S EATTLE
2 GREEN BOSTON
3 KOCHAR SEATTLE
4 SMITH NEW YORK
You want to list all cities that have more than one customer along with the customer details.
Evaluate the following query:
SQL>SELECT c1.custname, c1.city
FROM Customers c1 __________________ Customers c2
ON (c1.city=c2.city AND c1.custname<>c2.custname);
Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.)
A. JOIN
B. NATURAL JOIN
C. LEFT OUTER JOIN
D. FULL OUTER JOIN
E. RIGHT OUTER JOIN
答案:AE
二、题目翻译
查看CUSTOMERS表的数据:
列出多于一个客户的城市和客户的信息。
评估下面的查询
哪两种JOIN用在上面的空白处能给出正确的结果?
列出多于一个客户的城市和客户的信息。
评估下面的查询
哪两种JOIN用在上面的空白处能给出正确的结果?
三、题目解析
B选项不正确,NATURAL JOIN自然连接不需要关联条件,所以下面的ON子句会报错。
CD选项不正确,LEFT OUTER JOIN和FULL OUTER JOIN都会显示有客户但是没在某个城市的人。
CD选项不正确,LEFT OUTER JOIN和FULL OUTER JOIN都会显示有客户但是没在某个城市的人。
四、测试
测试结果,A,E正确。
SQL> Create table customers (CUSTNO NUMBER,CUSTNAME VARCHAR2(20),CITY VARCHAR2(20));
Table created.
SQL> insert into customers values(1,'KING','SEATTLE');
1 row created.
SQL> insert into customers values(2,'GREEN','BOSTON');
1 row created.
SQL> insert into customers values(3,'KOCHAR','SEATTLE');
1 row created.
SQL> insert into customers values(4,'SMITH','NEY YORK');
1 row created.
SQL> COMMIT;
Commit complete.
--题目要求客户多于一个的城市,先看一下,有哪些城市,查询结果,只有SEAITLE这个城市。
SQL> select CITY from customers
2 group by city
3 having count(CUSTNAME)>1
4 order by city;CITY
---------------------------------
SEATTLE
SQL> select c1.custname, c1.city
2 from Customers c1 JOIN Customers c2
3 ON (c1.city=c2.city AND c1.custname<>c2.custname);
CUSTNAME CITY
---------------------------------------- ----------------------------------------
KOCHAR SEATTLE
KING SEATTLE
SQL> select c1.custname, c1.city
2 from Customers c1 NATURAL JOIN Customers c2
3 ON (c1.city=c2.city AND c1.custname<>c2.custname);
select c1.custname, c1.city
from Customers c1 NATURAL JOIN Customers c2
ON (c1.city=c2.city AND c1.custname<>c2.custname)
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select c1.custname, c1.city
2 from Customers c1 LEFT OUTER JOIN Customers c2
3 ON (c2.city=c1.city AND c2.custname<>c1.custname);
CUSTNAME CITY
---------------------------------------- -----------
KOCHAR SEATTLE
KING SEATTLE
GREEN BOSTON
SQL> select c1.custname, c1.city
2 from Customers c1 FULL OUTER JOIN Customers c2
3 ON (c2.city=c1.city AND c2.custname<>c1.custname);
CUSTNAME CITY
---------------------------------------- -----------
KOCHAR SEATTLE
KING SEATTLE
GREEN BOSTON
SQL> select c1.custname, c1.city
2 from Customers c1 RIGHT OUTER JOIN Customers c2
3 ON (c2.city=c1.city AND c2.custname<>c1.custname);
CUSTNAME CITY
---------------------------------------- ----------------------------------------
KING SEATTLE
KOCHAR SEATTLE
表连接的用法,详见:
http://blog.csdn.net/holly2008/article/details/25704471