Oracle Database SQL Certified Associate Exam — Questions and Answers

Question 1: What does the SET col = NULL do in an UPDATE?

Raises an error always
Sets it to zero
Sets the column to NULL for matched rows (Correct answer)
Deletes the column

Correct answer: Sets the column to NULL for matched rows

It assigns NULL to the column in the rows matched by WHERE.

Question 2: What is a relational database primarily organized into?

Graph nodes
Nested folders
Key-value pairs only
Tables made of rows and columns (Correct answer)

Correct answer: Tables made of rows and columns

Relational databases store data in tables consisting of rows and columns.

Question 3: Two transactions waiting on each other's locks indefinitely is called a:

Livelock
Rollback
Deadlock (Correct answer)
Savepoint

Correct answer: Deadlock

A deadlock occurs when transactions each hold locks the other needs.

Question 4: What does a VIEW represent in SQL?

A user permission
A physical backup table
A database engine
A virtual table based on a query (Correct answer)

Correct answer: A virtual table based on a query

A view is a saved query that acts like a virtual table.

Question 5: What does GROUP BY ROLLUP(region, product) add to the result set?

Distinct rows only
Subtotal and grand total rows (Correct answer)
Sorted output only
Nothing extra

Correct answer: Subtotal and grand total rows

ROLLUP generates subtotals for each level plus a grand total row.

Question 6: Which scenario benefits MOST from a database index?

SELECT * FROM large_table with no WHERE clause
An UPDATE statement that modifies every row in the table
A SELECT with a WHERE clause filtering on an indexed column (Correct answer)
An INSERT of 10,000 rows in a single batch

Correct answer: A SELECT with a WHERE clause filtering on an indexed column

A SELECT filtering on an indexed column directly benefits from the index, enabling the database to locate matching rows with a seek instead of a full table scan.

Question 7: An INNER JOIN between two tables with a one-to-many relationship can return what?

Only NULL rows
Exactly one row per table
Fewer rows than either table always
More rows than the 'one' table has (Correct answer)

Correct answer: More rows than the 'one' table has

Each parent row repeats once per matching child row, multiplying output rows.

Question 8: A data analyst wants to create a report showing each employee's salary alongside the highest salary within their respective department. Which of the following window functions, when used with `OVER (PARTITION BY Department ORDER BY Salary DESC)`, will correctly identify the top salary for the department on every employee's row?

LAST_VALUE(Salary)
LEAD(Salary)
FIRST_VALUE(Salary) (Correct answer)
NTH_VALUE(Salary, 2)

Correct answer: FIRST_VALUE(Salary)

The `FIRST_VALUE()` function returns the value of the specified expression from the first row of the window frame. [28] By partitioning by `Department` and ordering by `Salary DESC` (descending), the first row in each partition will always be the one with the highest salary. `FIRST_VALUE(Salary)` will therefore return this maximum salary for every row within that department's partition. [24, 26]

Question 9: Which clause filters groups based on an aggregate condition like SUM(amount) > 1000?

ON
FILTER
HAVING (Correct answer)
WHERE

Correct answer: HAVING

HAVING applies conditions to grouped results after aggregation, unlike WHERE.

Question 10: How can you add a constraint to an existing table?

ALTER TABLE ... ADD CONSTRAINT (Correct answer)
SET CONSTRAINT
CREATE CONSTRAINT
INSERT CONSTRAINT

Correct answer: ALTER TABLE ... ADD CONSTRAINT

ALTER TABLE ... ADD CONSTRAINT attaches a new constraint to an already-existing table.

Question 11: Which constraint ensures a column cannot contain NULL values?

DEFAULT
NOT NULL (Correct answer)
CHECK
UNIQUE

Correct answer: NOT NULL

The NOT NULL constraint requires a column to always have a value.

Question 12: Placing a filter on the right table in the WHERE clause of a LEFT JOIN can have what effect?

It speeds the join up
It has no effect
It can turn the LEFT JOIN into an effective INNER JOIN (Correct answer)
It causes a syntax error

Correct answer: It can turn the LEFT JOIN into an effective INNER JOIN

Filtering non-NULL right-table values in WHERE removes the NULL-padded unmatched rows.

Question 13: To update multiple columns in one UPDATE, you separate assignments with what?

Commas (Correct answer)
AND
Semicolons
Pipes

