Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 351:

  Examine this statement:

  

  What is returned upon execution?

  A. 0 rows
  B. an error
  C. 1 row
  D. 2 rows
  C. 1 row

• Question 352:

  Which statement fails to execute successfully?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  B. Option B

• Question 353:

  Which two are true? (Choose two.)

  A. ADD_MONTHS adds a number of calendar months to a date.
  B. CEIL requires an argument which is a numeric data type.
  C. CEIL returns the largest integer less than or equal to a specified number.
  D. LAST_DAY returns the date of the last day of the current month only.
  E. LAST_DAY returns the date of the last day of the month for the date argument passed to the function.
  F. LAST_DAY returns the date of the last day of the previous month only.
  A. ADD_MONTHS adds a number of calendar months to a date.
  E. LAST_DAY returns the date of the last day of the month for the date argument passed to the function.

  ---

  Explanation/Reference:

  https //docs.oracle.com/database/121/SQLRF/functions025.htm https //docs.oracle.com/cd/B2835901/server111/b28286/functionsQ04.htm

• Question 354:

  Which two are true about the MERGE statement?

  A. The WHEN NOT MATCHED clause can be used to specify the deletions to be performed.
  B. The WHEN NOT MATCHED clause can be used to specify the inserts to be performed.
  C. The WHEN MATCHED clause can be used to specify the inserts to be performed.
  D. The WHEN NOT MATCHED clause can be used to specify the updates to be performed.
  E. The WHEN MATCHED clause can be used to specify the updates to be performed.
  B. The WHEN NOT MATCHED clause can be used to specify the inserts to be performed.
  E. The WHEN MATCHED clause can be used to specify the updates to be performed.

• Question 355:

  Examine the description of the MEMBERS table:

  

  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,2.
  B. ORDER BY last_name DESC,city ASC
  C. ORDER BY 1, LNAME DESC
  D. WHERE city='%AN%;
  E. WHERE city LIKE '%AN%;
  F. WHERE city IN ('%AN%')
  C. ORDER BY 1, LNAME DESC
  E. WHERE city LIKE '%AN%;

• Question 356:

  Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)?

  A. There must be an equal number of columns in each SELECT list.
  B. The name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.
  C. Each SELECT statement in the query can have an ORDER BY clause.
  D. None of the set operators can be used when selecting CLOB columns.
  E. The FOR UPDATE clause cannot be specified.
  A. There must be an equal number of columns in each SELECT list.
  E. The FOR UPDATE clause cannot be specified.

  ---

  Explanation/Reference:

  Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm

• Question 357:

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

  

  You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.

  Which query would produce the desired result?

  A. SELECT order_id FROM order_items GROUP BY order_id HAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)) FROM order_items GROUP BY order_id);
  B. SELECT order_id FROM order_items WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP BY order_id;
  C. SELECT order_id FROM order_items WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id;
  D. SELECT order_id FROM order_items WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP BY order_id)
  A. SELECT order_id FROM order_items GROUP BY order_id HAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity)) FROM order_items GROUP BY order_id);

• Question 358:

  Examine this statement,Which executes successfully:

  SELECT d.department_name, ROUND(AVG(NULLIF(e.salary,0))) AS avgsal, MAX(e.salary) AS maxsal FROM employees e JOIN departments d ON (e.department_id = d.department_id) GROUP BY d.department_name ORDER BY 2;

  In Which order are the rows displayed?

  A. sorted by DEPARTMENT_NAME
  B. sorted by DEPARTMENT_NAME and AVGSAL
  C. sorted by DEPARTMENT_NAME and MAXSAL
  D. sorted by AVGSAL
  E. Sorted by MAXSAL
  D. sorted by AVGSAL

• Question 359:

  You execute these commands:

  SQL> DEFINE hiredate = '01-APR -2011';

  SQL> SELECT employee_id, first_name, salary FROM employees WHERE hire date > andhiredate AND manager_id >andmgr_id;

  For Which substitution variables will you be prompted?

  A. none
  B. andhiredate and andmgr_id
  C. only andhiredate
  D. only andmgr_id
  D. only andmgr_id

• Question 360:

  Examine the contents of the EMP table:

  

  Examine this query that executes successfully:

  

  What is the result?

  A. It will return the five employees earning the lowest salaries, in ascending order.
  B. It will return the six employees earning the highest salaries, in descending order.
  C. It will return the six employees earning the lowest salaries, in ascending order.
  D. It will return the five employees earning the highest salaries, in descending order.
  A. It will return the five employees earning the lowest salaries, in ascending order.