Oracle Database SQL (1Z0-071) — Questions and Answers

Question 1: What is a self-join in Oracle SQL?

A join that uses no WHERE condition
Joining a table to itself using aliases (Correct answer)
Joining two copies of different tables
A join between primary and foreign key columns only

Correct answer: Joining a table to itself using aliases

A self-join joins a table to itself using different aliases, commonly used for hierarchical data like employee-manager relationships.

Question 2: Which of the following functions does not work as a grouping function?

MIN
COUNT
DISTINCT (Correct answer)
SUM

Correct answer: DISTINCT

Grouping functions, also known as aggregate functions, perform a calculation on a set of rows and return a single summary value. Examples include `MIN` (minimum value), `SUM` (total sum), and `COUNT` (number of rows). `DISTINCT`, however, is a keyword used to eliminate duplicate values from a result set or within an aggregate function, but it is not an aggregate function itself.

Question 3: In Oracle SQL, which DML statement modifies existing rows in a table?

CHANGE
UPDATE (Correct answer)
MODIFY
ALTER

Correct answer: UPDATE

The UPDATE statement modifies values in existing rows based on a WHERE condition, affecting all rows if WHERE is omitted.

Question 4: Which Oracle SQL INSERT syntax inserts data into specific columns only?

INSERT INTO table VALUES (...) COLUMNS (...)
INSERT INTO table SET col1=val1, col2=val2
INSERT INTO table (col1, col2) VALUES (val1, val2) (Correct answer)
INSERT PARTIAL INTO table VALUES (val1, val2)

Correct answer: INSERT INTO table (col1, col2) VALUES (val1, val2)

Listing column names in parentheses after the table name allows inserting values into specific columns; unspecified columns receive their default values or NULL.

Question 5: Which Oracle SQL feature allows you to set a named point within a transaction to partially roll back?

SAVEPOINT (Correct answer)
BOOKMARK
TRANSACTION MARK
CHECKPOINT

Correct answer: SAVEPOINT

SAVEPOINT marks a named point in a transaction so that ROLLBACK TO SAVEPOINT can undo changes made after that point without canceling the entire transaction.

Question 6: Which Oracle SQL constraint ensures a column's value meets a specific condition?

UNIQUE
NOT NULL
FOREIGN KEY
CHECK (Correct answer)

Correct answer: CHECK

A CHECK constraint validates that column values satisfy a specified Boolean condition, such as salary > 0.

Question 7: What does the BETWEEN operator do in Oracle SQL?

Checks if a value falls within an inclusive range (Correct answer)
Compares two strings for equality
Checks if a value is NULL
Returns rows between two tables

Correct answer: Checks if a value falls within an inclusive range

BETWEEN tests whether a value is within a range inclusively, equivalent to value >= lower AND value <= upper.

Question 8: Which Oracle SQL clause is used to eliminate groups that don't meet a condition after GROUP BY?

HAVING (Correct answer)
FILTER BY
EXCLUDING
WHERE

Correct answer: HAVING

HAVING filters grouped rows based on aggregate conditions, while WHERE filters individual rows before grouping.

Question 9: What is the purpose of the UNPIVOT clause in Oracle SQL?

Converts rows into columns
Reverses the ORDER BY clause
Pivots data back to its original position
Converts columns into rows (Correct answer)

Correct answer: Converts columns into rows

UNPIVOT rotates columns into rows, the opposite of PIVOT, transforming wide data formats into tall (normalized) formats.

Question 10: What does the PIVOT clause do in Oracle SQL?

Creates a pivot table in Excel format
Transposes the table structure permanently
Rotates rows into columns, converting distinct row values into column headers (Correct answer)
Sorts data by multiple columns simultaneously

Correct answer: Rotates rows into columns, converting distinct row values into column headers

The PIVOT clause transforms rows into columns by rotating distinct values of one column into separate column headings with aggregated values.

Question 11: Which Oracle SQL feature allows recursive queries for hierarchical data like org charts?

HIERARCHICAL JOIN
CONNECT BY ... START WITH (Correct answer)
TREE SELECT
RECURSIVE JOIN

Correct answer: CONNECT BY ... START WITH

Oracle's CONNECT BY clause with START WITH enables hierarchical queries, traversing parent-child relationships within a table.

