Free 1Z0-071 Exam Dumps

Question 51

View the Exhibit and examine the data in the employees table.
1Z0-071 dumps exhibit
You want to generate a report showing the total compensation paid to each employee to date. You issue the following query:

What is the outcome?

A. It executes successfully but does not give the correct output.
B. It generates an error because the concatenation operator can be used to combine only two items.
C. It generates an error because the usage of the round function in the expression is not valid
D. It generates an error because the alias is not valid.
E. It executes successfully and gives the correct output.

Correct Answer:A

Question 52

View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.
1Z0-071 dumps exhibit
You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the views successfully?

A. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
B. CREATE OR REPLACE VIEW ord_vu (order_id, order_date)AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
C. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;
D. CREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)||"NO OF ITEMS"FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_dateWHITH CHECK OPTION;

Correct Answer:C

Question 53

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 54

Examine the structure of the ORDERS table: (Choose the best answer.)
1Z0-071 dumps exhibit
You want to find the total value of all the orders for each year and issue this command:
SQL> SELECT TO_CHAR(order_date,'rr'), SUM(order_total) FROM orders GROUP BY TO_CHAR(order_date, 'yyyy');
Which statement is true regarding the result?

A. It executes successfully but does not give the correct output.
B. It executes successfully but gives the correct output.
C. It returns an error because the TO_CHAR function is not valid.
D. It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Correct Answer:D

Question 55

Which two statements are true regarding subqueries? (Choose two.)

A. A subquery can appear on either side of a comparison operator.
B. Only two subqueries can be placed at one level.
C. A subquery can retrieve zero or more rows.
D. A subquery can be used only in SQL query statements.
E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Correct Answer:AC