1Z0-071 practice questions 191–200

1. Question 191 · 238 in bank

   Select 3

   Which three statements are true about Oracle synonyms? (Choose three.)

   • AA synonym cannot be created for a PL/SQL package.
   • BA synonym can be available to all users.
   • CA SEQUENCE can have a synonym.
   • DAny user can drop a PUBLIC synonym.
   • EA synonym created by one user can refer to an object belonging to another user.

   Show answer

   Answer: BCE

   Next question

2. Question 192 · 238 in bank

   Select 2

   Examine the description of the PRODUCT_DETAILS table:

   

   Which two statements are true? (Choose two.)

   • AEXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.
   • BPRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.
   • CPRODUCT_NAME cannot contain duplicate values.
   • DEXPIRY_DATE cannot be used in arithmetic expressions.
   • EPRODUCT_PRICE contains the value zero by default if no value is assigned to it.
   • FPRODUCT_ID can be assigned the PRIMARY KEY constraint.

   Show answer

   Answer: BF

   Next question

3. Question 193 · 238 in bank

   You create a table named 123. Which statement runs successfully?

   • ASELECT * FROM TABLE(123);
   • BSELECT * FROM "123";
   • CSELECT * FROM \'123\';
   • DSELECT * FROM '123';

   Show answer

   Answer: B

   References

   • Oracle documentation

   Next question

4. Question 194 · 238 in bank

   Select 2

   Which two statements are true regarding indexes? (Choose two.)

   • AAn update to a table can result in updates to any or all of the table's indexes.
   • BAn update to a table can result in no updates to any of the table's indexes.
   • CA UNIQUE index can be altered to be non-unique.
   • DWhen a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped.
   • EA table belonging to one user cannot have an index that belongs to a different user.

   Show answer

   Answer: AB

   Explanation

   D is worng: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9012.htm When you drop a table, any indexes on the table are dropped and put into the recycle bin along with the table. If subsequent space pressures arise, then the database reclaims space from the recycle bin by first purging indexes. In this case, when you flash back the table, you may not get back all of the indexes that were defined on the table.

   Source: Adapted from community discussion — Kuraudio

   References

   • Oracle documentation

   Next question

5. Question 195 · 238 in bank

   Select 2

   Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)? (Choose two.)

   • AThe name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.
   • BNone of the set operators can be used when selecting CLOB columns.
   • CThere must be an equal number of columns in each SELECT list.
   • DEach SELECT statement in the query can have an ORDER BY clause.
   • EThe FOR UPDATE clause cannot be specified.

   Show answer

   Answer: CE

   Explanation

   Hi, but in official doc from oracle, i found: The set operators are subject to the following restrictions: The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table. The UNION, INTERSECT, EXCEPT, and MINUS operators are not valid on LONG columns. If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause. You cannot also specify the for_update_clause with the set operators. You cannot specify the order_by_clause in the subquery of these operators. You cannot use these operators in SELECT statements containing TABLE collection expressions.

   Source: Adapted from community discussion — alelejaja

   References

   • Oracle documentation
   • Oracle documentation

   Next question

6. Question 196 · 238 in bank

   Select 3

   Which three statements are true about multiple row subqueries? (Choose three.)

   • ATwo or more values are always returned from the subquery.
   • BThey can contain HAVING clauses.
   • CThey can contain GROUP BY clauses.
   • DThey can return multiple columns.
   • EThey cannot contain a subquery.

   Show answer

   Answer: ABC

   Explanation

   A is wrong due to word ALWAYS. it is not necessary multiple row sub query always return multiple rows, it can return one row also depends on DB. BCD is correct

   Source: Adapted from community discussion — ArslanAltaf

   Next question

7. Question 197 · 238 in bank

   Select 3

   Which three actions can you perform on an existing table containing data? (Choose three.)

   • AIncrease the width of a numeric column.
   • BAdd a new column as the table's first column.
   • CDefine a default value that is automatically inserted into a column containing nulls.
   • DChange a DATE column containing data to a NUMBER data type.
   • EChange the default value of a column.
   • FAdd a new NOT NULL column with a DEFAULT value.

   Show answer

   Answer: AEF

   Explanation

   AEF B is wrong an additional column will appear at the end C is wrong default value will affect rows that will be inserted after creating default constraint

   Source: Adapted from community discussion — shotcom

   Next question

8. Question 198 · 238 in bank

   Select 2

   Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)? (Choose two.)

   • ARows from unrelated tables cannot be joined.
   • BRelating data from a table with data from the same table is implemented with a self join.
   • CImplementing a relationship between two tables might require joining additional tables.
   • DEvery relationship between the two tables must be implemented in a join condition.
   • EAn inner join relates rows within the same table.

   Show answer

   Answer: BC

   Explanation

   I thought that the option "A" for this question would apply the same reasoning as the question 50, where the option that said "PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it." was justified by saying "it doesn't make sense, it's not advisable, but it can be done".

   Source: Adapted from community discussion — Patrick_Alan

   Next question

9. Question 199 · 238 in bank

   Select 3

   Which three statements about roles are true? (Choose three.)

   • ARoles are assigned to users using the ALTER USER statement.
   • BPrivileges are assigned to a role using the GRANT statement.
   • CA role is a named group of related privileges that can only be assigned to a user.
   • DA single user can be assigned multiple roles.
   • EPrivileges are assigned to a role using the ALTER ROLE statement.
   • FRoles are assigned to roles using the ALTER ROLE statement.
   • GA single role can be assigned to multiple users.

   Show answer

   Answer: BDG

   Next question

10. Question 200 · 238 in bank

    Select 2

    The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOICE_DATE column of data type DATE. NLS_DATE_FORMAT is set to DD-MON-RR. Which two are true about data type conversions involving these columns in query expressions? (Choose two.)

    • Ainvoice_date = '15-march-2019' : uses implicit conversion
    • Bqty_sold BETWEEN '101' AND '110' : uses implicit conversion
    • Cinvoice_date > '01-02-2019' : uses implicit conversion
    • Dqty_sold = '0554982' : requires explicit conversion
    • ECONCAT (qty_sold, invoice_date) : requires explicit conversion

    Show answer

    Answer: AB

    Explanation

    A. Implicit conversion can be done as string matches the NSL_DATE_FORMAT. B. Implicit conversion to number C. Can't use implicit conversion as string doesn't match NSL_DATE_FORMAT D. Doesn't require explicit conversion E. Doesn't require explicit conversion

    Source: Adapted from community discussion — kuff

    Next question

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

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