Oracle Database SQL Certified Associate Exam — Questions and Answers

Question 1: What PostgreSQL keyword can force a CTE to be computed once and stored?

CACHED
STORED
PERSISTED
MATERIALIZED (Correct answer)

Correct answer: MATERIALIZED

PostgreSQL supports WITH cte AS MATERIALIZED (...) to force single evaluation.

Question 2: Which is the correct order of clauses in a join query?

JOIN ... FROM ... WHERE
FROM ... WHERE ... JOIN ... ON
WHERE ... FROM ... JOIN
FROM ... JOIN ... ON ... WHERE ... GROUP BY (Correct answer)

Correct answer: FROM ... JOIN ... ON ... WHERE ... GROUP BY

JOIN and ON come within FROM, before WHERE and GROUP BY.

Question 3: What is returned by FIRST_VALUE(price) OVER (PARTITION BY category ORDER BY price)?

The highest price overall
A random price
The lowest price in each category (Correct answer)
The average price

Correct answer: The lowest price in each category

FIRST_VALUE returns the first row's value per the ordering, here the lowest price per category.

Question 4: 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 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, 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 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 5: Can a SELECT statement be rolled back by ROLLBACK?

No, SELECT does not change data (Correct answer)
Yes, it undoes the query results
Only with a savepoint
Yes, it restores the cursor

Correct answer: No, SELECT does not change data

SELECT reads data and makes no changes, so there is nothing for ROLLBACK to undo.

Question 6: What does ORDER BY 1 DESC do?

Reverses all columns
Sorts a column named '1'
Returns 1 row descending
Sorts by the first SELECT column descending (Correct answer)

Correct answer: Sorts by the first SELECT column descending

The number 1 references the first column in the SELECT list, sorted descending.

Question 7: What does WHERE first_name = last_name compare?

Two columns in the same row against each other (Correct answer)
Nothing; it is invalid
All rows to a constant
A column against a string literal

Correct answer: Two columns in the same row against each other

WHERE can compare two columns, returning rows where their values are equal.

Question 8: This is a collection of data object descriptions provided for the convenience of programmers and others who may need to refer to them.

stored procedure
Virtual Address eXtension
Virtual File Allocation table
data dictionary (Correct answer)

Correct answer: data dictionary

A data dictionary is a centralized repository of information about data, often referred to as metadata (data about data). It provides descriptions of data objects, such as table names, column types, relationships, and constraints, for the convenience of programmers and users. It helps in understanding the structure and content of a database.

Question 9: Which SQL statement correctly creates a new index on a table?

MAKE INDEX idx_name ON employees (last_name)
CREATE INDEX idx_name ON employees (last_name) (Correct answer)
ADD INDEX idx_name ON employees (last_name)
BUILD INDEX idx_name FOR employees (last_name)

Correct answer: CREATE INDEX idx_name ON employees (last_name)

The correct syntax is CREATE INDEX followed by the index name, the ON keyword, the table name, and the column(s) to index.

Question 10: A university needs to find the relative rank of each student's GPA, defined as the percentage of students with a GPA less than or equal to the current student's GPA. Which window function calculates this cumulative distribution?

PERCENT_RANK()
DENSE_RANK()
NTILE(100)
CUME_DIST() (Correct answer)

Correct answer: CUME_DIST()

`CUME_DIST()` calculates the cumulative distribution of a value within a group of values. Specifically, it computes the fraction of partition rows that are less than or equal to the current row's value, which matches the requirement perfectly. [5, 15] `PERCENT_RANK()` calculates a different metric: `(rank - 1) / (total_rows - 1)`. [9]

Question 11: 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 Name FROM Customers WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);
SELECT C.Name FROM Customers C LEFT JOIN Orders O ON C.CustomerID = O.CustomerID WHERE O.OrderID IS NOT NULL;

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 12: What is the result of an UPDATE whose WHERE clause matches no rows?

The table is dropped
All rows are changed
An error is raised
Zero rows are changed and no error occurs (Correct answer)

Correct answer: Zero rows are changed and no error occurs

If no rows match, UPDATE simply affects zero rows without error.

Question 13: What happens to NULL values by default in an ORDER BY within OVER (in standard SQL)?

Their position depends on NULLS FIRST/LAST or the engine default (Correct answer)
They are always removed
They always sort first
They cause an error

Correct answer: Their position depends on NULLS FIRST/LAST or the engine default

NULL ordering follows NULLS FIRST/LAST specification or the database's default behavior.

