Oracle Database SQL Certified Associate Exam — Questions and Answers

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

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

Correct answer: SELECT replacing VALUES

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

Question 2: Which statement executes successfully?

SELECT TO_NUWBER(TO_DATE(INTERVAL '800' SECOND)) FROM DUAL;
SELECT TO_DATE(INTERVAL '800' SECOND,'HH24:MM') FROM DUAL;
SELECT TO_DATE(TO_NUMBER(INTERVATL '800' SECOND)) FROM DUAL;
SELECT TO_NUMBER(INTERVAL'800' SECOND, 'HH24:MM') 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 3: What will the following SQL statement return?

It returns 'Other' for all department_id 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 'Marketing' if department_id is 10, 'Administration' if department_id is 20, and 'Other' for all other 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 4: What is a self-join used for?

Joining a table using its primary key only
Joining without a WHERE clause
Joining a table to itself to compare rows within the same table (Correct answer)
Joining two identical tables from different schemas

Correct answer: Joining a table to itself to compare rows within the same table

A self-join joins a table to itself using aliases, typically to compare rows such as employee-manager relationships.

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

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

Correct answer: Nested subquery

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

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

The DDL raises an error until the transaction ends
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

Correct answer: The DDL implicitly commits the pending transaction first

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

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

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;
SELECT COALESCE(column1) 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 8: Which statement correctly nests group functions?

SELECT AVG(MAX(salary)) FROM employees
SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id (Correct answer)
SELECT SUM(AVG(salary)) FROM employees
SELECT COUNT(SUM(salary)) FROM employees

Correct answer: SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id

Oracle allows nesting group functions one level deep when a GROUP BY clause is present.

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

NVL
TO_NUMBER (Correct answer)
TO_DATE
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 10: When inserting a row, what value must you supply for a NOT NULL column with no DEFAULT?

Zero or empty string is accepted
A non-NULL explicit value must be provided (Correct answer)
NULL is inserted automatically
Oracle generates a sequence value

Correct answer: A non-NULL explicit value must be provided

A column defined as NOT NULL with no DEFAULT requires an explicit non-NULL value in the INSERT statement.

Question 11: 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 12: Which SQL statement will return all employees in the employees table sorted by their last_name in ascending order?

SELECT * FROM employees ORDER BY last_name ASC; (Correct answer)
SELECT * FROM employees WHERE last_name ORDER BY ASC;
SELECT * FROM employees SORT BY last_name;
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 13: A query uses GROUP BY department_id. Which column can appear in the SELECT list WITHOUT being inside an aggregate function?

employee_id
hire_date
salary
department_id (Correct answer)

Correct answer: department_id

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

Question 14: What does the MIN() function return when applied to a VARCHAR2 column?

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

Correct answer: The alphabetically first value

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

Question 15: What is a subquery in Oracle SQL?

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

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 16: Which three are true about the CREATE TABLE command?

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.
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.
It implicitly executes a commit. (Correct answer)
The owner of the table must have the UNLIMITED TABLESPACE system privilege.

Correct answer: The owner of the table should have space quota available on the tablespace where the table is defined.

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 17: When using a USING clause in a JOIN, what happens if you try to prefix the USING column with a table alias?

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

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 18: Which of the following correctly creates a table with a primary key constraint?

CREATE TABLE t (id NUMBER CONSTRAINT pk, name VARCHAR2(50))
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))

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 19: 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 column or set of columns that uniquely identifies each row in the table.
A key that defines the order in which records are stored 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 20: What does the EXISTS operator check in a subquery?

Whether the subquery returns at least one row (Correct answer)
Whether the subquery has no GROUP BY clause
Whether the subquery returns exactly one row
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 21: What does INTERSECT return when there are NO rows common to both result sets?

An empty result set containing zero rows (Correct answer)
All rows from both queries combined
NULL
An ORA-00942 table or view does not exist error

Correct answer: An empty result set containing zero rows

