Oracle Database SQL Certified Associate Exam — Questions and Answers

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

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 '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.

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

providing update capabilities for data in external files
processing sets of data (Correct answer)
providing database transaction control (Correct answer)
providing variable definition capabilities.
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 3: Which statement about the GROUP BY clause is TRUE?

NULL values cannot appear in a GROUP BY column
GROUP BY must always be followed by HAVING
Columns in SELECT not in an aggregate must appear in GROUP BY (Correct answer)
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 4: Which subquery type returns exactly one row and one column?

Inline view
Scalar subquery (Correct answer)
Multi-row subquery
Multiple-column 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 5: 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 LEFT JOIN employees mgr ON 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 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)

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 6: When using set operators, which columns must have compatible data types between the two SELECT statements?

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

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 7: Which type of join condition compares columns using an operator other than equals (=)?

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

Correct answer: Non-equijoin

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

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

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

Correct answer: AVG()

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

Question 9: Which of the following is NOT a valid set operator in Oracle SQL?

MINUS
UNION ALL
INTERSECT ALL (Correct answer)
UNION

Correct answer: INTERSECT ALL

Oracle SQL does not support INTERSECT ALL or MINUS ALL; the only valid set operators are UNION, UNION ALL, INTERSECT, and MINUS.

Question 10: 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 11: Which statement executes successfully?

SELECT TO_NUMBER(INTERVAL'800' SECOND, 'HH24:MM') FROM DUAL;
SELECT TO_DATE(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 12: What does the COMMIT statement do in Oracle SQL?

Saves all pending DML changes to the database permanently (Correct answer)
Rolls back all changes since the last COMMIT
Creates a savepoint
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 13: What is the effect of issuing ROLLBACK TO SAVEPOINT sp1 in a transaction?

Deletes the savepoint sp1 permanently
Commits everything before sp1
Undoes changes made after sp1 was set, but keeps sp1 and changes before it (Correct answer)
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 14: Which Oracle data type stores large amounts of character data beyond 4000 bytes?

CLOB (Correct answer)
VARCHAR2
TEXT
LONG

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 15: 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)
salary
employee_id
hire_date

Correct answer: department_id

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

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

Whether the subquery has no GROUP BY clause
Whether the subquery returns at least one row (Correct answer)
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 17: Which DDL statement removes all rows from a table but keeps its structure, and cannot be rolled back?

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

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 18: An EMPLOYEES table has 10 rows; a DEPARTMENTS table has 5 rows. A CROSS JOIN returns how many rows?

15
5
10
50 (Correct answer)

Correct answer: 50

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

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

PARTITION
CLUSTER BY
ORDER BY
GROUP BY (Correct answer)

Correct answer: GROUP BY

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

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

A database that organizes data into tables, with rows and columns, where each table represents an entity. (Correct answer)
A collection of procedures and functions that perform operations on data.
A database model where data is stored in a tree-like structure.
A collection of non-related data stored in a hierarchical 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 21: Which of the following is an example of a pairwise multiple-column subquery?

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)
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)

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 22: Which SQL clause is used to filter rows returned by a query based on a specified condition?

FROM
ORDER BY
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 23: What does the ALTER TABLE ... ADD COLUMN statement do?

Removes an existing column
Changes the data type of an existing column
Renames the table
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 24: Which of the following subqueries is a valid scalar subquery in a SELECT clause?

SELECT e.name, (SELECT dept_name FROM departments WHERE dept_id = e.dept_id) FROM employees e (Correct answer)
SELECT e.name, (SELECT * FROM departments WHERE dept_id = e.dept_id) FROM employees e
SELECT e.name, (SELECT dept_name, location FROM departments WHERE dept_id = e.dept_id) FROM employees e
SELECT e.name, (SELECT dept_name FROM departments) FROM employees e

Correct answer: SELECT e.name, (SELECT dept_name FROM departments WHERE dept_id = e.dept_id) FROM employees e

A scalar subquery in the SELECT list must return exactly one column and one row per outer row — correlating on dept_id achieves this.

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

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

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

WHERE
LIMIT
FILTER
HAVING (Correct answer)

Correct answer: HAVING

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

Question 27: Which statement about NATURAL JOIN is TRUE?

You can specify which columns to join on
It always performs a FULL OUTER JOIN
It automatically joins on all columns with the same name (Correct answer)
It joins only on primary key columns

Correct answer: It automatically joins on all columns with the same name

NATURAL JOIN implicitly equi-joins all columns that share the same name between the two tables.

Question 28: 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 29: What is a self-join used for?

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

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 30: 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 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.

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 31: 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 32: What is the purpose of using NOT EXISTS instead of NOT IN in a subquery?

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

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

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

Correct answer: FULL OUTER JOIN

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

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

= ALL
Both = ANY and IN (Correct answer)
IN
= 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 35: Which GROUP BY function returns the total sum of a numeric column?

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

Correct answer: SUM()

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

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

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

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 37: Which set operator best describes a 'logical difference' or 'relative complement' operation between two sets in Oracle SQL?

UNION ALL
INTERSECT
MINUS (Correct answer)
UNION

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

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

Correct answer: ALTER TABLE table ADD CONSTRAINT ...

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

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

CREATE TABLE t (id NUMBER UNIQUE NOT NULL, name VARCHAR2(50))
CREATE TABLE t (id NUMBER KEY, name VARCHAR2(50))
CREATE TABLE t (id NUMBER PRIMARY KEY, name VARCHAR2(50)) (Correct answer)
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 41: Which three are true about the CREATE TABLE command?

