Free 1Z0-071 Practice Test Questions and Answers (2026) Practice Questions

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

A . This statement is false. In NVL2, the first expression is returned if it is not null.

B . This statement is false. NVL2 takes exactly three arguments, not any number of expressions.

C . This statement is false. COALESCE stops evaluating its list of expressions as soon as it finds the first

non-null value.

D . This is true. COALESCE returns the first non-null expression in its list.

E . This statement is true. NVL requires the first and second expressions to be of the same or

compatible data types because it returns the first expression if it is not null, otherwise it returns the

second.

F . This statement is false. NVL takes exactly two arguments, not any number.

Reference:

Oracle Documentation on COALESCE:

https://docs.oracle.com/database/121/SQLRF/functions040.htm#SQLRF00625

Oracle Documentation on NVL:

https://docs.oracle.com/database/121/SQLRF/functions130.htm#SQLRF00683

Oracle Documentation on NVL2:

https://docs.oracle.com/database/121/SQLRF/functions123.htm#SQLRF00682

Regarding the DROP TABLE command:

A . ALL constraints defined on HR.EMPLOYEES are dropped: Dropping a table will automatically drop

all constraints associated with that table.

B . The HR.EMPLOYEES table may be moved to the recycle bin: In Oracle, unless explicitly using

PURGE, dropped tables go to the recycle bin, allowing recovery.

E . All indexes defined on HR, EMPLOYEES are dropped: When a table is dropped, all its indexes are

also automatically dropped.

Incorrect options:

C: Synonyms are not automatically dropped when the table is dropped; they become invalid.

D: Sequences are independent objects and are not dropped when a table is dropped.

F: Views are not dropped; however, they will return errors upon being queried if their base table is

dropped.

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

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.

Reference:

Oracle documentation on object privileges: Oracle Database SQL Language Reference

Explanation of REFERENCES privilege: Oracle Database Security Guide

A . True, arithmetic operators of equal precedence are evaluated from left to right within an

expression, according to the standard SQL operator precedence. B. True, multiple parentheses can be

used in an expression to change the order of operations and override the default precedence of

operators.

C, D, and E are not correct because: C. The + binary operator does not have the highest precedence;

multiplication and division have higher precedence in SQL.

D. NULLS do not influence the precedence

of operators in an expression; they may affect the result of an operation but not the order in which

operators are evaluated. E. The concatenation operator (||) has lower precedence than arithmetic

operators in SQL expressions.

Reference:

Oracle documentation on operator precedence: Oracle Database SQL Language Reference

In Oracle Database 12c SQL, regarding date/time functions and considering a session where

NLS_DATE_FORMAT is set to DD-MON-YYYY SH24:MI:SS:

C . CURRENT_DATE returns the current date and time as per the session time zone. This is correct as

CURRENT_DATE returns the current date and time in the time zone of the current SQL session, as set

by the ALTER SESSION command.

D . SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the

database server. This is partially correct. SYSDATE returns the current date and time from the

operating system of the database server. However, CURRENT_DATE returns the date and time set for

the client's operating system environment, adjusted to the session time zone.

Options A, B, E, and F are incorrect based on Oracle's documentation:

A is incorrect because SYSDATE is independent of the NLS_DATE_FORMAT setting.

B is incorrect because CURRENT_TIMESTAMP includes time zone information, which can differ from

CURRENT_DATE.

E is incorrect because CURRENT_TIMESTAMP differs from SYSDATE by including fractional seconds

and time zone.

F is incorrect as SYSDATE can be queried in any SELECT statement, not just from DUAL.

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

Data Manipulation Language (DML) operations in Oracle 12c are critical for handling data within

tables. Here’s the validity of each statement based on Oracle SQL documentation:

Option A: True. The DELETE statement can remove multiple rows from a table and supports the use

of multiple conditions in the WHERE clause to specify which rows should be removed. This allows for

flexibility in managing data deletion based on various criteria.

Option B: True. The INSERT statement can explicitly insert NULL values into a column, assuming there

are no constraints (like NOT NULL) preventing such entries. This is useful for representing the

absence of data or when data is unknown.

