Oracle 1Z0-082 Online Practice Questions and Exam Preparation

Oracle 1Z0-082 Online Questions & Answers

• Question 21:

  Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

  A. Aggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query
  B. WHERE and HAVING clauses can be used in the same statement only if applied to different table columns
  C. The HAVING clause can be used with aggregating functions in subqueries
  D. The WHERE clause can be used to exclude rows before dividing them into groups
  E. The WHERE clause can be used to exclude rows after dividing them into groups
  C. The HAVING clause can be used with aggregating functions in subqueries
  D. The WHERE clause can be used to exclude rows before dividing them into groups

• Question 22:

  Examine the description of the BOOKS table:

  

  The table has 100 rows.

  Examine this sequence of statements issued in a new session:

  INSERT INTO books VALUES (`ADV112', `Adventures of Tom Sawyer', NULL, NULL);

  SAVEPOINT a;

  DELETE FROM books;

  ROLLBACK TO SAVEPOINT a;

  ROLLBACK;

  Which two statements are true? (Choose two.)

  A. The second ROLLBACK command does nothing
  B. The second ROLLBACK command replays the delete
  C. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed
  D. The second ROLLBACK command undoes the insert
  E. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row
  C. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed
  D. The second ROLLBACK command undoes the insert

• Question 23:

  Which two statements are true about space-saving features in an Oracle Database? (Choose two.)

  A. Private Temporary Tables (PTTS) store metadata in memory only
  B. An index created with the UNUSABLE attribute has no segment
  C. If they exist for a session, Private Temporary Tables (PTTs) are always dropped at the next COMMIT OR ROLLBACK statement
  D. An index that is altered to be UNUSABLE will retain its segment
  E. A table that is truncated will always have its segment removed
  A. Private Temporary Tables (PTTS) store metadata in memory only
  B. An index created with the UNUSABLE attribute has no segment

  ---

  Explanation/Reference:

  A - https://oracle-base.com/articles/18c/private-temporary-tables-18c Oracle 18c introduced the concept of a private temporary table, a memory-based temporary table that is dropped at the end of the session or transaction depending on the setup. B - https://docs.oracle.com/html/E25494_01/indexes003.htm#CIHFIGDG Beginning with Oracle Database 11g Release 2, the database does not create an index segment when creating an unusable index. C - not always, depends on the setup of the table creation

• Question 24:

  Which two statements are true about INTERVAL data types? (Choose two.)

  A. INTERVAL DAY TO SECOND columns support fractions of seconds.
  B. The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.
  C. The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.
  D. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
  E. INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
  F. INTERVAL YEAR TO MONTH support yearly intervals.
  A. INTERVAL DAY TO SECOND columns support fractions of seconds.
  F. INTERVAL YEAR TO MONTH support yearly intervals.

  ---

  Explanation/Reference:

• Question 25:

  Which two are benefits of external tables? (Choose two.)

  A. They support DELETES which transparently deletes records in the file system as if they were table rows.
  B. They can be queried, transformed, and joined with other tables without having to load the data first.
  C. They support UPDATES which transparently updates records in the file system as if they were table rows.
  D. The results of a complex join or aggregating function or both can be unloaded to a file for transportation to another database.
  E. They can be queried while the database is in the MOUNT state like dynamic performance views.
  B. They can be queried, transformed, and joined with other tables without having to load the data first.
  D. The results of a complex join or aggregating function or both can be unloaded to a file for transportation to another database.

  ---

  Explanation/Reference:

• Question 26:

  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.
  C. They can be used in the where clause.
  D. A SQL statement may have multiple single row subquery blocks.
  F. They can be used in the having clause.

• Question 27:

  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 28:

  The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.

  The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson.

  Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?

  A. SELECT REPLACE(SUBSTR(cust_last_name, -3), 'An', 'O') FROM customers;
  B. SELECT REPLACE(TRIM(TRAILING 'son' FROM cust_last_name), 'An', 'O') FROM customers;
  C. SELECT INITCAP(REPLACE(TRIM('son' FROM cust_last_name), 'An', 'O')) FROM customers;
  D. SELECT REPLACE(REPLACE(cust_last_name, 'son', ''), 'An', 'O') FROM customers;
  D. SELECT REPLACE(REPLACE(cust_last_name, 'son', ''), 'An', 'O') FROM customers;

  ---

  Explanation/Reference:

• Question 29:

  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
  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
  F. Usernames of all users including database administrators are stored in the data dictionary

  ---

  Explanation/Reference:

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

• Question 30:

  Which three statements are true about the tools used to configure Oracle Net Services? (Choose three.)

  A. The Oracle Net Configuration Assistant is only used when running the Oracle installer
  B. Oracle Net Manager can be used to centrally configure listeners on any database server target
  C. The lsnrctl utility requires a listener.ora file to exist before it is started
  D. Oracle Net Manager can be used to locally configure naming methods on a database server
  E. Enterprise Manager Cloud Control can be used to centrally configure listeners on any managed database server
  F. Enterprise Manager Cloud Control can be used to centrally configure net service names for any database server target
  D. Oracle Net Manager can be used to locally configure naming methods on a database server
  E. Enterprise Manager Cloud Control can be used to centrally configure listeners on any managed database server
  F. Enterprise Manager Cloud Control can be used to centrally configure net service names for any database server target

  ---

  Explanation/Reference:

  Oracle Net Manager to locally control and Enterprise Manager Cloud Control to centrally control. https://docs.oracle.com/en/database/oracle/oracle-database/19/netag/introducing-oracle-net-services.html#GUID-6B1F0584-887D-4D2A-B982-40ACF15DF985

  1.

  Oracle Enterprise Manager Cloud Control combines configuration functionality across multiple file systems, along with listener administrative control to provide an integrated environment for configuring and managing Oracle Net Services.

  2.

  Oracle Net Manager provides configuration functionality for an Oracle home on a local client or server host.