CUST_CREDIT_LIMITNUMBER(5)

CUSTNO is the PRIMARYKEY in the table. You want to find out if any customers' details have been

entered more than onceusing different CUSTNO, by listing all the duplicate names.

Which two methods canyou use to get the required result (Choose two.)

A. self-join

B. subquery

C. full outer-join withself-join

D. left outer-join withself-join

E. right outer-join withself-join

Answer: AB

解析：

find out if any customers'details have been

entered more thanonce using different CUSTNO, by listing all the duplicate names.

需要用自连接比较names

需要使用子查询得到customers'details have beenentered more than once using different CUSTNO

126. View the Exhibitand examine the data in the PROJ_TASK_DETAILS table.

The PROJ_TASK_DETAILStable stores information about tasks involved in a project and the relation

between them.

The BASED_ON columnindicates dependencies between tasks. Some tasks do not depend on the

completion of any othertasks.

You need to generate areport showing all task IDs, the corresponding task ID they are dependent on,and

the name of the employeein charge of the task it depends on.

Which query would givethe required result

A. SELECT p.task_id,p.based_on, d.task_in_charge

FROM proj_task_details pJOIN proj_task_details d

ON (p.based_on =d.task_id);

B. SELECT p.task_id,p.based_on, d.task_in_charge

FROM proj_task_details pLEFT OUTER JOIN proj_task_details d

ON (p.based_on =d.task_id);

C. SELECT p.task_id,p.based_on, d.task_in_charge

FROM proj_task_details pFULL OUTER JOIN proj_task_details d

ON (p.based_on =d.task_id);

D. SELECT p.task_id,p.based_on, d.task_in_charge

FROM proj_task_details pJOIN proj_task_details d

ON (p.task_id =d.task_id);

Answer: B

解析：

showing all task IDs,the corresponding task ID they are dependent on, and

the name of the employeein charge of the task it depends on.

题目的意思不管是否有depend on 都需要排列出来，这时候就需要左外连接

引用官方文档：

■ A left outer joinreturns all the common column values from the left table in the

FROM clause.

■ A right outer joinreturns all the common column values from the right table in the

FROM clause.

■ A full outer joinreturns all the common column values from both joined tables.

127. Examine the data inthe CUSTOMERS table:

CUSTNO CUSTNAME CITY

1 KING SEATTLE

2 GREEN BOSTON

3 KOCHAR SEATTLE

4 SMITH NEW YORK

You want to list allcities that have more than one customer along with the customer details.

eva luate the followingquery:

SQL>SELECTc1.custname, c1.city

FROM Customers c1__________________ Customers c2

ON (c1.city=c2.city ANDc1.custname<>c2.custname);

Which two JOIN optionscan 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

Answer: AE

解析：

引用官方文档：

■ A left outer joinreturns all the common column values from the left table in the

FROM clause.

■ A right outer join returns all the common column valuesfrom the right table in the

FROM clause.

■ A full outer join returns all the common column valuesfrom both joined tables.

128. View the Exhibitsand examine the structures of the CUSTOMERS, SALES, and COUNTRIES

tables.

You need to generate areport that shows all country names, with corresponding customers (if any) and

sales details (if any),for all customers.

Which FROM clause givesthe required result

A. FROM sales JOINcustomers USING (cust_id)

FULL OUTER JOINcountries USING (country_id);