1Z0-071 practice questions 11–20

1. Question 11 · 238 in bank

   Select 3

   Examine this constraint information:

   

   Which three statements are true? (Choose three.)

   • AThe SALARY column must have a value.
   • BThe DEPTNO column in the EMP table can contain NULLS.
   • CThe COMMISION column can contain negative values.
   • DThe DEPTNO column in the EMP table can contain the value 1.
   • EThe MANAGER column is a foreign key referencing the EMPNO column.
   • FThe DNAME column has a unique constraint.
   • GAn index is created automatically in the MANAGER column.

   Show answer

   Answer: BCE

   Explanation

   BCE is Correct A. The SALARY column must have a value. .............FALSE B. The DEPTNO column in the EMP table can contain NULLS..........TRUE C. The COMMISION column can contain negative values. ....TRUE D. The DEPTNO column in the EMP table can contain the value 1...... TRUE WITH CONDITION. (If dept table primary key has 1 values) E. The MANAGER column is a foreign key referencing the EMPNO column........ TRUE F. The DNAME column has a unique constraint. FALSE G. An index is created automatically in the MANAGER column....FALSE

   Source: Adapted from community discussion — ArslanAltaf

   Next question

2. Question 12 · 238 in bank

   Select 3

   Which three are true about scalar subquery expressions? (Choose three.)

   • AThey can be nested.
   • BThey cannot be used in the VALUES clause of an INSERT statement.
   • CA scalar subquery expression that returns zero rows evaluates to zero.
   • DThey can be used as default values for columns in a CREATE TABLE statement.
   • EA scalar subquery expression that returns zero rows evaluates to NULL.
   • FThey cannot be used in GROUP BY clauses.

   Show answer

   Answer: AEF

   Explanation

   Sorry to say I don't share your point here. D is falso since, as official doc from Oracle highlights, scalar subqueries are not valid expressions as default values for columns. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Scalar-Subquery-Expressions.html#GUID-475D80C3-C873-4475-AB1A-8837C5CF8CE4

   Source: Adapted from community discussion — alelejaja

   References

   • Oracle documentation
   • Oracle documentation
   • Oracle documentation

   Next question

3. Question 13 · 238 in bank

   Select 2

   Which two are true about creating tables in an Oracle database? (Choose two.)

   • ACreating an external table will automatically create a file using the specified directory and file name.
   • BA system privilege is required.
   • CThe same table name can be used for tables in different schemas.
   • DA primary key constraint is mandatory.
   • EA CREATE TABLE statement can specify the maximum number of rows the table will contain.

   Show answer

   Answer: BC

   Explanation

   To create a relational table in your own schema, you must have the CREATE TABLE system privilege. To create a table in another user's schema, you must have the CREATE ANY TABLE system privilege. A) is wrong because external table creates a file only if it's created with 'CREATE AS SELECT' statement

   Source: Adapted from community discussion — zangado

   Next question

4. Question 14 · 238 in bank

   Examine this partial statement: SELECT ename, sal, comm FROM emp Now examine this output:

   

   Which ORDER BY clause will generate the displayed output?

   • AORDER BY comm DESC NULLS LAST, ename
   • BORDER BY NVL(coram, 0) ASC NULLS FIRST, ename
   • CORDER BY NVL(coram, 0) ASC NULLS LAST, ename
   • DORDER BY NVL(ccmm, 0) DESC, ename

   Show answer

   Answer: A

   References

   • Oracle documentation

   Next question

5. Question 15 · 238 in bank

   Select 2

   Examine the description of the CUSTOMERS table:

   

   Which two SELECT statements will return these results: (Choose two.)

   

   • ASELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE ‘MA*’;
   • BSELECT customer_name FROM customers WHERE customer_name = ‘*Ma*’;
   • CSELECT customer_name FROM customers WHERE customer_name LIKE ‘Ma*’;
   • DSELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE ‘MA%’;
   • ESELECT customer_name FROM customers WHERE customer_name LIKE ‘%a%’;
   • FSELECT customer_name FROM customers WHERE customer_name LIKE ‘Ma%’;
   • GSELECT customer_name FROM customers WHERE customer_name LIKE ‘*Ma*’;

   Show answer

   Answer: EF

   Next question

6. Question 16 · 238 in bank

   Select 2

   The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8,2). Evaluate this SQL statement: SELECT TO_CHAR(unit_price, '$9,999') FROM product_information; Which two statements are true about the output? (Choose two.)

   • AA row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,0236.
   • BA row whose UNIT_PRICE column contains the value 1023.95 will be displayed as $1,024.
   • CA row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,023.
   • DA row whose UNIT_PRICE column contains the value 10235.95 will be displayed as #######.
   • EA row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.

   Show answer

   Answer: BD

   Next question

7. Question 17 · 238 in bank

   Select 2

   Which two statements are true about Oracle databases and SQL? (Choose two.)

   • AUpdates performed by a database user can be rolled back by another user by using the ROLLBACK command.
   • BA query can access only tables within the same schema.
   • CThe database guarantees read consistency at select level on user-created tables.
   • DA user can be the owner of multiple schemas in the same database.
   • EWhen you execute an update statement, the database instance locks each updated row.

   Show answer

   Answer: CE

   References

   • Oracle documentation
   • Oracle documentation

   Next question

8. Question 18 · 238 in bank

   Which statement is true about TRUNCATE and DELETE?

   • AFor tables with multiple indexes and triggers, DELETE is faster than TRUNCATE.
   • BYou can never TRUNCATE a table if foreign key constraints would be violated.
   • CYou can DELETE rows from a table with referential integrity constraints.
   • DFor large tables, DELETE is faster than TRUNCATE.

   Show answer

   Answer: B

   Explanation

   C. A and D: False --> Truncate is better and has better performance than delete and Drop except for very small tables. B False --> You can truncate a table with a FK on Cascade. You can disable FK and then Truncate (except if FK is self-refential).

   Source: Adapted from community discussion — aamm74200

   References

   • Oracle documentation
   • Oracle documentation

   Next question

9. Question 19 · 238 in bank

   Select 2

   Which two statements are true? (Choose two.)

   • ACASE is a function and DECODE is not.
   • BNeither CASE nor DECODE is a function.
   • CAll conditions evaluated using CASE can also be evaluated using DECODE.
   • DAll conditions evaluated using DECODE can also be evaluated using CASE.
   • EDECODE is a function and CASE is not.
   • FBoth CASE and DECODE are functions.

   Show answer

   Answer: DE

   References

   • Oracle documentation

   Next question

10. Question 20 · 238 in bank

    Select 3

    Examine these statements executed in a single Oracle session:

    

    Which three statements are true? (Choose three.)

    • AThe code for pen is 10.
    • BThere is no row containing fountain pen.
    • CThere is no row containing pen.
    • DThere is no row containing pencil.
    • EThe code for fountain pen is 3.
    • FThe code for pen is 1.

    Show answer

    Answer: ADE

    Next question

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

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