Correct answer: Commas

Multiple column assignments in SET are separated by commas.

Question 14: A data analyst needs to find all products that have a list price higher than the average list price of all products. Which of the following queries correctly accomplishes this task?

SELECT ProductName, AVG(ListPrice) FROM Products HAVING ListPrice > AVG(ListPrice);
SELECT P1.ProductName FROM Products P1 JOIN Products P2 ON P1.ListPrice > AVG(P2.ListPrice);
SELECT ProductName FROM Products WHERE ListPrice > (SELECT AVG(ListPrice) FROM Products); (Correct answer)
SELECT ProductName FROM Products WHERE ListPrice > AVG(ListPrice);

Correct answer: SELECT ProductName FROM Products WHERE ListPrice > (SELECT AVG(ListPrice) FROM Products);

The correct query uses a scalar subquery in the WHERE clause. The subquery `(SELECT AVG(ListPrice) FROM Products)` is executed first, returning a single value (the average list price). The outer query then uses this single value to filter the products, comparing each product's `ListPrice` to the calculated average. Aggregate functions like `AVG()` cannot be used directly in a `WHERE` clause applied to individual rows.

Question 15: What does a CASCADE option on DROP TABLE typically do?

Drops dependent objects like foreign keys and views as well (Correct answer)
Prevents the drop entirely
Creates a copy before dropping
Drops only the indexes

Correct answer: Drops dependent objects like foreign keys and views as well

CASCADE automatically drops objects that depend on the table, such as referencing constraints or views.

Question 16: To fulfill an order report, you need to retrieve the customer's name, the order date, and the product name for every item in every order. This requires joining three tables: `Customers` (CustomerID, CustomerName), `Orders` (OrderID, CustomerID, OrderDate), and `OrderDetails` (OrderDetailID, OrderID, ProductID), and `Products` (ProductID, ProductName). Which query correctly joins these tables?

SELECT c.CustomerName, o.OrderDate, p.ProductName FROM Customers c JOIN Orders o JOIN OrderDetails od JOIN Products p;
SELECT c.CustomerName, o.OrderDate, p.ProductName FROM Customers c, Orders o, OrderDetails od, Products p WHERE c.CustomerID = o.CustomerID AND o.OrderID = od.OrderID AND od.ProductID = p.ProductID;
SELECT c.CustomerName, o.OrderDate, p.ProductName FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN OrderDetails od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID; (Correct answer)
SELECT c.CustomerName, o.OrderDate, p.ProductName FROM Customers c OUTER JOIN Orders o ON c.CustomerID = o.CustomerID OUTER JOIN OrderDetails od ON o.OrderID = od.OrderID OUTER JOIN Products p ON od.ProductID = p.ProductID;

Correct answer: SELECT c.CustomerName, o.OrderDate, p.ProductName FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN OrderDetails od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID;

To join multiple tables, you chain `JOIN` clauses together. The query correctly starts with `Customers`, joins to `Orders` on `CustomerID`, then joins that result to `OrderDetails` on `OrderID`, and finally joins that to `Products` on `ProductID`. Each `ON` clause correctly specifies the linking columns between the successive tables.

Question 17: Which constraint ensures a column cannot contain duplicate values but allows one NULL in many systems?

CHECK
DEFAULT
PRIMARY KEY
UNIQUE (Correct answer)

Correct answer: UNIQUE

A UNIQUE constraint forbids duplicate values while typically permitting a single NULL.

Question 18: Which approach lets you join a table to the results of a subquery?

USING SUBQUERY
JOIN SELECT ...
SUBJOIN
JOIN (SELECT ...) AS sub ON ... (Correct answer)

Correct answer: JOIN (SELECT ...) AS sub ON ...

A derived table is a subquery in the FROM clause given an alias and joined normally.

Question 19: A database administrator needs to remove all rows from a large 'Log_Archive' table quickly, without the need to roll back the operation. Which SQL command is the most efficient for this task?

DELETE FROM Log_Archive;
UPDATE Log_Archive SET IsActive = 0;
DROP TABLE Log_Archive;
TRUNCATE TABLE Log_Archive; (Correct answer)

Correct answer: TRUNCATE TABLE Log_Archive;

TRUNCATE TABLE is the most efficient command for deleting all rows from a table when rollback capability is not needed. It is a DDL operation that deallocates the data pages, which is much faster and uses fewer system and transaction log resources than DELETE, a DML operation that removes rows one by one.

