Oracle 1Z0-082 Online Practice Questions and Exam Preparation

Oracle 1Z0-082 Online Questions & Answers

• Question 101:

  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
  A. Column names in each SELECT in the compound query can be different
  D. INTERSECT returns rows common to both sides of the compound query

  ---

  Explanation/Reference:

  https://www.oracletutorial.com/oracle-basics/oracle-intersect/

• Question 102:

  The stores table has a column START_DATE of data type DATE, containing the date the row was inserted. You only want to display details of rows where START_DATE is within the last 25 months. Which where clause can be used?

  A. WHERE MONTHS_BETWEEN(SYSDATE, start_date)
  B. WHERE ADD_MONTHS(start_date, 25)
  C. WHERE TO_NUMBER(start date - SYSDATE)
  D. WHERE MONTHS_BETWEEN(start_date, SYSDATE)
  D. WHERE MONTHS_BETWEEN(start_date, SYSDATE)

• Question 103:

  Which two statements are true about the ORDER BY clause? (Choose two.)

  A. Only columns that are specified in the SELECT list can be used in the ORDER BY clause.
  B. Numeric values are displayed in descending order if they have decimal positions.
  C. NULLS are not included in the sort operation.
  D. Column aliases can be used in the ORDER BY clause.
  E. In a character sort, the values are case-sensitive.
  D. Column aliases can be used in the ORDER BY clause.
  E. In a character sort, the values are case-sensitive.

  ---

  Explanation/Reference:

• Question 104:

  Which three statements are true about sequences in a single instance Oracle database?

  A. Sequences can always have gaps.
  B. A sequence can Issue duplicate values.
  C. A sequence's unallocated cached values are lost if the instance shuts down.
  D. Two or more tables cannot have keys generated from the same sequence.
  E. A sequence can only be dropped by a DBA.
  F. A sequence number that was allocated can be rolled back if a transaction fails.
  A. Sequences can always have gaps.
  B. A sequence can Issue duplicate values.
  C. A sequence's unallocated cached values are lost if the instance shuts down.

  ---

  Explanation/Reference:

• Question 105:

  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;
  B. SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), `MONDAY') FROM employees;

  ---

  Explanation/Reference:

  "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 106:

  You need to calculate the number of days from 1st January 2019 until today.

  Dates are stored in the default format of DD-MON-RR.

  Which two queries give the required output? (Choose two.)

  A. SELECT TO_CHAR(SYSDATE,`DD-MON-YYYY') ?'01-JAN-2019' FROM DUAL;
  B. SELECT ROUND(SYSDATE ?'01-JAN-2019') FROM DUAL;
  C. SELECT ROUND(SYSDATE ?TO_DATE(`01/JANUARY/2019')) FROM DUAL;
  D. SELECT TO_DATE(SYSDATE, `DD/MONTH/YYYY') ?`01/JANUARY/2019' FROM DUAL;
  E. SELECT SYSDATE ?TO_DATE('01-JANUARY-2019') FROM DUAL;
  C. SELECT ROUND(SYSDATE ?TO_DATE(`01/JANUARY/2019')) FROM DUAL;
  E. SELECT SYSDATE ?TO_DATE('01-JANUARY-2019') FROM DUAL;

• Question 107:

  Which two statements are true regarding indexes? (Choose two.)

  A. A non-unique index can be altered to be unique.
  B. An update to a table can result in no updates to any of the table's indexes.
  C. The RECYCLE BIN never contains indexes.
  D. An update to a table can result in updates to any or all of the table's indexes.
  E. A table belonging to one user cannot have an index that belongs to a different user.
  B. An update to a table can result in no updates to any of the table's indexes.
  D. An update to a table can result in updates to any or all of the table's indexes.

  ---

  Explanation/Reference:

• Question 108:

  Which three statements are true about dropping and unused columns in an Oracle database? (Choose three.)

  A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
  B. An UNUSED column's space is reclaimed automatically when the block containing that column is next queried.
  C. An UNUSED column's space is reclaimed automatically when the row containing that column is next queried.
  D. Partition key columns cannot be dropped.
  E. A DROP COLUMN command can be rolled back
  F. A column that is set to UNUSED still counts towards the limit of 1000 columns per table
  A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
  D. Partition key columns cannot be dropped.
  F. A column that is set to UNUSED still counts towards the limit of 1000 columns per table

  ---

  Explanation/Reference:

  Answer A is RIGHT: Oders is Parent table with PRIMARY KEY ord_no, Order_items is child table which ord_no is REFERENCE KEY that reference ord_no of Parent table, now drop PRIMARY KEY on Orders by command: ALTER TABLE orders DROP COLUMN ORD_NO CASCADE CONSTRAINTS; Answer D is RIGHT: ORA-12984: cannot drop partitioning column Answer F is RIGHT: Unused Columns Count against 1000-column Table Limit Causing ORA-01792 on Compressed Table (Doc ID 2259600.1) and "ORA-01792: Maximum Number Of Columns In A Table Or View Is 1000" AND HIDDEN_COLUMN name is recreated with date and timestamp (Doc ID 2624150.1)

• Question 109:

  Which statement is true about the INTERSECT operator used in compound queries?

  A. Multiple INTERSECT operators are not possible in the same SQL statement
  B. It processes NULLs in the selected columns
  C. INTERSECT is of lower precedence than UNION or UNION ALL
  D. It ignores NULLs
  B. It processes NULLs in the selected columns

  ---

  Explanation/Reference:

  SET operators all have the same priority in Oracle Database. NULL values are processed with set operators

• Question 110:

  Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)

  A. A table can have only one primary key but multiple foreign keys
  B. A table can have only one primary key and one foreign key
  C. The foreign key columns and parent table primary key columns must have the same names
  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
  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
  F. Only the primary key can be defined at the column and table level
  G. Primary key and foreign key constraints can be defined at both the column and table level
  A. A table can have only one primary key but multiple foreign keys
  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
  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
  G. Primary key and foreign key constraints can be defined at both the column and table level

  ---

  Explanation/Reference: