Oracle 1Z0-082 Online Practice Questions and Exam Preparation

Oracle 1Z0-082 Online Questions & Answers

• Question 11:

  Which three statements are true about Oracle synonyms? (Choose three.)

  A. A synonym cannot be created for a PL/SQL package
  B. A synonym can be available to all users
  C. A SEQUENCE can have a synonym
  D. A synonym created by one user can refer to an object belonging to another user
  E. Any user can drop a PUBLIC synonym
  B. A synonym can be available to all users
  C. A SEQUENCE can have a synonym
  D. A synonym created by one user can refer to an object belonging to another user

  ---

  Explanation/Reference:

  Reference: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

• Question 12:

  Which two statements are true about substitution variables?

  A. A substitution variable can be used with any clause in a SELECT statement.
  B. A substitution variable used to prompt for a column name must be enclosed in double quotation marks.
  C. A substitution variable used to prompt for a column name must be enclosed in single quotation marks.
  D. A substitution variable prefixed with and always prompts only once for a value in a session.
  E. A substitution variable can be used only in a SELECT statement.
  F. A substitution variable prefixed with andand prompts only once for a value in a session unless it is set to undefined in the session.
  A. A substitution variable can be used with any clause in a SELECT statement.
  F. A substitution variable prefixed with andand prompts only once for a value in a session unless it is set to undefined in the session.

  ---

  Explanation/Reference:

• Question 13:

  Which two queries execute successfully? (Choose two.)

  A. SELECT NULLIF(NULL, 100) FROM DUAL;
  B. SELECT NULLIF(100, 'A') FROM DUAL;
  C. SELECT COALESCE(100, 'A') FROM DUAL;
  D. SELECT COALESCE(100, NULL, 200) FROM DUAL;
  E. SELECT NULLIF(100, 100) FROM DUAL;
  D. SELECT COALESCE(100, NULL, 200) FROM DUAL;
  E. SELECT NULLIF(100, 100) FROM DUAL;

  ---

  Explanation/Reference:

• Question 14:

  In the SALES database, DEFERRED_SEGMENT_CREATION is TRUE.

  Examine this command:

  SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);

  Which segment or segments, if any, are created as a result of executing the command?

  A. T1, an index segment for the primary key, a LOB segment, and a lobindex segment
  B. no segments are created
  C. T1 only
  D. T1 and an index segment created for the primary key only
  E. T1, an index segment for the primary key, and a LOB segment only
  B. no segments are created

  ---

  Explanation/Reference:

  DEFERRED_SEGMENT_CREATION specifies the semantics of deferred segment creation. If set to true, then segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.

  Before creating a set of tables, if it is known that a significant number of them will not be populated, then consider setting this parameter to true. This saves disk space and minimizes install time

  SQL> alter session set deferred_segment_creation=TRUE;

  Session altered.

  SQL> CREATE TABLE T1(c1 INT PRIMARY KEY, c2 CLOB);

  Table created.

  SQL> select segment_name,segment_type,bytes from user_segments;

  no rows selected

• Question 15:

  Which two statements are true about Database Instances and Real Application Clusters (RAC)?

  A. A RAC database can have one Instance.
  B. A RAC database can have instances on separate servers.
  C. Two RAC databases can share their instances.
  D. A RAC database must have three or more Instances.
  E. A RAC database must have two or more instances.
  A. A RAC database can have one Instance.
  B. A RAC database can have instances on separate servers.

• Question 16:

  In one of your databases, you create a user, HR, and then execute this command:

  GRANT CREATE SESSION TO hr WITH ADMIN OPTION;

  Which three actions can HR perform? (Choose three.)

  A. Revoke the CREATE SESSION privilege from other users
  B. Revoke the CREATE SESSION privilege from user HR
  C. Log in to the database instance
  D. Grant the CREATE SESSION privilege with ADMIN OPTION to other users
  E. Execute DDL statements in the HR schema
  F. Execute DML statements in the HR schema
  A. Revoke the CREATE SESSION privilege from other users
  B. Revoke the CREATE SESSION privilege from user HR
  C. Log in to the database instance
  D. Grant the CREATE SESSION privilege with ADMIN OPTION to other users

  ---

  Explanation/Reference:

  Reference: https://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG224

• Question 17:

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

  Examine the description of the PRODUCTS table:

  

  Which query is valid?

  A. SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id;
  B. SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id, release_date;
  C. SELECT prod_id, AVG(MAX(cost)) FROM products GROUP BY prod_id;
  D. SELECT prod_id, MAX(AVG(cost)) FROM products GROUP BY prod_id;
  B. SELECT prod_id, release_date, SUM(cost) FROM products GROUP BY prod_id, release_date;

  ---

  Explanation/Reference:

• Question 19:

  Which three statements are true about a self join? (Choose three.)

  A. The ON clause must be used
  B. The query must use two different aliases for the table
  C. It must be an equijoin
  D. It must be an inner join
  E. The ON clause can be used
  F. It can be an outer join
  B. The query must use two different aliases for the table
  E. The ON clause can be used
  F. It can be an outer join

• Question 20:

  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