Question 14: A query needs to produce a list of every possible combination of `ShirtSize` from a `Sizes` table and `ShirtColor` from a `Colors` table to generate all potential inventory items. If the `Sizes` table has 5 rows and the `Colors` table has 10 rows, how many rows will the result set of a `CROSS JOIN` between these two tables contain?

It depends on the matching keys.
10
50 (Correct answer)
15

Correct answer: 50

A `CROSS JOIN` produces a Cartesian product of the two tables, meaning it combines each row from the first table with every row from the second table. The total number of rows is the number of rows in the first table multiplied by the number of rows in the second table (5 * 10 = 50). No `ON` clause is used with a `CROSS JOIN`.

Question 15: What does GROUP BY GROUPING SETS((a),(b)) produce?

Separate aggregations grouped by a and by b (Correct answer)
An error
A cross join
Grouping by a and b together

Correct answer: Separate aggregations grouped by a and by b

GROUPING SETS computes multiple independent groupings in one query.

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

Creates a copy before dropping
Prevents the drop entirely
Drops dependent objects like foreign keys and views as well (Correct answer)
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 17: What is the purpose of a temporary table created with CREATE TEMPORARY TABLE?

It replaces a view
It cannot store data
It exists only for the session or transaction and is dropped automatically (Correct answer)
It persists permanently across sessions

Correct answer: It exists only for the session or transaction and is dropped automatically

A temporary table lives only for the duration of the session or transaction and is then automatically removed.

Question 18: A data analyst is asked to provide a report showing only the product categories whose average list price is greater than $150. Which combination of clauses is required to produce this result?

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

Correct answer: SELECT, FROM, GROUP BY, HAVING

To filter the results of an aggregate function like AVG(), the HAVING clause is required. The GROUP BY clause is needed to group the products by category so the average price can be calculated for each one. The WHERE clause filters rows before aggregation, so it cannot be used to filter on the result of AVG().

Question 19: Which DML statement would you use to change existing data without adding rows?

UPDATE (Correct answer)
DROP
INSERT
TRUNCATE

Correct answer: UPDATE

UPDATE modifies values in existing rows.

Question 20: Which window function assigns the same rank to ties but leaves gaps in the sequence afterward?

RANK() (Correct answer)
ROW_NUMBER()
NTILE()
DENSE_RANK()

Correct answer: RANK()

RANK() gives tied rows the same rank and skips the next ranks, leaving gaps.

Question 21: A database administrator needs to remove all rows from a large table named `LogData` but wants to keep the table structure for future use. Which DDL command is the most efficient for this task?

DROP TABLE LogData;
DELETE FROM LogData;
ALTER TABLE LogData EMPTY;
TRUNCATE TABLE LogData; (Correct answer)

Correct answer: TRUNCATE TABLE LogData;

The TRUNCATE TABLE command is designed to quickly delete all rows from a table. It is more efficient than DELETE because it deallocates the data pages without logging each individual row deletion. DROP TABLE would remove the entire table structure, which is not the desired outcome.

Question 22: Which returns rows less than AT LEAST ONE value from the subquery?

< ALL
< ONLY
< ANY (Correct answer)
< EVERY

Correct answer: < ANY

< ANY is true if the value is less than at least one returned value.

Question 23: A RIGHT JOIN of Employees and Departments keeps all rows from which table?

Departments (Correct answer)
Neither
Employees
Both equally

Correct answer: Departments

RIGHT JOIN preserves all rows from the right (second) table, Departments.

Question 24: Which clause combination is required for reliable pagination?

Just LIMIT/OFFSET
GROUP BY with LIMIT
ORDER BY with LIMIT/OFFSET (Correct answer)
Just ORDER BY

Correct answer: ORDER BY with LIMIT/OFFSET

Without ORDER BY, pagination results can vary between queries.

Question 25: A DELETE issued through an updatable single-table view will affect what?

Only the view's cache
The view definition
Rows in the underlying base table (Correct answer)
Nothing at all

Correct answer: Rows in the underlying base table

DML on an updatable view propagates to the underlying base table rows.

Question 26: An aggregate view using GROUP BY and SUM is typically what kind of view?

Non-updatable (Correct answer)
Always updatable
A system catalog view
A materialized view by default

Correct answer: Non-updatable

Views containing aggregation cannot be directly updated because rows do not map one-to-one.

Question 27: You need to find the minimum, maximum, and average salary for each job title in the 'Employees' table. Which query accomplishes this?

SELECT JobTitle, MIN(Salary), MAX(Salary), AVG(Salary) FROM Employees GROUP BY JobTitle; (Correct answer)
SELECT JobTitle, AGG(Salary) FROM Employees GROUP BY JobTitle;
SELECT JobTitle, MIN(Salary), MAX(Salary), AVG(Salary) FROM Employees;
SELECT JobTitle, Salary FROM Employees WHERE Salary = MIN() OR Salary = MAX() OR Salary = AVG();

