1Z0-082 practice questions 111–120

1. Question 111 · 167 in bank

   Select 2

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

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

   Show answer

   Answer: DE

   Explanation

   DE are correct. Precedence Precedence is the order in which Oracle evaluates different operators in the same expression. When evaluating an expression containing multiple operators, Oracle evaluates operators with higher precedence before evaluating those with lower precedence. Oracle evaluates operators with equal precedence from left to right within an expression. https://docs.oracle.com/cd/A87860_01/doc/server.817/a85397/operator.htm

   Source: Adapted from community discussion — Trowa

   References

   • Oracle documentation
   • Oracle documentation

   Next question

2. Question 112 · 167 in bank

   In one of your databases, user KING is: 1. Not a DBA user 2. An operating system (OS) user Examine this command and its output:

   

   What must you do so that KING is authenticated by the OS when connecting to the database instance?

   • ASet OS_AUTHENT_PREFIX to OPS$
   • BHave the OS administrator add KING to the OSDBA group
   • CGrant DBA to KING
   • DUnset REMOTE_LOGIN_PASSWORDFILE
   • EAlter user KING to be IDENTIFIED EXTERNALLY

   Show answer

   Answer: E

   Explanation

   E is the correct one: https://docs.oracle.com/cd/B19306_01/network.102/b14266/admnauth.htm#i1006459 Creating a User Who Is Authenticated Externally The following statement creates a user who is identified by Oracle and authenticated by the operating system or a network service. This example assumes that OS_AUTHENT_PREFIX = "". CREATE USER scott IDENTIFIED EXTERNALLY; Using CREATE USER ... IDENTIFIED EXTERNALLY, you create database accounts that must be authenticated by the operating system or network service. Oracle will then rely on this external login authentication when it provides that specific operating system user with access to the database resources of a specific user.

   Source: Adapted from community discussion — eddkzew

   References

   • Oracle documentation
   • Oracle documentation

   Next question

3. Question 113 · 167 in bank

   A database is configured to use automatic undo management with temporary undo enabled. An UPDATE is executed on a temporary table. Where is the UNDO stored?

   • Ain the undo tablespace
   • Bin the SYSAUX tablespace
   • Cin the SGA
   • Din the PGA
   • Ein the temporary tablespace

   Show answer

   Answer: E

   Explanation

   Funny how the reference they put up supports answer E and not answer A: 16.7 Managing Temporary Undo By default, undo records for temporary tables are stored in the undo tablespace and are logged in the redo, which is the same way undo is managed for persistent tables. However, you can use the TEMP_UNDO_ENABLED initialization parameter to separate undo for temporary tables from undo for persistent tables. When this parameter is set to TRUE, the undo for temporary tables is called temporary undo. 16.7.1 About Managing Temporary Undo Temporary undo records are stored in the database's temporary tablespaces and thus are not logged in the redo log. When temporary undo is enabled, some of the segments used by the temporary tablespaces store the temporary undo, and these segments are called temporary undo segments. The correct answer is: E

   Source: Adapted from community discussion — adoptc94

   Next question

4. Question 114 · 167 in bank

   View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables. You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a promotion time interval. Examine this SQL statement:

   

   Exhibit 1.

   

   Exhibit 2.

   

   What will be the result?

   • AIt gives an error because the ALL keyword is not valid
   • BIt gives an error because the GROUP BY clause is not valid
   • CIt executes successfully but does not give the required result
   • DIt executes successfully and gives the required result

   Show answer

   Answer: D

   Explanation

   C- correct answer display PROD_IDS whose promotion cost is less than the highest cost PROD_ID WHERE promo_cost < ALL (SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date - promo_begin_date))); “< ALL …” here is the mistake, if the next selection returns not only one max value, it returns the wrong, maybe even an empty result

   Source: Adapted from community discussion — NowOrNever

   Next question

5. Question 115 · 167 in bank

   Select 3

   Which three statements are true about GLOBAL TEMPORARY TABLES? (Choose three.)

   • AA TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted.
   • BGLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table.
   • CGLOBAL TEMPORARY TABLE space allocation occurs at session start.
   • DAny GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted.
   • EA GLOBAL TEMPORARY TABLE'S definition is available to multiple sessions.
   • FA DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back.

   Show answer

   Answer: ADE

   Explanation

   A. GLOBAL TEMPORARY TABLE rows inserted by a session are available to any other session whose user has been granted select on the table -> FALSE, you cant view data from other session B. GLOBAL TEMPORARY TABLE space allocation occurs at session start. -> AT First dml sentence, then space is allocated ¿false? C. A DELETE command on a GLOBAL TEMPORARY TABLE cannot be rolled back. -> Depends. If you create with on commit preserve rows, you can rollback and commit. -> FALSE D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions. -> Yes, with the appropiate permisions ¿TRUE? E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted. -> TRUE F. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted. -> TRUE, truncate delete rows from table ¿D,E,F? Very well presented explanations from Primisser https://www.examtopics.com/discussions/oracle/view/8895-exam-1z0-071-topic-1-question-298-discussion/

   Source: Adapted from community discussion — saif_alrwiliy

   References

   • Oracle documentation

   Next question