Question 12: What does the LAG analytic function do in Oracle SQL?

Calculates running totals
Retrieves data from the next row in the result set
Retrieves data from a previous row without a self-join (Correct answer)
Ranks rows within a partition

Correct answer: Retrieves data from a previous row without a self-join

LAG(column, n) accesses data from a row n positions before the current row in the result set, useful for comparing sequential values.

Question 13: What does CREATE TABLE AS SELECT do in Oracle SQL?

Creates a temporary table
Creates a view based on a SELECT
Creates a new table with structure and data copied from a query result (Correct answer)
Creates a table with only column structure, no data

Correct answer: Creates a new table with structure and data copied from a query result

CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it with the result set of the SELECT query.

Question 14: Which clause limits the query to rows that fulfill a set of criteria?

Where (Correct answer)
Describe
Distinct
Order by

Correct answer: Where

The `WHERE` clause in SQL is specifically used to filter the rows returned by a query, limiting the result set to only those rows that satisfy a specified condition or set of criteria. It evaluates a boolean expression for each row and includes only the rows for which the expression is true. Other clauses like `ORDER BY` sort the results, and `DISTINCT` removes duplicates, but `WHERE` is for row-level filtering.

Question 15: In Oracle SQL, what happens to uncommitted DML changes when a DDL statement is executed?

They are rolled back automatically
They are committed automatically (Correct answer)
They are saved to a temp table
Nothing changes; DDL does not affect DML

Correct answer: They are committed automatically

Oracle automatically issues an implicit COMMIT before and after every DDL statement, permanently saving any pending DML changes.

Question 16: What does the LEAD analytic function do in Oracle SQL?

Returns the first row in a partition
Accesses data from a row before the current row
Calculates the lead time between two dates
Accesses data from a row after the current row (Correct answer)

Correct answer: Accesses data from a row after the current row

LEAD(column, n) accesses data from a row n positions after the current row, the forward-looking complement to LAG.

Question 17: What does the CASCADE option do in DROP TABLE in Oracle SQL?

Forces the drop even if rows exist
Backs up the table before dropping
Drops related tables too
Drops the table and all dependent foreign key constraints (Correct answer)

Correct answer: Drops the table and all dependent foreign key constraints

DROP TABLE ... CASCADE CONSTRAINTS removes the table along with all foreign key constraints that reference it.

Question 18: Which Oracle SQL operator is used to compare a value against a list of values in a WHERE clause?

IN (Correct answer)
LIKE
BETWEEN
EXISTS

Correct answer: IN

The IN operator tests whether a value matches any value in a specified list, equivalent to multiple OR conditions.

Question 19: Which Oracle SQL clause is used to define a Common Table Expression (CTE)?

TEMP AS
WITH (Correct answer)
COMMON TABLE
DEFINE

Correct answer: WITH

The WITH clause (also called a subquery factoring clause) defines named CTEs that can be referenced multiple times in the main query.

Question 20: What is the default sort order when ORDER BY is used in Oracle SQL?

DESC
ASC (Correct answer)
RANDOM
Alphabetical only

Correct answer: ASC

By default, ORDER BY sorts in ascending order (ASC), placing the smallest or earliest values first.

Question 21: What is the purpose of the ORDER BY clause in a SELECT statement?

Filters rows based on a condition
Groups rows with the same value
Sorts the result set by one or more columns (Correct answer)
Joins two tables together

Correct answer: Sorts the result set by one or more columns

ORDER BY sorts the final result set in ascending (ASC) or descending (DESC) order based on specified columns.

Question 22: What is the purpose of the GRANT statement in Oracle SQL?

Creates a new user account
Audits user activity
Transfers object ownership
Gives privileges on database objects to users or roles (Correct answer)

Correct answer: Gives privileges on database objects to users or roles

GRANT gives specified privileges (SELECT, INSERT, UPDATE, etc.) on database objects to users or roles.

Question 23: What does the EXPLAIN PLAN statement do in Oracle SQL?

Analyzes table statistics
Shows the execution plan Oracle will use to run a query without executing it (Correct answer)
Generates documentation for a stored procedure
Runs a query and explains the result

Correct answer: Shows the execution plan Oracle will use to run a query without executing it

