1Z0-071 practice questions 41–50

1. Question 41 · 238 in bank

   Select 2

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

   • AAfter issuing a savepoint, you can roll back to the savepoint name within the current transaction.
   • BThey make uncommitted updates visible to sessions owned by other users.
   • CYou can commit updates done between two savepoints without committing other updates in the current transaction.
   • DA ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error.
   • EThey make uncommitted updates visible to other sessions owned by the same user.
   • FAfter issuing a savepoint, you cannot roll back the complete transaction.

   Show answer

   Answer: AD

   Next question

2. Question 42 · 238 in bank

   Select 3

   Examine these statements executed in a single Oracle session:

   

   Which three statements are true? (Choose three.)

   • AThere is no row containing pencil.
   • BThe code for pen is 10.
   • CThere is no row containing fountain pen.
   • DThe code for pen is 1.
   • EThere is no row containing pen.
   • FThe code for fountain pen is 3.

   Show answer

   Answer: ABF

   Next question

3. Question 43 · 238 in bank

   Select 2

   The ORDERS table has a column ORDER_DATE of data type DATE. The default display format for a date is DD-MON-RR. Which two WHERE conditions demonstrate the correct usage of conversion functions? (Choose two.)

   • AWHERE order_date > TO_DATE(‘JUL 10 2018’, ‘ MON DD YYYY’)
   • BWHERE order_date > TO_DATE(ADD_MONTH(SYSDATE, 6), ‘MON DD YYYY’)
   • CWHERE order_date IN (TO_DATE(‘Oct 21 2018’, ‘Mon DD YYYY’), TO_CHAR(‘Nov 21 2018’, ‘Mon DD YYYY’))
   • DWHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), ‘MON DD YYYY’)
   • EWHERE TO_CHAR(order_date. ‘MON DD YYYY’) = ‘JAN 20 2019’

   Show answer

   Answer: AE

   Next question

4. Question 44 · 238 in bank

   Examine this query:

   

   What is the result?

   • Aan error
   • Bno rows
   • C1 row
   • D3 rows
   • E6 rows
   • F8 rows

   Show answer

   Answer: B

   Next question

5. Question 45 · 238 in bank

   Select 2

   Which two object privileges can be restricted to a subset of columns in a table? (Choose two.)

   • AINDEX
   • BALTER
   • CUPDATE
   • DINSERT
   • EDELETE

   Show answer

   Answer: CD

   Next question

6. Question 46 · 238 in bank

   Select 3

   View the Exhibit and examine the description of the tables. You execute this SQL statement:

   

   Which three statements are true? (Choose three.)

   • AThe statement will execute successfully and a new row will be inserted into the SALES table.
   • BA product can have a different unit price at different times.
   • CThe statement will fail if a row already exists in the SALES table for product 23.
   • DThe statement will fail because a subquery may not be contained in a VALUES clause.
   • EA customer can exist in many countries.
   • FThe SALES table has five foreign keys.

   Show answer

   Answer: ABF

   Next question

7. Question 47 · 238 in bank

   Select 3

   Which three statements are true about an ORDER BY clause? (Choose three.)

   • ABy default an ORDER BY clause sorts rows in descending order
   • BAn ORDER BY clause will always precede a HAVING clause if both are used in the same top-level query.
   • CAn ORDER BY clause always sorts NULL values last.
   • DBy default an ORDER BY clause sorts rows in ascending order.
   • EAn ORDER BY clause can perform a binary sort.
   • FAn ORDER BY clause can perform a linguistic sort.

   Show answer

   Answer: DEF

   Explanation

   Shouldn't it be DEF? A - FALSE; by default sorting is ASC B - FALSE; In both syntax and execution ORDER BY is always after HAVING C - FALSE; NULLS FIRST can be used to sort NULL values first D - TRUE; E, F - TRUE; parameter NLS_SORT can be changed between BINARY and linguistic_definition. https://docs.oracle.com/database/121/REFRN/GUID-04243CDA-43CA-4AF8-B59D-6FB841B307BE.htm#REFRN10127

   Source: Adapted from community discussion — Darnun

   References

   • Oracle documentation

   Next question

8. Question 48 · 238 in bank

   Select 3

   Examine the description of EMPLOYEES table:

   

   Which three queries return all rows for which SALARY + COMMISSION is greater than 20000? (Choose three.)

   • ASELECT * FROM employees WHERE NVL2(salary + commission, salary + commission, salary) >= 20000;
   • BSELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >= 20000
   • CSELECT * FROM employees WHERE NVL(salary + commission, 0) >= 20000;
   • DSELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000;
   • ESELECT * FROM employees WHERE COALESCE(salary, commission) >= 20000;
   • FSELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;

   Show answer

   Answer: ABF

   Next question

9. Question 49 · 238 in bank

   Select 3

   Which three statements are true about Structured Query Language (SQL)? (Choose three.)

   • AIt requires that data be contained in hierarchical data storage.
   • BIt best supports relational databases.
   • CIt provides independence for logical data structures being manipulated from the underlying physical data storage.
   • DIt is the only language that can be used for both relational and object-oriented databases.
   • EIt guarantees atomicity, consistency, isolation, and durability (ACID) features.
   • FIt is used to define encapsulation and polymorphism for a relational table.

   Show answer

   Answer: BCE

   References

   • Oracle documentation

   Next question

10. Question 50 · 238 in bank

    Select 2

    Examine the description of EMPLOYEES table:

    

    The session time zone is the same as the database server. Which two statements will list only the employees who have been working with the company for more than five years? (Choose two.)

    • ASELECT employee_name FROM employees WHERE (SYSDATE - hire_date) / 12 > 5;
    • BSELECT employee_name FROM employees WHERE (SYSTIMSSTAMF - hire_date) / 12 > INTERVAL '5' YEAR;
    • CSELECT employee_name FROM employees WHERE (CURRENT_DATE - hire_date) / 12 > 5
    • DSELECT employee_name FROM employees WHERE (CURRENT_DATE - hire_date) / 365 >
    • ESELECT employee_name FROM employees WHERE (SYSDATE - hire_date) / 365 > 5;
    • FSELECT employee_name FROM employees WHERE (SYSTIMESTAMP - hire_date) / 365 > INTERVAL '1825' DAY;

    Show answer

    Answer: DE

    Explanation

    DE is correct (even though D seems to be with typo). F is not correct as default precision of DAY is 2 - should be INTERVAL '1845' DAY(4)

    Source: Adapted from community discussion — Darnun

    Next question

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

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