6. Question 116 · 167 in bank

   Select 2

   Which two statements are true about trace files produced by the Oracle Database server? (Choose two.)

   • AThey can be written by server processes
   • BTrace files are written to the Fast Recovery Area (FRA)
   • CThey can be written by background processes
   • DAll trace files contain error information that require contacting Oracle Support
   • ETrace file names are based on the database name concatenated with a sequential number

   Show answer

   Answer: AC

   Explanation

   Reference: https://gerardnico.com/db/oracle/trace_file

   Source: Adapted from community discussion — GraceYu

   References

   • Oracle documentation

   Next question

7. Question 117 · 167 in bank

   Select 2

   st You need to calculate the number of days from 1 January 2019 until today. Dates are stored in the default format of DD-MON-RR. Which two queries give the required output? (Choose two.)

   • ASELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') ג€" '01-JAN-2019' FROM DUAL;
   • BSELECT ROUND(SYSDATE ג€" '01-JAN-2019') FROM DUAL;
   • CSELECT ROUND(SYSDATE ג€" TO_DATE('01/JANUARY/2019')) FROM DUAL;
   • DSELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') ג€" '01/JANUARY/2019' FROM DUAL;
   • ESELECT SYSDATE ג€" TO_DATE('01-JANUARY-2019') FROM DUAL;

   Show answer

   Answer: CE

   Next question

8. Question 118 · 167 in bank

   Select 2

   Which two statements are true about the DUAL table? (Choose two.)

   • AIt can be accessed only by the SYS user
   • BIt consists of a single row and single column of VARCHAR2 data type
   • CIt can display multiple rows but only a single column
   • DIt can be used to display only constants or pseudo columns
   • EIt can be accessed by any user who has the SELECT privilege in any schema
   • FIt can display multiple rows and columns

   Show answer

   Answer: BF

   Explanation

   B and F are correct answers. F is correct : SQL> SELECT level, sysdate 2 FROM dual 3 CONNECT BY 4 level <= 4; LEVEL SYSDATE ---------- --------- 1 06-AUG-20 2 06-AUG-20 3 06-AUG-20 4 06-AUG-20 It can return multple rows and columns. D is wrong, you just create a brand new user and do not grant any privilege but still it can select dual table.

   Source: Adapted from community discussion — Sharif1

   References

   • Oracle documentation
   • Oracle documentation

   Next question

9. Question 119 · 167 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.)

   • AA table can have only one primary key but multiple foreign keys
   • 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
   • DIt is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted
   • EIt 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
   • FOnly the primary key can be defined at the column and table level
   • GPrimary key and foreign key constraints can be defined at both the column and table level

   Show answer

   Answer: ADEG

   Explanation

   ADEG in my opinion too... A. A table can have only one primary key but multiple foreign keys --> For sure B. A table can have only one primary key and one foreign key --> There is no such restrictions C. The foreign key columns and parent table primary key columns must have the same names --> There is no such restriction. D. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted --> Can be achieved by disabling the FK constraint and re enabling with no validate clause after performing delete on parent table. E. It 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 --> CASCADE option F. Only the primary key can be defined at the column and table level --> No such restrictions G. Primary key and foreign key constraints can be defined at both the column and table level --> True.

   Source: Adapted from community discussion — avanand

   Next question

10. Question 120 · 167 in bank

    Examine the description of the EMPLOYEES table:

    

    Which query is valid?

    • ASELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;
    • BSELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;
    • CSELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;
    • DSELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;

    Show answer

    Answer: C

    Explanation

    A (FALSE). SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id; select DEPARTMENT_ID, AVG(MAX(SALARY)) from EMPLOYEES group by DEPARTMENT_ID; ERROR at line 1: ORA-00937: not a single-group group function B (FALSE). SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id; select DEPARTMENT_ID, HIRE_DATE,sum(salary) from EMPLOYEES group by DEPARTMENT_ID * ERROR at line 1: ORA-00979: not a GROUP BY expression C (TRUE). SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date; SQL> select DEPARTMENT_ID, HIRE_DATE,SUM(SALARY) from EMPLOYEES group by DEPARTMENT_ID , HIRE_DATE; DEPARTMENT_ID HIRE_DATE SUM(SALARY) ------------- --------- ----------- 60 25-JUN-05 4800 D (FALSE). SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id; SELECT DEPARTMENT_ID , MAX(AVG(salary)) FROM employees GROUP BY DEPARTMENT_ID * ERROR at line 1: ORA-00937: not a single-group group function

    Source: Adapted from community discussion — FelipeC

    Next question

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

1Z0-082 practice, question 111. 167 questions available.