Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 301:

  Examine the description of the PRODUCT_ STATUS table:

  

  The STATUS column contains the values IN STOCK or OUT OF STocK for each row. Which two queries will execute successfully?

  A. SELECT prod_id || q"'s not available" FROM product_status WHERE status = `OUT OF STOCK';

  B. SELECT prod_id || q'(`s not available)' `CURRENT AVAILABILITY' FROM product_status WHERE status = `OUT OF STOCK';

  C. SELECT prod_id q's not available" FROM product_status WHERE status = `OUT OF STOCK';

  D. SELECT prod_id "CURRENT AVAILABILITY" || q'(`s not available)' FROM product_status WHERE status = `OUT OF STOCK';

  E. SELECT prod_id || q'(`s not available)' FROM product_status WHERE status = `OUT OF STOCK';

  F. SELECT prod_id || q'(`s not available)' "CURRENT AVAILABILITY" FROM product status WHERE status = `OUT OF STOCK';

  Correct Answer: EF

• Question 302:

  Which statement is true about the INTERSECT operator used in compound queries?

  A. It processes NULLS in the selected columns.

  B. INTERSECT is of lower precedence than UNION or UNION ALL.

  C. It ignores NULLS.

  D. Multiple INTERSECT operators are not possible in the same SQL statement.

  Correct Answer: A

• Question 303:

  Examine the command to create the BOOKS table.

  

  The BOOK_ID value 101 does not exist in the table. Examine the SQL statement:

  

  Which statement is true?

  A. It executes successfully and the row is inserted with a null PLBLISHER_ID.

  B. It executes successfully only if NULL is explicitly specified in the INSERT statement.

  C. It executes successfully only NULL PUBLISHER_ID column name is added to the columns list in the INSERT statement.

  D. It executes successfully onlyif NULL PUBLISHER ID column name is added to the columns list and NULL is explicitly specified In the INSERT statement.

  Correct Answer: A

• Question 304:

  View the Exhibit and examine the description of the tables. You execute this SQL statement:

  

  Which three statements are true? (Choose three.)

  A. The statement will execute successfully and a new row will be inserted into the SALES table.

  B. A product can have a different unit price at different times.

  C. The statement will fail if a row already exists in the SALES table for product 23.

  D. The statement will fail because a subquery may not be contained in a VALUES clause.

  E. A customer can exist in many countries.

  F. The SALES table has five foreign keys.

  Correct Answer: ABF

• Question 305:

  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

  Correct Answer: D

• Question 306:

  Examine the description of the EMPLOYEES table:

  

  The session time zone is the same as the database server

  Which two statements will list only the employees who have been working with the company for more than five years? (Choose two.)

  A. SELECT employee_name FROM employees WHERE (SYSDATE - hire_date) / 12 > 5;

  B. SELECT employee_name FROM employees WHERE (SYSTIMSSTAMF - hire_date) / 12 > INTERVAL '5' YEAR;

  C. SELECT employee_name FROM employees WHERE (CURRENT_DATE - hire_date) / 12 > 5

  D. SELECT employee_name FROM employees WHERE (CURRENT_DATE - hire_date) / 365 >

  E. SELECT employee_name FROM employees WHERE (SYSDATE - hire_date) / 365 > 5;

  F. SELECT employee_name FROM employees WHERE (SYSTIMESTAMP - hire_date) / 365 > INTERVAL '1825' DAY;

  Correct Answer: DE

• Question 307:

  Which two statements are true about outer Joins?

  A. The outer join operator (+) can be used on both sides of the join condition in an outer join.

  B. An outer join is used to retrieve only the rows that do not meet the join condition.

  C. The IN operator cannot be used in a condition that Involves an outer join.

  D. A condition representing an outer join cannot be linked to another condition using the or logical operator.

  E. The outer join operator (+) is used next to the column of the table without the matching rows.

  Correct Answer: CD

• Question 308:

  Examine the data in the NEW_EMPLOYEES table:

  

  Examine the data in the EMPLOYEES table:

  

  You want to:

  1.

  Update existing employee details in the EMPLOYEES table with data from the NEW_EMPLOYEES table.

  2.

  Add new employee details from the NEW_EMPLOYEES table to the EMPLOYEES table. Which statement will do this?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  Correct Answer: A

• Question 309:

  Examine the description of the CUSTOMERS table:

  

  You want to display details of all customers who reside in cities starting with the letter D followed by at least two character. Which query can be used?

  A. SELECT * FROM customers WHERE city ='D_%';

  B. SELECT * FROM customers WHERE city ='%D_';

  C. SELECT * FROM customers WHERE city LIKE'D %';

  D. SELECT * FROM customers WHERE city LIKE'D_';

  Correct Answer: C

• Question 310:

  You create a table named 123. Which statement runs successfully?

  A. SELECT * FROM TABLE (123) ;

  B. SELECT * FROM andapos;123andapos;;

  C. SELECT * FROM "123";

  D. SELECT * FROM Vandapos;123Vandapos;;

  Correct Answer: C