Oracle Database SQL Certified Associate Exam — Questions and Answers

Question 1: Which keyword pair allows a multi-row subquery to check if a value matches any value in the returned list?

IN
= ALL
= ANY
Both = ANY and IN (Correct answer)

Correct answer: Both = ANY and IN

Both IN and = ANY check if a value matches at least one value in the subquery's result set and are functionally equivalent.

Question 2: Which Oracle data type stores large amounts of character data beyond 4000 bytes?

TEXT
VARCHAR2
LONG
CLOB (Correct answer)

Correct answer: CLOB

CLOB (Character Large Object) stores up to 128 TB of character data and is Oracle's recommended type for large text.

Question 3: What is the purpose of using NOT EXISTS instead of NOT IN in a subquery?

NOT EXISTS correctly handles NULLs unlike NOT IN (Correct answer)
NOT EXISTS is faster always
NOT EXISTS only works with correlated subqueries
Both options A and C

Correct answer: NOT EXISTS correctly handles NULLs unlike NOT IN

NOT EXISTS avoids the NULL-related pitfall of NOT IN because EXISTS only checks row existence, not column values.

Question 4: What is the primary key in a relational database table?

A key that defines the order in which records are stored in the table.
A column used to create a relationship between two tables.
A column that stores redundant data.
A column or set of columns that uniquely identifies each row in the table. (Correct answer)

Correct answer: A column or set of columns that uniquely identifies each row in the table.

The primary key in a relational database table is a crucial constraint that uniquely identifies each individual row within that table. It ensures that every record is distinct and provides a reliable way to reference specific data. This uniqueness is fundamental for data integrity and establishing relationships with other tables.

Question 5: What is a correlated subquery?

A subquery used only in the FROM clause
A subquery that returns a single value
A subquery with a GROUP BY clause
A subquery that references a column from the outer query (Correct answer)

Correct answer: A subquery that references a column from the outer query

A correlated subquery references a column from the outer query and is re-executed for each row of the outer query.

Question 6: Which JOIN type returns all rows from the left table and matching rows from the right table, with NULLs for non-matches?

CROSS JOIN
FULL OUTER JOIN
LEFT OUTER JOIN (Correct answer)
INNER JOIN

Correct answer: LEFT OUTER JOIN

LEFT OUTER JOIN returns all rows from the left table and matched rows from the right; unmatched right columns are NULL.

Question 7: Which GROUP BY function returns the total sum of a numeric column?

SUM() (Correct answer)
AVG()
MAX()
COUNT()

Correct answer: SUM()

SUM() adds up all non-NULL numeric values in the specified column.

Question 8: Which keyword allows an INSERT statement to include a subquery instead of literal values?

SELECT replacing VALUES (Correct answer)
SUBQUERY keyword
INTO keyword
VALUES with parentheses only

Correct answer: SELECT replacing VALUES

Replacing the VALUES clause with a SELECT statement allows inserting multiple rows returned by the query.

Question 9: Which of the following is true about relational databases?

They are based on a hierarchical model where data is stored in a tree-like structure.
They are designed to store unstructured data like images and videos.
They use tables to store data, where each table consists of rows and columns. (Correct answer)
They do not support relationships between data entities.

Correct answer: They use tables to store data, where each table consists of rows and columns.

Relational databases are fundamentally structured around tables, which are composed of rows and columns. Each row represents a single record, and each column represents an attribute of that record. This tabular structure is central to how relational databases store, organize, and manage data, allowing for clear definition and relationships between data entities.

Question 10: What is a subquery in Oracle SQL?

A synonym for a VIEW
A SELECT statement nested inside another SQL statement (Correct answer)
A stored procedure that returns a table
A query that uses GROUP BY

Correct answer: A SELECT statement nested inside another SQL statement

A subquery is a SELECT statement embedded within another SQL statement such as a SELECT, INSERT, UPDATE, or DELETE.

Question 11: What does the COUNT(*) function return?

Total number of rows including NULLs (Correct answer)
Number of non-NULL values in a column
Number of distinct values
Sum of all rows

Correct answer: Total number of rows including NULLs

COUNT(*) counts every row in the result set regardless of NULL values in any column.

Question 12: Which statement correctly updates the salary of employee 100 to 9000?

MODIFY employees SET salary = 9000 WHERE employee_id = 100
ALTER employees SET salary = 9000 WHERE employee_id = 100
UPDATE employees SET salary = 9000 WHERE employee_id = 100 (Correct answer)
UPDATE employees VALUES (9000) WHERE employee_id = 100

