Oracle 1z0-071 Exam Made Easy: Step-by-Step Preparation Guide

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.

The behavior of data insertion into a table with a specific numeric format in Oracle Database 12c can be explained as follows:

A. INSERT INTO rate_list VALUES (-.9) inserts the value as -.9: This is correct. The value -.9 is within the precision defined by the table column (NUMBER(6,2)), where 6 is the total number of digits (including both sides of the decimal point), and 2 is the number of digits after the decimal point.

E. INSERT INTO rate_list VALUES (0.551) inserts the value as .55: Oracle rounds the number to the nearest value that fits the specified scale of 2. Thus, 0.551 rounds down to 0.55.

References:

Oracle Database SQL Language Reference 12c, particularly sections on data types and numeric precision.

Global temporary tables (GTTs) in Oracle have unique properties related to session-based visibility and data persistence:

B. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted: True, TRUNCATE is session-specific for GTTs and will remove all rows inserted by the session that issues the TRUNCATE command.

D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions: The structure of a GTT is persistent and shared across sessions, but the data within is session-specific.

E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted: The data in GTTs is private for the session, and when the session ends, the data is deleted automatically.

References:

Oracle Database Concepts and SQL Language Reference 12c, especially the sections on temporary tables.

In Oracle SQL, the WITH CHECK OPTION clause in a CREATE VIEW statement ensures that all data manipulation statements performed through the view must conform to the view's defining query.

Option A: DELETE FROM emp80 WHERE department_id = 90;

This will violate the CHECK constraint because the WHERE clause condition does not meet the view's restriction (department_id = 80). This statement attempts to delete rows that the view is not supposed to have access to (since it should only include rows where department_id is 80).

Options B, C, and D do not violate the CHECK constraint because:

Option B: A SELECT statement does not modify data, so it does not violate the CHECK OPTION.

Option C: Selecting rows with department_id = 80 is consistent with the view’s definition.

Option D: There is a syntax error, but assuming it meant to set department_id to 80 where it is currently 90, it still would not violate the CHECK constraint because the CHECK OPTION on a view does not prevent updates that do not change the rows to fall outside the view's filter. However, since department_id is supposed to be 80 as per the view definition, this update operation doesn't make logical sense, as there should be no rows with department_id = 90 to update.

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.

Unused columns are a feature in Oracle that allows a column to be marked as unused without immediately dropping it from the table structure. The key points for each option are:

A. The DESCRIBE command does not display unused columns. These columns are also not visible in user_tab_cols but are visible in user_unused_col_tabs.

B. Primary key columns can be set to unused, but you must drop the primary key constraint before doing so.

C. Once a column is marked as unused, it is not possible to query it, and Oracle will not allow DML operations on that column.

D. This is true. Once a column is set to unused, you can add a new column with the same name because Oracle essentially considers the unused column as removed from the table.

E. Just like primary key columns, a foreign key column can be marked as unused, but you must drop the foreign key constraint first.

F. This is true. Unused columns retain their data until the column is dropped using the DROP UNUSED COLUMNS command.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ALTER TABLE"

Oracle Database Administrator’s Guide, 12c Release 1 (12.1)

Regarding the usage and rules of outer joins in SQL, specifically Oracle SQL:

D. A condition representing an outer join cannot be linked to another condition using the OR logical operator: In SQL, when using the Oracle-specific (+) notation for outer joins, it is not permitted to combine this condition with another using the OR operator. The use of (+) imposes restrictions to ensure the join logic is correctly interpreted.

E. The outer join operator (+) is used next to the column of the table without the matching rows: The (+) symbol in Oracle's SQL syntax denotes the table that should include "null" where data does not exist to satisfy the join condition, effectively including rows that do not have a match in the joined table.

Incorrect options:

A: The (+) operator cannot be used on both sides of a condition within the same join; it can only appear on one side to define which side of the join is the outer part.

B: An outer join is used to retrieve all rows from one table and the matched rows from the other table; it does not solely retrieve rows that do not meet the join condition.

C: The IN operator can be used in conditions involving an outer join, although specific rules and behaviors need to be considered depending on the SQL version and implementation.

A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option. (Incorrect)

Primary key columns that are referenced by foreign keys cannot be dropped as it would violate referential integrity. The CASCADE option does not apply in this context.

B. A DROP COLUMN command can be rolled back. (Correct)

Dropping a column from a table is a transactional operation. If it's not committed, it can be rolled back.

Understanding Oracle's data dictionary views is crucial for database management:

Option C: USER_TABLES displays all tables owned by the current user.

USER_TABLES is a view that shows all tables owned by the user executing the query.

Option F: All users can query DBA_TABLES successfully.

While all users can attempt to query DBA_TABLES, only users with the appropriate privileges can retrieve information from it. This option is tricky; "querying" is possible for all, but "retrieving data" depends on privileges.

Options A, B, D, and E are not entirely correct:

Option A and Option E are incorrect because USER_TABLES shows tables regardless of SELECT privileges.

Option B is incorrect; one does not need SELECT privileges on each table to view them in DBA_TABLES, but they need appropriate system privileges to access DBA_TABLES.

Option D is incorrect as ALL_TABLES displays tables accessible to the current user, not just owned by them.

A. True. The TO_CHAR function will convert SYSDATE to a string using the format 'MM/DD/YYYY', which displays the year as four digits. Then TO_DATE will convert this string back to a date without changing its appearance because the format matches.

E. True. The TO_CHAR function with the format 'MM/DD/YYYY' will display the date with the year as four digits, regardless of the NLS_DATE_FORMAT setting.

B, C, D, and F are incorrect because they either do not specify the format model needed to display the year as four digits or use TO_DATE which does not format the output but rather interprets the input.