It implicitly executes a commit. (Correct answer)
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 rolls back any pending transactions.
It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint (Correct answer)

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 42: Which statement correctly updates the salary of employee 100 to 9000?

UPDATE employees SET salary = 9000 WHERE employee_id = 100 (Correct answer)
ALTER employees SET salary = 9000 WHERE employee_id = 100
MODIFY employees SET salary = 9000 WHERE employee_id = 100
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 43: Which statement about DELETE vs. TRUNCATE is TRUE in Oracle?

DELETE is DDL; TRUNCATE is DML
Both can be rolled back
TRUNCATE can be rolled back; DELETE cannot
DELETE is DML and can be rolled back; TRUNCATE is DDL and cannot be rolled back (Correct answer)

Correct answer: DELETE is DML and can be rolled back; TRUNCATE is DDL and cannot be rolled back

DELETE is a DML statement that can be rolled back; TRUNCATE is DDL that implicitly commits and cannot be rolled back.

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

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

Correct answer: CREATE TABLE

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

Question 45: 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 SORT BY last_name;
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 46: 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 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.

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 47: 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 one row containing the value 1 (Correct answer)
Returns two rows each containing the value 1
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 48: A subquery in the FROM clause is called a:

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

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

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

Correct answer: MAX()

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

Question 50: 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, GROUP BY, WHERE, HAVING, ORDER BY
SELECT, FROM, HAVING, WHERE, GROUP BY, ORDER BY
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY (Correct answer)

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

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

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

SELECT SUM(salary) FROM employees WHERE SUM(salary) > 10000
SELECT SUM(salary), department_id FROM employees GROUP BY SUM(salary)
SELECT SUM(salary) FROM employees HAVING SUM(salary) > 10000 (Correct answer)
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 52: Which data type stores variable-length character strings up to 4000 bytes in Oracle?

CHAR
NCHAR
LONG
VARCHAR2 (Correct answer)

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 53: Which set operator returns rows from the first query that do NOT appear in the second query?

UNION ALL
UNION
INTERSECT
MINUS (Correct answer)

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 54: 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 55: Which constraint ensures each value in a column is unique and not NULL?

NOT NULL
UNIQUE
CHECK
PRIMARY KEY (Correct answer)

Correct answer: PRIMARY KEY

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

Question 56: Which Oracle-proprietary outer join syntax uses the (+) operator?

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

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 57: What does the ON DELETE CASCADE option on a FOREIGN KEY do?

Automatically deletes child rows when the parent row is deleted (Correct answer)
Sets child foreign key values to NULL when the parent is deleted
Prevents deletion of parent rows that have child rows
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 58: 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 do not support relationships between data entities.
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)

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

NOT IN
DIFFERENCE
INTERSECT
EXCEPT (Correct answer)

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 60: What will the following SQL statement return?

It returns 'Other' for all department_id values.
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)

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 61: A subquery that contains another subquery inside it is called a:

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

Correct answer: Nested subquery

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

Question 62: 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 * 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;

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 63: Which DML statement adds a new row to a table?

INSERT (Correct answer)
UPDATE
CREATE
MERGE

Correct answer: INSERT

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

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

NULL values cause an ORA-01400 error in set operator queries
Set operators treat two NULL values as equal when determining row matches (Correct answer)
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 66: What happens when corresponding columns in a UNION query have completely incompatible data types (for example, DATE and NUMBER)?

An ORA-01790 error is returned (Correct answer)
NULL values replace rows with incompatible types
Oracle converts all values to VARCHAR2 automatically
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 67: 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 HAVING salary > AVG(salary)
SELECT * FROM employees WHERE salary > ALL(SELECT salary FROM employees)
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: What does the ORDER BY clause do in a SQL query?

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

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 69: How would you use the COALESCE function to return the first non-NULL value from a list of columns?

SELECT COALESCE(column1, column2, column3) FROM table_name; (Correct answer)
SELECT COALESCE(column1 AND column2 AND column3) FROM table_name;
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 70: What does an implicit COMMIT occur after in Oracle?

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

Correct answer: After DDL statements like CREATE and DROP

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

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

To convert a string to a date.
To convert a string to a number.
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 72: In which clause is it MOST common to write a correlated subquery?

ORDER BY
FROM
GROUP BY
WHERE (Correct answer)

Correct answer: WHERE

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

Question 73: Which MERGE clause handles the case where a source row already exists in the target table?

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

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

Nothing different from regular GROUP BY
Subtotals for each department_id and a grand total (Correct answer)
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 75: Which SQL function can be used to convert a string to a number?

TO_NUMBER (Correct answer)
TO_CHAR
TO_DATE
NVL

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 76: What does the ALL operator do when used with a subquery?

Returns TRUE only if the condition is met for every row returned by the subquery (Correct answer)
Returns TRUE if the condition is met for at least one row
Acts identically to IN
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 77: What is the result of a CROSS JOIN between a table with 4 rows and a table with 3 rows?

7 rows
4 rows
3 rows
12 rows (Correct answer)

Correct answer: 12 rows

A CROSS JOIN (Cartesian product) returns every combination, so 4 × 3 = 12 rows.

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

They are ignored entirely (Correct answer)
They are counted as zero
They are replaced with 1
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 79: Which DML statement can both insert new rows and update existing rows in a single operation?

INSERT OR UPDATE
MERGE (Correct answer)
UPSERT
REPLACE

Correct answer: MERGE

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

Question 80: 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
INTERSECT
MINUS (Correct answer)
UNION ALL

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.

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