View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.
You want to display the quantity on hand for all the products available in the PRODUCT_INFORMATION table that have the PRODUCT_STATUS as ‘orderable’. QUANTITY_ON_HAND is a column in the INVENTORIES table.
The following SQL statement was written to accomplish the task:
SELECT pi.product_id, pi.product_status, sum(i.quantity_on_hand) FROM product_information pi LEFT OUTER JOIN inventories i ON (pi.product_id = i.product_id)
WHERE (pi.product_status = ‘orderable’)
GROUP BY pi.product_id, pi.product_status;
Which statement is true regarding the execution of this SQL statement?
A.
The statement would execute and produce the desired output.
B.
The statement would not execute because the WHERE clause is used before the GROUP BY clause.
C.
The statement would not execute because prefixing table alias to column names is not allowed with the ON clause.
D.
The statement would not execute because the WHERE clause is not allowed with LEFT OUTER JOIN.
hi i think D is correct answer………
because here we use where clause ith left outer join which is not allowed…………..
select i.empno from emp i left outer join emp u on (i.empno=u.empno)
where (i.sal > 100);
D
the correct answer is A for me.
Yes, definitely A.
Tested on OE schema:
PRODUCT_ID PRODUCT_S SUM(I.QUANTITY_ON_HAND)
———- ——— —————————————
3129 orderable 762
3176 orderable 788
3359 orderable 1080
etc.
Correct Answer is A