1Z0-071 practice questions 161–170

1. Question 161 · 238 in bank

   Which statement will return the last sequence number generated by the EMP_SEQ sequence?

   • ASELECT CURRVAL FROM emp_seq;
   • BSELECT emp_seq.CURRVAL FROM DUAL;
   • CSELECT NEXTVAL FROM emp_seq;
   • DSELECT emp_seq.NEXTVAL FROM DUAL;

   Show answer

   Answer: B

   References

   • Oracle documentation

   Next question

2. Question 162 · 238 in bank

   Select 3

   Which three statements are true about single-row functions? (Choose three.)

   • AThey return a single result row per table.
   • BThey can be nested to any level.
   • CThey can accept only one argument.
   • DThe argument can be a column name, variable, literal or an expression.
   • EThey can be used only in the WHERE clause of a SELECT statement.
   • FThe data type returned can be different from the data type of the argument.

   Show answer

   Answer: BDF

   Next question

3. Question 163 · 238 in bank

   Select 2

   Which two statements are true about single row functions? (Choose two.)

   • ATRUNC : can be used with NUMBER and DATE values
   • BFLOOR : returns the smallest integer greater than or equal to a specified number
   • CMOD : returns the quotient of a division operation
   • DCEIL : can be used for positive and negative numbers
   • ECONCAT : can be used to combine any number of values

   Show answer

   Answer: AD

   Next question

4. Question 164 · 238 in bank

   Select 2

   Examine this query: SELECT * FROM bricks, colors - Which two statements are true? (Choose two.)

   • AYou can add a USING clause with a join condition.
   • BYou can add an ON clause with a join condition.
   • CYou can add a WHERE clause with filtering criteria.
   • DIt returns the number of rows in bricks plus the number of rows in COLORS.
   • EIt returns the same rows as SELECT * FROM bricks CROSS JOIN colors;.

   Show answer

   Answer: CE

   Next question

5. Question 165 · 238 in bank

   Select 2

   Which two are true about the USING clause when joining tables? (Choose two.)

   • AIt is used to specify an equijoin of columns that have the same name in both tables.
   • BIt can never be used with a full outer join.
   • CIt can never be used with a natural join.
   • DAll column names in a USING clause must be qualified with a table name or table alias.
   • EIt is used to specify an explicit join condition involving operators.

   Show answer

   Answer: AC

   Next question

6. Question 166 · 238 in bank

   Select 2

   Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS? (Choose two.)

   • ASYSDATE can be queried only from the dual table.
   • BSYSDATE can be used in expressions only if the default date format is DD-MON-RR.
   • CCURRENT_DATE returns the current date and time as per the session time zone.
   • DCURRENT_TIMESTAMP returns the same date as CURRENT_DATE.
   • ECURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional seconds.
   • FSYSDATE and CURRENT__DATE return the current date and time set for the operating system of the database server.

   Show answer

   Answer: CD

   Next question

7. Question 167 · 238 in bank

   Select 3

   Which three are true about the CREATE TABLE command? (Choose three.)

   • AIt can include the CREATE..INDEX statement for creating an index to enforce the primary key constraint.
   • BThe owner of the table should have space quota available on the tablespace where the table is defined.
   • CIt implicitly executes a commit.
   • DIt implicitly rolls back any pending transactions.
   • EA user must have the CREATE ANY TABLE privilege to create tables.
   • FThe owner of the table must have the UNLIMITED TABLESPACE system privilege.

   Show answer

   Answer: BCE

   Next question

8. Question 168 · 238 in bank

   Select 3

   Examine this description of the PRODUCTS table:

   

   Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode. Which three commands execute successfully on PRODUCTS? (Choose three.)

   • AALTER TABLE products DROP COLUMN expiry_date;
   • BTRUNCATE TABLE products;
   • CDROP TABLE products;
   • DCREATE INDEX price_idx ON products (price);
   • EALTER TABLE products DROP UNUSED COLUMNS;
   • FALTER TABLE products SET UNUSED (expiry_date);

   Show answer

   Answer: CDE

   Next question

9. Question 169 · 238 in bank

   Select 2

   Which two statements are true about *_TABLES views? (Choose two.)

   • AUSER_TABLES displays all tables owned by the current user.
   • BYou must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in USER_TABLES.
   • CAll users can query DBA_TABLES successfully.
   • DYou must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in DBA_TABLES.
   • EALL_TABLES displays all tables owned by the current user.
   • FYou must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in ALL_TABLES.

   Show answer

   Answer: AF

   Explanation

   AD , the right answers. The data dictionary views that begin with DBA_ are restricted. These views can be accessed only by users with the SELECT_ANY_TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed. https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SRF73/ch2a.htm

   Source: Adapted from community discussion — Hrishi41

   References

   • Oracle documentation

   Next question

10. Question 170 · 238 in bank

    Select 4

    Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)

    • APrimary key and foreign key constraints can be defined at both the column and table level.
    • BA table can have only one primary key and one foreign key.
    • CThe foreign key columns and parent table primary key columns must have the same names.
    • DA table can have only one primary key but multiple foreign keys.
    • EOnly the primary key can be defined at the column and table level.
    • FIt is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted.
    • GIt is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted.

    Show answer

    Answer: ADFG

    Next question

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

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