Oracle 1Z0-071 Online Practice Questions and Exam Preparation

Oracle 1Z0-071 Online Questions & Answers

• Question 301:

  What is true about non-equijoin statement performance? (Choose two.)

  A. The BETWEEN condition always performs less well than using the >= and
  B. The BETWEEN condition always performs better than using the >= and
  C. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
  D. Table aliases can improve performance.
  E. The join syntax used makes no difference to performance.
  D. Table aliases can improve performance.
  E. The join syntax used makes no difference to performance.

• Question 302:

  Which statement executes successfully?

  A. SELECT TO_NUMBER(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;
  B. SELECT TO_CHAR(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;
  C. SELECT TO_NUMBER(TO_DATE(INTERVAL `800' SECOND)) FROM DUAL;
  D. SELECT TO_DATE(TO_NUMBER(INTERVAL `800' SECOND)) FROM DUAL;
  E. SELECT TO_DATE(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;
  B. SELECT TO_CHAR(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;

• Question 303:

  Which two queries execute successfully? (Choose two.)

  A. SELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL
  B. SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;
  C. SELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;
  D. SELECT INTERVAL '1' DAY + INTERVAL '1' MONTH FROM DUAL;
  E. SELECT SYSDATE * INTERVAL '1' DAY FROM DUAL;
  A. SELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL
  B. SELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;

• Question 304:

  View the Exhibit and examine the data in the PRODUCTS table.

  

  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:

  

  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.
  A. It executes successfully but returns no result.

• Question 305:

  Which two statements are true about date/time functions in a session where NLS_DATE_PORMAT is set to DD-MON-YYYY SH24:MI:SS

  A. SYSDATE can be used in expressions only if the default date format is DD-MON-RR.
  B. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.
  C. CURRENT_DATE returns the current date and time as per the session time zone
  D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server.
  E. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of functional seconds.
  F. SYSDATE can be queried only from the DUAL table.
  C. CURRENT_DATE returns the current date and time as per the session time zone
  E. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of functional seconds.

• Question 306:

  Examine the structure of the EMPLOYEES table.

  

  There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.

  You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.

  Which query provides the correct output?

  A. SELECT e.last_name, m.manager_id FROM employees e RIGHT OUTER JOIN employees m on (e.manager_id = m.employee_id) AND e.employee_id = 123;
  B. SELECT e.last_name, m.manager_id FROM employees e LEFT OUTER JOIN employees m on (e.employee_id = m.manager_id) WHERE e.employee_id = 123;
  C. SELECT e.last_name, e.manager_id FROM employees e RIGHT OUTER JOIN employees m on (e.employee_id = m.employee_id) WHERE e.employee_id = 123;
  D. SELECT m.last_name, e.manager_id FROM employees e LEFT OUTER JOIN employees m on (e.manager_id = m.manager_id) WHERE e.employee_id = 123;
  D. SELECT m.last_name, e.manager_id FROM employees e LEFT OUTER JOIN employees m on (e.manager_id = m.manager_id) WHERE e.employee_id = 123;

• Question 307:

  Examine the description of the EMPLOYEES table:

  

  Which statement will execute successfully, returning distinct employees with non-null first names?

  A. SELECT first_name, DISTINCT last_name FROM employees WHERE first_name NULL;
  B. SELECT first_name, DISTINCT last_name FROM employees WHERE first_name IS NOT NULL;
  C. SELECT DISTINCT * FROM employees WHERE first_name IS NOT NULL;
  D. SELECT DISTINCT * FROM employees WHERE first_name NULL;
  C. SELECT DISTINCT * FROM employees WHERE first_name IS NOT NULL;

• Question 308:

  You want to display 5 percent of the rows from the SALES table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the

  rows.

  Which query will provide the required result?

  A. SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS WITH TIES;
  B. SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;
  C. SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;
  D. SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS ONLY;
  A. SELECT prod_id, cust_id, amount_sold FROM sales ORDER BY amount_sold FETCH FIRST 5 PERCENT ROWS WITH TIES;

• Question 309:

  Examine the description of the PRODUCT_INFORMATION table:

  

  Which query retrieves the number of products with a null list price?

  A. SELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price is NULL;
  B. SELECT COUNT(list_price) FROM product_information WHERE list_price = NULL;
  C. SELECT COUNT(list_price) FROM product_information WHERE list_price IS NULL;
  D. SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price IS NULL;
  A. SELECT COUNT(NVL(list_price, 0)) FROM product_information WHERE list_price is NULL;

• Question 310:

  Which two statements best describe the benefits of using the WITH clause? (Choose two.)

  A. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
  B. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
  C. It enables sessions to store a query block permanently in memory and use it to create complex queries.
  D. It enables sessions to store the results of a query permanently.
  A. It can improve the performance of a large query by storing the result of a query block having the WITH clause in the session's temporary tablespace.
  B. It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.