Act now and download your Oracle 1Z0-071 test today! Do not waste time for the worthless Oracle 1Z0-071 tutorials. Download Down to date Oracle Oracle Database 12c SQL exam with real questions and answers and begin to learn Oracle 1Z0-071 with a classic professional.

Q11. Examine the commands used to createDEPARTMENT_DETAILS andCOURSE_DETAILS:

You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.

Which SQL statement must you use? 

A)

B)

C)

D)

A. Option A

B. Option B

C. Option C

D. Option D

Answer: C


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

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

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

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

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

E. 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

Answer: A,D

Explanation:

Gaps in the Sequence

Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. Therefore, if you roll back a statement containing a sequence, the number is lost.

Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, those values are lost if the system crashes.

Because sequences are not tied directly to tables, the same sequence can be used for multiple tables.

However, if you do so, each table can contain gaps in the sequential numbers.

Modifying a Sequence

If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement

To remove a sequence, use the DROP statement:

DROP SEQUENCE dept_deptid_seq;


Q13. Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.)

A. Combining more than two columns or expressions into a single column in the output

B. Displaying a date in a nondefault format

C. Substituting a character string in a text expression with a specified string

D. Finding the number of characters in an expression

Answer: B,C,D


Q14. View the Exhibit and examine the descriptions of the DEPT and LOCATIOMS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.

Which SQL statement would you execute to accomplish the task?

A. UPDATE dept d

SET city = ANY (SELECT city FROM locations l);

B. UPDATE dept d

SET city = (SELECT city FROM locations l) WHERE d.location_id = l.location_id;

C. UPDATE dept d

SET city = (SELECT city FROM locations l

WHERE d.location_id = l.location_id);

D. UPDATE dept d

SET city = ALL (SELECT city FROM locations l

WHERE d.location_id = l.location_id);

Answer: C


Q15. Examine the structure of the members table:

What is the outcome?

A. It fails because the alias name specified after the column names is invalid.

B. It fails because the space specified in single quotation marks after the first two column names is invalid.

C. It executes successfully and displays the column details in a single column with only the alias column heading.

D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

Answer: D


Q16. Which three statements are true regarding group functions? (Choose three.)

A. They can be used on columns or expressions.

B. They can be passed as an argument to another group function.

C. They can be used only with a SQL statement that has the GROUP BY clause.

D. They can be used on only one column in the SELECT clause of a SQL statement.

E. They can be used along with the single-row function in the SELECT clause of a SQL statement.

Answer: A,B,E


Q17. Examine the structure of the invoice table.

Which two SQL statements would execute successfully? 

A)

B)

C)

D)

A. Option A

B. Option B

C. Option C

D. Option D

Answer: C


Q18. Which statements are true? (Choose all that apply.)

A. The data dictionary is created and maintained by the database administrator.

B. The data dictionary views can consist of joins of dictionary base tables and user-defined tables.

C. The usernames of all the users including the database administrators are stored in the data dictionary.

D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.

E. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.

F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary

Answer: C,D,F


Q19. Evaluate the following two queries:

Which statement is true regarding the above two queries?

A. Performance would improve query 2 only if there are null values in the CUST CREDIT LIMIT column.

B. There would be no change in performance.

C. Performance would degrade in query 2.

D. Performance would improve in query 2.

Answer: B


Q20. Examine thestructureof the members table:

You want to display details of all members who reside in states starting with the letter A followed by exactly one character.

Which SQL statement must you execute?

A. SELECT * FROM MEMBERS WHERE state LIKE '%A_* ;

B. SELECT * FROM MEMBERS WHERE state LIKE 'A_*;

C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%'

D. SELECT * FROM MEMBERS WHERE state LIKE 'A%'

Answer: A