1Z0-071 practice questions 31–40

1. Question 31 · 238 in bank

   Select 3

   Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

   • AA sequence’s unallocated cached values are lost if the instance shuts down.
   • BA sequence number that was allocated can be rolled back if a transaction fails.
   • CA sequence can only be dropped by a DBA.
   • DA sequence can issue duplicate values.
   • ESequences can always have gaps.
   • FTwo or more tables cannot have keys generated from the same sequence.

   Show answer

   Answer: ADE

   Explanation

   ADE are correct: A - TRUE; cash values are lost if instance shuts down B - FALSE; Transaction can be rolled back if not COMMITed yet, but sequence number was already used and cannot be reused C - FALSE; user can create sequence and therefore has all privileges to this object, including DROP D - TRUE; CYCLE parameter can be set and values can be looped E - TRUE; there is a list of activities that can influence the gaps generation, like failed INSERT or DELETE; COMMIT or INSERT; ROLLBACK etc. F - FALSE; from ORACLE documentation "Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables"

   Source: Adapted from community discussion — Darnun

   Next question

2. Question 32 · 238 in bank

   Select 2

   The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type number. Which two queries execute successfully? (Choose two.)

   • ASELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers;
   • BSELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers
   • CSELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;
   • DSELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
   • ESELECT TO_CHAR(NVL(cust_credit_iimit * .15, 'Not Available')) FROM customers;

   Show answer

   Answer: BD

   Explanation

   NVL2 requires 3 arguments and it doesnt have to be the same data type NVL requires 2 arguments and it must be the same datatype or something that is implicitly converted

   Source: Adapted from community discussion — kesammy9

   Next question

3. Question 33 · 238 in bank

   Select 2

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

   • AThe ROLLBACK statement does not release locks resulting from table updates.
   • BData consistency is not guaranteed after a rollback.
   • CA transaction interrupted by a system failure is automatically rolled back.
   • DIf the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted.
   • EData Control Language (DCL) statements, such as GRANT and REVOKE, can be rolled back.

   Show answer

   Answer: CD

   Next question

4. Question 34 · 238 in bank

   Select 3

   Which three statements are true about dropping and unused columns in an Oracle database? (Choose three.)

   • AAn UNUSED column's space is reclaimed automatically when the row containing that column is next queried.
   • BA column that is set to UNUSED still counts towards the limit of 1000 columns per table.
   • CA DROP COLUMN command can be rolled back.
   • DA primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
   • EAn UNUSED column's space is reclaimed automatically when the block containing that column is next queried.
   • FPartition key columns cannot be dropped.

   Show answer

   Answer: BDF

   Next question

5. Question 35 · 238 in bank

   Select 3

   Which three actions can you perform by using the ORACLE_DATAPUMP access driver? (Choose three.)

   • ACreate a directory object for a flat file.
   • BCreate a directory object for an external table.
   • CRead data from an external table and load it into a table in the database.
   • DExecute DML statements on an external table.
   • ERead data from a table in the database and insert it into an external table.
   • FQuery data from an external table.

   Show answer

   Answer: CEF

   Next question

6. Question 36 · 238 in bank

   Which statement is true about aggregate functions?

   • AAggregate functions can be nested to any number of levels.
   • BThe AVG function implicitly converts NULLS to zero.
   • CThe MAX and MIN functions can be used on columns with character data types.
   • DAggregate functions can be used in any clause of a SELECT statement.

   Show answer

   Answer: C

   Explanation

   A. Aggregate functions can be nested to any number of levels. -> Can be nested upto one level only. B. The AVG function implicitly converts NULLS to zero. -> AVG function ignores NULLS. C. The MAX and MIN functions can be used on columns with character data types. -> True D. Aggregate functions can be used in any clause of a SELECT statement. -> It can't be used with where clause.

   Source: Adapted from community discussion — archit4321

   Next question

7. Question 37 · 238 in bank

   Select 3

   Which three are true about multitable INSERT statements? (Choose three.)

   • AThey can insert each computed row into more than one table.
   • BThey can be performed on remote tables.
   • CThey can be performed on external tables using SQL*Loader.
   • DThey can be performed on views.
   • EThey can be performed only by using a subquery.
   • FThey can be performed on relational tables.

   Show answer

   Answer: AEF

   Next question

8. Question 38 · 238 in bank

   Select 3

   Which three statements are true regarding single row subqueries? (Choose three.)

   • AThey must be placed on the left side of the comparison operator or condition.
   • BThey must be placed on the right side of the comparison operator or condition.
   • CThey must return a row to prevent errors in the SQL statement.
   • DA SQL statement may have multiple single row subquery blocks.
   • EThey can be used in the HAVING clause.
   • FThey can be used in the WHERE clause.

   Show answer

   Answer: DEF

   Next question

9. Question 39 · 238 in bank

   Select 3

   You own table DEPARTMENTS, referenced by views, indexes, and synonyms. Examine this command which executes successfully: DROP TABLE departments PURGE; Which three statements are true? (Choose three.)

   • AIt will remove the DEPARTMENTS table from the database.
   • BIt will drop all indexes on the DEPARTMENTS table.
   • CIt will remove all views that are based on the DEPARTMENTS table.
   • DIt will remove all synonyms for the DEPARTMENTS table.
   • ENeither can it be rolled back nor can the DEPARTMENTS table be recovered.
   • FIt will delete all rows from the DEPARTMENTS table, but retain the empty table.

   Show answer

   Answer: ABE

   Explanation

   In ORACLE SQL, you own table DEPARTMENTS, referenced by views, indexes, and synonyms. Examine this command which executes successfully: DROP TABLE departments PURGE; Which three statements are true? (Choose three.) A. It will remove the DEPARTMENTS table from the database. (True) B. It will drop all indexes on the DEPARTMENTS table. (True) C. It will remove all views that are based on the DEPARTMENTS table. (False, it will not remove the views that are based on the DEPARTMENTS table) D. It will remove all synonyms for the DEPARTMENTS table. (False, it will not remove the synonyms for the DEPARTMENTS table) E. Neither can it be rolled back nor can the DEPARTMENTS table be recovered. (True) F. It will delete all rows from the DEPARTMENTS table, but retain the empty table. (False, it will delete all rows and the table itself)

   Source: Adapted from community discussion — subtype11

   References

   • Oracle documentation
   • Oracle documentation

   Next question

10. Question 40 · 238 in bank

    Select 2

    In your session NLS_DATE_FORMAT is set to DD-MON-RR. Which two queries display the year as four digits? (Choose two.)

    • ASELECT TO_DATE(SYSDATE, 'RRRR-MM-DD') FROM DUAL;
    • BSELECT TO_CHAR(SYSDATE, MM/DD/YYYY) FROM DUAL;
    • CSELECT TO_DATE(ADD_MONTHS(SYSDATE, 6), 'dd-non-yyyy') FROM DUAL;
    • DSELECT TO_CHAR(ADD_MONTHS(SYSDATE, 6)) FROM DUAL;
    • ESELECT TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'dd-mon-yyyy') FROM DUAL;
    • FSELECT TO_DATE(TO_CHAR(SYSDATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') FROM DUAL;

    Show answer

    Answer: BE

    Explanation

    B is not correct, because the date format 'MM/DD/YYYY' should be enclosed in single quotes, otherwise it's a syntax error.

    Source: Adapted from community discussion — salander71

    Next question

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

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