1Z0-071 practice questions 21–30

1. Question 21 · 238 in bank

   Select 2

   Which is true about the & and && prefixes with substitution variables? (Choose all that apply.)

   • ABoth & and && can prefix a substitution variable name in queries and DML statements.
   • BAn & prefix to an undefined substitution variable, which is referenced twice in the same query, will prompt for a value twice.
   • C& can prefix a substitution variable name only in queries.
   • DAn && prefix to an undefined substitution variable, which is referenced multiple times in multiple queries, will prompt for a value once per query.
   • EThe && prefix will not prompt for a value even if the substitution variable is not previously defined in the session.

   Show answer

   Answer: AB

   References

   • Oracle documentation

   Next question

2. Question 22 · 238 in bank

   Select 2

   Examine this query:

   

   Which two methods should you use to prevent prompting for a hire date value when this query is executed? (Choose two.)

   • AUse the DEFINE command before executing the query.
   • BReplace '&1' with '&&1' in the query.
   • CUse the UNDEFINE command before executing the query.
   • DExecute the SET VERIFY OFF command before executing the query.
   • EExecute the SET VERIFY ON command before executing the query.
   • FStore the query in a script and pass the substitution value to the script when executing it.

   Show answer

   Answer: AF

   Explanation

   B. By replacing '&1' with '&&1' in the query, it creates a double-ampersand substitution variable which will remember the value of the variable throughout the session and will not prompt for a value every time it is used. F. By storing the query in a script and passing the substitution value to the script when executing it, you can provide a value for the variable before the query is executed. This prevents the query from prompting for a value when it is executed.

   Source: Adapted from community discussion — tangerine141

   Next question

3. Question 23 · 238 in bank

   Select 2

   Which two queries execute successfully? (Choose two.)

   • ASELECT INTERVAL '1' DAY - INTERVAL '1' MINUTE FROM DUAL
   • BSELECT SYSTIMESTAMP + INTERVAL '1' DAY FROM DUAL;
   • CSELECT INTERVAL '1' DAY - SYSDATE FROM DUAL;
   • DSELECT INTERVAL '1' DAY + INTERVAL '1' MONTH FROM DUAL;
   • ESELECT SYSDATE * INTERVAL '1' DAY FROM DUAL;

   Show answer

   Answer: AB

   Next question

4. Question 24 · 238 in bank

   Examine these statements which execute successfully:

   

   Examine the result:

   

   If LOCALTIMESTAMP was selected at the same time, what would it return?

   • A11-JUL-2019 6.00.00.00000000 AM -05:00
   • B11-JUL-2019 11.00.00.00000000 AM
   • C11-JUL-2019 6.00.00.00000000 AM
   • D11-JUL-2019 11.00.00.00000000 AM -05:00

   Show answer

   Answer: C

   Next question

5. Question 25 · 238 in bank

   Select 2

   Examine these statements which execute successfully:

   

   Which two are true? (Choose two.)

   • AUser FIN_CLERK can grant SELECT on SCOTT.EMP to user FIN_MANAGER.
   • BDropping user FINANCE will automatically revoke SELECT on SCOTT.EMP from user FIN_CLERK.
   • CUser FINANCE can grant CREATE SESSION to user FIN_MANAGER.
   • DRevoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK.
   • EUser FINANCE is unable to grant all on SCOTT.EMP to FIN_MANAGER.

   Show answer

   Answer: DE

   Explanation

   A, D A. User FIN_CLERK can grant SELECT on SCOTT.EMP to user FIN_MANAGER - This is true because user FINANCE has been granted SELECT privilege on SCOTT.EMP with the GRANT OPTION, and user FINANCE subsequently granted SELECT on SCOTT.EMP to user FIN_CLERK. The GRANT OPTION allows the privilege to be further granted to other users. D. Revoking SELECT on SCOTT.EMP from user FINANCE will also revoke the privilege from user FIN_CLERK - This is true because when a privilege is revoked from a user who was granted the privilege with the GRANT OPTION, and who subsequently granted that privilege to other users, then the privilege is also revoked from the other users.

   Source: Adapted from community discussion — mayman

   Next question

6. Question 26 · 238 in bank

   Select 2

   Which two are true about granting privileges on objects? (Choose two.)

   • AAn object privilege can be granted to other users only by the owner of that object.
   • BThe owner of an object acquires all object privileges on that object by default.
   • CThe WITH GRANT OPTION clause can be used only by DBA users.
   • DAn object privilege can be granted to a role only by the owner of that object.
   • EA table owner must grant the REFERENCES privilege to allow other users to create FOREIGN KEY constraints using that table.

   Show answer

   Answer: BE

   Next question

7. Question 27 · 238 in bank

   Select 2

   Examine the description of the EMPLOYEES table:

   

   Which two queries will execute successfully? (Choose two.)

   • ASELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id HAVING hire_date > '01-JAN-19';
   • BSELECT dept_id, SUM(salary) FROM employees WHERE hire_date > '01-JAN-19' GROUP BY dept_id;
   • CSELECT dept_id, MAX(SUM(salary)) FROM employees GROUP BY dept_id;
   • DSELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;
   • ESELECT AVG(MAX(salary)) FROM employees GROUP BY salary;

   Show answer

   Answer: BE

   Explanation

   I think answer B is the only correct answer because In SQL, a group function operates on a set of rows and returns a single value for each group. Group functions include AVG, COUNT, MAX, MIN, and SUM. These functions cannot be nested within one another. For example, in query A, the AVG function is nested within the MAX function which is not allowed. Similarly, in query C, D and E, the MAX function is nested within the AVG function which is also not allowed. What do you think?

   Source: Adapted from community discussion — CyberP

   Next question

8. Question 28 · 238 in bank

   Select 2

   Which two statements are true about the rules of precedence for operators? (Choose two.)

   • AThe + binary operator has the highest precedence in an expression in a SQL statement.
   • BThe concatenation operator || is always evaluated before addition and subtraction in an expression.
   • CMultiple parentheses can be used to override the default precedence of operators in an expression.
   • DNULLS influence the precedence of operators in an expression.
   • EArithmetic operators with equal precedence are evaluated from left to right within an expression.

   Show answer

   Answer: CE

   Next question

9. Question 29 · 238 in bank

   Examine this query which executes successfully:

   

   What will be the result?

   • AIt will return rows from both SELECT statements including duplicate rows.
   • BIt will return rows from both SELECT statements after eliminating duplicate rows.
   • CIt will return rows that are not common to both SELECT statements.
   • DIt will return rows common to both SELECT statements.

   Show answer

   Answer: A

   Next question

10. Question 30 · 238 in bank

    You need to allow user ANDREW to: 1. Modify the TITLE and ADDRESS columns of your CUSTOMERS table. 2. GRANT that permission to other users. Which statement will do this?

    • AGRANT UPDATE ON customers.title, customers.address TO andrew;
    • BGRANT UPDATE (title, address) ON customers TO andrew;
    • CGRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION;
    • DGRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;
    • EGRANT UPDATE ON customers.title, customers.address TO andrew WITH GRANT OPTION;
    • FGRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;

    Show answer

    Answer: C

    References

    • Oracle documentation

    Next question

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

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