Question 20: Which of the following statements about system information in an RDBMS is correct?

This information often cannot be updated by a user.
RDBMS store database definition information in system-created tables.
This information can be accessed using SQL.
All of the above. (Correct answer)

Correct answer: All of the above.

Relational Database Management Systems (RDBMS) store metadata, which is information about the database structure (like table names, column types, constraints), in special system-created tables, often called a data dictionary or catalog. This system information is typically read-only for regular users but can be accessed and queried using standard SQL commands. Therefore, all the statements provided are correct regarding system information in an RDBMS.

Question 21: A developer wants to change the name of an existing table from `tbl_Users` to `Users`. Which of the following commands should be used?

MODIFY TABLE tbl_Users RENAME TO Users;
CREATE ALIAS Users FOR tbl_Users;
UPDATE TABLE tbl_Users SET NAME = Users;
RENAME TABLE tbl_Users TO Users; (Correct answer)

Correct answer: RENAME TABLE tbl_Users TO Users;

The `RENAME TABLE` command is the standard DDL statement used to change the name of an existing table. While some database systems might use a variation like `ALTER TABLE ... RENAME TO`, `RENAME TABLE` is also a common and direct syntax.

Question 22: Given the following query, what will be the value in the `NextSale` column for the row where `Month` is '2024-02-01'?

10000
0
12000
11000 (Correct answer)

Correct answer: 11000

The `LEAD(Sales, 1, 0)` function looks ahead one row (`offset` of 1) in the result set ordered by `Month`. For the row '2024-02-01', the next row is '2024-03-01', which has a `Sales` value of 11000. Therefore, 11000 is returned. [2, 7] The default value of 0 would only be used for the last row in the set where there is no subsequent row. [3]

Question 23: A query is written to find the total sales for each product. Why is the following query syntactically incorrect? `SELECT ProductName, Price FROM Sales GROUP BY ProductName;`

The `SUM()` function is missing from the `GROUP BY` clause.
An alias is required for the `ProductName` column.
The `GROUP BY` clause cannot be used with a non-numeric column like `ProductName`.
The `Price` column is in the SELECT list but is not part of an aggregate function or the GROUP BY clause. (Correct answer)

Correct answer: The `Price` column is in the SELECT list but is not part of an aggregate function or the GROUP BY clause.

When using `GROUP BY`, any column in the `SELECT` list must either be part of the `GROUP BY` clause or be used within an aggregate function (like SUM(), AVG(), COUNT()). In this case, `Price` is a non-aggregated column and is not in the `GROUP BY` list, which will cause an error in most SQL dialects.

Question 24: What is the result of joining a table to an empty table with an INNER JOIN?

Zero rows (Correct answer)
NULL-filled rows
All rows from the non-empty table
An error

Correct answer: Zero rows

With no matching rows available, an INNER JOIN returns nothing.

Question 25: When should you prefer a CTE over a view?

When you need to enforce permissions
For a one-off query where you do not need a reusable database object (Correct answer)
When many queries must reuse the logic permanently
When you need to store data physically

Correct answer: For a one-off query where you do not need a reusable database object

CTEs suit single-query use, while views are better for logic reused across many queries.

Question 26: When you ALTER a column's data type, what risk should you consider?

It only affects future rows
Existing data may be incompatible and cause errors or truncation (Correct answer)
It is reversible with ROLLBACK in all systems
Indexes are always rebuilt automatically with no impact

Correct answer: Existing data may be incompatible and cause errors or truncation

Changing a column's type can fail or truncate data if existing values don't fit the new type.

Question 27: What does the LIKE operator do in a WHERE clause?

Sorts results
Joins tables
Compares numbers
Matches a pattern in text (Correct answer)

Correct answer: Matches a pattern in text

LIKE performs pattern matching using wildcards such as % and _.

Question 28: Which is the correct syntax to insert a single row?

ADD INTO t (1,2)
INSERT t VALUES = 1,2
INSERT INTO t (a,b) VALUES (1,2) (Correct answer)
INSERT t SET (1,2)

Correct answer: INSERT INTO t (a,b) VALUES (1,2)

INSERT INTO table (columns) VALUES (values) is standard insert syntax.

Question 29: What is a covering index?