Correct answer: UPDATE employees SET salary = 9000 WHERE employee_id = 100

UPDATE uses the SET clause to assign new values and a WHERE clause to target specific rows.

Question 13: Which of the following best describes a relational database?

A database model where data is stored in a tree-like structure.
A collection of non-related data stored in a hierarchical structure.
A collection of procedures and functions that perform operations on data.
A database that organizes data into tables, with rows and columns, where each table represents an entity. (Correct answer)

Correct answer: A database that organizes data into tables, with rows and columns, where each table represents an entity.

A relational database organizes data into structured tables, where each table represents an entity and consists of rows (records) and columns (attributes). These tables are related to each other through common fields, allowing for efficient storage, retrieval, and management of interconnected data. This tabular structure with defined relationships is the defining characteristic of a relational database.

Question 14: Which of the following is true about the DECODE function?

It compares an expression to one or more values and returns a corresponding result. (Correct answer)
It cannot handle NULL values.
It always returns a numeric value.
It is used only for numeric data types.

Correct answer: It compares an expression to one or more values and returns a corresponding result.

The `DECODE` function, specific to Oracle SQL, provides conditional logic by comparing an expression to a series of search values. If a match is found, it returns the corresponding result; otherwise, it returns an optional default value. It acts as a shorthand for a simple `CASE` statement, handling various data types and NULL values effectively.

Question 15: In a compound query using set operators, an ORDER BY clause can reference output columns using which methods?

Column aliases defined in the first SELECT statement only
Column aliases from the first SELECT or position numbers (Correct answer)
Table-qualified column names such as emp.salary
Position numbers such as ORDER BY 1, 2 only

Correct answer: Column aliases from the first SELECT or position numbers

In set operator queries, ORDER BY can reference columns by aliases from the first SELECT statement or by their positional number; table-qualified names are not permitted.

Question 16: In a MERGE statement, what does the WHEN NOT MATCHED clause do?

Rolls back the merge operation
Deletes rows that don't match
Inserts source rows that have no matching row in the target (Correct answer)
Updates existing rows in the target

Correct answer: Inserts source rows that have no matching row in the target

WHEN NOT MATCHED triggers when the source row has no corresponding row in the target, allowing an INSERT action.

Question 17: Which clause determines how rows are divided into groups before aggregation?

CLUSTER BY
PARTITION
GROUP BY (Correct answer)
ORDER BY

Correct answer: GROUP BY

GROUP BY divides the rows of a result set into groups for aggregation by group functions.

Question 18: When using a USING clause in a JOIN, what happens if you try to prefix the USING column with a table alias?

Oracle raises an ORA-25154 error (Correct answer)
The query uses the left table's value
The query returns duplicate columns
The alias is silently ignored

Correct answer: Oracle raises an ORA-25154 error

Oracle raises ORA-25154 if you prefix the USING clause column with a table alias because it is common to both tables.

Question 19: An EMPLOYEES table has 10 rows; a DEPARTMENTS table has 5 rows. A CROSS JOIN returns how many rows?

10
50 (Correct answer)
5
15

Correct answer: 50

A CROSS JOIN (Cartesian product) multiplies all rows: 10 × 5 = 50 rows.

Question 20: What is the purpose of a SAVEPOINT in Oracle transactions?

To mark an intermediate point in a transaction so a partial rollback is possible (Correct answer)
To create a backup of the current data
To lock a table at a specific moment
To commit changes up to a specific point

Correct answer: To mark an intermediate point in a transaction so a partial rollback is possible

SAVEPOINT creates a named marker within a transaction, allowing ROLLBACK TO SAVEPOINT to undo only part of the transaction.

Question 21: What is the effect of a DDL statement on an active DML transaction in Oracle?

The DDL statement is queued until the transaction commits
The DDL implicitly commits the pending transaction first (Correct answer)
The DDL rolls back the pending transaction
The DDL raises an error until the transaction ends

Correct answer: The DDL implicitly commits the pending transaction first

Oracle implicitly commits any pending DML transaction before executing a DDL statement.

Question 22: What does a CHECK constraint do?

Verifies that column values satisfy a specific condition (Correct answer)
Prevents duplicate values in a column
Ensures a column references a valid primary key
Guarantees a column is never NULL

Correct answer: Verifies that column values satisfy a specific condition

A CHECK constraint enforces a business rule by validating that column values meet a specified Boolean condition.

Question 23: A subquery in the FROM clause is called a:

