1Z0-071 practice questions 61–70

1. Question 61 · 238 in bank

   Select 2

   Which two statements are true about a full outer join? (Choose two.)

   • AIt returns matched and unmatched rows from both tables being joined.
   • BThe Oracle join operator (+) must be used on both sides of the join condition in the WHERE clause.
   • CIt returns only unmatched rows from both tables being joined.
   • DIt includes rows that are returned by an inner join.
   • EIt includes rows that are returned by a Cartesian product.

   Show answer

   Answer: AD

   Next question

2. Question 62 · 238 in bank

   Select 2

   Which two statements are true about a self join? (Choose two.)

   • AIt can be a left outer join.
   • BIt must be an equijoin.
   • CIt must be a full outer join.
   • DThe join key column must have an index.
   • EIt can be an inner join.

   Show answer

   Answer: AE

   Next question

3. Question 63 · 238 in bank

   Select 2

   Examine the description of the CUSTOMERS table:

   

   Which two statements will do an implicit conversion? (Choose two.)

   • ASELECT * FROM customers WHERE customer_id - '0001';
   • BSELECT * FROM customers WHERE customer_id - 0001;
   • CSELECT * FROM customers WHERE insert_date - DATE '2019-01-01';
   • DSELECT * FROM customers WHERE insert_date - '01-JAN-19';
   • ESELECT * FROM customers WHERE TO_CHAR(customer_id) - '0001';

   Show answer

   Answer: AD

   Next question

4. Question 64 · 238 in bank

   Select 2

   Which two statements are true about CURRENT_TIMESTAMP? (Choose two.)

   • AThe value varies depending on the setting of SESSIONTIMEZONE.
   • BIt returns a value of data type TIMESTAMP.
   • CThe date is in the time zone of DBTIMEZONE.
   • DIt returns the same date as CURRENT_TIME.
   • EThe time is in the time zone of DBTIMEZONE.
   • FIt always returns the same value as SYSTEMTIMESTAMP.

   Show answer

   Answer: AB

   Explanation

   select tzname, tz_offset(tzname) from v$timezone_names; select tzname, tz_offset(tzname) from v$timezone_names where tzname = 'America/Anchorage'; alter session set time_zone='America/Anchorage'; select sessiontimezone, current_timestamp, dbtimezone, systimestamp from dual; select current_time from dual; A → OK. B → NOOK: Returns TIMESTAMP WITH TIME ZONE. C → NOOK: No, in the SESSIONTIMEZONE.. D → CURRENT_TIME doesn’t exist. E → NOOK: No, in the SESSIONTIMEZONE. F → NOOK. SYSTIMESTMP returns date based on DBSTIMEZONE.

   Source: Adapted from community discussion — SantiBZ_07032022_1744

   Next question

5. Question 65 · 238 in bank

   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 characters. Which query can be used?

   • ASELECT * FROM customers WHERE city LIKE ‘D__%’;
   • BSELECT * FROM customers WHERE city = ‘%D__’;
   • CSELECT * FROM customers WHERE city = ‘D__%’;
   • DSELECT * FROM customers WHERE city LIKE ‘D__’;

   Show answer

   Answer: A

   Next question

6. Question 66 · 238 in bank

   Select 2

   Which two are true about using the FOR UPDATE clause in a SELECT statement? (Choose two.)

   • AIt can be used with SET operators (UNION, INTERSECT etc.).
   • BIt cannot be used with the DISTINCT keyword.
   • CIf the NOWAIT clause is added, the statement will automatically acquire locks from their owning transactions and not wait.
   • DThe statement skips rows locked by other transactions.
   • EIt can be used with joins.

   Show answer

   Answer: BE

   Next question

7. Question 67 · 238 in bank

   Examine this statement which returns the name of each employee and their manager:

   

   You want to extend the query to include employees with no manager. What must you add before JOIN to do this?

   • ACROSS
   • BRIGHT OUTER
   • CLEFT OUTER
   • DFULL OUTER

   Show answer

   Answer: C

   Next question

8. Question 68 · 238 in bank

   Which two are true about constraints? (Choose two.)

   • AA column with a FOREIGN KEY constraint can never contain a NULL value.
   • BA constraint can be disabled even if the constrained column contains data.
   • CConstraints are enforced only during INSERT operations.
   • DAll constraints can be defined at the table or column level.
   • EA column with a UNIQUE constraint can contain a NULL value.

   Show answer

   Answer: B

   Next question

9. Question 69 · 238 in bank

   Select 2

   Examine the ORDER_ITEMS table:

   

   Which two queries return rows where QUANTITY is a multiple of ten? (Choose two.)

   • ASELECT * FROM order_items WHERE quantity / 10 - TRUNC(quantity
   • BSELECT * FROM order_items WHERE MOD(quantity, 10) - 0;
   • CSELECT * FROM order_items WHERE FLOOR(quantity / 10) = TRUNC(quantity / 10);
   • DSELECT * FROM order_items WHERE quantity = TRUNC(quantity, -1);
   • ESELECT * FROM order_items WHERE quantity = ROUND(quantity, 1);

   Show answer

   Answer: BD

   Next question

10. Question 70 · 238 in bank

    Which is true about the ROUND, TRUNC and MOD functions?

    • ATRUNC(MOD(25,3),-1) is invalid.
    • BROUND(MOD(25,3),-1) is invalid.
    • CROUND(MOD(25,3),-1) and TRUNC(MOD(25,3),-1) are both valid and give the same result.
    • DROUND(MOD(25,3),-1) and TRUNC(MOD(25,3),-1) are both valid but give different results.

    Show answer

    Answer: C

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

1Z0-071 practice, question 61. 238 questions available.