1z0-071 Oracle Database 12c SQL Free Practice Exam Questions (2025 Updated)

In Oracle Database 12c, the following are true about privileges and roles:

Option B: PUBLIC acts as a default role granted to every user in a database.

The PUBLIC role is a special role that is automatically granted to all users. It can be used to provide privileges to every user.

Option C: A user has all object privileges for every object in their schema by default.

By default, a user has all privileges on the objects they own, i.e., the objects in their schema.

Option D: A role can contain a combination of several privileges and roles.

Roles are designed to group together privileges and other roles, making it easier to manage and assign security settings to users.

Options A, E, F, and G are incorrect because:

Option A: System privileges can be set for specific schemas or objects, not always for the entire database.

Option E: Roles are not "owned" by users in the traditional sense of ownership; they are managed by the database and can be granted to any user.

Option F: Not all roles are owned by the SYS schema; roles are a database security concept and do not have an "owner".

Option G: The PUBLIC role is not something that can be revoked from a user; it is a default role that exists for all users.

The statement that will fail is B. In Oracle SQL, the WHERE clause cannot contain aggregate functions directly. The HAVING clause is used instead to apply conditions that involve aggregates, but it is applied after the GROUP BY clause.

A. While the HAVING clause is used before the GROUP BY clause which is not standard SQL syntax, Oracle SQL may still execute it successfully due to its flexibility in syntax.

B. This statement will fail because it uses an aggregate function, COUNT(*), in the WHERE clause, which is not allowed. The correct approach is to use the HAVING clause to filter the results of aggregate functions after the GROUP BY clause.

C. This statement is correct; it places the HAVING clause after the GROUP BY clause, applying the filter on the aggregated count.

D. This is a correctly constructed statement with the WHERE clause filtering individual records before grouping, and the HAVING clause filtering groups based on the aggregate function.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "WHERE Clause"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "HAVING Clause"

A. It executes successfully and the row is inserted with a null PUBLISHER_ID: The SQL statement does not specify the publisher_id, and since there is no NOT NULL constraint on this column, Oracle will insert the row with a NULL value for publisher_id. The statement is syntactically correct, assuming the column names and values are properly specified and formatted.

To find employees whose manager works in a different department, you can use a subquery or a join that compares the DEPARTMENT_ID of the employee with the DEPARTMENT_ID of their manager.

A. This query is incorrect because the NOT IN subquery incorrectly attempts to compare EMPLOYEE_ID with MANAGER_ID, and the correlation condition inside the subquery is incorrect.

B. This query is correct. The NOT EXISTS clause correctly identifies employees whose MANAGER_ID matches the EMPLOYEE_ID of another employee (mgr) and where the DEPARTMENT_ID differs from that manager's DEPARTMENT_ID.

C. This query is incorrect because the LEFT JOIN will return all employees, and there is no WHERE clause to filter out those employees whose managers are in the same department.

D. This query is incorrect. The RIGHT JOIN does not ensure that the resulting rows are for employees whose manager works in a different department. It also returns all managers, which is not the requirement.

E. This query is correct. The JOIN ensures that the returned rows are for employees (emp) whose MANAGER_ID matches the EMPLOYEE_ID of managers (mgr), and it correctly filters to include only those employees whose DEPARTMENT_ID is different from their manager's DEPARTMENT_ID.

Regarding the properties and capabilities of virtual columns in Oracle Database:

C. They can be indexed: In Oracle, virtual columns can indeed be indexed. This allows for performance optimization on queries that utilize these columns, despite the fact that their values are derived and not stored physically.

D. They cannot have a data type explicitly specified: The data type of a virtual column is derived from the expression used to define it. Therefore, it is not specified explicitly but is inferred from the expression itself.

Incorrect options:

A: Virtual columns cannot be referenced in the WHERE clause of UPDATE or DELETE statements because they are not stored and cannot be individually modified.

B: Virtual columns cannot be referenced in the SET clause of an UPDATE statement since they are derived from other column values and cannot be updated directly.

E: Virtual columns cannot reference other virtual columns in their expressions due to the potential for recursive and complex dependencies.

C: True. A UNIQUE constraint in Oracle SQL allows for the inclusion of NULL values; specifically, it permits multiple NULLs in a column or set of columns but ensures that all non-NULL values are unique.

D: True. A PRIMARY KEY constraint can indeed be added to a table after it has been created and even after it has been populated, as long as the existing data does not violate the primary key constraint rules (i.e., all values must be unique and not NULL).

F: True. A UNIQUE constraint can utilize a pre-existing index on the columns it covers. If a suitable index already exists, Oracle can use this index to enforce the constraint, optimizing performance and resource utilization.

