1Z0-082 Real Exam Questions and Verified Answers

Question 1 Multiple choice

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

Show answer and explanation

Correct answers: A, C

Question 2 Single choice

Which statement is true about database links?

A
Private database link creation requires the same user to exist in both the local and the remote databases.
B
A public database link can be created only by SYS.
C
A database link can be created only between two Oracle databases.
D
A public database link can be used by a user connected to the local database instance to connect to any schema in the remote database instance.
E
A database link created in a database allows a connection from that database's instance to the target database's Instance, but not vice versa.

Show answer and explanation

Correct answer: E

Question 3 Multiple choice

Which three statements are true about connection strings and service names used to connect to an Oracle database instance?

A
A single database instance can support connections for multiple service names.
B
A service name is created by a listener.
C
A single connection string can refer to multiple database instances.
D
A connection string including a service name must be defined in the cnsnames.ora file.
E
A connection string must include the SID of a database Instance.
F
Different connection strings in the same tnsnames.ora file can contain the same service name, host and port parameters.

Show answer and explanation

Correct answers: A, C, F

Question 4 Multiple choice

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.

Show answer and explanation

Correct answers: A, F

Question 5 Multiple choice

Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined? (Choose three.)

A
Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
B
The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
C
Delete statements can always be done on a table through a view.
D
Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.
E
Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
F
Insert statements can always be done on a table through a view.

Show answer and explanation

Correct answers: B, D, E

Question 6 Multiple choice

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

Show answer and explanation

Correct answers: A, B, C, D

Explanation

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

Question 7 Single choice

View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.

You want to display PROD_IDS whose promotion cost is less than the highest cost PROD_ID in a promotion time interval.

Examine this SQL statement:

Exhibit 1.

Exhibit 2.

What will be the result?

A
It gives an error because the ALL keyword is not valid
B
It gives an error because the GROUP BY clause is not valid
C
It executes successfully but does not give the required result
D
It executes successfully and gives the required result

Show answer and explanation

Correct answer: D

Question 8 Multiple choice

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

Show answer and explanation

Correct answers: B, E, F

Question 9 Multiple choice

Which two statements are true regarding a SAVEPOINT? (Choose two.)

A
Rolling back to a SAVEPOINT can undo a CREATE INDEX statement
B
Rolling back to a SAVEPOINT can undo a TRUNCATE statement
C
Only one SAVEPOINT may be issued in a transaction
D
A SAVEPOINT does not issue a COMMIT
E
Rolling back to a SAVEPOINT can undo a DELETE statement

Show answer and explanation

Correct answers: D, E

Question 10 Multiple choice

Which two statements are true about UNDO and REDO? (Choose two.)

A
The generation of UNDO generates REDO
B
DML modifies Oracle database objects and only generates UNDO
C
The generation of REDO generates UNDO
D
DML modifies Oracle database objects and only generates REDO
E
DML modifies Oracle database objects and generates UNDO and REDO

Show answer and explanation

Correct answers: A, E

Explanation

... undo is stored in redo logs also. During roll forward phase of instance/media recovery, as changes (redo in redo logs) are applied to datafiles, undo stored in redo logs is used to generate undo segments. These segments are subsequently used to rollback uncommitted changes during rollback phase of instance/ media recovery.

https://www.experts-exchange.com/articles/13880/UNDO-AND-REDO-IN-ORACLE.html