Oracle 1Z0-071 Exam Practice Questions

Question 1

Examine the description of the PROMOTIONS table:

You want to display the unique promotion costs in each promotion category.

Which two queries can be used? (Choose two.)

1. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
2. SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
3. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
4. SELECT promo_category DISTINCT promo_cost, FROM promotions ORDER BY 2;
5. SELECT promo_cost, promo_category FROM promotions ORDER BY 1;

Question 2

Examine the description of the PRODUCTS table:

Which three queries use valid expressions? (Choose three.)

1. SELECT product_id, unit_price, S "Discount", unit_price + surcharge - discount FROM products;
2. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
3. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;
4. SELECT product_id, unit_price || 5 "Discount", unit_price + surcharge - discount FROM products;
5. SELECT product_id, expiry_date * 2 FROM products;
6. SELECT product_id, unit_price, unit_price + surcharge FROM products; ✅Correct Answer: B, C, F
2. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;
3. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;
6. SELECT product_id, unit_price, unit_price + surcharge FROM products; Valid expressions must reference real columns and use operators the data types allow. B performs multiplication and division on numeric values, which is allowed. C subtracts one DATE from another to get a number of days and multiplies that result by 2, which is allowed. F adds UNIT_PRICE to SURCHARGE; SURCHARGE is VARCHAR2, but Oracle implicitly converts a numeric string to a number, so the expression is valid. The other three fail on column and data type rules: DISCOUNT is not a column of PRODUCTS, a column alias such as "Discount" cannot be reused elsewhere in the same SELECT list, S is not a column either, and a DATE value cannot be an operand of multiplication.

Question 3

What is true about non-equijoin statement performance? (Choose two.)

1. The BETWEEN condition always performs less well than using the >= and <= conditions.
2. The BETWEEN condition always performs better than using the >= and <= conditions.
3. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
4. Table aliases can improve performance.
5. The join syntax used makes no difference to performance.