Oracle Database Administration I (1Z0-082)

✅ Option B (Correct) Reasoning: Single-row functions accept various argument types including column names, literal values, bind variables, or other expressions. For instance, you can use UPPER(employee_name), LENGTH('Oracle'), or ROUND(salary 1.1) as arguments. The flexibility of argument types makes them highly versatile.✅ Option C (Correct) Reasoning: The return data type of a single-row function can often differ from its argument's data type. Examples include TO_CHAR(SYSDATE) which converts a DATE to a VARCHAR2, or TO_NUMBER('123') which converts a VARCHAR2 to a NUMBER, demonstrating type conversion capabilities.✅ Option D (Correct) Reasoning: Single-row functions can be nested, where the output of one function serves as an input argument for another. For example, ROUND(NVL(salary, 0), 2). There is no strict predefined hard limit to the nesting level beyond practical SQL statement length and complexity constraints.❌ Why the other choices are incorrect:

Option A is incorrect: Single-row functions are not restricted to the WHERE clause; they are commonly used in the SELECT list, WHERE clause, ORDER BY clause, and within DML statements to manipulate data. They provide per-row transformation capabilities in many contexts.

Option E is incorrect: Many single-row functions accept multiple arguments. For instance, SUBSTR(string, start_position, length), ROUND(number, decimal_places), and NVL(expression1, expression2) all require more than one argument to operate as intended.

Option F is incorrect: Single-row functions return one result for each row processed in the query. They do not return a single result per table; that behavior is characteristic of aggregate (group) functions when no GROUP BY clause is specified.

✅ Option B (Correct) Reasoning: An index created with the UNUSABLE attribute creates a dictionary entry but does not allocate a segment, deferring storage allocation until it is rebuilt.✅ Option D (Correct) Reasoning: When an existing index is altered to UNUSABLE, its segment is not deallocated; the storage remains, but the index cannot be used.❌ Why the other choices are incorrect:

Option A is incorrect: Private Temporary Tables (PTTs) store their metadata and data in the temporary tablespace, not just in memory.

Option C is incorrect: Private Temporary Tables (PTTs) are dropped automatically at the end of the session, not necessarily at the next COMMIT or ROLLBACK statement.

Option E is incorrect: A table truncated with the REUSE STORAGE option will retain its segment; only data is removed, not the segment.

✅ Option A (Correct) Reasoning: A TRUNCATE command on a GLOBAL TEMPORARY TABLE deletes all rows for the issuing session, as GTT data is session-specific.✅ Option D (Correct) Reasoning: Data in a GLOBAL TEMPORARY TABLE is always session-specific. Any rows inserted into it are automatically deleted upon session termination, regardless of the ON COMMIT clause.✅ Option E (Correct) Reasoning: While data in a GLOBAL TEMPORARY TABLE is session-specific, its definition (schema, columns, constraints) is persistent and available to all sessions like a regular table.❌ Why the other choices are incorrect:

Option B is incorrect: GLOBAL TEMPORARY TABLE data is private to the session that inserted it and is not visible to other sessions, even with SELECT grants.

Option C is incorrect: Space for a GLOBAL TEMPORARY TABLE is allocated dynamically when data is first inserted into it by a session, not at session start.

Option F is incorrect: A DELETE command on a GLOBAL TEMPORARY TABLE is a DML operation and can be rolled back within the current transaction.

✅ Option B (Correct) Reasoning: The DUAL table is a special table in Oracle Database, consisting of a single column named DUMMY with VARCHAR2(1) data type and a single row containing the value 'X'. This structure is fundamental to its purpose.
✅ Option E (Correct) Reasoning: The DUAL table is owned by the SYS user but is universally accessible because the SELECT privilege on it is granted to PUBLIC by default. Therefore, any user with basic privileges can query DUAL.
❌ Why the other choices are incorrect:

Option A is incorrect: The DUAL table is publicly accessible to all users, not just SYS.

Option C is incorrect: DUAL contains only a single row and a single column.

Option D is incorrect: While DUAL is primarily used for displaying constants, pseudo-columns, or performing calculations, the statement that it can display only these is not entirely accurate, as you can also select its DUMMY column.

Option F is incorrect: DUAL consists of a single row and a single column.

✅ Option B (Correct) Reasoning: All SELECT statements in a query using SET operators must return the same number of columns. Column names are derived from the first SELECT statement; subsequent query column names are ignored.✅ Option E (Correct) Reasoning: For SET operations, the data type of corresponding columns in subsequent queries must either exactly match or be implicitly convertible to the data type of the first query's corresponding column.❌ Why the other choices are incorrect:

Option A is incorrect: The data type of each column does not strictly need to be in the same 'data type group' if implicit conversion is possible between different groups (e.g., VARCHAR2 to NUMBER).