EXPLAIN PLAN stores the optimizer's execution plan for a SQL statement in the PLAN_TABLE without actually running the query.

Question 24: Which privilege is required to create a table in another user's schema in Oracle SQL?

INSERT ANY TABLE
CREATE ANY TABLE (Correct answer)
ALTER TABLE
CREATE TABLE

Correct answer: CREATE ANY TABLE

CREATE ANY TABLE privilege allows a user to create tables in any schema, not just their own.

Question 25: What is a phantom read in the context of database transactions?

Reading a row that was physically deleted but still appears in the buffer cache
A read operation that takes an unexpectedly long time due to lock contention
A transaction re-executes a range query and finds new rows inserted and committed by another transaction since the first execution (Correct answer)
Reading the same single row twice and getting different column values each time

Correct answer: A transaction re-executes a range query and finds new rows inserted and committed by another transaction since the first execution

A phantom read occurs when a transaction re-executes a range query and finds new rows ('phantoms') inserted and committed by another transaction since the first execution.

Question 26: What is a bind variable in Oracle SQL and why is it important?

A variable that binds two tables together
A variable defined in a view
A placeholder in SQL that promotes cursor sharing and prevents hard parses (Correct answer)
A constant value defined at database startup

Correct answer: A placeholder in SQL that promotes cursor sharing and prevents hard parses

Bind variables replace literal values with placeholders (e.g., :var), allowing Oracle to reuse execution plans and preventing SQL injection.

Question 27: Which clause is used to filter rows AFTER grouping in Oracle SQL?

GROUP FILTER
FILTER
WHERE
HAVING (Correct answer)

Correct answer: HAVING

The HAVING clause filters rows after the GROUP BY aggregation is applied, unlike WHERE which filters before grouping.

Question 28: Which Oracle SQL analytic function assigns a unique rank to each row, skipping ranks for ties?

NTILE()
RANK() (Correct answer)
ROW_NUMBER()
DENSE_RANK()

Correct answer: RANK()

RANK() assigns the same rank to tied rows but skips the next rank number(s), causing gaps in the ranking sequence.

Question 29: Which Oracle SQL analytic function calculates a running total?

SUM() OVER (ORDER BY) (Correct answer)
CUMULATIVE_SUM()
TOTAL() OVER ()
RUNNING_SUM()

Correct answer: SUM() OVER (ORDER BY)

SUM() used as an analytic function with OVER (ORDER BY column) computes a cumulative running total up to each row.

Question 30: What does the LISTAGG function do in Oracle SQL?

Aggregates string values from multiple rows into a single delimited string (Correct answer)
Lists all aggregate functions available
Splits a delimited string into rows
Returns the aggregate count of a list

Correct answer: Aggregates string values from multiple rows into a single delimited string

LISTAGG(column, delimiter) concatenates values from multiple rows into a single string with the specified separator.

Question 31: Which Oracle SQL feature allows you to query XML data stored in a column using SQL?

EXTRACT
JSON_TABLE
XML_QUERY
XMLTABLE (Correct answer)

Correct answer: XMLTABLE

XMLTABLE transforms XML data into relational rows and columns, enabling SQL queries against XML content stored in XMLType columns.

Question 32: Which Oracle SQL function returns the number of characters in a string?

STRLEN
SIZE
LEN
LENGTH (Correct answer)

Correct answer: LENGTH

The LENGTH function returns the number of characters in a string, or NULL if the string is NULL.

Question 33: Which Oracle SQL UPDATE syntax uses a subquery to update multiple columns at once?

UPDATE t SET (col1,col2) = (SELECT v1,v2 FROM src WHERE ...) (Correct answer)
UPDATE t COLUMNS (col1,col2) VALUES (SELECT ...)
UPDATE t SELECT col1=v1, col2=v2 FROM src
UPDATE t SET col1,col2 FROM (SELECT ...)

Correct answer: UPDATE t SET (col1,col2) = (SELECT v1,v2 FROM src WHERE ...)

Oracle allows updating multiple columns simultaneously using SET (col1, col2) = (subquery) to pull values from another table.

Question 34: What does the RETURNING clause do in an Oracle SQL DML statement?

Returns the number of rows affected
Returns to the previous transaction state
Returns affected row values into a variable or bind variable (Correct answer)
Rolls back the DML operation