Correct answer: SELECT JobTitle, MIN(Salary), MAX(Salary), AVG(Salary) FROM Employees GROUP BY JobTitle;

This query correctly groups the rows by `JobTitle` and then applies the `MIN()`, `MAX()`, and `AVG()` aggregate functions to the `Salary` column for each of those groups, providing the required statistics per job title.

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

NULL
An error
1
0 (Correct answer)

Correct answer: 0

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

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

GROUP BY only
ON
HAVING (Correct answer)
WHERE

Correct answer: HAVING

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

Question 30: A query stacks three CTEs where each builds on the previous. This pattern is best described as:

A correlated subquery
A chained or pipelined transformation of data (Correct answer)
A recursive join
A cross product

Correct answer: A chained or pipelined transformation of data

Sequential CTEs that each transform the prior result form a readable, pipelined data flow.

Question 31: What is the result type expected by an IN subquery?

A single column of values (Correct answer)
Multiple columns
A scalar only
A boolean

Correct answer: A single column of values

An IN subquery should return one column whose values are compared for membership.

Question 32: Which combination is valid: SELECT dept, MAX(salary) FROM emp GROUP BY dept ORDER BY MAX(salary) DESC?

Valid: orders departments by their max salary (Correct answer)
Invalid: cannot ORDER BY an aggregate
Valid only with HAVING
Invalid: ORDER BY needs GROUP BY column

Correct answer: Valid: orders departments by their max salary

ORDER BY can reference aggregate expressions to sort grouped output.

Question 33: Which set of rows does the difference between a LEFT JOIN and an INNER JOIN consist of?

Matched rows only
Right-table rows with no match
Cartesian pairs
Left-table rows with no match in the right table (Correct answer)

Correct answer: Left-table rows with no match in the right table

A LEFT JOIN adds the unmatched left rows that an INNER JOIN would drop.

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

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

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

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

Question 35: 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, AVG(o.OrderTotal) FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID;
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;

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 36: Which of the SQL commands below deletes all rows in the SalesData table?

DELETE rows FROM SalesData
DELETE FROM SalesData (Correct answer)
DELETE SalesData
DELETE ALL SalesData

Correct answer: DELETE FROM SalesData

The standard SQL command to delete all rows from a table is `DELETE FROM TableName`. This statement removes all records from the specified table. While it can also be used with a `WHERE` clause to delete specific rows, omitting the `WHERE` clause results in the deletion of all entries.

Question 37: What is the default window frame when ORDER BY is specified but no frame clause is given?

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
The whole partition
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (Correct answer)

Correct answer: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

The SQL default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

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

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

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 39: An INNER JOIN between two tables with a one-to-many relationship can return what?

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

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

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

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

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

Correct answer: Tables made of rows and columns

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

Question 41: Which clause lets you insert results from a query into a table?

UPDATE FROM SELECT
SELECT INTO WHERE
INSERT INTO ... SELECT (Correct answer)
INSERT VALUES ONLY

Correct answer: INSERT INTO ... SELECT

INSERT INTO ... SELECT inserts rows produced by a SELECT query.

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

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

Correct answer: ALTER TABLE ... ADD COLUMN

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

Question 43: Which DDL command removes an entire table including its structure and all data?

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

Correct answer: DROP TABLE

DROP TABLE deletes the table definition along with all its data and indexes.

Question 44: What is the effect of CREATE VIEW?

It physically copies table data
It builds an index
It creates a backup
It defines a stored query presented as a virtual table (Correct answer)

Correct answer: It defines a stored query presented as a virtual table

CREATE VIEW stores a query definition that behaves like a virtual table when referenced.

Question 45: Given WHERE a = 1 OR b = 2 AND c = 3, which is evaluated first?

All conditions equally
c = 3 OR a = 1
b = 2 AND c = 3 (Correct answer)
a = 1 OR b = 2

Correct answer: b = 2 AND c = 3

AND has higher precedence than OR, so the AND pair is evaluated first.

Question 46: What does = ALL require for the comparison to be TRUE with multiple distinct values?

All values must be NULL
It cannot be TRUE since one value cannot equal several different ones (Correct answer)
It is always TRUE
The subquery must be scalar

Correct answer: It cannot be TRUE since one value cannot equal several different ones

= ALL is TRUE only if the value equals every returned value, impossible when they differ.

Question 47: Where can a subquery used as a derived table appear?