An index that includes all columns needed by a specific query (Correct answer)
Another name for a primary key index
An index that spans multiple related tables
An index that covers NULL values for nullable columns

Correct answer: An index that includes all columns needed by a specific query

A covering index includes every column referenced in a query (in SELECT, WHERE, and JOIN clauses), so the query can be resolved from the index alone without accessing the base table.

Question 30: Which of these is NOT a TCL command?

COMMIT
ROLLBACK
TRUNCATE (Correct answer)
SAVEPOINT

Correct answer: TRUNCATE

TRUNCATE is a DDL command; COMMIT, ROLLBACK, and SAVEPOINT are TCL.

Question 31: You need to write a query that calculates the average order total for each customer and then joins this result back to the `Customers` table to display the customer's name and their average order total. The `Orders` table contains `CustomerID` and `OrderTotal`. What is the correct way to structure this query?

SELECT c.CustomerName, AVG(o.OrderTotal) FROM Customers c, Orders o WHERE c.CustomerID = o.CustomerID GROUP BY c.CustomerName HAVING AVG(o.OrderTotal);
SELECT c.CustomerName, Agg.AvgTotal FROM Customers c JOIN (SELECT CustomerID, AVG(OrderTotal) AS AvgTotal FROM Orders GROUP BY CustomerID) AS Agg ON c.CustomerID = Agg.CustomerID; (Correct answer)
SELECT c.CustomerName, (SELECT AVG(o.OrderTotal) FROM Orders o WHERE c.CustomerID = o.CustomerID) AS AvgTotal FROM Customers c;
SELECT c.CustomerName, AVG(o.OrderTotal) FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID;

Correct answer: SELECT c.CustomerName, Agg.AvgTotal FROM Customers c JOIN (SELECT CustomerID, AVG(OrderTotal) AS AvgTotal FROM Orders GROUP BY CustomerID) AS Agg ON c.CustomerID = Agg.CustomerID;

This scenario is a perfect use case for a subquery in the `FROM` clause, also known as a derived table. The subquery `(SELECT CustomerID, AVG(OrderTotal) AS AvgTotal FROM Orders GROUP BY CustomerID)` first calculates the average total for each customer. This result set is then treated like a temporary table (aliased as `Agg`) and joined with the `Customers` table to retrieve the customer names.

Question 32: Which command adds a new column to an existing table?

MODIFY TABLE ... NEW COLUMN
INSERT COLUMN INTO
UPDATE TABLE ... ADD
ALTER TABLE ... ADD COLUMN (Correct answer)

Correct answer: ALTER TABLE ... ADD COLUMN

ALTER TABLE with ADD COLUMN modifies an existing table's structure by adding a column.

Question 33: A marketing team wants to segment its customer base into four equal-sized groups (quartiles) based on their total purchase amount to identify top spenders. Which window function is specifically designed to divide an ordered partition of rows into a specified number of ranked groups?

CUME_DIST()
PERCENT_RANK()
RANK()
NTILE(4) (Correct answer)

Correct answer: NTILE(4)

The `NTILE(n)` function is the correct choice as it distributes the rows in an ordered partition into a specified number of groups, in this case, 4. [1, 8] It assigns a rank from 1 to `n` for each group, which is ideal for creating quartiles, deciles, or other percentile-based segments. [13]

Question 34: What does CREATE OR REPLACE VIEW do if the view already exists?

Drops all dependent views
Raises a duplicate error
Redefines the existing view (Correct answer)
Creates a second copy

Correct answer: Redefines the existing view

CREATE OR REPLACE VIEW redefines the view in place without dropping it first.

Question 35: After a DML change, what statement makes it permanent?

CLOSE
COMMIT (Correct answer)
SAVE
FLUSH

Correct answer: COMMIT

COMMIT permanently saves the changes made in the current transaction.

Question 36: Which command refreshes the stored data of a materialized view in many databases?

REBUILD VIEW
UPDATE VIEW
RELOAD VIEW
REFRESH MATERIALIZED VIEW (Correct answer)

Correct answer: REFRESH MATERIALIZED VIEW

REFRESH MATERIALIZED VIEW re-executes the underlying query and updates the cache.

Question 37: In MySQL, which setting disables automatic committing of each statement?

DISABLE COMMIT
SET commit = off
SET transaction = manual
SET autocommit = 0 (Correct answer)