Inline view (Correct answer)
Nested subquery
Correlated subquery
Scalar subquery

Correct answer: Inline view

A subquery placed in the FROM clause is called an inline view because it acts like a temporary named table.

Question 24: Which SQL function can be used to convert a string to a number?

TO_DATE
NVL
TO_NUMBER (Correct answer)
TO_CHAR

Correct answer: TO_NUMBER

The `TO_NUMBER` function in SQL is specifically designed to convert a character string into a numeric data type. This is essential when you have numbers stored as text and need to perform mathematical operations or comparisons on them. It allows for explicit type conversion, often with an optional format model.

Question 25: Which error occurs when a single-row operator is used with a multi-row subquery result?

ORA-01427 (Correct answer)
ORA-00904
ORA-01403
ORA-00936

Correct answer: ORA-01427

ORA-01427 'single-row subquery returns more than one row' is raised when = or < is used against a multi-row subquery.

Question 26: In Oracle SQL, the MINUS operator is the Oracle-specific equivalent of which ANSI SQL standard set operator?

EXCEPT (Correct answer)
DIFFERENCE
INTERSECT
NOT IN

Correct answer: EXCEPT

Oracle's MINUS operator is functionally equivalent to the ANSI SQL standard EXCEPT operator, both returning rows from the first query not found in the second.

Question 27: What happens to dependent objects like views when you DROP a table in Oracle?

They continue to work because Oracle caches the data
They are also dropped automatically
They become invalid but are not dropped (Correct answer)
Oracle raises an error and prevents the DROP

Correct answer: They become invalid but are not dropped

When a base table is dropped, views and synonyms that depend on it become INVALID but remain in the data dictionary.

Question 28: Which of the following correctly creates a table with a primary key constraint?

CREATE TABLE t (id NUMBER PRIMARY KEY, name VARCHAR2(50)) (Correct answer)
CREATE TABLE t (id NUMBER KEY, name VARCHAR2(50))
CREATE TABLE t (id NUMBER UNIQUE NOT NULL, name VARCHAR2(50))
CREATE TABLE t (id NUMBER CONSTRAINT pk, name VARCHAR2(50))

Correct answer: CREATE TABLE t (id NUMBER PRIMARY KEY, name VARCHAR2(50))

Placing PRIMARY KEY after the column definition creates an inline primary key constraint for that column.

Question 29: Which function returns the highest value in a set of values?

GREATEST()
MAX() (Correct answer)
UPPER()
TOP()

Correct answer: MAX()

MAX() is a group function that returns the maximum value across all rows in the group.

Question 30: Which two are SQL features?

processing sets of data (Correct answer)
providing update capabilities for data in external files
providing graphical capabilities
providing variable definition capabilities.
providing database transaction control (Correct answer)

Correct answer: processing sets of data

SQL (Structured Query Language) provides robust capabilities for managing database transactions, allowing users to commit or roll back changes to maintain data integrity. Additionally, SQL is designed to process sets of data, meaning it operates on entire groups of rows rather than individual records, which makes it highly efficient for database operations. These are core features distinguishing SQL from other programming languages.

Question 31: What is the effect of issuing ROLLBACK TO SAVEPOINT sp1 in a transaction?

Undoes changes made after sp1 was set, but keeps sp1 and changes before it (Correct answer)
Deletes the savepoint sp1 permanently
Commits everything before sp1
Ends the entire transaction

Correct answer: Undoes changes made after sp1 was set, but keeps sp1 and changes before it

ROLLBACK TO SAVEPOINT reverses DML changes made after the savepoint, preserving the savepoint and earlier changes.

Question 32: A subquery that contains another subquery inside it is called a:

Correlated subquery
Recursive subquery
Nested subquery (Correct answer)
Inline view

Correct answer: Nested subquery

A nested subquery is a subquery placed inside another subquery, creating multiple layers of query nesting.

Question 33: Which comparison operator is INVALID when used with a multi-row subquery?

ALL
ANY
= (Correct answer)
IN

Correct answer: =

The = operator expects a single value; using it with a multi-row subquery causes an ORA-01427 error.

Question 34: Which set operator best describes a 'logical difference' or 'relative complement' operation between two sets in Oracle SQL?

MINUS (Correct answer)
UNION
INTERSECT
UNION ALL

Correct answer: MINUS

MINUS returns the logical difference between two sets by returning rows present in the first result set that are absent from the second result set.

Question 35: How would you retrieve all rows from a table named employees where the salary is greater than 50000?

