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

The REPLACE function in Oracle SQL is used to replace occurrences of a specified substring with another substring. In this query, the inner REPLACE function call REPLACE(cust_last_name, 'son', '') removes the substring 'son' from cust_last_name. The outer REPLACE function call then replaces the substring 'An' with 'O'. For the given data, 'Anderson' would first be transformed to 'Ander' by the inner REPLACE, and then 'Ander' would be transformed to 'Oder' by the outer REPLACE. Similarly, 'Ausson' would first change to 'Aus' by the inner REPLACE, which is unaffected by the outer REPLACE.

Reference can be found in the Oracle Database SQL Language Reference documentation, which details the functionality of string functions, including REPLACE.

When creating a new table with a query, the statements that are true are:

A. SALES1 is created with 1 row.This is incorrect actually; the given query with WHERE 1=1 will retrieve all rows from the sales table, not just one row. Therefore, SALES1 will be created with the same number of rows as in the SALES table, assuming there are no WHERE clause conditions limiting the rows.

D. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table.This is true. When a table is created using a CREATE TABLE AS SELECT statement, the NOT NULL constraints on the columns in the selected columns are preserved in the new table.

Options B and C are incorrect:

B is incorrect because the primary key and unique constraints are not carried over to the new table when using the CREATE TABLE AS SELECT syntax.

C is incorrect in the context of this scenario. However, if interpreted as a standalone statement (ignoring A), C would be the correct description of the outcome since WHERE 1=1 will not filter out any rows.

Given the statements, the failure to execute would be due to improper SQL syntax or logical structuring of the JOIN clause:

A. SELECT * FROM employees e JOIN department d WHERE e.department_id=d.department_id AND d.department_id=90: This statement will fail because it incorrectly uses the JOIN syntax without an ON clause, which is necessary to specify the condition on which the tables are joined. This statement mistakenly places the join condition in the WHERE clause without using ON.

Correct options (executes successfully):

B: Correctly formulated JOIN with ON and an additional condition in the WHERE clause.

C: Incorporates the filtering directly in the ON clause, which is a valid method to restrict the rows returned by the JOIN based on conditions related to both tables.

D: Although it appears awkward due to the order of conditions in the ON clause, it is syntactically correct.

These answers reflect typical use and constraints in SQL queries and are aligned with Oracle Database 12c standards for SQL syntax and operations.

In the SQL statement provided, the error is caused by the GROUP BY clause. In Oracle SQL, when using GROUP BY, the expression in the GROUP BY clause must match exactly the expression in the SELECT list. The GROUP BY clause should reference the column name dept_no as mentioned in the SELECT list, not the alias department_id which is defined within the same SELECT statement.

The corrected SQL statement should be:

SELECT dept_no AS department_id, MAX(salary) As max_sal FROM employees WHERE salary > 10000 GROUP BY dept_no -- Changed from 'department_id' to 'dept_no' ORDER BY max_sal;

References:

Oracle Documentation on GROUP BY clause: GROUP BY Clause

For situations where the transaction would complete by committing the updates:

A. When the session logs out successfully: When a user session logs out, Oracle automatically commits any outstanding transactions.

C. When a CREATE INDEX statement is executed successfully in the same session: Most DDL statements, including CREATE INDEX, cause an implicit commit before and after they are executed.

F. When a DBA issues a successful SHUTDOWN TRANSACTIONAL statement: This type of shutdown ensures that active transactions are either committed or rolled back. If a COMMIT is then issued explicitly, it would be redundant but emphasizes the transaction completion.

Incorrect options:

B: SHUTDOWN IMMEDIATE will roll back transactions, not commit them.

D: A COMMIT in one session cannot affect the transaction state of another session; each session is isolated in terms of transaction management.

E: If a CREATE TABLE AS SELECT statement executes unsuccessfully, no implicit commit is performed; the statement failure means the transaction state remains unchanged.

In data modeling and database design, the direction of a relationship typically includes:

Option B: a cardinality

Cardinality refers to the numerical relationships between two entities, indicating the number of instances of one entity that can or must be associated with each instance of another.

Option C: label

A label is often used to describe the role or purpose of the relationship in a way that clarifies the nature of the interaction between entities.

Option D: an optionality

Optionality indicates whether or not a relationship is mandatory or optional; in other words, it shows if an instance of an entity must be related to an instance of another entity or if it can exist without such a relationship.

Options A, E, and F are not part of the direction of a relationship:

