View the Exhibit and examine the structure of the
PRODUCTINFORMATION and INVENTORIES tables.
You want to display the quantity on hand for all the products available in the
PRODUCTINFORMATION 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.
The statement would execute successfully to produce the required output.
B.
The statement would not execute because the WHERE clause is used before the
GROUP BY clause.
The statement would not execute because inline views and outer joins cannot be used
together.
C.
The statement would not execute because prefixing table alias to column names is not
allowed with the ON clause.
The statement would not execute because the ITEM_CNT alias cannot be displayed
in the outer query.
D.
The statement would not execute because the WHERE clause is not allowed with
LEFT OUTER JOIN.
QUESTION 70
View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. 
You executed the following query to display PRODUCT_NAME and the number of
times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?
The statement would not execute because the GROUP BY clause cannot be used in
the inline view.
A.
The statement would execute and produce the desired output.
The statement would execute successfully to produce the required output.
B.
The statement would not execute because the WHERE clause is used before the
GROUP BY clause.
The statement would not execute because inline views and outer joins cannot be used
together.
C.
The statement would not execute because prefixing table alias to column names is not
allowed with the ON clause.
The statement would not execute because the ITEM_CNT alias cannot be displayed
in the outer query.
D.
The statement would not execute because the WHERE clause is not allowed with
LEFT OUTER JOIN.
QUESTION 70
View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of
times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?
The statement would not execute because the GROUP BY clause cannot be used in
the inline view.