SELECT * ORDER BY salary > 50000 FROM employees;
SELECT * FROM employees ORDER BY salary > 50000;
SELECT * FROM employees WHERE salary > 50000; (Correct answer)
SELECT * WHERE salary > 50000 FROM employees;

Correct answer: SELECT * FROM employees WHERE salary > 50000;

To retrieve all rows from a table that meet a specific condition, the `SELECT * FROM table_name WHERE condition;` syntax is used. In this case, `SELECT * FROM employees` retrieves all columns from the `employees` table, and `WHERE salary > 50000` filters these rows to include only those where the salary is greater than 50000. This is the standard and correct SQL syntax for conditional data retrieval.

Question 36: Which aggregate function is used to find the average salary per department?

MEAN()
MEDIAN()
AVG() (Correct answer)
SUM()/COUNT(*)

Correct answer: AVG()

AVG() is the standard Oracle group function that computes the arithmetic mean of a numeric column.

Question 37: Which two queries return rows for employees whose manager works in a different department?

SELECT emp.* FROM employees emp WHERE NOT EXISTS ( SELECT NULL FROM employees mgr WHERE emp.manager id = mgr.employee_ id AND emp.department_id<>mgr.department_id );
SELECT emp. * FROM employees emp WHERE manager_ id NOT IN ( SELECT mgr.employee_ id FROM employees mgr WHERE emp. department_ id < > mgr.department_ id );
SELECT emp. * FROM employees emp JOIN employees mgr ON emp. manager_ id = mgr. employee_ id AND emp. department_ id<> mgr.department_ id; (Correct answer)
SELECT emp. * FROM employees emp RIGHT JOIN employees mgr ON emp.manager_ id = mgr. employee id AND emp. department id <> mgr.department_ id WHERE emp. employee_ id IS NOT NULL; (Correct answer)
SELECT emp.* FROM employees emp LEFT JOIN employees mgr ON emp.manager_ id = mgr.employee_ id AND emp. department id < > mgr. department_ id;

Correct answer: SELECT emp. * FROM employees emp JOIN employees mgr ON emp. manager_ id = mgr. employee_ id AND emp. department_ id<> mgr.department_ id;

Both queries D and E correctly identify employees whose manager works in a different department. Query E uses an `INNER JOIN` on `manager_id = employee_id` and then filters for `emp.department_id <> mgr.department_id`, directly selecting matching pairs where departments differ. Query D uses a `RIGHT JOIN` and then filters `emp.employee_id IS NOT NULL` to ensure we're looking at actual employees, while also applying the department ID mismatch condition in the join, effectively achieving the same result by focusing on employees whose manager is found and is in a different department.

Question 38: What is the result of the following query: SELECT 1 FROM DUAL UNION SELECT 1 FROM DUAL?

Returns one row containing the value 1 (Correct answer)
Returns two rows each containing the value 1
Returns NULL
Returns an error because DUAL cannot be referenced twice

Correct answer: Returns one row containing the value 1

UNION eliminates duplicate rows, so even though both SELECT statements return the value 1, the final result contains only one distinct row.

Question 39: Which SQL statement will return all employees in the employees table sorted by their last_name in ascending order?

SELECT * FROM employees WHERE last_name ORDER BY ASC;
SELECT * FROM employees SORT BY last_name;
SELECT * FROM employees WHERE ORDER BY last_name;
SELECT * FROM employees ORDER BY last_name ASC; (Correct answer)

Correct answer: SELECT * FROM employees ORDER BY last_name ASC;

To sort query results, the `ORDER BY` clause is used, followed by the column name(s) and the desired sort order. `ASC` specifies ascending order, which is also the default if no order is specified. Therefore, `SELECT * FROM employees ORDER BY last_name ASC;` correctly retrieves all employees and sorts them alphabetically by their last name.

Question 40: What type of join returns rows from both tables regardless of whether a match exists?

FULL OUTER JOIN (Correct answer)
RIGHT OUTER JOIN
LEFT OUTER JOIN
INNER JOIN

Correct answer: FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables, with NULLs where there is no matching row.

Question 41: Which subquery type returns exactly one row and one column?

Inline view
Multiple-column subquery
Scalar subquery (Correct answer)
Multi-row subquery

Correct answer: Scalar subquery

A scalar subquery returns a single row and a single column and can be used wherever a single value is expected.

Question 42: Which clause is used to filter the results of a GROUP BY query?

HAVING (Correct answer)
WHERE
FILTER
LIMIT