Correct answer: SET autocommit = 0

Setting autocommit to 0 requires explicit COMMIT to save changes.

Question 38: What is the default transaction behavior in many databases like Oracle for DML statements?

Changes roll back automatically
Changes are uncommitted until COMMIT is issued (Correct answer)
Changes are read-only
Every statement auto-commits

Correct answer: Changes are uncommitted until COMMIT is issued

In Oracle, DML changes are held in the transaction until an explicit COMMIT (or implicit DDL commit).

Question 39: You need to write a query that lists all university departments and the professors in them. The report must include all departments, even those that currently have no professors assigned. You have two tables: `Departments` (`DepartmentID`, `DepartmentName`) and `Professors` (`ProfessorID`, `ProfessorName`, `DepartmentID`). Which query should you use?

SELECT D.DepartmentName, P.ProfessorName FROM Departments D CROSS JOIN Professors P;
SELECT D.DepartmentName, P.ProfessorName FROM Professors P RIGHT JOIN Departments D ON P.DepartmentID = D.DepartmentID; (Correct answer)
SELECT D.DepartmentName, P.ProfessorName FROM Departments D INNER JOIN Professors P ON D.DepartmentID = P.DepartmentID;
SELECT D.DepartmentName, P.ProfessorName FROM Departments D, Professors P WHERE D.DepartmentID = P.DepartmentID;

Correct answer: SELECT D.DepartmentName, P.ProfessorName FROM Professors P RIGHT JOIN Departments D ON P.DepartmentID = D.DepartmentID;

A `RIGHT JOIN` returns all rows from the right table (`Departments`) and the matched rows from the left table (`Professors`). If a department has no professors, it will still appear in the list with a NULL value for `ProfessorName`. A `LEFT JOIN` with the tables swapped (`FROM Departments D LEFT JOIN Professors P...`) would also achieve the same result.

Question 40: Can window functions be used directly in a WHERE clause?

Only with RANK()
Only in PostgreSQL
Yes, always
No, you must use a subquery or CTE to filter on them (Correct answer)

Correct answer: No, you must use a subquery or CTE to filter on them

Window functions are evaluated after WHERE, so filtering on them requires wrapping in a subquery or CTE.

Question 41: What is the key difference between TRUNCATE and DELETE?

DELETE is faster than TRUNCATE
TRUNCATE removes the table structure
TRUNCATE only works on views
TRUNCATE cannot be filtered with WHERE and resets storage (Correct answer)

Correct answer: TRUNCATE cannot be filtered with WHERE and resets storage

TRUNCATE removes all rows without a WHERE clause and generally resets the table more efficiently than DELETE.

Question 42: What does the GROUP BY clause do?

Filters individual rows
Joins two tables
Sorts the result set
Groups rows sharing a value for aggregation (Correct answer)

Correct answer: Groups rows sharing a value for aggregation

GROUP BY groups rows with the same values so aggregate functions can summarize them.

Question 43: What does WHERE score >= 60 AND score <= 90 equal?

WHERE score = 60 OR 90
WHERE score BETWEEN 60 AND 90 (Correct answer)
WHERE score LIKE '60-90'
WHERE score IN (60, 90)

Correct answer: WHERE score BETWEEN 60 AND 90

BETWEEN is the inclusive-range equivalent of two combined comparison operators.

Question 44: You are writing a query to retrieve the names of products and the names of the categories they belong to. You have a `Products` table (with `ProductID`, `ProductName`, `CategoryID`) and a `Categories` table (with `CategoryID`, `CategoryName`). Which query will return only the products that have a matching category?

SELECT p.ProductName, c.CategoryName FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID; (Correct answer)
SELECT p.ProductName, c.CategoryName FROM Products p CROSS JOIN Categories c;
SELECT p.ProductName, c.CategoryName FROM Products p FULL OUTER JOIN Categories c ON p.CategoryID = c.CategoryID;
SELECT p.ProductName, c.CategoryName FROM Products p LEFT JOIN Categories c ON p.CategoryID = c.CategoryID;

Correct answer: SELECT p.ProductName, c.CategoryName FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID;

An `INNER JOIN` (or simply `JOIN`) is the correct choice because it selects records that have matching values in both tables. It will only return products that have a valid, matching `CategoryID` in the `Categories` table, filtering out any products with a `CategoryID` that does not exist in the `Categories` table.

