Free 1Z0-071 Exam Dumps

Question 66

View the Exhibit and examine the structure in the EMPLOYEES tables.
1Z0-071 dumps exhibit
Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees
WHERE department_id= 50 ORDER BY department_id UNION
SELECT employee_id, department_id FROM employees
WHERE department_id=90 UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=10;
What would be the outcome of the above SQL statement?

A. The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.
B. The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.
C. The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.
D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

Correct Answer:D

Question 67

View the Exhibit and examine the data in the PRODUCTS table. (Choose the best answer.)
1Z0-071 dumps exhibit
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?

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

Correct Answer:A

Question 68

View the Exhibit and examine the details of the PRODUCT_INFORMATION table.
1Z0-071 dumps exhibit
You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name, list_price FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?

A. It would not execute because the entire WHERE clause is not enclosed within parentheses.
B. It would execute but would return no rows.
C. It would not execute because the same column has been used twice with the AND logical operator.
D. It would execute and return the desired.

Correct Answer:B

Question 69

Examine the structure of the EMPLOYEES table. (Choose two.)
1Z0-071 dumps exhibit
You must display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would provide the correct output?

A. SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);
B. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE>SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE>SELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROM employeesWHERE hire_date < SYSDATE-365);

Correct Answer:BD

Question 70

View the Exhibit and examine the structure of the CUSTOMERS table.
1Z0-071 dumps exhibit
Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.
Customers with no credit limit should have “Not Available” displayed. Which SQL statement would produce the required result?

A. SELECT NVL (TO_CHAR(cust_credit_limit*.15), ‘Not Available’) “NEW CREDIT” FROM customers
B. SELECT TO_CHAR(NVL(cust_credit_limit*.15), ‘Not Available’)) “NEW CREDIT” FROMcustomers
C. SELECT NVL (cust_credit_limit*.15, ‘Not Available’) “NEW CREDIT” FROM customers
D. SELECT NVL (cust_credit_limit, ‘Not Available’)*.15 “NEW CREDIT” FROM customers

Correct Answer:C