Correct answer: HAVING

The HAVING clause filters groups after aggregation, unlike WHERE which filters rows before grouping.

Question 43: When combining two SELECT statements with a set operator, which of the following is a mandatory requirement?

Both queries must have identical WHERE clause conditions
Both SELECT statements must return the same number of columns (Correct answer)
The ORDER BY clause must be present in each SELECT
Both queries must reference the same tables

Correct answer: Both SELECT statements must return the same number of columns

All set operators require that both SELECT statements return the same number of columns for the operation to execute successfully.

Question 44: Which keyword must follow a table name when performing an ANSI-style FULL OUTER JOIN?

FULL JOIN
OUTER JOIN
ALL JOIN
FULL OUTER JOIN (Correct answer)

Correct answer: FULL OUTER JOIN

The complete ANSI keyword phrase FULL OUTER JOIN specifies that all rows from both tables should be returned.

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

Creates a savepoint
Rolls back all changes since the last COMMIT
Saves all pending DML changes to the database permanently (Correct answer)
Locks the table for exclusive access

Correct answer: Saves all pending DML changes to the database permanently

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

Question 46: What does the ON DELETE CASCADE option on a FOREIGN KEY do?

Prevents deletion of parent rows that have child rows
Sets child foreign key values to NULL when the parent is deleted
Automatically deletes child rows when the parent row is deleted (Correct answer)
Raises an error when a parent row deletion is attempted

Correct answer: Automatically deletes child rows when the parent row is deleted

ON DELETE CASCADE automatically deletes all child rows in the referencing table when the parent row is deleted.

Question 47: Which set operator returns rows from the first query that do NOT appear in the second query?

UNION ALL
UNION
MINUS (Correct answer)
INTERSECT

Correct answer: MINUS

MINUS (Oracle-specific) returns all distinct rows selected by the first query that are not present in the second query result.

Question 48: Which constraint ensures each value in a column is unique and not NULL?

PRIMARY KEY (Correct answer)
UNIQUE
CHECK
NOT NULL

Correct answer: PRIMARY KEY

A PRIMARY KEY constraint enforces both uniqueness and NOT NULL on the column(s) it covers.

Question 49: What is the purpose of the TO_CHAR function in SQL?

To convert a string to a number.
To convert a string to a date.
To convert a number or date to a string. (Correct answer)
To convert a date to a number.

Correct answer: To convert a number or date to a string.

The `TO_CHAR` function in SQL is primarily used to convert non-character data types, such as numbers or dates, into a character string. This is particularly useful for formatting output, allowing you to specify how dates or numbers should appear in a human-readable format. It enables flexible presentation of data from various types.

Question 50: What happens in an INNER JOIN when no rows satisfy the join condition?

All rows from the left table are returned
NULL rows are substituted
An ORA error is raised
An empty result set is returned (Correct answer)

Correct answer: An empty result set is returned

An INNER JOIN with no matching rows returns zero rows — it never substitutes NULLs like an outer join.

Question 51: Which three are true about the CREATE TABLE command?

It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint (Correct answer)
. It implicitly rolls back any pending transactions.
The owner of the table should have space quota available on the tablespace where the table is defined. (Correct answer)
A user must have the CREATE ANY TABLE privilege to create tables.
The owner of the table must have the UNLIMITED TABLESPACE system privilege.
It implicitly executes a commit. (Correct answer)

Correct answer: It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint

When creating a table in Oracle SQL, you can define a primary key constraint, which implicitly creates a unique index to enforce its uniqueness and non-nullability. This index is essential for efficient data retrieval and maintaining data integrity. Therefore, the `CREATE TABLE` command can indeed include statements that lead to the creation of an index for the primary key constraint.

Question 52: Which SQL clause is used to filter rows returned by a query based on a specified condition?

WHERE (Correct answer)
SELECT
ORDER BY
FROM

Correct answer: WHERE

The `WHERE` clause in SQL is specifically used to filter the rows returned by a query. It applies a specified condition to each row, and only those rows that satisfy the condition are included in the final result set. This allows users to retrieve a subset of data based on specific criteria.

Question 53: Which query correctly uses HAVING to show departments with more than 5 employees?

SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id HAVING COUNT(*) > 5 (Correct answer)
SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id WHERE COUNT(*) > 5
SELECT dept_id, COUNT(*) FROM emp WHERE COUNT(*) > 5 GROUP BY dept_id
SELECT dept_id, COUNT(*) FROM emp HAVING COUNT(*) > 5