Option C: False. DML statements like INSERT INTO...SELECT...FROM do not automatically commit in

Oracle. Transactions in Oracle require explicit COMMIT commands to finalize changes, unless in an

autocommit mode set by a client tool or environment, which is generally not the default behavior for

server-side operations.

Option D: False. DML statements do not require a primary key to be defined on a table. While having

a primary key is a best practice for identifying each row uniquely, it's not a prerequisite for

performing DML operations like INSERT, UPDATE, or DELETE.

Option E: True. The UPDATE statement can include different subqueries within the SET clause to

determine the values for each column being updated. This allows for complex calculations and data

retrieval to be part of an update, increasing the power and flexibility of data modification.

Synonyms in Oracle Database are aliases for database objects.

A . Correct. A synonym can be created for another synonym, essentially chaining synonyms. B.

Incorrect. Private synonyms must be unique within a schema. However, because each user has their

own schema, multiple users can have private synonyms with the same name, each pointing to

objects in their respective schemas. C. Correct. Any user with the necessary privileges can create a

PUBLIC synonym, which is accessible to all users. D. Incorrect. A synonym cannot be created for an

object within a package, but it can be created for the package itself. E. Incorrect. Synonyms, like

views, do not have object numbers because they do not occupy space in the database as tables do.

Reference can be found in the Oracle Database SQL Language Reference documentation, which

details the rules and functionality of synonyms.

a table in another schema, the user must have

the REFERENCES object privilege on the target table in the other schema. This privilege is specific

and necessary for the integrity constraints involving foreign keys.

Understanding the functionality of SQL joins and their application in a relational database:

Option A: Correct. A self join is used to relate or compare rows within the same table. It typically

involves using table aliases to distinguish the different instances of the table in the query.

Option B: Incorrect. An inner join relates rows between two different tables based on a common

condition, not within the same table.

Option C: Incorrect. SQL allows joining of rows from any tables, whether directly related by foreign

keys or not, as long as there is a logical condition that can relate their columns.

Option D: Correct. Sometimes establishing a relationship between two specific tables might require

joining through one or more intermediary tables, particularly in cases involving complex

relationships or normalization.

Option E: Incorrect. While joins are often used to implement relationships, not every logical

relationship necessitates a join condition, especially if data from only one table is required or if

multiple relations are possible but not relevant to the specific query.

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.

Reference:

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

tables.

For the EMPLOYEES table and given that NLS_DATE_FORMAT is set to DD-MON-YY, the query that

requires explicit data type conversion is:

D . SELECT join_date FROM employees where join_date > '10-02-2018'; This query compares the

JOIN_DATE column of type DATE with a string '10-02-2018'. The string must be explicitly converted to

a DATE using the TO_DATE function with a format mask that matches the NLS_DATE_FORMAT or the

string literal.

Options A, B, C, and E do not necessarily require explicit data type conversion:

A does not require conversion because adding a number to a number column is a valid operation.

B is syntactically incorrect; the correct function to extract a substring is SUBSTR(join_date, 1, 2) and

that would require an explicit conversion since JOIN_DATE is a DATE and SUBSTR expects a string, but

it seems to be a typo.

C uses an invalid concatenation operation with the pipe symbols, which could be a typo or

misunderstanding, but would not typically require a data type conversion.

E requires implicit data type conversion because adding a number to a date is valid in Oracle, which

adds the number as days to the date.

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

A . Correct. Foreign key constraints can be nullable. This allows the possibility of a row not having a

link to another table. B. Incorrect. Unique key constraints do not allow multiple rows to have null

values unless the constraint is defined on multiple columns. C. Incorrect. Primary key columns must

be NOT NULL and unique across the table. D. Incorrect. Primary or unique key values do not refer to

foreign keys; it's the foreign keys that refer to primary or unique keys. E. Correct. Foreign key

constraints enforce referential integrity by requiring that each foreign key value matches a primary or

unique key value in the related table.

These are standard rules in relational databases, which can be confirmed in the Oracle Database

Concepts Guide.

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.

In the context of SQL statements using SET operations like UNION in Oracle Database 12c:

A . The data type of each column returned by the second query must be implicitly convertible to the

data type of the corresponding column returned by the first query. This is correct. Oracle allows the

union of columns as long as their data types are implicitly convertible, not necessarily identical.

C . The number, but not names, of columns must be identical for all SELECT statements in the query.

This is correct. For a UNION operation to be valid, all SELECT statements involved must have the

same number of columns, although their names and exact data types need not match.

Options B, D, and E are incorrect:

B is incorrect because exact data type matches are not required, only that they be implicitly

convertible.

D is also incorrect for the same reason as B; it's enough that the types are compatible, not identical.

E is incorrect as the names

To prevent the prompting for a substitution variable value when executing a query:

B . Execute the SET VERIFY OFF command before executing the query: This command turns off the

substitution variable prompting, which means that SQL*Plus will not prompt for a value and will use

whatever value is currently set for that variable.

F . Use the DEFINE command before executing the query: By using the DEFINE command to set a

value for the variable before the query execution, you ensure that SQL*Plus uses this predefined

value instead of prompting for it.

Reference:

Oracle SQL*Plus User's Guide and Reference, which discusses how to manage substitution variables.

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.

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

Regarding Oracle join and ANSI join syntax: B . The Oracle join syntax supports the creation of a Cartesian product of two tables. This is true. In Oracle, if you list tables in the FROM clause without a join condition, it creates a Cartesian product. C . The SQL:1999 compliant ANSI join syntax supports natural joins. This is true. ANSI syntax supports natural joins, which join tables based on columns with the same names in the joined tables. F . The SQL:1999 compliant ANSI join syntax supports the creation of a Cartesian product of two tables. This is true. The ANSI standard allows for Cartesian products when tables are listed in the FROM clause without a join condition. Options A, D, E, and G are incorrect: A is incorrect because the Oracle join syntax supports all types of joins, including right outer joins. D is incorrect because Oracle's proprietary join syntax does not use the term "natural join." E is incorrect because there is no inherent performance difference between Oracle join syntax and ANSI join syntax; performance depends on how the query is written and how the database optimizer handles it. G is incorrect for the same reason as E. Reference: Oracle Database SQL Language Reference, 12c Release 1 (12.1)

Synonyms in Oracle are aliases for database objects that can simplify SQL statements for database

users.

A . The term "GLOBAL" is not used in the creation of synonyms in Oracle.

B . The statement without the keyword PUBLIC will create a private synonym that is only accessible

to the user creating the synonym, not all users.

C . The correct syntax does not include PUBLIC as a prefix to the synonym name itself, making this

option incorrect.

D . You cannot specify the SYS schema for creating synonyms, as it is reserved for system objects.

E . This is the correct syntax to create a public synonym, which makes the underlying object

accessible to all users.

Reference:

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

To find customers whose last names start with "Mc" or "MC", we need to ensure our SQL query

correctly identifies and compares these prefixes regardless of case variations. Let's analyze the given

options:

Option B: WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE UPPER('MC%')

This clause uses UPPER to convert both the extracted substring (starting just after the first space,

assuming it indicates the start of the last name) and the comparison string 'MC%' to uppercase. This

ensures case-insensitive comparison. The LIKE operator is used to match any last names starting with

"MC", which will correctly capture both "Mc" and "MC". This option is correct.

Option C: WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') + 1)) LIKE 'Mc%'

This clause applies INITCAP to the substring, which capitalizes the first letter of each word and makes

other letters lowercase. The result is compared to 'Mc%', assuming only the last name follows the

space. This approach will match last names starting with "Mc" (like "McEwen"), but not "MC".

However, considering we're looking for "Mc" specifically, this clause works under the assumption

that "Mc" is treated as proper capitalization for these last names. Thus, it can also be considered

correct, albeit less inclusive than option B.

The other options either use incorrect syntax or apply case-sensitive matches without ensuring that

both "Mc" and "MC" are captured:

Option A: Contains syntax errors (unmatched quotes and wrong use of IN).

Option D: Uses case-sensitive match without combining both "Mc" and "MC".

Option E: Only matches "Mc", which is too specific.