Oracle Database SQL Certified Associate Exam — Questions and Answers

Question 1: 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_NUWBER(TO_DATE(INTERVAL '800' SECOND)) FROM DUAL;
SELECT TO_DATE(TO_NUMBER(INTERVATL '800' SECOND)) FROM DUAL;
SELECT TO_CHAR(INTERVAL '800' SECOND, 'HH24:MM') FROM DUAL; (Correct answer)

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 2: What does the EXISTS operator check in a subquery?

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

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 3: How do you find the number of distinct job titles in the EMPLOYEES table?

SELECT COUNT(DISTINCT job_id) FROM employees (Correct answer)
SELECT COUNT(*) FROM employees
SELECT COUNT(job_id) FROM employees
SELECT DISTINCT COUNT(job_id) FROM employees

Correct answer: SELECT COUNT(DISTINCT job_id) FROM employees

COUNT(DISTINCT column) counts unique non-NULL values of that column.

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

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

Correct answer: Nested subquery

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

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

UNIQUE
PRIMARY KEY (Correct answer)
NOT NULL
CHECK

Correct answer: PRIMARY KEY

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

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

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

Correct answer: CREATE TABLE

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

Question 7: Which DML statement is used to remove specific rows from a table?

TRUNCATE
DROP
DELETE (Correct answer)
REMOVE

Correct answer: DELETE

DELETE is the DML statement that removes specific rows matching the WHERE condition from a table.

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

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

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 9: 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 rolls back the pending transaction
The DDL raises an error until the transaction ends
The DDL implicitly commits the pending transaction first (Correct answer)

Correct answer: The DDL implicitly commits the pending transaction first

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

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

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

Correct answer: It implicitly executes a commit.

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 11: Which function returns the highest value in a set of values?

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

Correct answer: MAX()

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

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

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

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 13: Which clause is mandatory in an UPDATE statement to avoid updating every row?

SET
WHERE (Correct answer)
HAVING
RETURNING

Correct answer: WHERE

Without a WHERE clause, UPDATE modifies every row in the table; WHERE restricts which rows are changed.

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

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

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 15: Which SQL function can be used to convert a string to a number?

TO_NUMBER (Correct answer)
NVL
TO_CHAR
TO_DATE

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 16: What is the main advantage of using table aliases in joins?

They change the permanent table name in the database
They improve index usage automatically
They are required by Oracle for all multi-table queries
They make queries shorter and resolve column name ambiguity (Correct answer)

Correct answer: They make queries shorter and resolve column name ambiguity

Table aliases provide shorthand names that reduce typing and clarify which table an ambiguous column belongs to.

Question 17: What happens when corresponding columns in a UNION query have completely incompatible data types (for example, DATE and NUMBER)?

NULL values replace rows with incompatible types
Oracle converts all values to VARCHAR2 automatically
An ORA-01790 error is returned (Correct answer)
Oracle uses the data type from the first SELECT

Correct answer: An ORA-01790 error is returned

Oracle returns ORA-01790 ('expression must have same datatype as corresponding expression') when corresponding columns have incompatible data types in a set operator query.

Question 18: 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 19: Which set operator returns only the rows that appear in both result sets?

INTERSECT (Correct answer)
UNION ALL
MINUS
UNION

Correct answer: INTERSECT

INTERSECT returns only the rows that are common to both SELECT statements, eliminating rows unique to either query.

Question 20: In a three-table join, how many join conditions are typically required?

1
2 (Correct answer)
4
3

Correct answer: 2

For N tables you need at minimum N-1 join conditions, so 3 tables require at least 2 join conditions.

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

10
5
50 (Correct answer)
15

Correct answer: 50

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

Question 22: 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 lock a table at a specific moment
To commit changes up to a specific point
To create a backup of the current data

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 23: Which subquery type returns exactly one row and one column?

Multiple-column subquery
Scalar subquery (Correct answer)
Inline view
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 24: 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 will produce an error.
The statement assigns grade 'A' to employees with a salary greater than 5000.
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)

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 25: What is the output of SELECT MAX(salary) - MIN(salary) FROM employees?

NULL if any salary is NULL
The difference between the first and last employee salary
An error because you cannot subtract aggregate functions
The salary range across all employees (Correct answer)

Correct answer: The salary range across all employees

Oracle allows arithmetic between aggregate functions in the SELECT clause, returning the salary range.

Question 26: When multiple set operators are used in a single query without parentheses, which operator has the highest precedence in Oracle SQL?

All set operators have equal precedence evaluated left to right
UNION and UNION ALL (equal precedence)
INTERSECT (Correct answer)
MINUS

Correct answer: INTERSECT

INTERSECT has higher precedence than UNION, UNION ALL, and MINUS in Oracle SQL, so it is evaluated first when no parentheses are used.

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

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

