Exam 1z0-061 topic 2 question 33 discussion

Actual exam question from Oracle's 1z0-061

Question #: 33
Topic #: 2

See the Exhibits and examine the structures of PRODUCTS, SALES and CUSTOMERS table:

You issue the following query:

Which statement is true regarding the outcome of this query?

A. It produces an error because the NATURAL join can be used only with two tables
B. It produces an error because a column used in the NATURAL join cannot have a qualifier
C. It produces an error because all columns used in the NATURAL join should have a qualifier
D. It executes successfully

Show Suggested Answer

Suggested Answer: B 🗳️
Creating Joins with the USING Clause
Natural joins use all columns with matching names and data types to join the tables. The
USING clause can be used to specify only those columns that should be used for an equijoin.

The Natural JOIN USING Clause -
The format of the syntax for the natural JOIN USING clause is as follows:
SELECT table1.column, table2.column

FROM table1 -
JOIN table2 USING (join_column1, join_column2);
While the pure natural join contains the NATURAL keyword in its syntax, the JOINUSING syntax does not.
An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOINUSING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword. This avoids the shortcomings associated with the pure natural join. Many situations demand that tables be joined only on certain columns, and this format caters to this requirement.

by Sugar at July 28, 2019, 6:31 a.m.

Comments

Submit Cancel

Sugar

5 years, 10 months ago

This is very wrong.. select c.country_name , l.city, d.department_name from countries c natural join locations l natural join departments d try the above and that worked or you can do this select c.country_name , city, department_name from countries c natural join locations l natural join departments d Please review it first before you post anything.

upvoted 2 times

hggz

5 years, 8 months ago

B correct: It produces an error because a column used in the NATURAL join cannot have a qualifier ORA-25155: column used in NATURAL join cannot have qualifier

upvoted 1 times

...

jackymak

3 years, 10 months ago

I do a test. The join column cannot have qualifier in select or where clause while the others, not join column can. Error: SELECT employee.emp_id, emp_name,office_name FROM employee NATURAL JOIN office --join column : emp_id Success: SELECT emp_id, employee.emp_name, office.office_name FROM employee NATURAL JOIN office --join column : emp_id The same situation if using "JOIN...USING()"

upvoted 1 times

...