Oracle 1Z0-071 Quiz 46 Topic 14 Questions 226-230

Question: 1

View the exhibit and examine the structure of the PROMOTIONS table.

q1_1Z0-071

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category.

Which query would give you the required output?

ASELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= 'INTERNET';

BSELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category= 'INTERNET');

CSELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = 'INTERNET');

DSELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category= 'INTERNET');

Show Answer

Question: 2

View the Exhibit and examine the structure of the ORDER_ITEMS table.

q2_1Z0-071

Examine the following SQL statement:

SELECT order_id, product_id, unit_price

FROM order_items

WHERE unit_price =

(SELECT MAX(unit_price)

FROM order_items

GROUP BY order_id);

You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID. What correction should be made in the above SQL statement to achieve this?

ARemove the GROUP BY clause from the subquery and place it in the main query

BReplace = with the >ANY operator

CReplace = with the >ALL operator

DReplace = with the IN operator

Show Answer

Question: 3

Evaluate the following CREATE TABLE command:

q3_1Z0-071

Which statement is true regarding the above SQL statement?

AIt would execute successfully and only ORD_ITM_IDX index would be created.

BIt would give an error because the USING INDEX clause cannot be used on a composite primary.

CIt would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.

DIt would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.

Show Answer

Question: 4

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level.

Which query would give the required result?

ASELECT cust_income_level || ' ' || cust_credit_limit * 0.50 AS ''50% Credit Limit'' FROM customers;

BSELECT DISTINCT cust_income_level || ' ' || cust_credit_limit * 0.50AS ''50% Credit Limit''FROM customers;

CSELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50AS ''50% Credit Limit''FROM customers;

DSELECT cust_income_level, DISTINCT cust_credit_limit * 0.50AS ''50% Credit Limit''FROM customers;

Show Answer

Question: 5

Which three statements are true regarding the SQL WHERE and HAVING clauses? (Choose three.)

AThe HAVING clause conditions can have aggregating functions.

BThe HAVING clause conditions can use aliases for the columns.

CThe WHERE and HAVING clauses cannot be used together in a SQL statement.

DThe WHERE clause is used to exclude rows before grouping data.

EThe HAVING clause is used to exclude one or more aggregated results after grouping data.

Show Answer

Oracle 1Z0-071 Quiz:46 Topic:14 Questions:226-230