Which two statements best describe the benefits of using the WITH clause? (Choose two.)

1. A. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
2. B. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
3. C. It enables sessions to store a query block permanently in memory and use it to create complex queries.
4. D. It enables sessions to store the results of a query permanently.

Correct Answer: AB

Evaluate the following SELECT statement and view the exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status, FROM user_constraints
WHERE table_name = 'ORDERS'; CONSTRAINT_NAME
CON SEARCH_CONDITION R_CONSTRAINT_NAME DELETE_RULE
STATUS ORDER_DATE_NN C
"ORDER_DATE" IS NOT NULL ENABLED ORDER_CUSTOMER_ID_NN C
"CUSTOMER_ID" IS NOT NULL ENABLED ORDER_MODE_LOV C
order _mode in ('direct', 'online') ENABLED
ORDER TOTAL MIN C
order total >= 0 ENABLED ORDER PK
P ENABLED
ORDERS CUSTOMER ID R
CUSTOMERS ID SET NULL ENABLED
ORDERS SALES REP R
EMP EMP ID SET NULL ENABLED
Which two statements are true about the output? (Choose two.)

1. A. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.
2. B. In the second column, 'c' indicates a check constraint.
3. C. The STATUS column indicates whether the table is currently in use.
4. D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

Correct Answer: BD

Evaluate the following query:

Which is the correct output of the above query?

1. A. +00-300, +54-02,+00 11:12:10.123457
2. B. +00-300,+00-650,+00 11:12:10.123457
3. C. +25-00, +54-02, +00 11:12:10.123457
4. D. +25-00,+00-650,+00 11:12:10.123457

Correct Answer: C

View the Exhibit and examine the description of the EMPLOYEES table.

You want to calculate the total renumeration for each employee. Total renumeration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.
Which SQL statement would you execute to get the desired output?

1. A. SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct, salary,salary+commission_pct))“Total”FROM EMPLOYEES;
2. B. SELECT first_name, salary, salary*12+salary*commission_pct “Total”FROM EMPLOYEES;
3. C. SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 “Total”FROM EMPLOYEES;
4. D. SELECT first_name, salary*12 + NVL(salary,0)*commission_pct, “Total”FROM EMPLOYEES;

Correct Answer: A

View the Exhibit and examine the data in the PRODUCTS table. (Choose the best answer.)

You must display product names from the PRODUCTS table that belong to the 'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:
SQL > SELECT prod_name, prod_category, prod_min_price FROM products
Where prod_category LIKE '%Other%' AND (prod_min_price = 2000 OR prod_min_price = 4000) AND prod_unit_of_measure <> ' ';
Which statement is true?

1. A. It executes successfully but returns no result.
2. B. It executes successfully and returns the required result.
3. C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
4. D. It generates an error because the condition specified for the PROD_CATEGORY column is not valid.

Correct Answer: A