Which query would you execute?

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?

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;



Leave a Reply 5

Your email address will not be published. Required fields are marked *


user

user

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?

user

user

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.

user

user

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

user

user

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 —

user

user

A will give an ambiguous column error