INTERSECT returns an empty result set with no rows when the two queries share no common rows; this is a valid outcome and not an error condition.

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

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

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

UNION
UNION ALL
MINUS
INTERSECT (Correct answer)

Correct answer: INTERSECT

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

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

IN
ANY
ALL
= (Correct answer)

Correct answer: =

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

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

FROM
WHERE (Correct answer)
SELECT
ORDER BY

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

ALTER TABLE table ADD CONSTRAINT ... (Correct answer)
INSERT CONSTRAINT INTO table
MODIFY TABLE table ADD CONSTRAINT ...
CREATE CONSTRAINT ON 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 28: What is a FOREIGN KEY constraint used for?

Ensuring uniqueness within a column
Limiting the range of values in a column
Preventing NULL values in a column
Enforcing referential integrity between two tables (Correct answer)

Correct answer: Enforcing referential integrity between two tables

A FOREIGN KEY constraint ensures that values in a column match values in a referenced primary key column of another table.

Question 29: Which of the following queries correctly places the ORDER BY clause when using set operators?

SELECT id FROM emp UNION SELECT id FROM dept ORDER BY id; (Correct answer)
SELECT id FROM emp ORDER BY id UNION SELECT id FROM dept;
SELECT id FROM emp UNION ORDER BY id SELECT id FROM dept;
ORDER BY id SELECT id FROM emp UNION SELECT id FROM dept;

Correct answer: SELECT id FROM emp UNION SELECT id FROM dept ORDER BY id;

The ORDER BY clause must appear at the very end of the compound query after the final SELECT statement in a set operator expression.

Question 30: Which is the correct order of clauses in a SELECT statement using GROUP BY and HAVING?

SELECT, FROM, WHERE, HAVING, GROUP BY, ORDER BY
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY (Correct answer)
SELECT, FROM, HAVING, WHERE, GROUP BY, ORDER BY
SELECT, FROM, GROUP BY, WHERE, HAVING, ORDER BY

Correct answer: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

The correct order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

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

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

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 32: In a MERGE statement, what does the WHEN NOT MATCHED clause do?

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
Rolls back the merge operation

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 33: 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?

UNION ALL
UNION
INTERSECT
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 34: What happens if you issue a DELETE without a WHERE clause?

All rows in the table are deleted (Correct answer)
An error is raised
Only duplicate rows are deleted
Only the first row is 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 35: What does the ALTER TABLE ... ADD COLUMN statement do?

Renames the table
Changes the data type of an existing column
Removes an existing column
Adds a new column to an existing table (Correct answer)

Correct answer: Adds a new column to an existing table

ALTER TABLE with the ADD clause inserts a new column definition into an existing table.

Question 36: How many SELECT statements can be combined using set operators in a single Oracle SQL compound query?

Maximum of 4
There is no practical limit imposed by Oracle (Correct answer)
Maximum of 255
Maximum of 2

Correct answer: There is no practical limit imposed by Oracle

Oracle SQL does not impose a strict practical limit on the number of SELECT statements that can be chained together using set operators in a single compound query.

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

SELECT * FROM employees WHERE salary > 50000; (Correct answer)
SELECT * FROM employees ORDER BY salary > 50000;
SELECT * WHERE salary > 50000 FROM employees;
SELECT * ORDER BY 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 38: Which DML statement adds a new row to a table?

UPDATE
CREATE
MERGE
INSERT (Correct answer)

Correct answer: INSERT

INSERT adds one or more new rows to a table using either the VALUES clause or a subquery.

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

They become invalid but are not dropped (Correct answer)
Oracle raises an error and prevents the DROP
They are also dropped automatically
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 40: What does an implicit COMMIT occur after in Oracle?

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

Correct answer: After DDL statements like CREATE and DROP

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

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

1
4
2 (Correct answer)
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 42: Which of the following is true about the DECODE function?

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

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