Correct answer: SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id HAVING COUNT(*) > 5

HAVING must follow GROUP BY and can reference aggregate functions like COUNT(*) to filter groups.

Question 54: A subquery that returns more than one column is called a:

Multiple-column subquery (Correct answer)
Correlated subquery
Multi-row subquery
Scalar subquery

Correct answer: Multiple-column subquery

A multiple-column subquery returns more than one column and is typically used in pairwise comparisons.

Question 55: Which clause in a JOIN allows you to specify a single common column name without a table prefix?

WHERE
ON
BY
USING (Correct answer)

Correct answer: USING

The USING clause specifies a common column by name, and that column cannot be prefixed with a table alias.

Question 56: What does the EXISTS operator check in a subquery?

Whether the subquery has no GROUP BY clause
Whether the subquery returns exactly one row
Whether the subquery returns at least one row (Correct answer)
Whether all rows in the subquery are non-NULL

Correct answer: Whether the subquery returns at least one row

EXISTS returns TRUE if the subquery produces at least one row, regardless of column values.

Question 57: Which set operator returns all rows from both queries, including duplicate rows?

INTERSECT
UNION ALL (Correct answer)
MINUS
UNION

Correct answer: UNION ALL

UNION ALL combines result sets from both queries and retains all duplicate rows without performing any deduplication.

Question 58: Which statement about the GROUP BY clause is TRUE?

Columns in SELECT not in an aggregate must appear in GROUP BY (Correct answer)
NULL values cannot appear in a GROUP BY column
GROUP BY must always be followed by HAVING
GROUP BY eliminates duplicate rows without aggregation

Correct answer: Columns in SELECT not in an aggregate must appear in GROUP BY

Any non-aggregated column in the SELECT list must also appear in the GROUP BY clause.

Question 59: What happens if you issue a DELETE without a WHERE clause?

All rows in the table are deleted (Correct answer)
Only the first row is deleted
An error is raised
Only duplicate rows are deleted

Correct answer: All rows in the table are deleted

DELETE without a WHERE clause removes every row from the table while keeping the table structure intact.

Question 60: Where can a single-row subquery be used in a SELECT statement?

Only in the FROM clause
Only in the ORDER BY clause
In the WHERE, HAVING, and SELECT clauses (Correct answer)
Only in the WHERE clause

Correct answer: In the WHERE, HAVING, and SELECT clauses

Single-row subqueries can appear in the WHERE, HAVING, and SELECT clauses of the outer query.

Question 61: What does GROUP BY ROLLUP(department_id, job_id) produce in addition to normal groups?

Subtotals for each department_id and a grand total (Correct answer)
Nothing different from regular GROUP BY
Only subtotals for each job_id
Only grand total row

Correct answer: Subtotals for each department_id and a grand total

ROLLUP creates subtotal rows for each level of the grouping hierarchy plus a grand total row.

Question 62: What does the ORDER BY clause do in a SQL query?

Sorts the result set based on one or more columns. (Correct answer)
Combines rows from multiple tables.
Limits the number of rows returned by the query.
Filters rows based on a condition.

Correct answer: Sorts the result set based on one or more columns.

The `ORDER BY` clause in a SQL query is used to sort the result set based on the values in one or more specified columns. You can sort in ascending (ASC) or descending (DESC) order. This allows for presenting query results in a meaningful and organized sequence, making data easier to analyze.

Question 63: When a NULL is returned by a subquery used with NOT IN, what is the result?

No rows are returned (Correct answer)
NULLs are ignored
All rows are returned
An ORA error is raised

Correct answer: No rows are returned

If a subquery used with NOT IN returns any NULL, the entire NOT IN condition evaluates to UNKNOWN and no rows are returned.

Question 64: Which of the following is a valid use of group functions in Oracle?

SELECT SUM(salary) FROM employees HAVING SUM(salary) > 10000 (Correct answer)
SELECT department_id, SUM(salary) FROM employees HAVING department_id = 10
SELECT SUM(salary) FROM employees WHERE SUM(salary) > 10000
SELECT SUM(salary), department_id FROM employees GROUP BY SUM(salary)

Correct answer: SELECT SUM(salary) FROM employees HAVING SUM(salary) > 10000

A group function like SUM() can be used in HAVING but not in WHERE without a subquery.

Question 65: What happens to NULL values when using the AVG() function in Oracle SQL?

They are replaced with 1
They are ignored entirely (Correct answer)
They are counted as zero
They cause an error