Question 45: What does LAST_VALUE typically require to return the true final value of a partition?

A GROUP BY
DISTINCT
An explicit frame like ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (Correct answer)
Nothing extra

Correct answer: An explicit frame like ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Because the default frame ends at the current row, LAST_VALUE needs a frame extending to UNBOUNDED FOLLOWING.

Question 46: Can a subquery appear in the HAVING clause?

Only with EXISTS
Yes, to compare against aggregated results (Correct answer)
Only in MySQL
No, never

Correct answer: Yes, to compare against aggregated results

Subqueries are valid in HAVING to filter groups based on computed or external values.

Question 47: What is the result of WHERE quantity NOT IN (1, 2, 3)?

Rows where quantity is NULL
Rows where quantity is none of 1, 2, or 3 (Correct answer)
Rows where quantity equals 1, 2, or 3
Rows where quantity is between 1 and 3

Correct answer: Rows where quantity is none of 1, 2, or 3

NOT IN excludes rows matching any value in the list.

Question 48: Which aggregate would you use to find the total revenue across all orders?

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

Correct answer: SUM(amount)

SUM adds all values together to give the total revenue.

Question 49: What does the MIN function return for a group of dates?

The latest date
The earliest date (Correct answer)
The count of dates
NULL always

Correct answer: The earliest date

MIN returns the smallest value, which for dates is the earliest one.

Question 50: A query is written to find employees whose salary is greater than ALL salaries in the 'Intern' department. The subquery `(SELECT Salary FROM Employees WHERE Department = 'Intern')` returns the values (30000, 32000, 35000). Which of the following `WHERE` clauses will correctly identify an employee with a salary of 40000?

WHERE Salary = ALL (SELECT Salary FROM Employees WHERE Department = 'Intern')
WHERE Salary IN (SELECT Salary FROM Employees WHERE Department = 'Intern')
WHERE Salary > ANY (SELECT Salary FROM Employees WHERE Department = 'Intern')
WHERE Salary > ALL (SELECT Salary FROM Employees WHERE Department = 'Intern') (Correct answer)

Correct answer: WHERE Salary > ALL (SELECT Salary FROM Employees WHERE Department = 'Intern')

The `ALL` operator is used with a comparison operator to compare a value to every value in a list returned by a subquery. The condition `> ALL` evaluates to TRUE only if the value is greater than every single value in the subquery's result set. A salary of 40000 is greater than 30000, 32000, and 35000, so it satisfies the condition.

Question 51: To find customers who have NEVER placed an order, you LEFT JOIN Orders and then filter how?

WHERE Orders.id IS NULL (Correct answer)
HAVING COUNT(*) = 0
WHERE Orders.id = 0
WHERE Orders.id != NULL

Correct answer: WHERE Orders.id IS NULL

Unmatched rows have NULL in the order columns, so IS NULL isolates them.

Question 52: Which join type is most likely to produce an unexpectedly huge result if the ON condition is omitted?

INNER JOIN with ON
LEFT JOIN with ON
CROSS JOIN (or a JOIN written without ON) (Correct answer)
Self join with ON

Correct answer: CROSS JOIN (or a JOIN written without ON)

Without a join condition, every row pairs with every row, exploding the row count.

Question 53: A NULL in a NOT IN subquery list can cause what problem?

It forces a syntax error
It is automatically ignored
It speeds up the query
The whole predicate may return no rows unexpectedly (Correct answer)

Correct answer: The whole predicate may return no rows unexpectedly

NOT IN with a NULL in the list can yield UNKNOWN, filtering out rows you expected to keep.

Question 54: What does ACID stand for in database transactions?

Atomic, Concurrent, Isolated, Distributed
Access, Concurrency, Indexing, Design
Accuracy, Control, Integrity, Data
Atomicity, Consistency, Isolation, Durability (Correct answer)

Correct answer: Atomicity, Consistency, Isolation, Durability

ACID describes the four properties guaranteeing reliable transactions.

Question 55: Which of the following best describes the scope and purpose of a Common Table Expression (CTE)?

A named temporary result set that exists only for the duration of a single SQL statement (SELECT, INSERT, UPDATE, or DELETE). (Correct answer)
A special type of subquery that can only be used in the WHERE clause to filter results.
A permanent database object, similar to a view, that is used to simplify complex queries.
A temporary, named result set stored in memory or on disk that persists for the entire user session.

