Oracle Datbase 12c SQL

Here you have the best Oracle 1z0-071 practice exam questions

You have 243 total questions to study from
Each page has 5 questions, making a total of 49 pages
You can navigate through the pages using the buttons at the bottom
This questions were last updated on November 16, 2024

Question 1 of 243

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.)

SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;

SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

SELECT promo_category DISTINCT promo_cost, FROM promotions ORDER BY 2;

SELECT promo_cost, promo_category FROM promotions ORDER BY 1;

Correct Answer: A, C

To display the unique promotion costs in each promotion category, the DISTINCT keyword should be used correctly. Option A correctly uses DISTINCT to ensure unique combinations of promo_category and promo_cost, even with concatenation. Option C uses DISTINCT correctly on the combination of promo_category and promo_cost. Other options either misuse the DISTINCT keyword or do not achieve the required result of displaying unique costs for each category.

Question 2 of 243

Examine the description of the PRODUCTS table:

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

SELECT product_id, unit_price, S "Discount", unit_price + surcharge - discount FROM products;

SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;

SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;

SELECT product_id, unit_price || 5 "Discount", unit_price + surcharge - discount FROM products;

SELECT product_id, expiry_date * 2 FROM products;

SELECT product_id, unit_price, unit_price + surcharge FROM products;

Correct Answer: B, C, F

The three queries that use valid expressions are: 1. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products; This query is valid because it uses a mathematical expression involving unit_price, which is of type NUMBER. 2. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products; This query is valid because it calculates the difference between two dates, which yields a NUMBER representing the number of days between them. This result can then be multiplied by 2. 3. SELECT product_id, unit_price, unit_price + surcharge FROM products; This query is valid assuming that the VARCHAR2 column surcharge contains numeric values. Oracle SQL implicitly converts VARCHAR2 to NUMBER in such cases, making the addition valid.

Question 3 of 243

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

The BETWEEN condition always performs less well than using the >= and <= conditions.

The BETWEEN condition always performs better than using the >= and <= conditions.

The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.

Table aliases can improve performance.

The join syntax used makes no difference to performance.

Correct Answer: D, E

Table aliases can improve performance by making queries more readable and easier to parse, which can reduce the time spent on parsing the query. The join syntax used typically does not make a significant difference in performance because modern SQL optimizers handle both Oracle and SQL:1999 compliant ANSI join syntaxes effectively.

Question 4 of 243

Which two are true? (Choose two.)

ADD_MONTHS adds a number of calendar months to a date.

CEIL requires an argument which is a numeric data type.

CEIL returns the largest integer less than or equal to a specified number.

LAST_DAY returns the date of the last day of the current month only.

LAST_DAY returns the date of the last day of the month for the date argument passed to the function.

LAST_DAY returns the date of the last day of the previous month only.

Correct Answer: A, E

ADD_MONTHS adds a number of calendar months to a date, which is a correct functionality of this function. LAST_DAY returns the date of the last day of the month for the date argument passed to the function, correctly describing what this function does. In contrast, the CEIL function returns the smallest integer greater than or equal to a specified number, which makes the suggested answer incorrect. CEIL does not return the largest integer less than or equal to a specified number. LAST_DAY is not limited to the current month only, as it can be applied to any date.

Question 5 of 243

Which three statements are true about Oracle synonyms? (Choose three.)

A synonym cannot be created for a PL/SQL package.

A synonym can be available to all users.

A SEQUENCE can have a synonym.

Any user can drop a PUBLIC synonym.

A synonym created by one user can refer to an object belonging to another user.

Correct Answer: B, C, E

A synonym in Oracle is an alias for another database object, such as a table, view, sequence, PL/SQL package, and others. Synonyms can be private or public. A synonym can be available to all users if it is created as a public synonym. A SEQUENCE can have a synonym, which allows easier reference across different sessions. Additionally, a synonym created by one user can refer to an object belonging to another user, making it useful for cross-schema references.