1Z0-071 Free Practice Test

View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SQL statement would you execute to accomplish the task?

1. A. UPDATE dept dSET city = ALL (SELECT cityFROM locations lWHERE d.location_id = l.location_id);
2. B. UPDATE dept dSET city = (SELECT cityFROM locations l)WHERE d.location_id = l.location_id;
3. C. UPDATE dept dSET city = ANY (SELECT cityFROM locations l)
4. D. UPDATE dept dSET city = (SELECT cityFROM locations lWHERE d.location_id = l.location_id);

Correct Answer: D

Which statement is true regarding the default behaviour of the ORDER by clause?

1. A. Numeric values are displayed in descending order if they have decimal positions.
2. B. Only columns that are specified in the SELECT list can be used in the ORDER by clause.
3. C. In a character sort, the values are case-sensitive.
4. D. NULLs are not including in the sort operation

Correct Answer: C

View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)

You executed this SQL statement: SELECT first_name, department_id, salary FROM employees
ORDER BY department_id, first_name, salary desc; Which two statements are true regarding the result?

1. A. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
2. B. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
3. C. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
4. D. The values in the all columns would be returned in descending order.
5. E. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

Correct Answer: AB

View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. (Choose all that apply.)

Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.
SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE JOB_ID = 'SA_MAN'
------------------------------------- SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE JOB_ID = 'SA_MAN';
Choose two correct SET operators which would cause the query to return the desired result.

1. A. UNION
2. B. MINUS
3. C. INTERSECT
4. D. UNION ALL

Correct Answer: AD

View the Exhibit and examine the structure of the CUSTOMERS table.

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?

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

Correct Answer: C