Examine the data in the CUSTOMERS table:
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
Create table cust (
CUSTNO NUMBER,
CUSTNAME VARCHAR2(20),
CITY VARCHAR2(20));
insert into cust values(1,’KING’,’SEATTLE’);
insert into cust values(2,’GREEN’,’BOSTON’);
insert into cust values(3,’KOCHAR’,’SEATTLE’);
insert into cust values(4,’SMITH’,’NEY YORK’);
COMMIT;
SQL> select c1.custname, c1.city
2 from Cust c1
3* JOIN Cust c2 ON (c1.city=c2.city AND c1.custnamec2.custname)
SQL> /
CUSTNAME CITY
——————– —————
KOCHAR SEATTLE
KING SEATTLE select c1.custname, c1.city
2 from Cust c1
3* RIGHT OUTER JOIN Cust c2 ON (c1.city=c2.city AND c1.custnamec2.custname)
SQL> /
CUSTNAME CITY
——————– —————
KING SEATTLE
KOCHAR SEATTLE
Note, in the example above apart from the normal join we are also selecting all rows from c2 that do not satisfy the join. However as columns are only selected from c1 and not c2 you do not see any of the details from these rows.
SQL> select c1.custname, c1.city
2 from Cust c1
3* LEFT OUTER JOIN Cust c2 ON (c1.city=c2.city AND c1.custnamec2.custname)
SQL> /
CUSTNAME CITY
——————– —————
KOCHAR SEATTLE
KING SEATTLE
GREEN BOSTON
SMITH NEY YORK
Note, in the example above apart from the normal join we are also selecting all rows from c1 that do not satisfy the join. However as columns are selected from c1 you see these rows.
SQL> select c1.custname, c1.city
2 from Cust c1
3* FULL OUTER JOIN Cust c2 ON (c1.city=c2.city AND c1.custnamec2.custname)
SQL> /
CUSTNAME CITY
——————– —————
KOCHAR SEATTLE
KING SEATTLE
GREEN BOSTON
SMITH NEY YORK
6 rows selected.
SQL>
Note, in the example above apart from the normal join we are also
(a) selecting all rows from c1 that do not satisfy the join, which appear.
(b) selecting all rows from c2 that do not satisfy the join, which appear empty.
E in incorrect. 4 rows will be returned but the last two rows will have NULL values for CUSTNAME, CITY.
Here it is:
ASH@orcl>select * from cust;
CUSTNO CUSTNAME CITY
———- ——————– ——————–
1 KING SEATTLE
2 GREEN BOSTON
3 KOCHAR SEATTLE
4 SMITH NEW YORK
4 rows selected.
ASH@orcl>select c1.custname, c1.city
2 from cust c1 join cust c2 on
3 (c1.city = c2.city and c1.custnamec2.custname)
4 /
CUSTNAME CITY
——————– ——————–
KOCHAR SEATTLE
KING SEATTLE
2 rows selected.
ASH@orcl>select c1.custname, c1.city
2 from cust c1 right outer join cust c2 on
3* (c1.city = c2.city and c1.custnamec2.custname)
CUSTNAME CITY
——————– ——————–
KING SEATTLE
KOCHAR SEATTLE
4 rows selected.
As you can see [with feedback on in SQLPLUS], 4 rows will be returned for ‘E’ option, and only 2 rows for ‘A’ option.