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
Explanation:
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.
B http://stackoverflow.com/questions/15069256/what-does-this-error-mean-column-used-in-natural-join-cannot-have-qualifier
The explanation for answer B is based on combined use of NATURAL JOIN and USING. But the question only includes NATURAL JOIN.
If you adapt the query to run in the sample schemas oe and sh, but using the same structure of SQL, it runs successfully … albeit with no rows selected due to product id 148 not existing in sample schemas. If you drop off the where clause, it produces output.
Try this:
select p.product_id, product_name, list_price, quantity_sold, cust_last_name
from oe.products p natural join sh.sales s natural join oe.customers c
where product_id = 148;
MC,
The object OE.PRODUCTS in your sample query is actually a VIEW (and NOT A TABLE) that belongs to the OE schema.
You should try the 3 tables mentioned in the SQL query from the SH schema
You will get the error cited in option B
This works successfully – D is the answer;
I try this on HR schema
SELECT E.FIRST_NAME,D.DEPARTMENT_NAME,L.CITY
FROM EMPLOYEES E
NATURAL JOIN DEPARTMENTS D
NATURAL JOIN LOCATIONS L
WHERE DEPARTMENT_ID=20
;
If you put WHERE D.DEPARTMENT_ID=20 you will get an error;
no, it’s wrong, cause there is select has quantifier p.prod_id. correct B
It should by D
for example :-
SELECT e.employee_id, d.department_name
FROM employees e
natural JOIN departments d NATURAL JOIN LOCATIONS
where DEPARTMENT_ID=50;