Oracle Oracle Database 1Z0-082 Questions & Answers

• Question 21:

  A script abc.sql must be executed to perform a job.

  A database user HR, who is defined in this database, executes this command:

  $ sqlplus hr/hr@orcl @abc.sql

  What will happen upon execution?

  A. The command succeeds and HR will be connected to the orcl and abc.sql databases

  B. The command succeeds and HR will be connected to the orcl database instance, and the abc.sql script will be executed

  C. The command fails because the script must refer to the full path name

  D. The command fails and reports an error because @ is used twice

  Correct Answer: B

• Question 22:

  Your database instance was shut down normally and then started in NOMOUNT state. You then execute

  this command:

  ALTER DATABASE MOUNT;

  Which two actions are performed? (Choose two.)

  A. The online redo logs are opened

  B. The online data files are opened

  C. The alert log records the execution details

  D. The Oracle background processes are started

  E. The initialization parameter file is read

  F. The control file is read

  Correct Answer: CF

• Question 23:

  Which two are true about shrinking a segment online? (Choose two.)

  A. It is not possible to shrink either indexes or Index Organized Tables (IOTs)

  B. It always eliminates all migrated rows if any exist in the table

  C. To shrink a table it must have a PRIMARY KEY constraint

  D. To shrink a table it must have a UNIQUE KEY constraint

  E. To shrink a table it must have row movement enabled

  F. It must be in a tablespace that uses Automatic Segment Space Management (ASSM)

  Correct Answer: EF

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

• Question 24:

  Examine these commands: Which two statements are true about the sqlldr execution? (Choose two.)

  

  A. It overwrites data in EMP with data in EMP.DAT

  B. It uses the database buffer cache to load data

  C. It generates a log that contains control file entries, which can be used with normal SQL*Loader operations

  D. It generates a sql script that it uses to load data from EMP.DAT to EMP

  E. It appends data from EMP.DAT to EMP

  Correct Answer: CE

  C and E are correct - ..."SQL*Loader express mode generates a log file that includes a SQL*Loader control file. The log file also contains SQL scripts for creating the external table and performing the load using a SQL INSERT AS SELECT statement. Neither the control file nor the SQL scripts are used by SQL*Loader express mode. They are made available to you in case you want to use them as a starting point to perform operations using regular SQL*Loader or standalone external tables; the control file is for use with SQL*Loader, whereas the SQL scripts are for use with standalone external tables operations."

• Question 25:

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

  Which statement is true about aggregate functions?

  A. Aggregate functions can be nested to any number of levels

  B. The AVG function implicitly converts NULLS to zero

  C. Aggregate functions can be used in any clause of a SELECT statement

  D. The MAX and MIN functions can be used on columns with character data types

  Correct Answer: D

• Question 27:

  Which three statements are true about inner and outer joins? (Choose three.)

  A. A full outer join returns matched and unmatched rows

  B. An inner join returns matched rows

  C. Outer joins can only be used between two tables per query

  D. A full outer join must use Oracle syntax

  E. Outer joins can be used when there are multiple join conditions on two tables

  F. A left or right outer join returns only unmatched rows

  Correct Answer: ABE

  E. Outer joins can be used when there are multiple join conditions on two tables can be like following query:

  - select * from table a left join table1 b on a.col1 = b.col1 and a.col2=b.col2;

  Reference: https://www.studytonight.com/dbms/joining-in-sql.php

• Question 28:

  Examine this description of the TRANSACTIONS table:

  

  Which two SQL statements execute successfully? (Choose two.)

  A. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100 "DUES" FROM transactions;

  B. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions;

  C. SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 "DUES AMOUNT" FROM transactions;

  D. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;

  E. SELECT customer_id AS `CUSTOMER-ID', transaction_date AS DATE, amount + 100 `DUES AMOUNT' FROM transactions;

  Correct Answer: BD

• Question 29:

  Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.)

  A. The CURRENT_TIMESTAMP function returns data without time zone information

  B. A TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row

  C. A TIMESTAMP data type column contains information about year, month, and day

  D. The DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

  E. The SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

  Correct Answer: CDE

  "TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that is sensitive to time zone information. It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone information is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone."

  So, the data is stored with the DB Timezone, even if it's retrieved with the session timezone. A is clearly wrong by example, so the rest are true. With D and E we can also have an easy sample, while for C, which is apparently the most ambiguous, I agree with whom was saying that the TIMESTAMP data type contains indeed informations about day, month and year, and in the answer is never stated that they are the only ones present. So as it's put, it's definitely right.

  https://docs.oracle.com/database/121/NLSPG/ch4datetime.htm#GUID-CD2954CE-45E2-4938-A599CCB96879510F

• Question 30:

  Which two are true about a SQL statement using SET operators such as UNION? (Choose two.)

  A. The data type group of each column returned by the second query must match the data type of the corresponding column returned by the first query.

  B. The number, but not names, of columns must be identical for all SELECT statements in the query.

  C. The data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.

  D. The names and number of columns must be identical for all SELECT statements in the query.

  E. The data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.

  Correct Answer: AB

  https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/The-UNION-ALL-INTERSECTMINUS-Operators.html#GUID-B64FE747-586E-4513-945F-80CB197125EE