Only in GROUP BY
Only in WHERE
In the FROM clause (Correct answer)
Only in ORDER BY

Correct answer: In the FROM clause

A derived table is a subquery placed in the FROM clause and must be given an alias.

Question 48: A developer needs to synchronize a 'Products' table with a 'Staging_Products' table. The operation should insert new products, update existing products with new prices, and delete products that no longer exist in the staging table, all within a single atomic statement. Which DML command is best suited for this scenario?

A series of INSERT, UPDATE, and DELETE statements
INSERT
MERGE (Correct answer)
UPDATE

Correct answer: MERGE

The MERGE statement is designed specifically for this 'upsert' or synchronization scenario. It can combine INSERT, UPDATE, and DELETE operations into a single, conditional statement, making the process more efficient and atomic.

Question 49: 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 50: Which statement removes all rows but is technically DDL, not DML?

DELETE
INSERT
TRUNCATE (Correct answer)
MERGE

Correct answer: TRUNCATE

TRUNCATE removes all rows but is classified as DDL because it is not row-by-row logged like DML.

Question 51: How can you reuse the same window specification across multiple functions?

It is impossible
Copy it each time
Define a named WINDOW clause (Correct answer)
Use GROUP BY

Correct answer: Define a named WINDOW clause

A named WINDOW clause lets multiple functions share one OVER specification.

Question 52: Which statement best describes the relationship between DDL and the data dictionary?

DDL has no effect on metadata
DDL modifies the metadata describing database objects (Correct answer)
DDL only reads metadata
DDL only changes row data

Correct answer: DDL modifies the metadata describing database objects

DDL commands update the data dictionary (system catalog) that stores definitions of database objects.

Question 53: Which wildcard in LIKE matches any sequence of characters?

_
% (Correct answer)
*
?

Correct answer: %

The % wildcard matches zero or more characters in a LIKE pattern.

Question 54: What does the standard SQL command START TRANSACTION do?

Commits the current transaction
Ends a session
Sets a savepoint
Begins a new transaction block (Correct answer)

Correct answer: Begins a new transaction block

START TRANSACTION begins an explicit transaction in ANSI SQL and MySQL.

Question 55: Table columns are also referred to as

Attributes
Fields (Correct answer)
None of the above
Records

Correct answer: Fields

In the context of database tables, columns are frequently referred to as fields. Each field represents a specific attribute or piece of information that is stored for every record (row) in the table. For example, in a 'Persons' table, 'FirstName' and 'Age' would be considered fields or columns.

Question 56: What does AVG(salary) ignore when computing the average?

Negative values
NULL values (Correct answer)
Duplicate values
Zero values

Correct answer: NULL values

AVG ignores NULL rows entirely, dividing the sum by the count of non-NULL values.

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

FILTER
WHERE
ON
HAVING (Correct answer)

Correct answer: HAVING

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

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

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

Correct answer: WHERE Orders.id IS NULL

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

Question 59: Which statement renames an existing database object in standard SQL?

RENAME TABLE / ALTER TABLE ... RENAME (Correct answer)
UPDATE TABLE NAME
CHANGE TABLE NAME
SET TABLE NAME

Correct answer: RENAME TABLE / ALTER TABLE ... RENAME

Renaming is done via ALTER TABLE ... RENAME (or RENAME TABLE in some dialects), both DDL operations.

Question 60: To count orders per customer including customers with no orders, you combine a LEFT JOIN with which aggregate trick?

COUNT(*) always returns zero
COUNT(Orders.id) instead of COUNT(*) (Correct answer)
SUM(NULL)
COUNT(Customers.id)

Correct answer: COUNT(Orders.id) instead of COUNT(*)

COUNT of the right-table key ignores NULLs, yielding 0 for customers with no orders.

Question 61: In some databases, a CTE result referenced multiple times in a query may be:

Converted to an index
Re-evaluated each time unless materialized (Correct answer)
Always cached automatically
Stored on disk permanently

Correct answer: Re-evaluated each time unless materialized

Many engines re-evaluate a CTE per reference unless it is explicitly or implicitly materialized.

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

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

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 63: What does the MIN function return for a group of dates?

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

Correct answer: The earliest date

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

Question 64: What is required when a subquery is compared with = (equals)?

It must use EXISTS
It must be correlated
It must return a single value (Correct answer)
It must return multiple rows

Correct answer: It must return a single value

Using = with a subquery requires the subquery to return exactly one value, or an error occurs.

Question 65: In 'SELECT * FROM A JOIN B USING (id)', what does USING require?

