View the Exhibit and examine the structure for the ORDERS and ORDER_ITEMS tables.
You want to display ORDER_ID, PRODUCT_ID, and TOTAL (UNIT_PRICE multiplied by QUANTITY) for all the orders placed in the last seven days.
Which query would you execute?
A.
SELECT order_id, product_id, unit_price*quantity “TOTAL” FROM order_items oi JOIN orders o
ON (o.order_id=oi.order_id)
WHERE o.order_date>=SYSDATE-7;
B.
SELECT o.order_id,oi.product_id, oi.unit_price*oi.quantity ” TOTAL” FROM order_items oi JOIN orders o
USING (order_id)
WHERE o.order_date>=SYSDATE-7;
C.
SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity “TOTAL” FROM order_items oi JOIN orders o
WHERE o.order_date>=SYSDATE-7
ON (o.order_id=oi.order_id);
D.
SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity “TOTAL” FROM order_items oi JOIN orders o
ON (o.order_id=oi.order_id)
WHERE o.order_date>=SYSDATE-7;
What is difference between choice B and D? There is no difference? both are the exactly the same. Is this an error on the writers’ part?
Both B and D will work and display same results.
B ==> USING (order_id)
D ==> JOIN … ON (o.order_id=oi.order_id)
Query D does not make a “better” choice than B. You can use column alias with USING joins. There is virtually no difference in results.
ORA-25154: column in USING clause cannot have qualifier.
If the column is in USING clause, it cannot be qualifier.
But other columns that are not in USING, they can have qualifier.
select a.sal, a.empno from emp a join emp b using (empno); <== ORA Error
select a.sal from emp a join emp b using (empno); <== OK
SELECT order_id, product_id, unit_price*quantity FROM order_items oi JOIN orders o
ON (o.order_id=oi.order_id)
WHERE o.order_date>=SYSDATE-7;
— column ambiguously defined —
A will give an ambiguous column error