Correct answer: They are ignored entirely

Oracle's AVG() function ignores NULL values and calculates the average of only non-NULL rows.

Question 66: When using set operators, which columns must have compatible data types between the two SELECT statements?

Only the first column in each SELECT statement
Only the primary key columns
All corresponding columns in both SELECT statements (Correct answer)
Only columns appearing in the WHERE clause

Correct answer: All corresponding columns in both SELECT statements

Every corresponding column pair (column 1 with column 1, column 2 with column 2, and so on) must have compatible data types across both SELECT statements.

Question 67: What is the correct way to find employees who earn more than the average salary?

SELECT * FROM employees HAVING salary > AVG(salary)
SELECT * FROM employees WHERE salary > ALL(SELECT salary FROM employees)
SELECT * FROM employees WHERE salary > AVG(salary)
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) (Correct answer)

Correct answer: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees)

A single-row subquery that returns AVG(salary) is placed in the WHERE clause to compare each employee's salary.

Question 68: Which DDL statement creates a new table in Oracle?

CREATE TABLE (Correct answer)
INSERT TABLE
MAKE TABLE
BUILD TABLE

Correct answer: CREATE TABLE

CREATE TABLE defines a new table with its columns, data types, and optional constraints.

Question 69: Which DDL statement removes all rows from a table but keeps its structure, and cannot be rolled back?

DELETE FROM table
TRUNCATE TABLE (Correct answer)
DROP TABLE
REMOVE TABLE

Correct answer: TRUNCATE TABLE

TRUNCATE TABLE is a DDL command that removes all rows instantly without logging individual row deletions and cannot be rolled back.

Question 70: Which statement executes successfully?

SELECT TO_DATE(INTERVAL '800' SECOND,'HH24:MM') FROM DUAL;
SELECT TO_NUMBER(INTERVAL'800' SECOND, 'HH24:MM') FROM DUAL;
SELECT TO_CHAR(INTERVAL '800' SECOND, 'HH24:MM') FROM DUAL; (Correct answer)
SELECT TO_DATE(TO_NUMBER(INTERVATL '800' SECOND)) FROM DUAL;
SELECT TO_NUWBER(TO_DATE(INTERVAL '800' SECOND)) FROM DUAL;

Correct answer: SELECT TO_CHAR(INTERVAL '800' SECOND, 'HH24:MM') FROM DUAL;

The `TO_CHAR` function is used to convert a value of a different data type (like `INTERVAL`, `DATE`, or `NUMBER`) into a character string. In this statement, `INTERVAL '800' SECOND` creates an interval value, and `TO_CHAR` can correctly format this interval into a string representation like 'HH24:MM' (hours and minutes). Other options attempt invalid conversions between `INTERVAL`, `DATE`, and `NUMBER` types directly.

Question 71: What does an implicit COMMIT occur after in Oracle?

After every SELECT statement
Every DML statement
After a ROLLBACK
After DDL statements like CREATE and DROP (Correct answer)

Correct answer: After DDL statements like CREATE and DROP

Oracle automatically issues an implicit COMMIT before and after every DDL statement.

Question 72: Which data type stores variable-length character strings up to 4000 bytes in Oracle?

NCHAR
VARCHAR2 (Correct answer)
LONG
CHAR

Correct answer: VARCHAR2

VARCHAR2 stores variable-length character data up to 4000 bytes (or 32767 in extended mode) and is Oracle's recommended string type.

Question 73: What is a foreign key in a relational database?

A key that defines the order in which records are stored in the table.
A column that stores data external to the database.
A column or set of columns that uniquely identifies each row in the table.
A key that is used to link two tables together by establishing a relationship. (Correct answer)

Correct answer: A key that is used to link two tables together by establishing a relationship.

A foreign key is a column or set of columns in one table that refers to the primary key in another table. Its primary purpose is to establish and enforce a link or relationship between two tables. This ensures referential integrity, meaning that relationships between data are consistent and valid across the database.

Question 74: How can you retrieve only the top 5 highest-paid employees from the employees table?

SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY; (Correct answer)
SELECT TOP 5 * FROM employees ORDER BY salary DESC;
SELECT * FROM employees WHERE salary DESC LIMIT 5;
SELECT * FROM employees SORT BY salary LIMIT 5 DESC;

Correct answer: SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;

