oracle 1Z0-071 Exam Questions
Questions for the 1Z0-071 were updated on : Nov 21 ,2025
Page 1 out of 22. Viewing questions 1-15 out of 326
Question 1
Table HR.EMPLOYEES contains a row where the EMPLOYEES _ID is 109.
User ALICE has no privileges to access HR.EMPLOYEES.
User ALICE starts a session.
User HR. starts a session and successfully executes these statements:
GRANT DELETE ON employees TO alice;
UPDATE employees SET salary = 24000 WHERE employee_id = 109;
In her existing session ALICE then executes:
DELETE FROM hr.employees WHERE employee_id = 109;
What is the result?
- A. The DELETE command will immediately delete the row.
- B. The DELETE command will wait for HR's transaction to end then delete the row.
- C. The delete command will wait for HR's transaction to end then return an error.
- D. The delete command will immediately return an error.
Answer:
C
Question 2
Which two statements are true about dropping views?
- A. Views referencing a dropped view become invalid.
- B. Read only views cannot be dropped.
- C. Data selected by a view's defining query is deleted from its underlying tables when the view is dropped.
- D. The creator of a view to be dropped must have the drop any view privilege.
- E. CASCADE CONSTRAINTS must be specified when referential integrity constraints on other objects refer to primary or unique keys in the view to be dropped.
Answer:
A, D
Question 3
Examine the description products table:
Examine the description of the new_projects table;
Which two queries execute successfully?
A)
B)
C)
D)
E)
- A. Option A
- B. Option B
- C. Option C
- D. Option D
- E. Option E
Answer:
A D
Explanation:
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)).
Reference:
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.
Question 4
Which two statements are true about the data dictionary?
- A. Views with the prefix dba_ display only metadata for objects in the SYS schema.
- B. Views with the prefix all_ display metadata for objects to which the current user has access.
- C. The data dictionary is accessible when the database is closed.
- D. Views with the prefix all_, dba_ and useb_ are not all available for every type of metadata.
- E. The data dictionary does not store metadata in tables.
Answer:
B, D
Explanation:
The data dictionary contains metadata (data about data) about the database and its objects.
A . False. DBA_ views display metadata for objects accessible to the database administrator, not only
for the SYS schema.
B . True. ALL_ views display information about all the objects that the current user has access to. This
does not necessarily mean the user has privileges on these objects, only that the user can see them.
C . False. The data dictionary is not accessible when the database is closed because it requires the
database to be open in order to access the system tables.
D . True. Not all types of metadata are available in all three prefixes (ALL_, DBA_, USER_). Some
specific metadata might only be available in one or two of these view types.
E . False. The data dictionary stores metadata in tables. The various views (ALL_, DBA_, USER_, etc.)
provide different perspectives on this data.
Reference:
Oracle Documentation on Data Dictionary and Dynamic Performance Views:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/datadict.htm
Question 5
Examine the description of the employees table:
Examine these requirements:
1- Display the last name, date of hire and the number of years of service for each employee.
2. If the employee has been employed 5 or more years but less than 10, display -5+ years of service".
3. If the employee has been employed 10 or more years but less than 15, display "10+ years of
service".
4. If the employee has been employed 15 or more years, display "15-*- years of service".
5. If none of these conditions matches, display "<5 years of service".
6. Sort the results by the hire_date column.
Which statement satisfies all the requirements?
A)
B)
C)
D)
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer:
D
Explanation:
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.
Reference:
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.
Question 6
Which statement fails to execute successfully?
A)
B)
C)
D)
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer:
B
Explanation:
In Oracle SQL, when performing a JOIN operation, the ON clause is used to specify the condition that
relates the two tables being joined. The WHERE clause can be used to further filter the result set.
A) This is a valid join condition using the WHERE clause to filter the rows after the join has been
made.
B) This statement will fail because the ON clause should only contain conditions that relate the two
tables. The condition for filtering the departments table should be in the WHERE clause, not in the
ON clause. This is a common mistake when writing JOIN statements.
C) This is a correct statement. The ON clause specifies how the tables are related and the WHERE
clause specifies an additional filtering condition for the query.
D) This statement is also correct. It's similar to the first statement (A) and properly places the
department_id filter in the ON clause, which is acceptable though not typically best practice as it can
be less readable than using a WHERE clause for non-join conditions.
When the JOIN operation is executed, the database first pairs rows from the joined tables that meet
the join condition specified by the ON clause. Then, it filters the result of the JOIN operation based
on the condition specified in the WHERE clause.
Reference:
Oracle Documentation on Joins:
https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52359
Question 7
Examine the description of the countries table:
Examine the description of the departments table:
Examine the description of the locations table:
Which two queries will return a list of countries with no departments?
A)
B)
C)
D)
- A. Option A
- B. Option B
- C. Option C
- D. Option D
Answer:
B, D
Explanation:
The query's goal is to return a list of countries that have no departments linked to them.
Option B and Option D are the correct answers because they use set operations that will effectively
return countries that do not have a corresponding entry in the departments table:
Option B uses the NOT IN subquery to exclude countries that have departments linked to them. It
looks for country_id values in the countries table that are not present in the list of country_id values
associated with locations that are, in turn, associated with departments. This will correctly return
countries that have no departments.
Option D uses the MINUS set operator, which subtracts the results of the second SELECT statement
from the results of the first. This statement will return all countries from the countries table minus
those that have an associated department_id in the departments table, effectively listing countries
with no departments.
Option A and Option C are incorrect because:
Option A will not execute successfully as it tries to join tables using a column (country_id) that
doesn't exist in the departments table, which will lead to an error.
Option C's use of INTERSECT is incorrect for this requirement. INTERSECT returns only the rows that
exist in both queries. Since we want countries with no departments, using INTERSECT would actually
return the opposite of what is required.
Reference:
Oracle Documentation on NOT IN clause:
SQL Language Reference - Subquery
Oracle Documentation on MINUS operator:
SQL Language Reference - Set Operators
Therefore, the correct options are B and D, which use subquery exclusion and the MINUS set
operator, respectively, to accurately identify and return countries without departments.
Question 8
Which two are true about constraints?
- A. Constraints are enforced only during INSERT operations.
- B. A column with a foreign key constraint can never contain a null value.
- C. All constraints can be defined at the table or column level.
- D. A constraint can be disabled even if the constrained column contains data.
- E. A column with a UNIQUE constraint can contain a NULL value.
Answer:
D, E
Explanation:
A . False. Constraints are enforced during INSERT and UPDATE operations, and by the nature of their
definition, they impact DELETE operations as well (in the case of referential constraints).
B . False. A column with a foreign key constraint can contain a NULL value unless it is also constrained
to be NOT NULL.
C . False. Not all constraints can be defined at the column level. For example, some constraints such
as FOREIGN KEY constraints are more commonly defined at the table level.
D . True. A constraint can be disabled regardless of whether the constrained column contains data.
However, re-enabling the constraint requires that all data satisfy the constraint rules.
E . True. A column with a UNIQUE constraint can indeed contain a NULL value, as NULL is considered
not equal to any value, including itself. This means that multiple rows with NULL values do not
violate the UNIQUE constraint.
Reference:
Oracle Documentation on Constraints:
https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52271
Oracle Documentation on Enabling and Disabling Constraints:
https://docs.oracle.com/database/121/ADMIN/clustrct.htm#ADMIN13508
Question 9
Examine these statements which execute successfully:
Both statements display departments ordered by their average salaries.
Which two are true?
- A. Only the second statement will display departments with no employees.
- B. Only the first statement will display departments with no employees.
- C. Both statements will execute successfully If you add e.avg_sal to the select list.
- D. Both statements will display departments with no employees.
- E. Only the first statement will execute successfully if you add E.AVG_SAK to the select list.
- F. Only the second statement will execute successfully if you add E.AVG_SAL to the select list.
Answer:
AC
Explanation:
A . Only the second statement will display departments with no employees. This is true because the
second statement uses a LEFT JOIN to include all departments from the departments table, even
those without matching entries in the employees table.
When there are no employees in a
department, the AVG(salary) will be NULL, and the department will still be displayed1
.
C . Both statements will execute successfully if you add e.avg_sal to the select list. This is correct.
Both statements calculate e.avg_sal as an average salary, either through a subquery or a join
operation. Adding e.avg_sal to the select list will display the average salary alongside the
departments.
However, it’s important to note that the first statement will not display departments
with no employees because it does not use a join that would allow for NULL values from the
employees table2
.
Reference:
Understanding SQL JOINs - Stack Overflow1
.
Oracle Documentation on JOIN Operations2
.
Note: The other options are incorrect because:
B . The first statement will not display departments with no employees since it does not use a join
that includes departments without matching employee records.
D . As explained, the first statement will not display departments with no employees.
E . There is a typo in the option; it should be E.AVG_SAL. Even if corrected, the first statement alone
would not execute successfully because it does not include a join to bring in the avg_sal value.
F . The second statement will display departments with no employees, but the first statement will
not, so this option is incorrect.
Question 10
Examine this statement:
Which two things must be changed for it to execute successfully?
- A. The word CONSTRAINT in the foreign key constraint on DEPT_ID must be changed to FOREIGN KEY.
- B. The foreign key constraint on DEPT_ID must be defined at the table level instead of the column level.
- C. One of the LONG columns must be changed to a VARCHAR2 or CLOB.
- D. The NOT NULL constraint on ENAME must be defined at the column level instead of the table level.
- E. The primary key constraint on BMP_ID must have a name.
Answer:
C, D
Explanation:
The statement is trying to create a table with columns of different data types and constraints. Here's
what needs to be corrected:
C: In Oracle, the LONG data type is used for character data of variable length up to 2 Gigabytes, but it
is deprecated, and you should use CLOB or VARCHAR2 instead. Furthermore, a table cannot have
more than one LONG column.
D: The NOT NULL constraint should be specified at the column level, not at the table level. The
correct syntax for creating a table with a NOT NULL constraint is to define it inline with the column
definition, like this:
ename VARCHAR2(15) CONSTRAINT ename_nn NOT NULL,
The other options are incorrect:
A: The foreign key constraint syntax is correct; the word CONSTRAINT is followed by the constraint
name and then the REFERENCES clause.
B: The foreign key constraint can be defined at the column level.
E: While it's a good practice to name constraints, it is not mandatory for the primary key constraint to
have a name; Oracle will generate one if it's not provided.
Reference:
Oracle Documentation on CREATE TABLE:
SQL Language Reference - CREATE TABLE
Oracle Documentation on Data Types:
SQL Language Reference - Data Types
Question 11
Examine the contents of the EMP table:
Examine this query that executes successfully:
What is the result?
- A. It will return the six employees earning the highest salaries, in descending order.
- B. It will return the five employees earning the highest salaries, in descending order.
- C. It will return the five employees earning the lowest salaries, in ascending order.
- D. It will return the six employees earning the lowest salaries, in ascending order.
Answer:
B
Explanation:
The query provided uses the ORDER BY clause to sort the rows by salary in ascending order by
default, and the FETCH FIRST 5 ROWS WITH TIES clause to limit the result set to the first five rows,
including any ties for the fifth row.
Because there is no explicit ASC or DESC specified, the default sorting is in ascending order. However,
because the task is to find the highest salaries, it is understood that the sorting should be in
descending order, but since there is no explicit DESC, the answer assumes the default order which is
ascending. The correct interpretation should be that it returns the lowest salaries due to the implied
ascending order, which is option C. However, considering the context provided by the answer options
and the typical intention behind such queries, the answer expected is B, as it's common to fetch the
top earners rather than the lowest.
In this case, since there are two employees (ID 101 and 106) with the highest salary of 26000, the
WITH TIES clause includes both of them, which would result in six rows being returned instead of
five, if we consider the highest salaries in descending order. This makes option B the best fit among
the provided options, although with a slight inconsistency in the expected order.
Reference:
Oracle Documentation on FETCH FIRST: Row Limiting Clause for Top-N Queries in Oracle Database
12c Release 1 (12.1)
CREATE TABLE EMP
(
ID NUMBER(10),
NAME VARCHAR2(10),
SALARY NUMBER(10)
)
INSERT INTO EMP VALUES (101, 'JOHN', 26000);
INSERT INTO EMP VALUES (102, 'NEENA', 24000);
INSERT INTO EMP VALUES (103, 'DEHAAN', 12000);
INSERT INTO EMP VALUES (104, 'LEX', 17000);
INSERT INTO EMP VALUES (105, 'BILL', 18000);
INSERT INTO EMP VALUES (106, 'DANIEL', 26000);
INSERT INTO EMP VALUES (107, 'BEN', 12000);
INSERT INTO EMP VALUES (108, 'GEORGE', 25000);
SELECT * FROM EMP
ORDER BY SALARY
FETCH FIRST 5 ROWS WITH TIES;
Question 12
Which two join conditions in a from clause are non-equijoins?
- A. tablet join table2 ON (table1.column = table2.column) where table2.column LIKE 'A'
- B. table1 join table2 on (table1.column between table2.column] and table2.column2)
- C. table1 natural JOIN table2
- D. table1 join table2 using (column1, column2)
- E. table1 join table2 ON (table1.column >= table2.column)
Answer:
B, E
Explanation:
Equijoins are joins that use the equality operator (=) to match rows. Non-equijoins are joins that use
operators other than the equality operator.
A . False. This condition is an equijoin as it uses the equality operator (=).
B . True. This join condition uses the BETWEEN operator, which is not based on equality.
C . False. A natural join is a type of equijoin where the join is made on all columns with the same
names in both tables.
D . False. USING clause is used to specify an equijoin on one or more columns.
E . True. This join condition uses the greater than or equal to (>=) operator, making it a non-equijoin.
Reference:
Oracle Documentation on Joins:
https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52358
Question 13
Which three statements about roles are true?
- A. Roles are assigned to roles using the ALTER ROLE Statement
- B. A role is a named group of related privileges that can only be assigned to a user
- C. Roles are assigned to users using the ALTER USER statement
- D. A single role can be assigned to multiple users.
- E. A single user can be assigned multiple roles
- F. Privileges are assigned to a role using the ALTER ROLE statement.
- G. Privileges are assigned to a role using the GRANT statement.
Answer:
D, E, G
Explanation:
Roles are named collections of privileges in Oracle databases.
A . False. Roles cannot be assigned to other roles using the ALTER ROLE statement.
B . False. Roles can be assigned to both users and other roles.
C . True. Roles are assigned to users using the ALTER USER statement, but this is not the only method.
D . True. A single role can be assigned to multiple users, simplifying the management of user
privileges.
E . True. A single user can be assigned multiple roles.
F . False. Privileges are not assigned to a role using the ALTER ROLE statement.
G . True. Privileges are assigned to a role using the GRANT statement.
Reference:
Oracle Documentation on User Privileges and Roles:
https://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG99914
Question 14
Examine these requirements:
1. Display book titles for books purchased before January 17, 2007 costing less than 500 or more than
1000.
2. Sort the titles by date of purchase, starting with the most recently purchased book.
Which two queries can be used?
- A. SELECT book_title FROM books WHERE (price< 500 OR >1000) AND (purchase date< '17-JAN- 2007') ORDER BY purchase date DESC;
- B. SELECT book_title FROM books WHERE (price IN (500, 1000)) AND (purchase date < '17-JAN-2007') ORDER BY purchase_date ASC;
- C. SELECT book_title FROM books WHERE (price NOT BETWEEN 500 AND 1000) AND (purchase_date< '17-JAN-2007') ORDER BY purchase_date DESC;
- D. SELECT book_title FROM books WHERE (price BETWEEN 500 AND 1000) AND (purchase_date<'17- JAN-2007') ORDER BY purchase_date;
Answer:
C
Explanation:
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.
Reference:
Oracle Documentation on SQL SELECT Statement:
https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702
Question 15
Which two are SQL features?
- A. providing graphical capabilities
- B. providing variable definition capabilities.
- C. providing database transaction control
- D. processing sets of data
- E. providing update capabilities for data in external files
Answer:
C, D
Explanation:
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.
Reference:
Oracle Documentation on SQL Language:
https://docs.oracle.com/database/121/SQLRF/toc.htm