Free Oracle 1z0-071 Practice Test & Real Exam Questions, Page 2

Question #15

Examine the description of the BOOKStable:

The table has 100 rows.
Examine this sequence of statements issued in a new session:

Which two statements are true? (Choose two.)

A. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.

B. The second ROLLBACK command undoes the insert.

C. The second ROLLBACK command does nothing.

D. The second ROLLBACK command replays the delete.

E. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.

Discussion 0

Correct Answer: B,E Vote an answer

Question #16

Which statement is true regarding external tables?

A. The data and metadata for an external table are stored outside the database.

B. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

C. The default REJECT LIMIT for external tables is UNLIMITED.

D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #17

Examine this query:
SELECT 2 FROM DUAL d1 CROSS JOIN DUAL d2 CROSS JOIN DUAL d3;
What is returned upon execution?

A. 0 rows

B. 3 rows

C. 8 rows

D. 6 rows

E. an error

F. 1 row

Discussion 0

Correct Answer: F Vote an answer

Question #18

Examine the following query:

What is the output of this query?

A. It displays 5 percent of the products with the lowest amount sold.

B. It displays 5 percent of the products with the highest amount sold.

C. It displays the first 5 percent of the rows from the SALES table.

D. It results in an error because the ORDER BY clause should be the last clause.

Discussion 0

Correct Answer: A Vote an answer

Question #19

Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?

A. First normal form

B. Fourth normal form

C. Third normal form

D. Second normal form

Discussion 0

Correct Answer: D Vote an answer

Question #20

View the Exhibit and examine the structure of the stores table.

You want to display the name of the store along with the address, START_DATE, PROPERTV_PRICE, and the projected property price, which is 115% of the property price. The stores displayed must have START_DATE in the range of 36 months starting from 01- Jan-2000 and above.
Which SQL statement would get the desired output?

A. SELECT name, concat (address||','| |city| |', ', country) AS
full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
<=36;

B. SELECT name, concat (address| | ','| |city| |', ', country) AS
full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;

C. SELECT name, concat (address| | ','| |city| |', ', country) AS
full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

D. SELECT name, address||','||city||','||country AS full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
<=36;

Discussion 0

Correct Answer: A Vote an answer

Question #21

Examine the structure of the invoice table.

Which two SQL statements would execute successfully?

A.

B.

C.

D.

Discussion 0

Correct Answer: A,B Vote an answer

Question #22

Examine the data in the NEW_EMPLOYEEStable:

Examine the data in the EMPLOYEEStable:

You want to:
1. Update existing employee details in the EMPLOYEEStable with data from the NEW_EMPLOYEES table.
2. Add new employee details from the NEW_EMPLOYEES table to the EMPLOYEEStable.
Which statement will do this?

A.

B.

C.

D.

Discussion 0

Correct Answer: D Vote an answer

Question #23

Which two statements are true about date/time functions in a session where NLS_DATE_FORMAT is set to DD-MON-YYYY HH24:MI:SS? (Choose two.)

A. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server.

B. SYSDATE can be used in expressions only if the default date format is DD-MON-RR.

C. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of fractional seconds.

D. CURRENT_DATE returns the current date and time as per the session time zone.

E. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.

F. SYSDATE can be queried only from the DUAL table.

Discussion 0

Correct Answer: D,F Vote an answer

Question #24

When does a transaction complete? (Choose all that apply.)

A. When a PL/SQL anonymous block is executed

B. When a data definition language statement is executed

C. When a ROLLBACK command is executed

D. When a TRUNCATE statement is executed after the pending transaction

E. When a DELETE statement is executed

Discussion 0

Correct Answer: B,C,D Vote an answer

Question #25

Evaluate these commands which execute successfully:

Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?
(Choose two.)

A. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.

B. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times.

C. If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.

D. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.

E. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into and no explicit value is given for ORD_NO.
ORD_ITEMS

Discussion 0

Correct Answer: A,D Vote an answer

Question #26

Which three statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose three.)

A. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block

B. If the query block name and the table name are the same, then the table name takes precedence.

C. The WITH clause can hold more than one query.

D. It can be used only with the SELECT clause.

Discussion 0

Correct Answer: A,C,D Vote an answer

Question #27

View the Exhibit and examine the data in the PRODUCT_INFORMATION table.

Which two tasks would require subqueries? (Choose two.)

A. displaying all supplier IDs whose average list price is more than 500

B. displaying all the products whose minimum list prices are more than average list price of products having the status orderable

C. displaying the number of products whose list prices are more than the average list price

D. displaying the minimum list price for each product status

E. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE

Discussion 0

Correct Answer: B,C Vote an answer

Question #28

Which two statements are true about sequences created in a single instance database? (Choose two.)

A. The numbers generated by a sequence can be used only for one table

B. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted

C. CURRVAL is used to refer to the last sequence number that has been generated

D. DELETE <sequencename> would remove a sequence from the database

E. When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement

Discussion 0

Correct Answer: C,E Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Free Oracle 1z0-071 Practice Test & Real Exam Questions

Download Free Oracle 1z0-071 Demo