Option C is incorrect: An exact data type match is not required, as Oracle allows implicit data type conversion between compatible types.

Option D is incorrect: Only the number of columns must be identical. The column names from the second and subsequent queries are not used in the final result set.

✅ Option A (Correct) Reasoning: Statement 1 uses the MAX() aggregate function without a GROUP BY clause. This processes all rows as a single group, always returning a single row representing the overall maximum. ✅ Option E (Correct) Reasoning: Statement 2 includes a GROUP BY order_id clause. This calculates the MAX() value for each unique order_id, thus potentially returning multiple rows, one for each distinct order_id. ❌ Why the other choices are incorrect:

Option B is incorrect: Statement 2 uses GROUP BY order_id. If there are multiple distinct order_id values, it will return one row per order_id, resulting in multiple rows, not just one.

Option C is incorrect: The MAX() aggregate function ignores NULL values. It returns NULL only if all values in the processed set (or group) are NULL; otherwise, it returns the maximum of the non-NULL values.

Option D is incorrect: Statement 1 provides a single overall maximum. Statement 2 provides a maximum for each order_id. Their outputs differ in both content and the number of rows.

✅ Option E (Correct) Reasoning: Views defined with a GROUP BY clause are not inherently updatable. Attempting an INSERT operation on such a view will result in an ORA-01732 error.✅ Option F (Correct) Reasoning: Similar to INSERT, views containing a GROUP BY clause are not updatable. A DELETE operation performed on this type of view will also raise an ORA-01732 error.✅ Option G (Correct) Reasoning: The WITH CHECK OPTION ensures that all DML operations (INSERT or UPDATE) performed on the view satisfy the WHERE clause conditions of the view, preventing rows from being inserted or updated if they would not be visible through the view.❌ Why the other choices are incorrect:

Option A is incorrect: While privileges on a view persist as long as the view object exists, if the view is dropped and recreated, privileges would need to be re-granted. Also, this statement is less universally true compared to the restrictions on DML for complex views.

Option B is incorrect: A view can be created using the CREATE FORCE VIEW command even if the underlying tables do not exist at the time of creation.

Option C is incorrect: The WITH CHECK OPTION restricts DML operations (INSERT/UPDATE), not the display of rows during querying.

Option D is incorrect: Many types of views, such as those involving GROUP BY, DISTINCT, or complex joins, are not updatable and thus DML cannot always be used.

✅ Option A (Correct) Reasoning: External table data files are accessible via OS directories. If different databases have access to the same file system location and directory objects, they can share external table files.✅ Option C (Correct) Reasoning: The ORACLE_DATAPUMP access driver is designed to unload data from a database into external files, which are then treated as external tables.✅ Option E (Correct) Reasoning: The ORACLE_DATAPUMP access driver can also load data into a database from external files, effectively using an external table as a source for data loading.❌ Why the other choices are incorrect:

Option B is incorrect: The ORACLE_LOADER access driver loads data into the database, not unloads data from it.

Option D is incorrect: External tables can be partitioned, enabling better performance for large datasets.

Option F is incorrect: External tables are read-only and do not support DML operations like INSERT, UPDATE, or DELETE.

✅ Option D (Correct) Reasoning: The MAX and MIN aggregate functions can be applied to columns with character data types, returning the highest and lowest values respectively based on the database's collation sequence.❌ Why the other choices are incorrect:

Option A is incorrect: Aggregate functions can be nested, but typically only to a limited extent (e.g., two levels deep), not an arbitrary 'any number of levels'.

Option B is incorrect: The AVG function, like most aggregate functions, implicitly ignores NULL values. It does not convert them to zero; explicit functions like NVL or COALESCE are needed for that.

Option C is incorrect: Aggregate functions cannot be used in the WHERE clause, as WHERE filters individual rows before aggregation. They are valid in the SELECT list, HAVING, and ORDER BY clauses.

✅ Option A (Correct) Reasoning: The UPDATE statement's subquery retrieves SALARY for employee_id = 200, which is then assigned to employee_id = 100.

✅ Option D (Correct) Reasoning: The UPDATE statement's subquery retrieves JOB_ID for employee_id = 200, which is then assigned to employee_id = 100.

❌ Why the other choices are incorrect:

Option B is incorrect: The WHERE employee_id = 100 clause ensures that only employee 100's record is modified. Employee 200's JOB_ID remains unchanged.

Option C is incorrect: Similar to Option B, employee 200's SALARY remains unchanged because its row is not targeted by the UPDATE statement.

Option E is incorrect: Employee 100's JOB_ID is explicitly updated to that of employee 200, so it does not remain the same.

Option F is incorrect: Employee 100's SALARY is explicitly updated to that of employee 200, so it does not remain the same.