1Z0-071 practice questions 201–210

1. Question 201 · 238 in bank

   Select 3

   Which three statements are true about inner and outer joins? (Choose three.)

   • AA full outer join returns matched and unmatched rows.
   • BOuter joins can be used when there are multiple join conditions on two tables.
   • CA full outer join must use Oracle syntax.
   • DOuter joins can only be used between two tables per query.
   • EA left or right outer join returns only unmatched rows.
   • FAn inner join returns matched rows.

   Show answer

   Answer: ABF

   Explanation

   I can't understand why answer B is right. For example, the query below (with OUTER JOIN) works but don't use multiple join condiitions : SELECT A.col FROM tableA LEFT OUTER JOIN tableB ON tableA.col = tableB.col WHERE tableB.col IS NULL;

   Source: Adapted from community discussion — KizTine

   Next question

2. Question 202 · 238 in bank

   Select 3

   Which three statements are true about the Oracle join and ANSI join syntax? (Choose three.)

   • AThe Oracle join syntax supports creation of a Cartesian product of two tables.
   • BThe Oracle join syntax only supports right outer joins.
   • CThe SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
   • DThe Oracle join syntax performs less well than the SQL:1999 compliant ANSI join syntax.
   • EThe Oracle join syntax supports natural joins.
   • FThe Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
   • GThe SQL:1999 compliant ANSI join syntax supports natural joins.

   Show answer

   Answer: ACG

   References

   • Oracle documentation

   Next question

3. Question 203 · 238 in bank

   Select 2

   Which two are true about the NVL, NVL2, and COALESCE functions? (Choose two.)

   • ANVL must have expressions of the same data type.
   • BNVL can have any number of expressions in the list.
   • CNVL2 can have any number of expressions in the list.
   • DCOALESCE stops evaluating the list of expressions when it finds the first non-null value.
   • EThe first expression in NVL2 is never returned.
   • FCOALESCE stops evaluating the list of expressions when it finds the first null value.

   Show answer

   Answer: DE

   Explanation

   DE is correct: D. COALESCE stops evaluating the list of expressions when it finds the first non-null value. TRUE https://www.oracletutorial.com/oracle-comparison-functions/oracle-coalesce/ E. The first expression in NVL2 is never returned. TRUE If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3. (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL2.html)

   Source: Adapted from community discussion — CMjer

   References

   • Oracle documentation
   • Oracle documentation

   Next question

4. Question 204 · 238 in bank

   Examine this statement:

   

   What is returned upon execution?

   • Aan error
   • B2 rows
   • C0 rows
   • D1 row

   Show answer

   Answer: D

   Next question

5. Question 205 · 238 in bank

   Examine this statement:

   

   What is returned upon execution?

   • Aan error
   • B2 rows
   • C0 rows
   • D1 row

   Show answer

   Answer: D

   Explanation

   D tested select 1 as id, 'john' as first_name, NULL as commission from dual intersect select 1, 'john' as name, null from dual order by 3;

   Source: Adapted from community discussion — zouve

   Next question

6. Question 206 · 238 in bank

   Select 3

   An Oracle Database session has an uncommitted transaction in progress which updated 5000 rows in a table. In which three situations does the transaction complete thereby committing the updates? (Choose three.)

   • Awhen a CREATE TABLE AS SELECT statement is issued in the same session but fails with a syntax error
   • Bwhen a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT
   • Cwhen the session logs out successfully
   • Dwhen a CREATE INDEX statement is executed successfully in the same session
   • Ewhen a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
   • Fwhen a COMMIT statement is issued by the same user from another session in the same database instance

   Show answer

   Answer: BCD

   Explanation

   B should be correct, because the session will be killed after the last transaction will be committed or the user will use rollback... A is incorrect because the statement failed with a syntax error

   Source: Adapted from community discussion — iuliana23

   References

   • Oracle documentation

   Next question

7. Question 207 · 238 in bank

   Select 2

   Examine these SQL statements which execute successfully:

   

   Which two statements are true after execution? (Choose two.)

   • AThe primary key constraint will be enabled and IMMEDIATE.
   • BThe foreign key constraint will be enabled and DEFERRED.
   • CThe primary key constraint will be enabled and DEFERRED.
   • DThe foreign key constraint will be disabled.
   • EThe foreign key constraint will be enabled and IMMEDIATE.

   Show answer

   Answer: AD

   Next question

8. Question 208 · 238 in bank

   Select 2

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

   • ANOT NULL can be specified at the column and at the table level.
   • BA table can have only one PRIMARY KEY and one FOREIGN KEY constraint.
   • CA FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parent table must have the same names.
   • DPRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table level.
   • EA table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.
   • FA table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

   Show answer

   Answer: DF

   Explanation

   Is the way you specify the constraints syntactically. There are two ways... by column or by table (at the end of the statement). PK and FK can be defined in both.

   Source: Adapted from community discussion — J4vi

   References

   • Oracle documentation

   Next question

9. Question 209 · 238 in bank

   Select 2

   Examine this statement:

   

   On which two columns of the table will an index be created automatically? (Choose two.)

   • AORDER_ID
   • BORDER_TOTAL
   • CORDER_DATE
   • DPRODUCT_ID
   • ESTATUS
   • FSERIAL_NO

   Show answer

   Answer: AF

   Next question

10. Question 210 · 238 in bank

    Examine this partial query:

    

    Examine this output:

    

    Which GROUP BY clause must be added so the query returns the results shown?

    • AGROUP BY ch.channel_type, ROLLUP(t.month, co.country_code);
    • BGROUP BY ch.channel_type, t.month, ROLLUP(co.country_code);
    • CGROUP BY CUBE(ch.channel_type, t.month, co.country_code);
    • DGROUP BY ch.channel_type, t.month, co.country_code;

    Show answer

    Answer: A

    Next question

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

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