Prepare with Oracle : 1z0-071 exam braindumps as your best preparation materials

Examine the structure and data in the PRICE_LIST table:

You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.
Which SQL statement would give the required result?

• A. SELECT TO_CHAR (TO_NUMBER(prod_price) * .25, `$99,999.00') FROM PRICE_LIST
• B. SELECT TO_NUMBER (TO_NUMBER(prod_price, `$99,999.99') * . 25, `$99,999.00') FROM PRICE_LIST
• C. SELECT TO_CHAR (prod_price* .25, `$99,999.99')
  FROM PRICE_LIST
• D. SELECT TO_CHAR (TO_NUMBER(prod_price, `$99,999.99') * . 25, `$99,999.00') FROM PRICE_LIST

Which three statements are true about multiple row subqueries? (Choose three.)

• A. Two or more values are always returned from the subquery.
• B. They can contain HAVING clauses.
• C. They cannot contain a subquery.
• D. They can contain GROUP BY clauses.
• E. They can return multiple columns.

Examine the description of the MEMBERStable:

Examine the partial query:
SELECT city, last_name LNAME FROM members ...;
You want to display all cities that contain the string AN. The cities must be returned in ascending order, with the last names further sorted in descending order.
Which two clauses must you add to the query? (Choose two.)

• A. ORDER BY 1, LNAME DESC
• B. WHERE city = `%AN%'
• C. ORDER BY last_name DESC, city ASC
• D. WHERE city LIKE `%AN%'
• E. ORDER BY 1, 2
• F. WHERE city IN (`%AN%')

Examine this data in the EMPLOYEES table:

Which statement will execute successfully?

• A. SELECT dept_id, INSTR(last_name, `A'), SUM(salary) FROM employees GROUP BY dept_id;
• B. SELECT dept_id, STDDEV(last_name), SUM(salary) FROM employees GROUP BY dept_id;
• C. SELECT dept_id, MAX(last_name), SUM(salary) FROM employees GROUP BY dept_id;
• D. SELECT dept_id, LENGTH(last_name), SUM(salary) FROM employees GROUP BY dept_id;

View the exhibit and examine the structures of the EMPLOYEESand DEPARTMENTStables.

You want to update EMPLOYEEStable as follows:
Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
Set department_idfor these employees to the department_idcorresponding to London (location_id 2100).
Set the employees' salary in location_id2100 to 1.1 times the average salary of their department.
Set the employees' commission in location_id2100 to 1.5 times the average commission of their department.
You issue the following command:

What is outcome?

• A. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.
• B. It executes successfully but does not give the desired update
• C. It executes successfully and gives the desired update
• D. It generates an error because a subquery cannot have a join condition in a UPDATE statement.