Correct answer: A named temporary result set that exists only for the duration of a single SQL statement (SELECT, INSERT, UPDATE, or DELETE).

A Common Table Expression (CTE) is defined using the WITH clause and creates a temporary, named result set. Its scope is limited to the single statement that immediately follows it, after which it is discarded. It is not a permanent object like a view, nor does it persist for an entire session like a temporary table.

Question 56: Which often performs better than a correlated subquery for the same logic?

A JOIN (Correct answer)
A second database
A nested loop in application code
A view with no indexes

Correct answer: A JOIN

Rewriting a correlated subquery as a JOIN frequently lets the optimizer run it more efficiently.

Question 57: When two joined tables both have a column called 'status', how do you select it unambiguously?

Use status twice
SQL picks the left one automatically
Prefix it with the table name or alias (Correct answer)
It cannot be selected

Correct answer: Prefix it with the table name or alias

Ambiguous column names must be qualified by their table or alias.

Question 58: What does SOME mean as a subquery quantifier?

It means ALL
It means NONE
It means EXACTLY ONE
It is a synonym for ANY (Correct answer)

Correct answer: It is a synonym for ANY

In SQL, SOME and ANY are interchangeable quantifiers with identical meaning.

Question 59: What is returned by AVG over the values 2, 4, NULL, 6?

4.5
NULL
3
4 (Correct answer)

Correct answer: 4

AVG ignores NULL, so (2+4+6)/3 = 4.

Question 60: Which statement about combining aggregate and non-aggregate columns is true in standard SQL?

Any column may be mixed freely
Only one aggregate is allowed
Non-aggregated columns must appear in GROUP BY (Correct answer)
Aggregates cannot be used with GROUP BY

Correct answer: Non-aggregated columns must appear in GROUP BY

Every non-aggregated SELECT column must be in the GROUP BY clause.

Question 61: Which comparison is INVALID for matching NULLs in a join condition?

COALESCE(column, 0) = 0
column IS NOT NULL
column IS NULL
column = NULL (Correct answer)

Correct answer: column = NULL

NULL is never equal to anything, so '= NULL' never matches; use IS NULL.

Question 62: How do ROWS and RANGE frame modes differ when there are duplicate ORDER BY values?

They are identical
RANGE counts physical rows
ROWS ignores ties entirely
ROWS counts physical rows; RANGE groups peers with equal values (Correct answer)

Correct answer: ROWS counts physical rows; RANGE groups peers with equal values

ROWS treats each row individually while RANGE includes all peer rows sharing the ordering value.

Question 63: Which standard SQL clause limits rows using FETCH?

FETCH n ROWS LIMIT
FETCH TOP n ROWS
FETCH FIRST n ROWS ONLY (Correct answer)
FETCH LIMIT n

Correct answer: FETCH FIRST n ROWS ONLY

FETCH FIRST n ROWS ONLY is the ANSI SQL standard for limiting rows.

Question 64: What does WHERE price * quantity > 1000 demonstrate?

It always returns all rows
Multiplication is forbidden in filters
WHERE cannot use arithmetic
Expressions can be used in WHERE conditions (Correct answer)

Correct answer: Expressions can be used in WHERE conditions

WHERE can evaluate arithmetic expressions and compare the result.

Question 65: Which clause can use a column alias defined in SELECT in many databases?

HAVING (Correct answer)
GROUP BY only
WHERE
ON

Correct answer: HAVING

Some databases allow SELECT aliases in HAVING since it runs after SELECT logically in those engines.

Question 66: Why are table aliases especially useful in multi-table joins?

They speed up the query engine
They shorten references and disambiguate same-named columns (Correct answer)
They create indexes automatically
They are required by SQL syntax

Correct answer: They shorten references and disambiguate same-named columns

Aliases make queries readable and resolve ambiguity when columns share names.

Question 67: Which DML statement adds new rows to a table?

ALTER
INSERT (Correct answer)
UPDATE
CREATE

Correct answer: INSERT

INSERT adds new rows of data into a table.

Question 68: What does COUNT return when applied to an empty table?

0 (Correct answer)
An error
1
NULL

Correct answer: 0

COUNT returns 0 on an empty set, unlike SUM or AVG which return NULL.

