1Z0-071 practice questions 171–180

1. Question 171 · 238 in bank

   Select 2

   Which two are true to create an index in your own schema for a table owned by another schema? (Choose two.)

   • AYou must have the UNLIMITED TABLESPACE privilege.
   • BYou must have either the SELECT privilege on the table or the SELECT ANY TABLE privilege.
   • CYou must have the CREATE ANY INDEX privilege.
   • DYou have the UNLIMITED TABLESPACE privilege or sufficient quota for the tablespace to contain the index.
   • EYou have either the INDEX privilege on the table or the CREATE ANY INDEX privilege.

   Show answer

   Answer: DE

   Next question

2. Question 172 · 238 in bank

   Select 2

   Which two statements are true about conditional INSERT ALL? (Choose two.)

   • AEach row returned by the subquery can be inserted into only a single target table.
   • BA single WHEN condition can be used for multiple INTO clauses.
   • CEach WHEN condition is tested for each row returned by the subquery.
   • DIt cannot have an ELSE clause.
   • EThe total number of rows inserted is always equal to the number of rows returned by the subquery.

   Show answer

   Answer: BC

   Next question

3. Question 173 · 238 in bank

   Select 2

   Which two statements are true about the COUNT function? (Choose two.)

   • ACOUNT(*) returns the number of rows in a table including duplicate rows and rows containing NULLs in any column.
   • BIt can only be used for NUMBER data types.
   • CCOUNT(DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULLs in the INV_AMT column.
   • DCOUNT(inv_amt) returns the number of rows in a table including rows with NULL in the INV_AMT column
   • EA SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

   Show answer

   Answer: AC

   Next question

4. Question 174 · 238 in bank

   Select 2

   What is true about non-equijoin statement performance? (Choose two.)

   • AThe BETWEEN condition always performs less well than using the >= and <= conditions.
   • BThe BETWEEN condition always performs better than using the >= and <= conditions.
   • CThe Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
   • DTable aliases can improve performance.
   • EThe join syntax used makes no difference to performance.

   Show answer

   Answer: DE

   Explanation

   A B incorrect because Oracle Between is converted to >= <= C Is arguable D is not true, you don't find a book saying table alias can improve performance. E is arguable C E to answer this poorly designed question

   Source: Adapted from community discussion — subtype11

   References

   • Oracle documentation
   • Oracle documentation

   Next question

5. Question 175 · 238 in bank

   The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE. You want to display the date of the first Monday after the completion of six months since hiring. The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day of the week. Which query can be used?

   • ASELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY('MONDAY') FROM employees;
   • BSELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;
   • CSELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;
   • DSELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees;

   Show answer

   Answer: D

   References

   • Oracle documentation

   Next question

6. Question 176 · 238 in bank

   Select 2

   Which two statements are true about the SET VERIFY ON command? (Choose two.)

   • AIt displays values for variables used only in the WHERE clause of a query.
   • BIt displays values for variables created by the DEFINE command.
   • CIt can be used only in SQL*Plus.
   • DIt displays values for variables prefixed with &&.
   • EIt can be used in SQL Developer and SQL*Plus.

   Show answer

   Answer: BE

   Explanation

   I think it is just BE. If I make sure the variable is undefined before I use && then I don't get the new and old references in script output.

   Source: Adapted from community discussion — jm9999

   Next question

7. Question 177 · 238 in bank

   Select 2

   Examine this list of requirements for a sequence: 1. Name: EMP_SEQ 2. First value returned: 1 3. Duplicates are never permitted. 4. Provide values to be inserted into the EMPLOYEES.EMPLOYEE_ID column. 5. Reduce the chances of gaps in the values. Which two statements will satisfy these requirements? (Choose two.)

   • ACREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CYCLE;
   • BCREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;
   • CCREATE SEQUENCE emp_seq;
   • DCREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE;
   • ECREATE SEQUENCE emp_seq NOCACHE;
   • FCREATE SEQUENCE emp_seq START WITH 1 CACHE;

   Show answer

   Answer: DE

   Explanation

   if there is cache option sequence get some numbers (for example 10). if oracle used some of this (less than 10) and restart instance occurs, then will be gap, because oracle will cache next 10 numbers.

   Source: Adapted from community discussion — mikom80

   Next question

8. Question 178 · 238 in bank

   Select 3

   Which three queries execute successfully? (Choose three.)

   • ASELECT 1 - SYSDATE - DATE '2019-01-01' FROM DUAL;
   • BSELECT SYSDATE - DATE '2019-01-01' - 1 FROM DUAL;
   • CSELECT SYSDATE / DATE '2019-01-01' - 1 FROM DUAL;
   • DSELECT SYSDATE - 1 - DATE '2019-01-01' FROM DUAL;
   • ESELECT (SYSDATE - DATE '2019-01-01') / 1 FROM DUAL;
   • FSELECT 1 / SYSDATE - DATE '2019-01-01' FROM DUAL;

   Show answer

   Answer: BDE

   Next question

9. Question 179 · 238 in bank

   Select 2

   Which two are true about granting object privileges on tables, views, and sequences? (Choose two.)

   • AINSERT can be granted only on tables and sequences.
   • BDELETE can be granted on tables, views, and sequences.
   • CSELECT can be granted on tables, views, and sequences.
   • DALTER can be granted only on tables and sequences.
   • EREFERENCES can be granted only on tables.

   Show answer

   Answer: CD

   Explanation

   The following OBJECT PRIV'S can be granted on tables, views and sequences--- UPDATE: tables, views. REFERENCES: tables, views ALTER: tables, sequences. SELECT: tables, views, sequences

   Source: Adapted from community discussion — holdfaststrongly

   References

   • Oracle documentation

   Next question

10. Question 180 · 238 in bank

    Select 2

    Examine the description of the BOOKS table:

    

    The table has 100 rows. Examine this sequence of statements issued in a new session: INSERT INTO books VALUES ('ADV112', 'Adventures of Tom Sawyer', NULL, NULL); SAVEPOINT a; DELETE FROM books; ROLLBACK TO SAVEPOINT a; ROLLBACK; Which two statements are true? (Choose two.)

    • AThe second ROLLBACK command replays the delete.
    • BThe first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.
    • CThe first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.
    • DThe second ROLLBACK command undoes the insert.
    • EThe second ROLLBACK command does nothing.

    Show answer

    Answer: CD

    Next question

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

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