G: True. Columns that are part of a FOREIGN KEY constraint can contain NULL values. This is permissible under SQL standards and Oracle implementation, as a NULL foreign key value is considered to not refer to any row in the referenced table and thus does not violate referential integrity.

In Oracle Database 12c, regarding a full outer join:

A. It includes rows that are returned by an inner join. This is true. A full outer join includes all rows from both joined tables, matching wherever possible. When there's a match in both tables (as with an inner join), these rows are included.

D. It returns matched and unmatched rows from both tables being joined. This is correct and the essence of a full outer join. It combines the results of both left and right outer joins, showing all rows from both tables with matching rows from the opposite table where available.

Options B, C, and E are incorrect:

B is incorrect because the Oracle join operator (+) is used for syntax in older versions and cannot implement a full outer join by using (+) on both sides. Proper syntax uses the FULL OUTER JOIN keyword.

C is incorrect as a Cartesian product is the result of a cross join, not a full outer join.

E is incorrect because it only describes the scenario of a full anti-join, not a full outer join.

Assuming the statement involves creating a new table SALES1 from the existing SALES table:

B. SALES1 created with 55,000 rows: If the statement involved creating SALES1 by selecting all rows from SALES (such as with a CREATE TABLE AS SELECT operation), then all 55,000 rows would be copied if no WHERE clause limited the selection.

E. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table: When creating a table using the CREATE TABLE AS SELECT syntax, any NOT NULL constraints on columns in the original table will also apply to the new table.

Incorrect options:

A: PRIMARY KEY and UNIQUE constraints are not automatically copied with a CREATE TABLE AS SELECT; they must be explicitly redefined.

C: The table is created with rows if the original SELECT statement (assumed here) selected rows.

D: The statement about creating a table with 1 row is incorrect based on the assumed full selection of the original table's rows.

In reference to the attributes of the PRODUCT_DETAILS table:

A. This statement is false. If PRODUCT_PRICE has no value (i.e., it is NULL), it cannot be used directly in arithmetic expressions because any operation with NULL results in NULL.

B. This statement is true. PRODUCT_ID has the NOT NULL constraint and is of type NUMBER, making it eligible to be a PRIMARY KEY. Primary keys require all values to be unique and not null.

C. This statement is true. EXPIRY_DATE is of type DATE, and it cannot be used in arithmetic expressions directly without a date function that operates on dates.

D. This statement is false. By default, columns of type DATE do not have a default value unless explicitly assigned using the DEFAULT keyword in the column definition.

E. This statement is false. By default, numeric columns do not have a default value unless specified with the DEFAULT keyword.

F. This statement is false. The statement about PRODUCT_NAME not containing duplicate values would be true if it were a UNIQUE or PRIMARY KEY, but there is no such constraint indicated in the provided table description.

E. True. Both & and && can prefix substitution variables in queries as well as in DML statements. & is used for defining a substitution variable that prompts the user every time the variable is encountered. && is used for a persistent substitution variable that, once defined in the session, will not prompt the user again for a value if used subsequently.

When using the TRUNCATE TABLE command in Oracle, several aspects of the table's structure and associated database objects are impacted. Here's an explanation of each option:

A: Incorrect. TRUNCATE TABLE does not drop triggers associated with the table; they remain defined.

B: Correct. Indexes on the table are retained and not dropped when you truncate a table. However, if the index is a domain index, it may be dropped depending on its type.

C: Correct. Integrity constraints such as primary keys, foreign keys, etc., are retained unless they are on a disabled state where truncation can lead to constraint being dropped.

D: Incorrect. A TRUNCATE TABLE operation cannot be rolled back. It is a DDL (Data Definition Language) operation and commits automatically.

E: Incorrect. The TRUNCATE TABLE operation deallocates the space used by the data unless the REUSE STORAGE clause is specified.

F: Incorrect. TRUNCATE TABLE operation removes all the rows in a table and does not log individual row deletions, thus FLASHBACK TABLE cannot be used to retrieve the data.

In SQL, the GROUP BY clause is used in conjunction with aggregate functions to group the result-set by one or more columns.

A. This statement will execute successfully. MAX() is an aggregate function that can be used to return the highest value of the selected column, and SUM() is an aggregate function used to sum the values. Both are valid in the SELECT statement with a GROUP BY clause grouping the results by dept_id.

B. This statement will not execute successfully when used with GROUP BY because LENGTH(last_name) is not an aggregate function and it doesn't appear in the GROUP BY clause. All selected columns that are not under an aggregate function must be included in the GROUP BY clause.

C. This statement will not execute successfully because STDDEV() is an aggregate function that calculates the standard deviation of a set of numbers, and it can only be used on numeric data types. last_name is not a numeric column.

