Oracle Oracle Certifications 1Z0-082 Questions & Answers

• Question 71:

  Which three statements are true regarding single row subqueries?

  A. They must be placed on the right side of the comparison operator or condition.

  B. They must be placed on the left side of the comparison operator or condition.

  C. They can be used in the where clause.

  D. A SQL statement may have multiple single row subquery blocks.

  E. They must return a row to prevent errors in the SQL statement.

  F. They can be used in the having clause.

  Correct Answer: CDF

• Question 72:

  Which three files are used by conventional path SQL*Loader when the TABLE option is not specified? (Choose three.)

  A. dump files

  B. control files

  C. password files

  D. bad files

  E. input files

  Correct Answer: BDE

  SQLLDR CONTROL=sample.ctl, LOG=sample.log, BAD=baz.bad, DATA=etc.dat USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dsc, DISCARDMAX=5

  https://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_params.htm

• Question 73:

  View the Exhibit and examine the structure of the PRODUCTS table.

  Which two tasks require subqueries? (Choose two.)

  

  A. Display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE

  B. Display suppliers whose PROD_LIST_PRICE is less than 1000

  C. Display products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable

  D. Display the total number of products supplied by supplier 102 which have a product status of obsolete

  E. Display the minimum PROD_LIST_PRICE for each product status

  Correct Answer: AC

• Question 74:

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

  A. It can be accessed only by the SYS user

  B. It consists of a single row and single column of VARCHAR2 data type

  C. It can display multiple rows but only a single column

  D. It can be used to display only constants or pseudo columns

  E. It can be accessed by any user who has the SELECT privilege in any schema

  F. It can display multiple rows and columns

  Correct Answer: BF

  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.

  Reference: https://en.wikipedia.org/wiki/DUAL_table

• Question 75:

  The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER. Which two queries execute successfully? (Choose two.)

  A. SELECT NVL(cust_credit_limit * .15, `Not Available') FROM customers;

  B. SELECT NVL2(cust_credit_limit * .15, `Not Available') FROM customers;

  C. SELECT NVL(TO_CHAR(cust_credit_limit * .15), `Not Available') FROM customers;

  D. SELECT TO_CHAR(NVL(cust_credit_limit * .15, `Not Available')) FROM customers;

  E. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), `Not Available') FROM customers;

  Correct Answer: CE

  A) SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;

  B) SELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers;

  C) SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;

  D) SELECT TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) FROM customers;

  E) SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;

• Question 76:

  Examine the description of the product_details table:

  

  Which two statements are true?

  A. PRODUCT_ID can be assigned the PRIMARY KEY constraint.

  B. PRODUCT_PRICE contains the value zero by default if no value is assigned to it.

  C. PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it.

  D. EXPIRY_DATE contains the SYSDATE by default if no date is assigned to it.

  E. PRODUCT_NAME cannot contain duplicate values.

  F. EXPIRY_DATE cannot be used in arithmetic expressions.

  Correct Answer: AC

• Question 77:

  Which two statements are true about the Automatic Diagnostic Repository (ADR)? (Choose two.)

  A. The ADR base defaults to $ORACLE_HOME/rdbms/admin ifneither DIAGNOSTIC_DEST nor ORACLE_BASE is set

  B. The ADR base defaults to $ORACLE_HOME/dbs if the DIAGNOSTIC_DEST parameter and the ORACLE_BASE environment variable are not set

  C. It supports diagnostics for Automatic Storage Management (ASM)

  D. It supports diagnostics for Oracle Clusterware

  E. It is held inside an Oracle database schema

  Correct Answer: CD

  The database, Oracle Automatic Storage Management (Oracle ASM), the listener, Oracle Clusterware, and other Oracle products or components store all diagnostic data in the ADR.

  https://docs.oracle.com/database/121/ADMIN/diag.htm#ADMIN11260

• Question 78:

  Examine the description of the EMPLOYEES table:

  

  Which query is valid?

  A. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;

  B. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;

  C. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;

  D. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;

  Correct Answer: C

• Question 79:

  Which three statements are true about data block storage in an Oracle Database?

  A. A block header contains a row directory pointing to all rows in the block.

  B. An index block can contain row data.

  C. Row data is stored starting at the end of the block.

  D. A data block header is of a fixed length.

  E. A table block must always contain row data.

  Correct Answer: ABC

• Question 80:

  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?

  A. in the undo tablespace

  B. in the SYSAUX tablespace

  C. in the SGA

  D. in the PGA

  E. in the temporary tablespace

  Correct Answer: E

  Oracle database 12c Release 1 (12.1) introduced the concept of temporary undo, allowing the undo segments for global temporary tables to be stored in the temporary tablespace. This allows global temporary tables to be used in physical standby databases and read-only databases, as well as removing the need to create redo.

  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.

  Reference: http://www.dba-oracle.com/t_temp_undo_enabled.htm Reference: https://docs.oracle.com/database/121/ADMIN/undo.htm#ADMIN11479