Correct answer: Returns affected row values into a variable or bind variable

The RETURNING clause captures column values from rows affected by INSERT, UPDATE, or DELETE into variables for use in PL/SQL.

Question 35: Which Oracle SQL hint forces the optimizer to use a specific index?

/*+ ALL_ROWS */
/*+ INDEX(table index_name) */ (Correct answer)
/*+ USE_NL(table) */
/*+ FULL(table) */

Correct answer: /*+ INDEX(table index_name) */

The INDEX hint instructs the optimizer to use a specific index for the query, overriding its cost-based decision.

Question 36: What is the purpose of the DBMS_STATS package in Oracle SQL?

Monitors active database sessions
Manages database backup statistics
Reports on SQL execution times
Gathers optimizer statistics on tables, indexes, and columns (Correct answer)

Correct answer: Gathers optimizer statistics on tables, indexes, and columns

DBMS_STATS gathers and manages optimizer statistics (row counts, data distribution, column histograms) that Oracle uses to generate efficient execution plans.

Question 37: Which Oracle SQL index type is best suited for columns with very few distinct values like gender (M/F)?

Function-based index
B-tree index
Bitmap index (Correct answer)
Composite index

Correct answer: Bitmap index

Bitmap indexes are highly efficient for low-cardinality columns because they store a bit for each distinct value, enabling fast logical operations.

Question 38: Which Oracle SQL object stores the result of a query and updates it periodically?

Regular view
Materialized view (Correct answer)
Indexed view
Inline view

Correct answer: Materialized view

A materialized view physically stores query results and can be refreshed on a schedule or on demand, improving query performance.

Question 39: When using explicit cursors, list the correct command sequence to process a batch of records.

CURSOR, GET, FETCH, CLOSE
CURSOR, FETCH, CLOSE
INITIALIZE, GET, CLOSE
OPEN, FETCH, CLOSE (Correct answer)

Correct answer: OPEN, FETCH, CLOSE

When working with explicit cursors in PL/SQL to process a batch of records, the correct command sequence after declaring the cursor is `OPEN`, `FETCH`, and then `CLOSE`. The `OPEN` statement executes the query and identifies the active set of rows, `FETCH` retrieves rows one by one from this active set, and `CLOSE` releases the resources associated with the cursor.

Question 40: Which Oracle SQL statement permanently saves changes made by DML operations?

COMMIT (Correct answer)
SAVE
APPLY
FINALIZE

Correct answer: COMMIT

COMMIT makes all changes made since the last COMMIT or ROLLBACK permanent and visible to other sessions.

Question 41: What does a LEFT OUTER JOIN return in Oracle SQL?

All rows from both tables regardless of match
All rows from the left table and matching rows from the right (Correct answer)
Only rows matching in both tables
All rows from the right table and matching rows from the left

Correct answer: All rows from the left table and matching rows from the right

A LEFT OUTER JOIN returns all rows from the left table and NULL for unmatched columns from the right table.

Question 42: What does ROLLBACK do in Oracle SQL?

Restores a previous database backup
Undoes all uncommitted DML changes in the current transaction (Correct answer)
Deletes all rows in a table
Reverts DDL changes

Correct answer: Undoes all uncommitted DML changes in the current transaction

ROLLBACK undoes all uncommitted DML changes in the current transaction, restoring the database to its state at the last COMMIT.

Question 43: What does a CROSS JOIN produce in Oracle SQL?

A Cartesian product of all rows from both tables (Correct answer)
NULL rows for non-matching records
Rows that exist in one table but not the other
Only matching rows from both tables

Correct answer: A Cartesian product of all rows from both tables

A CROSS JOIN produces a Cartesian product, pairing every row from the first table with every row from the second table.

Question 44: Which Oracle SQL feature enables row-level security by filtering rows based on the current user?

Column-level encryption
Virtual Private Database (VPD) (Correct answer)
Fine-Grained Auditing
Label Security

Correct answer: Virtual Private Database (VPD)

Virtual Private Database (VPD) uses DBMS_RLS to attach security policies to tables, automatically appending WHERE clauses based on the current user.

Question 45: What does the MERGE statement do in Oracle SQL?