Correct answer: Multiple-column subquery

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

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

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

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 29: What type of join returns rows from both tables regardless of whether a match exists?

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

Correct answer: FULL OUTER JOIN

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

Question 30: Which two are SQL features?

processing sets of data (Correct answer)
providing update capabilities for data in external files
providing variable definition capabilities.
providing graphical 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: Which set operator returns all rows from both queries, including duplicate rows?

UNION ALL (Correct answer)
UNION
MINUS
INTERSECT

Correct answer: UNION ALL

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

Question 32: Which INSERT syntax copies rows from one table to another without using VALUES?

COPY target_table FROM source_table
INSERT SELECT * FROM source_table INTO target_table
INSERT INTO target_table SELECT * FROM source_table (Correct answer)
INSERT INTO target_table FROM source_table

Correct answer: INSERT INTO target_table SELECT * FROM source_table

INSERT ... SELECT inserts rows returned by a SELECT statement directly into the target table.

Question 33: 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 34: What is the primary key in a relational database table?

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

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 35: What does the ORDER BY clause do in a SQL query?

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

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 36: What is the result of the following query: SELECT 1 FROM DUAL UNION SELECT 1 FROM DUAL?

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

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 37: How can you retrieve only the top 5 highest-paid employees from the employees table?

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

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 38: In Oracle SQL, the MINUS operator is the Oracle-specific equivalent of which ANSI SQL standard set operator?

DIFFERENCE
EXCEPT (Correct answer)
NOT IN
INTERSECT

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 39: Which Oracle data type stores large amounts of character data beyond 4000 bytes?

CLOB (Correct answer)
TEXT
LONG
VARCHAR2

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 40: 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 OR column2 OR column3) FROM table_name;
SELECT COALESCE(column1 AND column2 AND column3) FROM table_name;
SELECT COALESCE(column1, column2, column3) FROM table_name; (Correct answer)

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 41: 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 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.
It will produce an error.

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.

Question 42: 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 date to a number.
To convert a number or date to a string. (Correct answer)

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 43: Which clause is used to filter the results of a GROUP BY query?

HAVING (Correct answer)
FILTER
LIMIT
WHERE

Correct answer: HAVING

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

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

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

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 45: Which Oracle statement adds a constraint to an existing table?

ALTER TABLE table ADD CONSTRAINT ... (Correct answer)
MODIFY TABLE table ADD CONSTRAINT ...
CREATE CONSTRAINT ON table
INSERT CONSTRAINT INTO table

Correct answer: ALTER TABLE table ADD CONSTRAINT ...

ALTER TABLE with ADD CONSTRAINT allows you to add a new constraint to an existing table.

Question 46: What is the result of: SELECT id FROM t1 UNION SELECT id FROM t2 UNION ALL SELECT id FROM t3?

Distinct rows from (t1 UNION t2) combined with ALL rows from t3 including duplicates (Correct answer)
All rows from all three tables with full deduplication applied
All rows from all three tables with no deduplication at all
An error because UNION and UNION ALL cannot be mixed in one query

Correct answer: Distinct rows from (t1 UNION t2) combined with ALL rows from t3 including duplicates

UNION and UNION ALL have equal precedence and are evaluated left to right, so t1 UNION t2 is computed first with deduplication, then UNION ALL appends all rows from t3.

Question 47: Which type of join condition compares columns using an operator other than equals (=)?

Self-join
Equijoin
Non-equijoin (Correct answer)
Natural join

Correct answer: Non-equijoin

A non-equijoin uses comparison operators like BETWEEN, <, or > instead of = to match rows.

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

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

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 49: What does GROUP BY ROLLUP(department_id, job_id) produce in addition to normal groups?

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

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 50: Which DDL command is used to remove a column from an existing table?

MODIFY TABLE ... REMOVE COLUMN
REMOVE COLUMN
ALTER TABLE ... DROP COLUMN (Correct answer)
DELETE COLUMN

Correct answer: ALTER TABLE ... DROP COLUMN

ALTER TABLE ... DROP COLUMN removes a column and all its data from an existing table.

Question 51: A query uses GROUP BY department_id. Which column can appear in the SELECT list WITHOUT being inside an aggregate function?

department_id (Correct answer)
employee_id
hire_date
salary

Correct answer: department_id

Only department_id can appear unaggregated in SELECT because it is in the GROUP BY clause.

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

ORDER BY
FROM
WHERE (Correct answer)
SELECT

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 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 54: Where must the ORDER BY clause be placed when used with set operators in Oracle SQL?

Only at the very end of the entire compound query (Correct answer)
Before the first SELECT statement
After each individual SELECT statement
ORDER BY cannot be used with set operators

Correct answer: Only at the very end of the entire compound query