Identical row counts
A WHERE clause
A primary key on A only
A column named id in both tables (Correct answer)

Correct answer: A column named id in both tables

USING joins on a column that exists with the same name in both tables.

Question 66: This form of database, which may be used to mine data for business patterns, does not require SQL queries and instead allows users to ask questions like <br> "How many Aptivas have been sold in Nebraska this year?"

functional specification
line information database
High Performance Storage System
multidimensional database (Correct answer)

Correct answer: multidimensional database

A multidimensional database (MDDB), often used in Online Analytical Processing (OLAP) and data warehousing, is designed for fast analysis of data from multiple perspectives. It stores data in a cube-like structure, enabling intuitive, non-SQL queries that resemble natural language questions for business intelligence, making it ideal for pattern mining without complex SQL.

Question 67: What is the result type of AVG over an integer column in most databases?

Always integer
A decimal or floating-point value (Correct answer)
A string
Boolean

Correct answer: A decimal or floating-point value

AVG typically returns a decimal/float to preserve fractional results.

Question 68: How many times does a correlated subquery conceptually execute?

Once per column
Never
Once per outer-query row (Correct answer)
Exactly once total

Correct answer: Once per outer-query row

A correlated subquery is re-evaluated for each row processed by the outer query.

Question 69: Which of the following statements regarding embedded SQL is correct?

Hard—coded SQL statements in a procedure
Hard—coded SQL statements in a program language such as Java. (Correct answer)
The process of making an application capable of generating specific SQL code on the fly-
Hard—coded SQL statements in a trigger.

Correct answer: Hard—coded SQL statements in a program language such as Java.

Embedded SQL refers to SQL statements that are directly integrated or 'hard-coded' within a host programming language, such as Java, C, or Python. These SQL commands are processed by a precompiler or interpreter along with the host language code, allowing applications to interact with databases by executing predefined queries. It contrasts with dynamic SQL, where queries are constructed at runtime.

Question 70: A data analyst wants to sort a `Customers` table first by `Country` in ascending order, and then by `TotalSales` in descending order for customers within the same country. Which `ORDER BY` clause is correct?

ORDER BY Country, TotalSales BOTH DESC;
ORDER BY Country DESC, TotalSales ASC;
ORDER BY Country, TotalSales;
ORDER BY Country ASC, TotalSales DESC; (Correct answer)

Correct answer: ORDER BY Country ASC, TotalSales DESC;

To sort by multiple columns, you list them in the `ORDER BY` clause in the desired order of precedence. The query first sorts by `Country` in ascending order (ASC is the default). Then, for rows with the same country, it sorts by `TotalSales` in descending order, as specified by the `DESC` keyword.

Question 71: What does the GROUP BY clause do?

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

Correct answer: Groups rows sharing a value for aggregation

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

Question 72: To grant another user SELECT access on a view, you use which statement?

GRANT SELECT ON view_name TO user (Correct answer)
PERMIT SELECT view_name
OPEN view_name TO user
ALLOW view_name FOR user

Correct answer: GRANT SELECT ON view_name TO user

GRANT SELECT ON the view name gives read privileges to a user.

Question 73: Which is a disadvantage of materialized views compared to regular views?

They consume no storage
Data can become stale until refreshed (Correct answer)
They cannot store joins
They cannot be indexed

Correct answer: Data can become stale until refreshed

Because results are cached, materialized views may show outdated data between refreshes.

Question 74: Which clause limits the number of rows affected by an UPDATE in databases that support it?

TOP only in WHERE
RANGE
LIMIT (Correct answer)
CAP

Correct answer: LIMIT

Some databases like MySQL allow LIMIT to cap rows changed by UPDATE.

Question 75: Which is the most readable equivalent of WHERE x = 1 OR x = 2 OR x = 3?

WHERE x IN (1, 2, 3) (Correct answer)
WHERE x = (1,2,3)
WHERE x BETWEEN 1 OR 3
WHERE x LIKE (1,2,3)

Correct answer: WHERE x IN (1, 2, 3)

IN provides a concise list-based alternative to multiple OR equalities.

Question 76: What does WHERE name LIKE 'Sm_th' match?

Smith and Smyth (Correct answer)
Only Smith
Smooth
Names ending in th only

Correct answer: Smith and Smyth

The underscore matches exactly one character, so Smith and Smyth both qualify.

Question 77: A LEFT JOIN between Customers and Orders returns customers with no orders. What appears in the Orders columns for those rows?

NULL (Correct answer)
0
Empty string
The row is excluded

Correct answer: NULL

Unmatched rows from the right table produce NULL values in the result.

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

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

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.

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