Oracle Oracle Database 1Z0-082 Questions & Answers

• Question 51:

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

  A. The concatenation operator | | is always evaluated before addition and subtraction in an expression

  B. NULLS influence the precedence of operators in an expression

  C. The + binary operator has the highest precedence in an expression in a SQL statement

  D. Arithmetic operators with equal precedence are evaluated from left to right within an expression

  E. Multiple parentheses can be used to override the default precedence of operators in an expression

  Correct Answer: DE

  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

• Question 52:

  Which three statements are true about the Oracle Data Dictionary? (Choose three.)

  A. Data dictionary views are created by joins of dictionary base tables and DBA-defined tables

  B. The data dictionary is created and maintained by the database administrator

  C. Views with the same name but different prefixes, such as CDB, DBA, ALL and USER, reference the same base tables from the data dictionary

  D. Base tables can be queried directly

  E. It is owned by the SYSTEM user F. Usernames of all users including database administrators are stored in the data dictionary

  Correct Answer: CDF

  https://docs.oracle.com/cd/B28359_01/server.111/b28318/datadict.htm#CNCPT1210

• Question 53:

  In one of your databases, the user HR has the password HRMGR.

  You want to connect to a database instance whose listener listens on port 1531 by using this statement:

  CONNECT HR/HRMGR@orcl

  No name server is used.

  Which statement is true about ORCL?

  A. It must be the value of the SERVICE_NAMES parameter on the client side

  B. It must resolve to a valid connect descriptor in the server's tnsnames.ora file

  C. It must resolve to a valid connect descriptor in the client's tnsnames.ora file

  D. It must be the name of the database to whose instance HR wishes to connect

  E. It must be the name of the server running the database to whose instance HR wishes to connect

  Correct Answer: C

• Question 54:

  Which two statements are true about views used for viewing tablespace and datafile information? (Choose two.)

  A. Tablespace free space can be viewed in V$TABLESPACE

  B. V$TABLESPACE displays information that is contained in the controlfile about tablespaces

  C. V$TABLESPACE displays information about tablespaces contained in the data dictionary

  D. Tablespace free space can be viewed in DBA_TABLESPACES

  E. A datafile can be renamed when the database is in MOUNT state and the new file name is displayed when querying DBA_DATA_FILES after the database is opened

  Correct Answer: BE

  SQL> shutdown immediate;

  Database closed.

  E correct.

  Database dismounted.

  ORACLE instance shut down.

  SQL> startup mount;

  ORACLE instance started.

  Total System Global Area 1375731600 bytes

  Fixed Size 8896400 bytes

  Variable Size 838860800 bytes

  Database Buffers 520093696 bytes

  Redo Buffers 7880704 bytes

  Database mounted.

  SQL> alter database move datafile '/oradata/ORA19/testing1.dbf' to '/oradata/ORA19/testing01.dbf';

• Question 55:

  Which two statements are true about the results of using the INTERSECT operator in compound queries? (Choose two.)

  A. Column names in each SELECT in the compound query can be different

  B. The number of columns in each SELECT in the compound query can be different

  C. Reversing the order of the intersected tables can sometimes affect the output

  D. INTERSECT returns rows common to both sides of the compound query

  E. INTERSECT ignores NULLs

  Correct Answer: AD

• Question 56:

  Which two statements are true about single row functions? (Choose two.)

  A. MOD : returns the quotient of a division operation

  B. FLOOR : returns the smallest integer greater than or equal to a specified number

  C. TRUNC : can be used to combine any number of values

  D. CONCAT : can be used to combine any number of values

  E. CEIL : can be used for positive and negative numbers

  Correct Answer: CE

  Reference: https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Single-RowFunctions.html#GUID-B93F789D-B486-49FF-B0CD-0C6181C5D85C

• Question 57:

  The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type

  DATE.

  You want to display the date of the first Monday after the completion of six months since hiring.

  The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day

  on the week.

  Which query can be used?

  A. SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY(`MONDAY') FROM employees;

  B. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), `MONDAY') FROM employees;

  C. SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;

  D. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

  Correct Answer: B

  "The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day

  of the week."

  1 is Sunday in that case.

  ALTER SESSION SET NLS_TERRITORY = "AMERICA";

  SELECT employee_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 'MONDAY') FROM employees; Result = 22-DEC-03 (Monday)

  SELECT employee_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees; Result = 21-DEC-03 (Sunday)

• Question 58:

  The ORCL database has RESUMABLE__TIMEOUT = 7200 and DEFERRED_SEGMENT_CREATION =

  FALSE

  User U1 has a 1 MB quota in tablespace DATA.

  U1 executes this command:

  SQL> CREATE TABLE t1 AS

  (SELECT object_name, sharing, created

  FROM dba_objects);

  U1 complains that the command is taking too long to execute.

  In the alert log, the database administrator (DBA) finds this:

  2017-03-06T12:15:17.183438+05:30

  statement in resumable session `User U1(136), Session 1, Instance 1' was suspended due to ORA-01536:

  space quota exceeded for tablespace `DATA'

  Which are three actions any one of which the DBA could take to resume the session? (Choose three.)

  A. Add a data file to DATA

  B. Drop other U1 objects in DATA

  C. Increase U1's quota sufficiently in DATA

  D. Set DEFERRED_SEGMENT_CREATION to TRUE

  E. Grant UNLIMITED TABLESPACE to U1

  F. Set AUTOEXTEND ON for data files in DATA

  Correct Answer: BCE

  Set DEFERRED_SEGMENT_CREATION to TRUE, when creating the table you are already inserting rows as select, therefore the segment has already been created the D is not.

  Configure Set AUTOEXTEND ON for data files in DATA, if you have 1M quota it will not work.

  A. Add a data file to DATA -- Won't help as problem is quota to user not the space

  B. Drop other U1 objects in DATA -- Will help as it will reduce the used space from quota for that user

  C. Increase U1's quota sufficiently in DATA -- Will help.

  D. Set DEFERRED_SEGMENT_CREATION to TRUE -- Won't help, segments has to be created as its CTAS

  E. Grant UNLIMITED TABLESPACE to U1 -- Will help.

  F. Set AUTOEXTEND ON for data files in DATA -- Won't help as problem is quota to user not the space

• Question 59:

  Which three statements are true about the DESCRIBE command? (Choose three.)

  A. It displays the PRIMARY KEY constraint for any column or columns that have that constraint

  B. It can be used from SQL Developer

  C. It displays the NOT NULL constraint for any columns that have that constraint

  D. It can be used to display the structure of an existing view

  E. It displays all constraints that are defined for each column

  F. It can be used only from SQL*Plus

  Correct Answer: BCD

• Question 60:

  Examine the description of the CUSTOMERS table:

  

  For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.

  Which query should be used?

  A. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers

  WHERE cust_income_level IS NOT NULL

  AND due_amount IS NOT NULL;

  B. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND cust_credit_level !=NULL;

  C. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level <> NULL AND due_amount <> NULL;

  D. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND due_amount != NULL;

  E. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;

  Correct Answer: E