When using set operators, a single ORDER BY clause must appear at the end of the entire compound query and applies to the complete combined result set.

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

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

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 56: Which of the following correctly describes how set operators handle NULL values when comparing rows?

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

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 57: What happens to NULL values when using the AVG() function in Oracle SQL?

They are counted as zero
They are replaced with 1
They are ignored entirely (Correct answer)
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 58: Which MERGE clause handles the case where a source row already exists in the target table?

WHEN FOUND THEN MODIFY
WHEN NOT MATCHED THEN INSERT
WHEN EXISTS THEN UPDATE
WHEN MATCHED THEN UPDATE (Correct answer)

Correct answer: WHEN MATCHED THEN UPDATE

WHEN MATCHED THEN UPDATE specifies the action to take when the source row has a corresponding row in the target.

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

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

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 60: What does the MIN() function return when applied to a VARCHAR2 column?

The alphabetically first value (Correct answer)
The shortest string
The last inserted value
An error, because MIN() only works on numbers

Correct answer: The alphabetically first value

MIN() on a VARCHAR2 column returns the alphabetically (or collation-order) lowest string value.

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

VARCHAR2 (Correct answer)
CHAR
NCHAR
LONG

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 62: 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 JOIN employees mgr ON emp. manager_ id = mgr. employee_ id AND emp. department_ id<> mgr.department_ id; (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;
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 WHERE manager_ id NOT IN ( SELECT mgr.employee_ id FROM employees mgr WHERE 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 63: Which of the following is a valid use of group functions in Oracle?

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

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 64: Which Oracle-proprietary outer join syntax uses the (+) operator?

SELECT * FROM a, b ON a.id(+) = b.id
SELECT * FROM a OUTER b WHERE a.id(+) = b.id(+)
SELECT * FROM a, b WHERE a.id = b.id(+) (Correct answer)
SELECT * FROM a LEFT JOIN b ON a.id = b.id(+)

Correct answer: SELECT * FROM a, b WHERE a.id = b.id(+)

Oracle's proprietary outer join syntax places (+) on the side of the table that may have missing rows (the optional side).

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

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

Correct answer: AVG()

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

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

NOT EXISTS only works with correlated subqueries
NOT EXISTS is faster always
NOT EXISTS correctly handles NULLs unlike NOT IN (Correct answer)
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 67: What is a subquery in Oracle SQL?

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

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 68: Which DML statement can both insert new rows and update existing rows in a single operation?

UPSERT
MERGE (Correct answer)
REPLACE
INSERT OR UPDATE

Correct answer: MERGE

MERGE (also called an upsert) matches source rows to target rows and inserts new ones or updates existing ones conditionally.

Question 69: 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 * WHERE salary > 50000 FROM employees;
SELECT * FROM employees WHERE salary > 50000; (Correct answer)
SELECT * FROM employees ORDER BY salary > 50000;

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 70: What does a CHECK constraint do?

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

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 71: Which view can you query to see all tables owned by the current user in Oracle?

ALL_TABLES
DBA_TABLES
USER_TABLES (Correct answer)
V$TABLES

Correct answer: USER_TABLES

USER_TABLES shows metadata about all tables owned by the currently logged-in Oracle user.

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

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

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 73: Which of the following is an example of a pairwise multiple-column subquery?

WHERE dept_id = (SELECT dept_id FROM employees) OR job_id = (SELECT job_id FROM employees)
WHERE (SELECT dept_id, job_id FROM employees WHERE id=1) = (dept_id, job_id)
WHERE dept_id IN (SELECT dept_id FROM employees) AND job_id IN (SELECT job_id FROM employees)
WHERE (dept_id, job_id) IN (SELECT dept_id, job_id FROM employees WHERE salary > 5000) (Correct answer)

Correct answer: WHERE (dept_id, job_id) IN (SELECT dept_id, job_id FROM employees WHERE salary > 5000)

A pairwise multiple-column subquery compares a tuple of columns as a unit against tuples returned by the subquery.

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

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

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 75: What is the correct way to find employees who earn more than the average salary?

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

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 76: What does the COUNT(*) function return?

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

Correct answer: Total number of rows including NULLs

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

Question 77: 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 78: A business analyst needs to find all product IDs that appeared in January sales but did NOT appear in February sales. Which set operator is most appropriate?

INTERSECT
UNION
UNION ALL
MINUS (Correct answer)

Correct answer: MINUS

MINUS returns rows from the first query (January sales) that are not present in the second query (February sales), exactly matching the requirement to find the difference.

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

REMOVE TABLE
DELETE FROM table
TRUNCATE TABLE (Correct answer)
DROP 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 80: In which clause is it MOST common to write a correlated subquery?

FROM
GROUP BY
WHERE (Correct answer)
ORDER BY

Correct answer: WHERE

Correlated subqueries most commonly appear in the WHERE clause to filter outer query rows based on related inner query data.

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