Merges duplicate rows within a table
Performs INSERT, UPDATE, or DELETE based on matching conditions in a single statement (Correct answer)
Combines two tables permanently
Joins two schemas together

Correct answer: Performs INSERT, UPDATE, or DELETE based on matching conditions in a single statement

MERGE (also called UPSERT) conditionally inserts or updates rows based on whether a match exists between source and target tables.

Question 46: What is the purpose of the NOCACHE option when creating an Oracle SEQUENCE?

Creates a descending sequence
Prevents other sessions from using the sequence
Prevents the sequence from being cached in memory, issuing numbers one at a time (Correct answer)
Stops the sequence from cycling

Correct answer: Prevents the sequence from being cached in memory, issuing numbers one at a time

NOCACHE forces Oracle to generate each sequence number with a separate database operation, avoiding gaps but reducing performance.

Question 47: Choose the incorrect variable declarations.

Foo_text varchar2(10) := 'hello world'; (Correct answer)
Foo_text number(10);
Foo_number varchar2(10);
Foo_char char(1) := 'Y';

Correct answer: Foo_text varchar2(10) := 'hello world';

In Oracle PL/SQL, when declaring a `VARCHAR2` variable, the specified length defines its maximum capacity. The literal string 'hello world' contains 11 characters. Declaring `Foo_text varchar2(10)` means it can hold a maximum of 10 characters, so assigning an 11-character string to it will result in a `VALUE_ERROR` at runtime or a compilation error, making this an incorrect declaration.

Question 48: What does the ROUND function do in Oracle SQL when called as ROUND(45.678, 1)?

Returns 45
Returns 45.7 (Correct answer)
Returns 45.68
Returns 46

Correct answer: Returns 45.7

ROUND(45.678, 1) rounds to one decimal place, returning 45.7.

Question 49: Which Oracle SQL constraint ensures no two rows in a table have the same value in a column?

UNIQUE (Correct answer)
NOT NULL
CHECK
PRIMARY KEY

Correct answer: UNIQUE

A UNIQUE constraint prevents duplicate values in a column or combination of columns, but unlike PRIMARY KEY, it allows NULL values.

Question 50: Which Oracle SQL statement modifies the structure of an existing table?

UPDATE TABLE
MODIFY TABLE
CHANGE TABLE
ALTER TABLE (Correct answer)

Correct answer: ALTER TABLE

ALTER TABLE is used to add, modify, or drop columns and constraints in an existing table without recreating it.

Question 51: What does SET TRANSACTION READ ONLY accomplish in Oracle SQL?

Makes all tables in the current session permanently read-only
Prevents any DDL operations for the lifetime of the current session
Sets the entire database instance to read-only mode for maintenance
Starts a transaction that can only SELECT and sees a consistent data snapshot as of the transaction start time (Correct answer)

Correct answer: Starts a transaction that can only SELECT and sees a consistent data snapshot as of the transaction start time

SET TRANSACTION READ ONLY starts a read-only transaction that sees a consistent snapshot of data as of the transaction's start time, and prevents any DML within that transaction.

Question 52: What is a synonym in Oracle SQL?

A backup of an index
A stored procedure alias
A copy of a table's data
An alternate name for a database object (Correct answer)

Correct answer: An alternate name for a database object

A synonym is an alternative name for a database object (table, view, sequence, etc.) that simplifies access and hides the actual object name or owner.

Question 53: What is the result of dividing a number by zero in Oracle SQL?

Returns NULL
Raises an ORA-01476 error (Correct answer)
Returns 0
Returns Infinity

Correct answer: Raises an ORA-01476 error

Oracle raises ORA-01476 'divisor is equal to zero' error when a division by zero occurs in a query.

Question 54: Which Oracle SQL clause creates a window frame for analytic functions based on a range of rows?

RANGE SET
BETWEEN ROWS
WINDOW FRAME
ROWS BETWEEN (Correct answer)

Correct answer: ROWS BETWEEN

ROWS BETWEEN n PRECEDING AND n FOLLOWING (or CURRENT ROW/UNBOUNDED) defines the sliding window of rows for analytic function calculations.

Question 55: What is the effect of omitting the WHERE clause in an Oracle SQL UPDATE statement?

No rows are updated
All rows in the table are updated (Correct answer)
The statement fails with an error
Only the first row is updated