Question 69: What does the CREATE INDEX statement accomplish?

Inserts rows faster by caching them
Encrypts a column
Creates a structure to speed up data retrieval on specified columns (Correct answer)
Deletes duplicate rows

Correct answer: Creates a structure to speed up data retrieval on specified columns

CREATE INDEX builds a data structure that improves the speed of queries filtering or sorting on the indexed columns.

Question 70: Which keyword tests whether a subquery returns any rows at all?

INCLUDES
CONTAINS
EXISTS (Correct answer)
HASROWS

Correct answer: EXISTS

EXISTS returns TRUE if the subquery produces one or more rows.

Question 71: A developer is writing a query to find all customers who have placed at least one order. There are two tables: `Customers` (CustomerID, Name) and `Orders` (OrderID, CustomerID). For large tables, which query is generally the most efficient for this existence check?

SELECT Name FROM Customers WHERE CustomerID = ANY (SELECT CustomerID FROM Orders);
SELECT Name FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID); (Correct answer)
SELECT C.Name FROM Customers C LEFT JOIN Orders O ON C.CustomerID = O.CustomerID WHERE O.OrderID IS NOT NULL;
SELECT Name FROM Customers WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);

Correct answer: SELECT Name FROM Customers WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.CustomerID = Customers.CustomerID);

The `EXISTS` operator is typically more efficient for checking the existence of related rows, especially with large datasets. It stops scanning the subquery as soon as it finds the first matching row, as it only needs to determine if the subquery returns any rows (TRUE/FALSE). In contrast, `IN` with a subquery often requires the database to materialize the entire result set of the subquery first before processing the outer query.

Question 72: What does LEAD(value, 1, 0) return when no following row exists?

An error
0 (Correct answer)
The current value
NULL

Correct answer: 0

The third argument supplies a default (0 here) when the offset row does not exist.

Question 73: The primary - foreign key relations are utilized to

to index the database.
clean-up the database.
cross-reference database tables (Correct answer)
None of the above

Correct answer: cross-reference database tables

Primary and foreign keys are essential components for establishing relationships between tables in a relational database. A foreign key in one table references the primary key in another table, creating a logical link that allows data to be cross-referenced and ensures referential integrity across the database schema. This mechanism is crucial for maintaining consistent and related data.

Question 74: Can you nest aggregate functions like SUM(MAX(x)) directly in a single GROUP BY query?

Only in subqueries with WHERE
Yes, always
No, it is generally not allowed (Correct answer)
Only with COUNT

Correct answer: No, it is generally not allowed

Nesting aggregates directly is not permitted; you must use a subquery instead.

Question 75: What does a TRUNCATE TABLE do?

deletes all rows from a table (Correct answer)
All of the above
checks if the table has primary key specified
deletes the table

Correct answer: deletes all rows from a table

The `TRUNCATE TABLE` statement is a Data Definition Language (DDL) command that removes all rows from a table, effectively emptying it. Unlike `DELETE`, it does not log individual row deletions, making it significantly faster and more efficient for large tables, but it also means the operation cannot typically be rolled back. It does not delete the table itself, only its contents.

Question 76: Why can chaining window functions sometimes require a CTE?

Window functions need indexes
CTEs are faster
It is purely stylistic
Window functions cannot be nested directly in one another (Correct answer)

Correct answer: Window functions cannot be nested directly in one another

You cannot nest a window function inside another, so a CTE materializes the first result for the second.

Question 77: Which of the following is a standard interactive and programming language for retrieving and changing data from a database?

WebLogic
Erlang programming language
Structured Query Language (Correct answer)
dynamic data exchange

Correct answer: Structured Query Language

Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. It is used for querying data, inserting, updating, and deleting records, as well as defining database schemas. SQL serves as both an interactive language for direct database interaction and a programming language for embedding within applications.

Question 78: A view defined as SELECT col FROM t WHERE x > 5 is generally updatable only if it references how many base tables?

Two
Unlimited
Three
One (Correct answer)

Correct answer: One

Simple updatable views typically map to a single underlying base table.

Oracle Database SQL Certified Associate Exam

The Oracle Database SQL (1Z0-071) exam validates proficiency in SQL concepts including data retrieval, manipulation, and definition using Oracle Database. Passing earns the Oracle Database SQL Certified Associate credential.

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