1z0-071 Premium Exam Questions

Single-row functions in SQL operate on each row independently and can modify the returned value:

Option A: Incorrect. Single row functions can be used in multiple parts of a SELECT statement, including SELECT, WHERE, and ORDER BY clauses.

Option B: Incorrect. Single row functions can accept more than one argument, such as the CONCAT function, which can accept multiple string arguments.

Option C: Incorrect. They return one result for each row processed, not per table.

Option D: Correct. Single row functions can take various types of arguments including column names, literals, variables, and other expressions.

Option E: Correct. Functions can be nested within other functions, allowing complex expressions and calculations.

Option F: Correct. The data type of the result can differ from the arguments’ data types, such as the SUBSTR function returning a VARCHAR2 even when used on a number after converting it to a string.

Since the statement seems to imply that a sort operation involving department names and an average salary (AVGSAL) was executed, the best logical inference, given typical SQL query behavior and assuming a typical structure, is:

B. sorted by DEPARTMENT_NAME and AVGSAL: Generally, when SQL queries involve grouping and aggregation, the ORDER BY clause (if explicitly mentioned or implied in your statement details) would sort by the grouping column first (DEPARTMENT_NAME) and then by any aggregated column such as average salary (AVGSAL). This order ensures that within each department, the rows are sorted according to the average salary.

A: NEXTVAL is used to increment the sequence and return the next value; it does not give the last number generated.

B: CURRVAL returns the current value of the sequence, which is the last value generated in the user's current session. However, CURRVAL cannot be queried unless NEXTVAL has been called at least once in that session.

C: CURRVAL is used correctly, but the syntax 'sequence.CURRVAL' is not correct in Oracle SQL.

D: NEXTVAL is used to generate the next sequence number, not to retrieve the last one generated.

A: This is true. Using PURGE in the DROP TABLE command will permanently remove the table and its dependent objects so that it cannot be recovered, and the action cannot be rolled back.

B: This statement is true. Dropping the base table will result in the removal of all views that are based on the DEPARTMENTS table.

C: This statement is false. The PURGE option removes the table and does not just delete rows.

D: This statement is true. The DROP TABLE command will remove the DEPARTMENTS table from the database.

E: This statement is true. When a table is dropped, all synonyms for that table are also removed.

F: This statement is true. Dropping a table will automatically drop all indexes associated with the table.