View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.
You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)
A.
SELECT product_id, quantity_on_hand , supplier_id
FROM product_information
NATURAL JOIN inventories AND quantity_on_hand < 5;
B.
SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventories i
USING (product_id) AND quantity_on_hand < 5;
C.
SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventories i
ON (pi.product_id=i.product_id)
WHERE quantity_on_hand < 5;
D.
SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventories i
ON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
column used in NATURAL join cannot have qualifier.
NATURAL JOIN cannot have other expressions.
USING JOIN cannot have other expressions.
But JOIN ON can have other expressions.
select e.sal, empno from emp e join empu u using (empno); — ok —
select e.sal, e.empno from emp e join empu u using (empno); — column in USING cannot have qualifier —
select e.job from emp e join empu u using (empno) where sal > 100; — ambiguous —
select e.job from emp e join empu u using (empno) and e.sal > 100; — USING cannot have expression —