1Z0-071 Oracle Exam Questions and Free Practice Test

Question 13

View the Exhibit and examine the description of the EMPLOYEES table.
1Z0-071 dumps exhibit
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?

A. SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct, salary,salary+commission_pct))“Total”FROM EMPLOYEES;

B. SELECT first_name, salary, salary*12+salary*commission_pct “Total”FROM EMPLOYEES;

C. SELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 “Total”FROM EMPLOYEES;

D. SELECT first_name, salary*12 + NVL(salary,0)*commission_pct, “Total”FROM EMPLOYEES;

Correct Answer:A

Question 14

View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.
1Z0-071 dumps exhibit
You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)

A. SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id)WHERE quantity_on_hand < 5;

B. SELECT product_id, quantity_on_hand, supplier_idFROM product_informationNATURAL JOIN inventories AND quantity_on_hand < 5>SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOIN inventories iON (pi.product_id=i.product_id) AND quantity_on_hand < 5>SELECT i.product_id, i.quantity_on_hand, pi.supplier_idFROM product_information pi JOINinventories iON (pi.product_id=i.product_id)USING (product_id) AND quantity_on_hand < 5;

Correct Answer:AC

Question 15

Evaluate the following query:
SQL> SELECT TRUNC (ROUND (156.00, -1),-1) FROM DUAL;
What would be the outcome?

A. 150

B. 200

C. 160

D. 16

E. 100

Correct Answer:C
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2127.htm

Question 16

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
1Z0-071 dumps exhibit
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SQL>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?

A. The statement would execute successfully to produce the required output.

B. The statement would not execute because inline views and outer joins cannot be used together.

C. 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 GROUP BY clause cannot be used in the inline.

Correct Answer:A

Question 17

Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

A. Finding the lowest value

B. Finding the quotient

C. Raising to a power

D. Subtraction

E. Addition

Correct Answer:ACE

Question 18

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

A. SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL

B. SELECT TO_CHAR (1890.55, '$9,999V99') FROM DUAL;

C. SELECT TO_CHAR (1890.55, '$0G000D00') FROM DUAL;

D. SELECT TO_CHAR (1890.55, '$99,999D99') FROM DUAL;

E. SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL

Correct Answer:ACE

START 1Z0-071 EXAM