D. This statement will not execute successfully for the same reason as B: INSTR(last_name, 'A') is not an aggregate function and must appear in the GROUP BY clause if it's in the SELECT clause.

C: True. You can add a new NOT NULL column to an existing table, provided you specify a DEFAULT value for the column. This ensures that existing rows in the table can be updated with a default value, thus maintaining the NOT NULL constraint integrity.

E: True. It is permissible to increase the width of a numeric column, provided the change does not truncate existing data. This change is generally safe as it allows for greater values or more precision without affecting existing data.

F: True. The default value of a column can be changed. This action affects subsequent insert operations but does not alter the values of existing rows. Changing the default is useful for adapting tables to evolving business rules without impacting existing data.

B: True. The REFERENCES privilege can be granted on specific columns within a table. This is necessary when a user needs to define foreign key constraints that reference those particular columns.

C: True. The UPDATE privilege can be granted on specific columns, allowing users to update only designated columns within a table. This is useful for restricting write access to sensitive or critical data within a table.

F: True. The INSERT privilege can also be granted on specific columns, meaning a user can be permitted to insert data into only certain columns of a table. This helps in maintaining data integrity and controlling access to data fields based on user roles.

A. True. The GRANT statement with the WITH ADMIN OPTION allows the grantee to not only modify the specified columns but also to grant that permission to other users.

B, C, D, E, and F are incorrect because:

B and C are incorrect due to the syntax; the correct syntax does not include the table name before each column when listing multiple columns in a GRANT statement.

D and E do not include the necessary WITH GRANT OPTION which is required to enable the grantee to grant the permission to others.

F is incorrect because the keyword WITH ADMIN OPTION should be used to allow the grantee to further grant the permission to others; WITH GRANT OPTION is correct in the context of object privileges.

Global temporary tables in Oracle Database 12c have unique characteristics, primarily around their visibility and lifespan which is session-specific:

B. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted: This is accurate as TRUNCATE in the context of a global temporary table only affects the rows inserted during the session that issues the command. The effect is isolated to the session.

D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions: The definition (i.e., the structure of the table such as column names, data types, etc.) of a global temporary table is persistent and visible across sessions. However, the data within is session-specific.

E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted: True, as the data in a global temporary table is designed to be temporary for the duration of a session. When the session ends, the data is automatically deleted.

References:

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

A. True, the owner of an object in Oracle automatically has all privileges on that object. This means they can perform any operation on the object, including SELECT, INSERT, UPDATE, DELETE, and so forth.C. True, the REFERENCES privilege must be granted explicitly by the table owner to another user or role to allow the grantee to define foreign key constraints that reference the primary or unique key of the owner's table. This is crucial in scenarios involving relational integrity constraints across tables owned by different users.

References:

Oracle documentation on object privileges: Oracle Database SQL Language Reference

Explanation of REFERENCES privilege: Oracle Database Security Guide

C. True. The error occurs because the alias "Department" used in the GROUP BY clause is enclosed in double quotes, which makes it case-sensitive. However, the column deptno is not originally created with double quotes in the table definition, so you cannot refer to it with a case-sensitive alias in the GROUP BY clause. Oracle interprets "Department" as a string literal, not a column alias, in the GROUP BY clause.

A is incorrect because you can use an alias in an ORDER BY clause. B is incorrect because an alias can contain space characters if it is quoted. D is incorrect because an alias does not always have to be specified in quotes, only when it includes special characters or spaces or if it is case-sensitive.

A. The constraint commission < salary implies that COMMISSION must be less than SALARY, so it can have a negative value if the salary is greater than zero, making this statement true.

B. The constraint named CR_MGR is listed as a Referential (R) constraint on the MANAGER column, which implies it is a foreign key that would reference the EMPNO column, making this statement true.

C. The SALARY column is part of a Check (C) constraint CC_COMM that requires the SALARY to be greater than 100, which means it must have a value and cannot be NULL, making this statement true.

D. The presence of a foreign key constraint does not automatically create an index on the foreign key column. While Oracle may implicitly create indexes for primary keys, it does not do so for foreign keys.

E. The constraint deptno > 9 prevents DEPTNO from containing values less than or equal to 9, so it cannot contain the value 1, making this statement false.

F. There is no NOT NULL constraint listed on the DEPTNO column in the constraints image, so it can contain NULL values, making this statement false.

G. There is no indication of a unique constraint on the DNAME column in the constraints image; the constraint DNAME IS NOT NULL only implies that the column cannot contain NULL values, making this statement false.

References:

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

Oracle Database Concepts, 12c Release 1 (12.1): "Indexes and Index-Organized Tables"