To retrieve a limited number of rows, such as the top N, you first need to sort the data in the desired order using `ORDER BY`. For the highest-paid, this means `ORDER BY salary DESC`. Oracle SQL then uses `FETCH FIRST N ROWS ONLY` (or `ROWNUM` in older versions) to limit the output to the specified number of rows from the sorted set. This combination ensures you get the top records based on the sorting criteria.

Question 75: What will be the output of the following SQL statement?

The statement assigns grade 'B' to employees with a salary greater than 10000.
The statement assigns grade 'A' to employees with a salary greater than 10000, 'B' to those with a salary between 5001 and 10000, and 'C' to others. (Correct answer)
The statement assigns grade 'A' to employees with a salary greater than 5000.
The statement will produce an error.

Correct answer: The statement assigns grade 'A' to employees with a salary greater than 10000, 'B' to those with a salary between 5001 and 10000, and 'C' to others.

Assuming a standard `CASE` statement structure, the conditions are evaluated sequentially. An employee with a salary greater than 10000 will first match `WHEN salary > 10000 THEN 'A'`. If that's false, the next condition `WHEN salary > 5000 THEN 'B'` is checked, meaning salaries between 5001 and 10000 will get 'B'. Finally, `ELSE 'C'` catches all remaining salaries (5000 or less). This logic correctly assigns grades based on the specified salary ranges.

Question 76: Which of the following is a valid use of the CASE expression in SQL?

CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END (Correct answer)
CASE salary > 50000 THEN 'High' ELSE 'Low' END
CASE salary WHEN > 50000 THEN 'High' ELSE 'Low' END
CASE salary WHEN > 50000 THEN 'High' END

Correct answer: CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END

The `CASE` expression in SQL allows for conditional logic, similar to if-then-else statements. The correct syntax for a searched `CASE` expression, which evaluates multiple conditions, is `CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END`. Option B correctly follows this structure by using `WHEN` followed by a boolean condition.

Question 77: Which of the following correctly describes how set operators handle NULL values when comparing rows?

Set operators treat two NULL values as equal when determining row matches (Correct answer)
NULL values cause an ORA-01400 error in set operator queries
Set operators always convert NULL to zero before comparing
NULL values are ignored and never returned by any set operator

Correct answer: Set operators treat two NULL values as equal when determining row matches

Unlike most Oracle comparisons where NULL does not equal NULL, set operators (UNION, INTERSECT, MINUS) treat two NULL values as equal when determining whether rows match between the two result sets.

Question 78: How would you use the COALESCE function to return the first non-NULL value from a list of columns?

SELECT COALESCE(column1) FROM table_name;
SELECT COALESCE(column1 AND column2 AND column3) FROM table_name;
SELECT COALESCE(column1, column2, column3) FROM table_name; (Correct answer)
SELECT COALESCE(column1 OR column2 OR column3) FROM table_name;

Correct answer: SELECT COALESCE(column1, column2, column3) FROM table_name;

The `COALESCE` function is used to return the first non-NULL expression from a list of arguments. You simply provide the columns or expressions as a comma-separated list within the function's parentheses. The function then evaluates them from left to right and returns the first one that is not NULL, making `SELECT COALESCE(column1, column2, column3) FROM table_name;` the correct usage.

Question 79: What does the ALL operator do when used with a subquery?

Acts identically to IN
Returns TRUE if the condition is met for at least one row
Returns TRUE only if the condition is met for every row returned by the subquery (Correct answer)
Returns all rows regardless of the condition

Correct answer: Returns TRUE only if the condition is met for every row returned by the subquery

ALL requires the comparison to be true for every value returned by the subquery.

Question 80: What will the following SQL statement return?

It returns 'Marketing' if department_id is 10, 'Administration' if department_id is 20, and 'Other' for all other values.
It will produce an error.
It returns 'Administration' if department_id is 10, 'Marketing' if department_id is 20, and 'Other' for all other values. (Correct answer)
It returns 'Other' for all department_id values.

Correct answer: It returns 'Administration' if department_id is 10, 'Marketing' if department_id is 20, and 'Other' for all other values.

Assuming a `CASE` or `DECODE` statement, the logic evaluates the `department_id` against specified values. If `department_id` is 10, it returns 'Administration'. If it's 20, it returns 'Marketing'. For any other `department_id` value not explicitly listed, the `ELSE` or default clause takes effect, returning 'Other'. This provides a clear conditional mapping for department names.

Oracle Database SQL Certified Associate Exam

The 1Z0-071 exam validates proficiency in Oracle Database SQL, covering relational concepts, data retrieval, joins, subqueries, DML, DDL, and data conversion functions.

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