Option A: An attribute is a property or characteristic of an entity, not the direction of a relationship.

Option E: A unique identifier uniquely identifies each instance of an entity, not a relationship's direction.

Option F: An entity is a thing with distinct and independent existence in a database, usually mapped to a table, not a part of the direction of a relationship.

The requirements specify that we need to select book titles based on a price condition and a date condition, and then sort the results by the date of purchase.

A. This query will not execute successfully because there is a syntax error in the price condition. It should be price < 500 OR price > 1000.

B. This query is incorrect. The requirement specifies that the price must be less than 500 or more than 1000, not in a list of values.

C. This query is correct. It selects books where the price is not between 500 and 1000 (thus, less than 500 or more than 1000) and the purchase date is before January 17, 2007, ordered by purchase date in descending order.

D. This query is incorrect because it selects books with prices between 500 and 1000, which is not what the requirement specifies.

For the sorting of output in compound queries (INTERSECT, UNION, etc.):

D. The first column in the first SELECT of the compound query: By default, Oracle does not automatically sort the results of SET operations unless an ORDER BY clause is explicitly stated. However, if an ORDER BY is implied or specified without explicit columns, the default sorting would logically involve the first column specified in the first SELECT statement of the compound query.

Option D is the correct SQL statement that satisfies all the requirements mentioned. The CASE statement correctly compares the hire_date to date intervals subtracted from the current date (SYSDATE) to determine the number of years of service. This CASE statement is also appropriately ordered to ensure that the first condition matched is the one returned, preventing overlapping of the conditions.

Here is how Option D works according to the requirements:

It selects the last_name and hire_date from the employees table.

The CASE statement is used to calculate the number of years of service and to display the appropriate message according to the intervals defined.

The ORDER BY hire_date clause ensures the results are sorted by the date of hire.

In Option D, the intervals are defined in the correct order, ensuring that the first true condition is the one that is used:

The first WHEN checks if the hire date is 15 or more years ago.

The second WHEN checks if it is 10 or more years ago.

The third WHEN checks if it is 5 or more years ago.

The ELSE clause covers any hire dates less than 5 years ago.

The correct syntax for the intervals is SYSDATE - hire_date >= TO_YMINTERVAL('15-0') and similarly for the 10 and 5 years intervals.

Options A, B, and C are incorrect because they have various issues, such as incorrect ordering of the CASE statement's conditions, which could lead to incorrect results due to overlapping intervals, or the use of the TO_YMINTERVAL function that may not properly cover the intended date ranges.

References:

Oracle Documentation on CASE Expressions: SQL Language Reference - CASE Expression

Oracle Documentation on TO_YMINTERVAL Function: SQL Language Reference - TO_YMINTERVAL

Oracle Documentation on ORDER BY Clause: SQL Language Reference - ORDER BY Clause

Therefore, Option D is the statement that fulfills all the requirements for displaying the number of years of service based on the employee's hire date and ordered by the hire date.

Key components of an Entity-Relationship Model (ERM) include:

B. an attribute: Attributes are properties or characteristics of an entity, such as a person's name, date of birth, etc., and are essential in describing the data aspects of an entity in an ER model.

E. a relationship: Relationships describe how entities interact with each other within the database structure, such as a customer placing an order.

F. an entity: Entities are the key components of an ER model, representing objects or things within the domain that have a distinct existence, like 'Customer' or 'Order'.

Incorrect options:

A: A table is a database structure used to implement an entity in relational databases, not a component of the ER model itself.

C: While unique identifiers are crucial in database implementation (typically as primary keys), they are a specific attribute type, not a general component of ER models.

D: An activity is not a component of an ER model; activities relate more to process models or behavioral models in systems design.

Single-row functions in SQL return one result per row, and the following statements are true:

A: The data type of the returned value can indeed be different from the data type of the argument provided to the function. For example, the TO_CHAR function can take a numeric input and return a character string.

B: Single-row functions can be nested within each other to any level that is supported by Oracle. This means you can have a function call as an argument to another function, and so on.

E: The argument to a single-row function can be a column name, a variable, a literal, or an expression. This flexibility allows these functions to be very powerful in SQL expressions.

The incorrect options are:

C: Single-row functions do not return a single result row per table; they return a result for each row that is processed.

D: They are not limited to accepting only one argument. Some functions, like NVL, accept multiple arguments.

F: They are not limited to use in the WHERE clause; single-row functions can be used in any part of a SQL statement, including SELECT and ORDER BY clauses.

References:

Oracle Documentation on Single-Row Functions: SQL Functions

To determine which queries will execute successfully, we need to consider the compatibility of the column definitions and the structure of the SELECT statements:

Option A uses the MINUS set operator, which subtracts rows returned by the second SELECT statement from the rows returned by the first. For MINUS to work, the number and the order of columns and their data types must be the same in both queries. This query will not execute successfully because the second SELECT statement does not include all columns from the first SELECT statement, and the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).

Option B uses the UNION ALL set operator, which appends the results of the second SELECT statement to the results of the first. Unlike UNION, UNION ALL does not eliminate duplicate rows. This query will execute successfully because UNION ALL does not require the same data types or sizes, and the result will contain all columns from the first SELECT statement filled with NULL for non-matching columns from the second SELECT statement.

Option C uses the UNION set operator, which requires the same number of columns and compatible data types. This query will not execute successfully because PROD_NAME has different data types (CHAR(4) vs VARCHAR2(10)), and the result of a UNION must have the same number of columns with compatible data types in the two SELECT statements.

Option D uses the UNION set operator as well, but unlike Option C, it does not require a specific data type match because both SELECT statements include all columns and UNION is used (which will automatically handle type conversion where necessary). This query will execute successfully.

Option E uses the INTERSECT set operator, which requires the same number and order of columns and their data types to be identical or compatible. This query will not execute successfully because the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).

References:

Oracle Documentation on Set Operators: SQL Language Reference - Set Operators

Oracle Documentation on Data Type Precedence: SQL Language Reference - Data Type Precedence

In conclusion, only Option B and Option D will execute successfully because they adhere to the rules of the UNION ALL and UNION operators respectively, regarding column count and data type compatibility.

Multiple row subqueries are subqueries that are used where a subquery is allowed, but they can return more than one row. This means they can be used with operators that allow multiple values like IN, ANY, ALL, etc.

Option A: They can contain HAVING clauses. This is true. A multiple row subquery can use aggregate functions and, therefore, can include a HAVING clause to filter the results of these aggregates. A HAVING clause is used in a similar way to a WHERE clause, except that it is used to filter rows after an aggregation is performed.

Option D: They can return multiple columns. This is true. A multiple row subquery can indeed return more than one column, which can be used in the outer query, depending on the context, such as in the column comparison subqueries.

Option E: They can contain GROUP BY clauses. This is true. Since multiple row subqueries can include aggregate functions, they can also include a GROUP BY clause to specify the grouping of data on which to apply the aggregate functions.

Regarding the incorrect options:

Option B: Two or more values are always returned from the subquery. This is false. Multiple row subqueries have the potential to return multiple rows, but they are not required to do so. They could return zero, one, or multiple rows depending on the data and the specific query.

Option C: They cannot contain subquery. This is false. Multiple row subqueries can contain other subqueries within them, which are known as nested subqueries.

For further reading and verification of these points, one can refer to the Oracle Database SQL Language Reference 12c, specifically the sections on subquery syntax and semantics, which discuss the use of HAVING, GROUP BY, and the ability of subqueries to return multiple rows or columns.

When evaluating whether expressions in SQL queries are valid, consider data type compatibility and the operations performed:

Option A: Valid. The expression performs arithmetic operations (multiplication and division) on numeric data types (unit_price, constants). These operations are allowed and make sense in a mathematical context.

Option B: Valid. This query calculates the difference between two dates (expiry_date and delivery_date), which results in a numeric value representing the number of days between the dates. The result is then multiplied by 2, which is a valid operation on a numeric result.

Option C: Invalid. The expression unit_price || 5 attempts to concatenate a numeric value with a number, which is not valid without explicit conversion to a string. Moreover, the use of quotes around "Discount" is syntactically incorrect in this context.

Option D: Invalid. The expression expiry_date * 2 attempts to multiply a DATE datatype by a numeric value, which is not a valid operation.

Option E: Invalid. Similar to Option C, it incorrectly attempts to concatenate a number directly with a numeric value without conversion. Additionally, the aliasing with quotes is incorrectly placed.

Option F: Valid. This query simply adds two numeric columns (unit_price and surcharge), which is a valid and commonly used arithmetic operation in SQL.

This SQL query checks for product IDs (prod_id) from the costs table where the promotion ID (promo_id) is associated with promotions whose costs are less than the maximum promotional cost of all promotions, grouped by the duration of each promotion (calculated as promo_end_date - promo_begin_date). The query is correctly written to fulfill these conditions.

