1Z0-082 Online Practice Questions and Answers

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) <=25

B. WHERE ADD_MONTHS(start_date, 25) <= SYSDATE

C. WHERE TO_NUMBER(start date - SYSDATE) <= 25

D. WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25

In the ORCL database, UNDOTBS1 is the active undo tablespace with these properties:

1.

A size of 100 MB

2.

AUTOEXTEND is off

3.

UNDO_RETENTION is set to 15 minutes

4.

It has RETENTION GUARANTEE

UNDOTBS1 fills with uncommitted undo 10 minutes after the database opens.

What will happen when the next update is attempted by any transaction?

A. It succeeds and the generated undo is stored in SYSTEM.

B. It fails and returns the error message "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'".

C. It succeeds and the least recently written undo block of UNDOTBS1 is overwritten by the generated undo.

D. It succeeds and the generated undo is stored in SYSAUX.

E. It succeeds and the least recently read undo block of UNDOTBS1 is overwritten by the generated undo.

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

Examine the description of the BOOKS_TRANSACTIONS table:

Examine this partial SQL statement:

SELECT * FROM books_transactions

Which two WHERE conditions give the same result? (Choose two.)

A. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND member_id = 'A101' OR member_id = 'A102');

B. WHERE borrowed_date = SYSDATE AND transaction_type = 'RM' OR member_id IN ('A101', 'A102');

C. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' AND (member_id = 'A101' OR member_id = 'A102'))

D. WHERE borrowed_date = SYSDATE AND (transaction_type = 'RM' OR menber_id IN ('A101', 'A102'));

E. WHERE (borrowed_date = SYSDATE AND transaction_type - 'RM') OR member_id IN ('A101', VA102');

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;

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.

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

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

A. It can be an inner join.

B. It can be a left outer join.

C. It must be an equijoin.

D. The join key column must have an index.

E. It must be a full outer join.