A column used to create a relationship between two tables.
A column or set of columns that uniquely identifies each row in the table. (Correct answer)
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 44: 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
Both options A and C
NOT EXISTS only works with correlated subqueries

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 45: Which DDL statement creates a new table in Oracle?

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

Correct answer: CREATE TABLE

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

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

15
50 (Correct answer)
10
5

Correct answer: 50

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

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

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

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 48: Which of the following best describes a relational database?

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

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

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 50: Which two are SQL features?

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

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 51: Which keyword must follow a table name when performing an ANSI-style FULL OUTER JOIN?

OUTER JOIN
FULL 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 52: 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)
= ANY
IN
= ALL

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 53: Which two queries return rows for employees whose manager works in a different department?

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 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 WHERE manager_ id NOT IN ( SELECT mgr.employee_ id FROM employees mgr WHERE emp. department_ id < > mgr.department_ id );
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 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;

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

Returns an error because DUAL cannot be referenced twice
Returns two rows each containing the value 1
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 56: Which clause is mandatory in an UPDATE statement to avoid updating every row?

WHERE (Correct answer)
HAVING
SET
RETURNING

Correct answer: WHERE

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

Question 57: 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 WHERE COUNT(*) > 5 GROUP BY dept_id
SELECT dept_id, COUNT(*) FROM emp GROUP BY dept_id WHERE COUNT(*) > 5
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 58: 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 59: Which clause is used to filter the results of a GROUP BY query?

HAVING (Correct answer)
FILTER
WHERE
LIMIT

Correct answer: HAVING

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

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

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

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

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

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 62: Which GROUP BY function returns the total sum of a numeric column?

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

Correct answer: SUM()

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

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

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

Correct answer: MAX()

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

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

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

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

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

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 66: Which of the following is a valid use of the CASE expression in SQL?

CASE salary > 50000 THEN 'High' ELSE 'Low' END
CASE salary WHEN > 50000 THEN 'High' END
CASE salary WHEN > 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 67: Which of the following is true about relational databases?

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

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

SELECT COUNT(*) FROM employees
SELECT DISTINCT COUNT(job_id) FROM employees
SELECT COUNT(DISTINCT job_id) FROM employees (Correct answer)
SELECT 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 69: What is the result of using COUNT(column_name) when some rows have NULL in that column?

It returns NULL
It counts only non-NULL rows (Correct answer)
It raises an ORA error
It counts all rows including NULLs

Correct answer: It counts only non-NULL rows

COUNT(column_name) excludes NULL values, counting only rows where the column has a non-NULL value.

Question 70: What is the CHAR data type's behavior regarding storage length?

It allows Unicode characters only
It pads values with spaces to the defined fixed length (Correct answer)
It stores variable-length strings
It stores only single characters

Correct answer: It pads values with spaces to the defined fixed length

CHAR stores fixed-length character data by right-padding shorter values with spaces to reach the declared length.

Question 71: 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 72: A subquery that returns more than one column is called a:

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

Correct answer: Multiple-column subquery

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

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

NOT NULL
PRIMARY KEY (Correct answer)
CHECK
UNIQUE

Correct answer: PRIMARY KEY

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

Question 74: 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 department_id, SUM(salary) FROM employees HAVING department_id = 10
SELECT SUM(salary) FROM employees HAVING SUM(salary) > 10000 (Correct answer)
SELECT SUM(salary) FROM employees WHERE SUM(salary) > 10000

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

EXCEPT (Correct answer)
DIFFERENCE
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 76: Which set operator returns all rows from both queries, including duplicate rows?

UNION ALL (Correct answer)
MINUS
INTERSECT
UNION

Correct answer: UNION ALL

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

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

FULL OUTER JOIN (Correct answer)
INNER JOIN
LEFT OUTER JOIN
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 78: 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 79: Which DML statement is used to remove specific rows from a table?

DELETE (Correct answer)
TRUNCATE
DROP
REMOVE

Correct answer: DELETE

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

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

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

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.

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