ORACLE DATABASE 12c SQL EXAM

ORACLE
2022




DATABASE 12c
SQL EXAM


Oracle 1Z0-071
ORACLE



Exam Version | April 18, 2022

,Oracle Database SQL

Question #1
Evaluate the following SQL statement:
SQL> select cust_id, cust_last_name "Last name"

FROM customers -

WHERE country_id = 10 -

UNION -
SELECT cust_id CUST_NO, cust_last_name

FROM customers -

WHERE country_id = 30 -
Identify three ORDER BY clauses either one of which can complete the query.

• A. ORDER BY "Last name"
• B. ORDER BY 2, cust_id
• C. ORDER BY CUST_NO
• D. ORDER BY 2, 1
• E. ORDER BY "CUST_NO"

Question #2
Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement?
(Choose three.)

• A. WHERE and HAVING clauses cannot be used together in a SQL statement.
• B. The HAVING clause conditions can have aggregate functions.
• C. The HAVING clause conditions can use aliases for the columns.
• D. The WHERE clause is used to exclude rows before the grouping of data.
• E. The HAVING clause is used to exclude one or more aggregated results after grouping
data.

Question #3
Which statement is true regarding external tables?

• A. The CREATE TABLE AS SELECT statement can be used to upload data into a normal table
in the database from an external table.
• B. The data and metadata for an external table are stored outside the database.
• C. The default REJECT LIMIT for external tables is UNLIMITED.
• D. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when
used with an external table.


ORACLE 1

,Oracle Database SQL



Question #4
Which two statements are true about Data Manipulation Language (DML) statements?

• A. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
• B. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on
a table.
• C. A DELETE FROM..... statement can remove rows based on only a single condition on a
table.
• D. An INSERT INTO... VALUES..... statement can add a single row based on multiple
conditions on a table.
• E. A DELETE FROM..... statement can remove multiple rows based on multiple conditions on
a table.
• F. An UPDATE....SET.... statement can modify multiple rows based on only a single condition
on a table.

Question #5
Which two statements are true regarding roles? (Choose two.)

• A. A role can be granted to itself.
• B. A role can be granted to PUBLIC.
• C. A user can be granted only one role at any point of time.
• D. The REVOKE command can be used to remove privileges but not roles from other users.
• E. Roles are named groups of related privileges that can be granted to users or other roles.

Question #6
Which two statements are true regarding constraints? (Choose two)

• A. A constraint is enforced only for an INSERT operation on a table.
• B. A foreign key cannot contain NULL values.
• C. A column with the UNIQUE constraint can store NULLS.
• D. You can have more than one column in a table as part of a primary key.




ORACLE 2

, Oracle Database SQL



Question #7
Evaluate the following statement.




Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT
statement?

• A. They are evaluated by all the three WHEN clauses regardless of the results of the
evaluation of any other WHEN clause.
• B. They are evaluated by the first WHEN clause. If the condition is true, then the row would
be evaluated by the subsequent WHEN clauses.
• C. They are evaluated by the first WHEN clause. If the condition is false, then the row would
be evaluated by the subsequent WHEN clauses.
• D. The insert statement would give an error because the ELSE clause is not present for
support in case none of WHEN clauses are true.

Question #8
Examine the structure of the MEMBERS table:




You want to display details of all members who reside in states starting with the letter A followed by
exactly one character.
Which SQL statement must you execute?

• A. SELECT * FROM MEMBERS WHERE state LIKE '%A_';
• B. SELECT * FROM MEMBERS WHERE state LIKE 'A_';

ORACLE 3