Correct answer: All rows in the table are updated

An UPDATE without a WHERE clause applies the SET changes to every row in the table, which is often an unintentional mistake.

Question 56: What does the ENABLE NOVALIDATE constraint state do in Oracle SQL?

Enables the constraint and checks all existing rows
Validates existing rows without enabling for new ones
Disables the constraint entirely
Enables the constraint for new rows only, without validating existing rows (Correct answer)

Correct answer: Enables the constraint for new rows only, without validating existing rows

ENABLE NOVALIDATE enforces the constraint only on new or modified rows without checking existing rows that may violate it.

Question 57: Choose variables that aren't valid.

INTEGER
CHAR
NUMBER
VARCHAR1 (Correct answer)

Correct answer: VARCHAR1

In Oracle SQL, `NUMBER`, `CHAR`, and `INTEGER` are all valid data types used for storing numeric, fixed-length character, and integer values, respectively. However, `VARCHAR1` is not a valid data type. The correct variable-length character data type in Oracle SQL is `VARCHAR2`, which is used to store character strings of varying lengths.

Question 58: What does ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id) do?

Replaces an existing primary key
Adds a primary key constraint to an existing table (Correct answer)
Adds a foreign key referencing employee_id
Creates a new column called employee_id

Correct answer: Adds a primary key constraint to an existing table

This ALTER TABLE statement adds a named PRIMARY KEY constraint on the employee_id column to an already-created table.

Question 59: Which Oracle SQL feature enables you to see the data in a table as it existed at a past point in time?

RESTORE SELECT
TIME TRAVEL SELECT
ROLLBACK AS OF
Flashback Query (AS OF TIMESTAMP) (Correct answer)

Correct answer: Flashback Query (AS OF TIMESTAMP)

Oracle Flashback Query uses AS OF TIMESTAMP or AS OF SCN to query historical versions of data stored in the undo tablespace.

Question 60: What does the PARTITION BY clause do in Oracle SQL analytic functions?

Groups rows like GROUP BY does
Filters rows before the analytic function runs
Splits the physical table into partitions
Divides the result set into groups for the analytic function to operate on (Correct answer)

Correct answer: Divides the result set into groups for the analytic function to operate on

PARTITION BY divides the result set into logical groups (partitions) within which the analytic function is computed independently.

Question 61: Which Oracle SQL statement creates a table that exists only for the duration of a session?

CREATE VOLATILE TABLE
CREATE SESSION TABLE
CREATE TEMP TABLE
CREATE GLOBAL TEMPORARY TABLE (Correct answer)

Correct answer: CREATE GLOBAL TEMPORARY TABLE

CREATE GLOBAL TEMPORARY TABLE creates a temporary table whose data is private to each session and is deleted at session or transaction end.

Question 62: What is a deadlock in Oracle SQL?

A situation where two or more transactions are each waiting for the other to release locks, creating a circular wait (Correct answer)
A transaction that has been waiting for a lock for more than 30 seconds
A single transaction holding more locks than the system limit
A lock that cannot be released due to a database corruption error

Correct answer: A situation where two or more transactions are each waiting for the other to release locks, creating a circular wait

A deadlock occurs when two or more transactions each hold locks that the other needs, creating a circular wait that Oracle detects and resolves by rolling back one transaction.

Question 63: Which Oracle SQL statement removes a view from the database?

TRUNCATE VIEW
REMOVE VIEW
DELETE VIEW
DROP VIEW (Correct answer)

Correct answer: DROP VIEW

DROP VIEW removes the view definition from the data dictionary; the underlying base tables are not affected.

Oracle Database SQL (1Z0-071)

The Oracle Database SQL certification exam (1Z0-071) tests candidates on their proficiency in writing and executing SQL statements against Oracle Database, covering data retrieval, manipulation, schema management, and advanced SQL features.

Questions

120 min

Time Limit

63%

To Pass

Checking for saved progress...

Exam Rules

You can skip questions and return to them later
Flag questions for review before submitting
No feedback shown until you submit the entire exam
Unanswered questions count as wrong — answer everything
10 pretest questions are mixed in and don't affect your score
Timer auto-submits when time runs out
Your progress is auto-saved every 30 seconds