The subquery SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_date - promo_begin_date) computes the maximum cost of promotions grouped by their duration, returning the highest cost for each distinct promotion length.

The WHERE promo_cost < ALL (...) clause then filters out the promotions where the promo_cost is less than all of the maximum costs returned by the subquery, ensuring that only promo_ids associated with costs less than the highest cost for any promotion length are considered.

Since this logical structure correctly implements the given requirement, the statement executes successfully and yields the correct results.

Regarding creating tables in an Oracle database:

B. The same table name can be used for tables in different schemas: In Oracle, a schema is essentially a namespace within the database; thus, the same table name can exist in different schemas without conflict, as each schema is distinct.

C. A system privilege is required: To create tables, a user must have the necessary system privileges, typically granted explicitly or through roles such as CREATE TABLE or administrative privileges depending on the environment setup.

Incorrect options for all three repeated questions:

A: Oracle SQL does not allow specifying the maximum number of rows directly in a CREATE TABLE statement; this is controlled by storage allocation and database design rather than table creation syntax.

D: Creating an external table does not create the physical file. It merely creates a table structure that allows access to data stored in an external file specified in the directory; the file itself must already exist or be managed outside of Oracle.

E: A primary key constraint is not mandatory for creating tables. While it is a common practice to define a primary key to enforce entity integrity, it is not required by the Oracle SQL syntax for table creation.

These answers and explanations are aligned with Oracle Database 12c SQL documentation and standard practices.

SQL (Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system.

A. False. SQL does not have graphical capabilities; it is a textual language for database interaction.

B. False. SQL supports variable definition, but it is not a core feature of the language. Variables are more commonly defined in procedural extensions to SQL, such as PL/SQL in Oracle.

C. True. SQL provides database transaction control through statements like COMMIT, ROLLBACK, and SAVEPOINT.

D. True. SQL is designed for processing sets of data, allowing for operations such as selection, projection, and joins on sets of rows.

E. False. SQL does not provide capabilities to update data in external files. It operates on data within the database.

In Oracle 12c SQL, the GROUP BY clause is used to arrange identical data into groups with the GROUP BY expression followed by the SELECT statement. The SUM() function is then used to calculate the sum for each grouped record on a specific column, which in this case is the salary column.

Option A is valid because it correctly applies the GROUP BY clause. Both dept_id and join_date are included in the SELECT statement, which is a requirement when using these columns in conjunction with the GROUP BY clause. This means that the query will calculate the sum of salaries for each combination of dept_id and join_date. It adheres to the SQL rule that every item in the SELECT list must be either an aggregate function or appear in the GROUP BY clause.

Option B is invalid due to a typo in SELECT depe_id and also because it ends with a colon rather than a semicolon.

Option C is invalid because you cannot nest aggregate functions like MAX(AVG(salary)) without a subquery.

Option D is invalid for the same reason as option C, where it tries to nest aggregate functions AVG(MAX(salary)), which is not allowed directly in SQL without a subquery.

For further reference, you can consult the Oracle 12c documentation, which provides comprehensive guidelines on how to use the GROUP BY clause and aggregate functions like SUM():

Oracle Database SQL Language Reference, 12c Release 1 (12.1): GROUP BY Clause

Oracle Database SQL Language Reference, 12c Release 1 (12.1): Aggregate Functions

In Oracle SQL, when you subtract a number from a date, the result is a date. When you subtract one date from another, the result is the number of days between the two dates.

B: PROMO_BEGIN_DATE - 5 will subtract 5 days from the PROMO_BEGIN_DATE, resulting in a new date that is 5 days earlier than PROMO_BEGIN_DATE.

C: PROMO_BEGIN_DATE - SYSDATE will return the number of days between the PROMO_BEGIN_DATE and the current date (SYSDATE).

The incorrect options are:

A: TONUMBER(PROMO_BEGIN_DATE) - 5 will not return a number because PROMO_BEGIN_DATE is a date, and TONUMBER is not a valid function to convert dates to numbers in Oracle.

D: PROMO_BEGIN_DATE - SYSDATE will not return an error; it will return the number of days between the two dates as explained above.

E: TODATE(PROMO_BEGIN_DATE * 5) will not return a date because PROMO_BEGIN_DATE * 5 is not a valid operation in Oracle SQL as you cannot multiply a date by a number, and TODATE is not a valid function. The correct function name is TO_DATE.

References:

Oracle Documentation on Date Arithmetic: Database SQL Language Reference - Datetime Functions