Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 291:

  Which statement falls to execute successfully?

  A. SELECT * FROM employees e JOIN department d WHERE e.department_id=d.department_id AND d.department_id=90;

  B. SELECT * FROM employees e JOIN departments d ON e.department_id=d.department_id WHERE d.department_id=90;

  C. SELECT * FROM employees e JOIN departments d ON e.department_id=d.department_id AND d.department_id=90;

  D. SELECT * FROM employees e JOIN departments d ON d.departments_id=90 WHERE e.department_id=d.department_id;

  Correct Answer: D

• Question 292:

  Which two statements are true about truncate and delete?

  A. the result of a delete can be undone by issuing a rollback

  B. delete can use a where clause to determine Which row(s) should be removed.

  C. TRUNCATE can use a where clause to determine Which row(s) should be removed.

  D. truncate leavers any indexes on the table in an UNUSABLE STATE.

  E. the result of a truncate can be undone by issuing a ROLLBACK.

  Correct Answer: AB

• Question 293:

  Examine this query:

  SELECT SUBSTR (SYSDATE,1,5) `Result' FROM DUAL

  Which statement is true?

  A. It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)

  B. It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)

  C. It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)

  D. It executes successfully with an implicit data type conversion

  Correct Answer: D

• Question 294:

  You need to calculate the number of days from 1st January 2019 until today.

  Dates are stored in the default format of DD-MON-RR.

  Which two queries give the required output? (Choose two.)

  A. SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') - '01-JAN-2019' FROM

  B. SELECT ROUND(SYSDATE - '01-JAN-2019') FROM DUAL;

  C. SELECT ROUND(SYSDATE ?TO_DATE('01/JANUARY/2019')) FROM DUAL;

  D. SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;

  E. SELECT SYSDATE - TO_DATE('01-JANUARY-2019') FROM DUAL;

  Correct Answer: CE

• Question 295:

  Examine the description of the EMPLOYEES table:

  

  For each employee in department 90 you want to display:

  1.

  their last name

  2.

  the number of complete weeks they have been employed

  The output must be sorted by the number of weeks, starting with the longest serving employee first.

  Which statement will accomplish this?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  Correct Answer: B

• Question 296:

  Which two actions can you perform with object privileges?

  A. Create roles.

  B. Delete rows from tables in any schema except sys.

  C. Set default and temporary tablespaces for a user.

  D. Create FOREIGN KEY constraints that reference tables in other schemas.

  E. Execute a procedure or function in another schema.

  Correct Answer: DE

  Reference: http://www.cruzroja.es/help/wvtdbobp.htm

• Question 297:

  Viev the Exhibit and examine the structure of the PRODUCT INFORMATION and INVENTORIEStables.

  

  You have a requirement from the supplies department to give a list containing PRODUCT _ID,SUPPLIER ID, and QUANTITY_ON HAND for all the products where in QUANTITY ON HAND is lessthan five.

  Which two SQL statements can accomplish the task? (Choose two)

  A. SELECT product id, quantity on hand, supplier id FROM product information NATURAL JOIN inventories AND quantity .on hand < 5;

  B. SELECT i. product id, i. quantity .on hand, pi. supplier_id FROM product_information pi JOIN inventories i ON (pi. product. id=i. product id) AND quantity on hand < 5;

  C. SELECT i. product_id, i. quantity_on hand, pi. supplier id FROM product information pi JOIN inventories i USING (product id) AND quantity .on hand < 5;

  D. SELECT i.product id, i. quantity on hand, pi. supplier id FROM product information pi JOIN inventories i ON (pi.product id=i. product id)WHERE quantity on hand < 5;

  Correct Answer: BD

• Question 298:

  Examine the description of the EMPLOYEES table

  

  You write this failing statement:

  

  Which clause causes the error?

  A. ORDER BY

  B. WHERE

  C. GROUP BY

  D. SELECT

  Correct Answer: C

• Question 299:

  Examine data in the BRICKS table:

  

  Examine the BOXES table:

  

  Which two queries only return CUBE? (Choose two.)

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: CD

• Question 300:

  Examine the description of the EMPLOYEES table:

  

  Which two queries will result in an error?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  F. Option F

  Correct Answer: CE