Oracle Database SQL Certified Associate Exam — Questions and Answers
Question 1: Which clause must accompany RANK() for deterministic results?
- GROUP BY
- HAVING
- DISTINCT
- ORDER BY inside OVER (Correct answer)
Correct answer: ORDER BY inside OVER
Ranking functions require an ORDER BY in the OVER clause to define the sequence.
Question 2: What does the ESCAPE clause do in a LIKE expression?
- Speeds up the LIKE search
- Converts text to uppercase
- Lets you treat wildcard characters as literals (Correct answer)
- Reverses the match logic
Correct answer: Lets you treat wildcard characters as literals
ESCAPE defines a character so wildcards like % or _ can be matched literally.
Question 3: How do you select rows where the email column has no value?
- WHERE email = ''
- WHERE email = NULL
- WHERE email != NULL
- WHERE email IS NULL (Correct answer)
Correct answer: WHERE email IS NULL
NULL comparisons require IS NULL because = NULL never evaluates true.
Question 4: Which clause limits the number of rows affected by an UPDATE in databases that support it?
- CAP
- LIMIT (Correct answer)
- TOP only in WHERE
- RANGE
Correct answer: LIMIT
Some databases like MySQL allow LIMIT to cap rows changed by UPDATE.
Question 5: When should you prefer a CTE over a view?
- When you need to store data physically
- For a one-off query where you do not need a reusable database object (Correct answer)
- When you need to enforce permissions
- When many queries must reuse the logic permanently
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 6: Which of the following SQL queries correctly calculates the number of employees in each department?
- SELECT department, SUM(employee_id) FROM employees GROUP BY department;
- SELECT COUNT(department) FROM employees;
- SELECT department, COUNT(employee_id) FROM employees;
- SELECT department, COUNT(employee_id) FROM employees GROUP BY department; (Correct answer)
Correct answer: SELECT department, COUNT(employee_id) FROM employees GROUP BY department;
This query correctly uses the COUNT() aggregate function to count employees and the GROUP BY clause to create a separate count for each distinct 'department'. The other options are incorrect because they either lack a GROUP BY clause, use the wrong aggregate function (SUM), or count the wrong thing.
Question 7: What is a key advantage of a CTE over a repeated subquery?
- It always runs faster
- It cannot be recursive
- It avoids indexes
- It can be referenced multiple times by name and improves readability (Correct answer)
Correct answer: It can be referenced multiple times by name and improves readability
A CTE defines a named result set reusable within the query and clarifies complex logic.
Question 8: Placing a filter on the right table in the WHERE clause of a LEFT JOIN can have what effect?
- It causes a syntax error
- It speeds the join up
- It can turn the LEFT JOIN into an effective INNER JOIN (Correct answer)
- It has no effect
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 9: What does ON DELETE CASCADE do?
- Automatically deletes related child rows (Correct answer)
- Locks the parent table
- Copies deleted rows
- Prevents any deletes
Correct answer: Automatically deletes related child rows
ON DELETE CASCADE removes child rows automatically when the parent is deleted.
Question 10: In ACID, what does Durability guarantee?
- Transactions run in order
- Transactions never conflict
- Data is always valid
- Committed changes survive system failures (Correct answer)
Correct answer: Committed changes survive system failures
Durability ensures committed data persists even after crashes or power loss.
Question 11: In a query with GROUP BY department, which column can appear in SELECT without being aggregated?
- hire_date
- department (Correct answer)
- salary
- employee_id
Correct answer: department
Only the grouping column(s) may appear ungrouped and unaggregated in the SELECT list.
Question 12: What does AVG(salary) ignore when computing the average?
- Negative values
- NULL values (Correct answer)
- Zero values
- Duplicate values
Correct answer: NULL values
AVG ignores NULL rows entirely, dividing the sum by the count of non-NULL values.
Question 13: What does GROUP BY GROUPING SETS((a),(b)) produce?
- An error
- Separate aggregations grouped by a and by b (Correct answer)
- Grouping by a and b together
- A cross join
Correct answer: Separate aggregations grouped by a and by b
GROUPING SETS computes multiple independent groupings in one query.
Question 14: What value does SUM return for a group where every row's column is NULL?
- NULL (Correct answer)
- 0
- The row count
- An error
Correct answer: NULL
SUM over all-NULL values returns NULL, not 0.
Question 15: A data analyst is writing a complex report. They first need to calculate total sales per region, and then, using that result, calculate the average sale amount for regions exceeding $1,000,000 in total sales. Why would a CTE be a good choice for this scenario?
- Because CTEs store their results permanently, allowing the analyst to query them later without rerunning the calculation.
- Because CTEs are the only way to perform aggregate functions like SUM() and AVG().
- Because CTEs improve readability and modularity by breaking the complex problem into logical, named steps. (Correct answer)
- Because CTEs are indexed automatically, making the multi-step calculation faster than any other method.
Correct answer: Because CTEs improve readability and modularity by breaking the complex problem into logical, named steps.
CTEs are highly valued for their ability to improve the readability and maintainability of complex SQL queries. By allowing the analyst to define the 'total sales per region' calculation in a named CTE, the main query can then simply refer to that name, making the logic for the second step (calculating the average) much clearer and easier to understand.
Question 16: When joining three tables A, B, and C, how many ON conditions are typically needed?
- Three
- Two (Correct answer)
- One
- Zero
Correct answer: Two
Each additional joined table generally needs one more ON condition to link it.
Question 17: Using a view to expose computed columns (like price * quantity) is an example of what?
- Normalizing the schema
- Indexing the table
- Encapsulating business logic (Correct answer)
- Partitioning data
Correct answer: Encapsulating business logic
Views can present derived/calculated columns, hiding the computation from users.
Question 18: What happens if you run DELETE without a WHERE clause?
- An error is raised
- All rows in the table are deleted (Correct answer)
- Only one row is deleted
- Nothing changes
Correct answer: All rows in the table are deleted
DELETE without WHERE removes every row in the table.
Question 19: Are DDL statements in most databases implicitly committed?
- DDL never affects transactions
- No, they always require an explicit COMMIT
- Only CREATE auto-commits
- Yes, most DDL auto-commits the current transaction (Correct answer)
Correct answer: Yes, most DDL auto-commits the current transaction
In most RDBMSs, DDL statements cause an implicit commit, making the change permanent immediately.
Question 20: Which is true about PARTITION BY in a window function?
- It sorts the output
- It collapses rows like GROUP BY
- It is mandatory
- It divides rows into independent groups for the calculation (Correct answer)
Correct answer: It divides rows into independent groups for the calculation
PARTITION BY splits rows into groups within which the window function operates independently.
Question 21: Which join would you use to list every product alongside its sales, including products with zero sales?
- CROSS JOIN
- LEFT JOIN with Products on the left (Correct answer)
- RIGHT JOIN with Sales on the left
- INNER JOIN
Correct answer: LEFT JOIN with Products on the left
A LEFT JOIN keeps all products and NULL-fills those without sales.
Question 22: Which function returns the largest value in a numeric column?
- MAX (Correct answer)
- GREATEST
- TOP
- HIGH
Correct answer: MAX
MAX returns the highest value among the rows in each group or set.
Question 23: Can a subquery appear in the HAVING clause?
- No, never
- Yes, to compare against aggregated results (Correct answer)
- Only in MySQL
- Only with EXISTS
Correct answer: Yes, to compare against aggregated results
Subqueries are valid in HAVING to filter groups based on computed or external values.
Question 24: Which wildcard in LIKE matches exactly one single character?
- Percent (%)
- Underscore (_) (Correct answer)
- Question mark (?)
- Asterisk (*)
Correct answer: Underscore (_)
In standard SQL LIKE, the underscore matches exactly one character.
Question 25: Which DDL statement creates a new database schema?
- MAKE SCHEMA
- ADD SCHEMA
- CREATE SCHEMA (Correct answer)
- NEW SCHEMA
Correct answer: CREATE SCHEMA
CREATE SCHEMA defines a new schema (namespace) to organize database objects.
Question 26: Which query finds products priced 100 or less OR with quantity above 50?
- WHERE price <= 100 AND quantity > 50
- WHERE price <= 100 OR quantity > 50 (Correct answer)
- WHERE price = 100 OR quantity = 50
- WHERE price < 100 OR quantity >= 50
Correct answer: WHERE price <= 100 OR quantity > 50
OR returns rows satisfying at least one of the two conditions exactly as written.
Question 27: Which is generally preferred for performance when checking existence of related rows?
- Loading all rows then filtering
- EXISTS (Correct answer)
- Using SELECT *
- COUNT(*) > 0 in a scalar subquery
Correct answer: EXISTS
EXISTS can stop at the first matching row, often outperforming counting approaches.
Question 28: Which clause limits the number of rows returned in standard MySQL/PostgreSQL?
- FETCH ONLY
- LIMIT (Correct answer)
- ROWNUM
- TOP
Correct answer: LIMIT
LIMIT restricts the number of rows returned in MySQL and PostgreSQL.
Question 29: What does LEAD(value, 1, 0) return when no following row exists?
- NULL
- 0 (Correct answer)
- An error
- The current value
Correct answer: 0
The third argument supplies a default (0 here) when the offset row does not exist.
Question 30: Which comparison returns rows greater than EVERY value the subquery returns?
- > SOME
- > ANY
- > ALL (Correct answer)
- > IN
Correct answer: > ALL
> ALL is true only when the value exceeds every value returned by the subquery.
Question 31: What does an empty OVER () clause cause an aggregate like AVG() to do?
- Error out
- Compute over the entire result set as one window (Correct answer)
- Ignore NULLs differently
- Compute per row only
Correct answer: Compute over the entire result set as one window
An empty OVER () treats all rows as a single window, returning the overall aggregate on each row.
Question 32: In LAG(salary, 2) OVER (ORDER BY hire_date), which row's value is returned?
- The current row
- The first row of the partition
- The row 2 positions before (Correct answer)
- The row 2 positions ahead
Correct answer: The row 2 positions before
LAG with offset 2 accesses the value from two rows prior in the ordering.
Question 33: 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?
- 50 (Correct answer)
- 10
- It depends on the matching keys.
- 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 34: A LEFT JOIN between Customers and Orders returns customers with no orders. What appears in the Orders columns for those rows?
- 0
- The row is excluded
- Empty string
- NULL (Correct answer)
Correct answer: NULL
Unmatched rows from the right table produce NULL values in the result.
Question 35: In SQL Server, which keyword limits the number of rows returned?
- MAX
- LIMIT
- FIRST
- TOP (Correct answer)
Correct answer: TOP
SQL Server uses TOP n to restrict the number of returned rows.
Question 36: Which is the correct order of clauses in a join query?
- FROM ... WHERE ... JOIN ... ON
- JOIN ... FROM ... WHERE
- 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 37: What is the result type expected by an IN subquery?
- A scalar only
- A boolean
- Multiple columns
- A single column of values (Correct answer)
Correct answer: A single column of values
An IN subquery should return one column whose values are compared for membership.
Question 38: What does SUM(DISTINCT amount) compute?
- Sum of all amounts
- Sum of only the unique amount values (Correct answer)
- Count of distinct amounts
- Maximum amount
Correct answer: Sum of only the unique amount values
DISTINCT removes duplicate values before summing the unique ones.
Question 39: Which combination of properties best describes a PRIMARY KEY?
- Allows duplicates but NOT NULL
- Unique and NOT NULL (Correct answer)
- Allows duplicates and NULLs
- Unique but allows NULLs
Correct answer: Unique and NOT NULL
A PRIMARY KEY is both unique and NOT NULL, uniquely identifying each row.
Question 40: Which clause CANNOT typically contain a subquery in standard SQL?
- FROM
- The keyword list of an INDEX definition (Correct answer)
- SELECT
- WHERE
Correct answer: The keyword list of an INDEX definition
Subqueries appear in SELECT, FROM, WHERE, and HAVING, but not within an index column definition.
Question 41: Which combination is valid: SELECT dept, MAX(salary) FROM emp GROUP BY dept ORDER BY MAX(salary) DESC?
- Invalid: ORDER BY needs GROUP BY column
- Valid only with HAVING
- Valid: orders departments by their max salary (Correct answer)
- Invalid: cannot ORDER BY an aggregate
Correct answer: Valid: orders departments by their max salary
ORDER BY can reference aggregate expressions to sort grouped output.
Question 42: Why are table aliases especially useful in multi-table joins?
- They shorten references and disambiguate same-named columns (Correct answer)
- They create indexes automatically
- They speed up the query engine
- 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 43: What is returned by AVG over the values 2, 4, NULL, 6?
- 4 (Correct answer)
- NULL
- 4.5
- 3
Correct answer: 4
AVG ignores NULL, so (2+4+6)/3 = 4.
Question 44: What does ALTER TABLE ... DROP CONSTRAINT do?
- Removes a named constraint from the table (Correct answer)
- Disables indexing globally
- Drops the whole table
- Deletes all rows
Correct answer: Removes a named constraint from the table
DROP CONSTRAINT removes a specified constraint definition while leaving the table and its data intact.
Question 45: What is index fragmentation?
- Disorder in index pages caused by ongoing insertions, updates, and deletes (Correct answer)
- An index that still references deleted rows
- An index with missing or corrupted entries
- An index spread across multiple physical files
Correct answer: Disorder in index pages caused by ongoing insertions, updates, and deletes
Index fragmentation occurs when frequent data modifications cause index pages to fall out of logical order, increasing I/O and degrading query performance.
Question 46: 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 with RANK()
- Only in PostgreSQL
- Yes, always
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 47: Which join type is most likely to produce an unexpectedly huge result if the ON condition is omitted?
- INNER JOIN with ON
- CROSS JOIN (or a JOIN written without ON) (Correct answer)
- Self join with ON
- LEFT 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 48: What distinguishes COMMENT ON (or column comments) as a DDL feature?
- It attaches descriptive metadata to database objects (Correct answer)
- It drops a table
- It changes column data
- It runs a query
Correct answer: It attaches descriptive metadata to database objects
COMMENT ON adds human-readable documentation as metadata to tables, columns, or other objects.
Question 49: Which of the following statements best describes the concept of Atomicity in the context of database transactions (ACID)?
- Concurrent transactions are isolated from each other, as if they were running serially.
- A transaction brings the database from one valid state to another, preserving data integrity.
- A transaction's changes, once committed, will persist even in the event of a system failure.
- A transaction is an 'all or nothing' proposition; either all of its operations succeed, or none of them are applied. (Correct answer)
Correct answer: A transaction is an 'all or nothing' proposition; either all of its operations succeed, or none of them are applied.
Atomicity is the 'A' in the ACID properties and guarantees that a transaction is treated as a single, indivisible unit of work. If any part of the transaction fails, the entire transaction is rolled back, leaving the database unchanged.
Question 50: A subquery that references a column from the outer query is called what?
- Nested join
- Derived table
- Correlated subquery (Correct answer)
- Scalar subquery
Correct answer: Correlated subquery
A correlated subquery depends on a column supplied by the outer query and runs per outer row.
Question 51: 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?"
- multidimensional database (Correct answer)
- High Performance Storage System
- functional specification
- line information database
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 52: Which feature lets a view physically store its query results for faster reads?
- System view
- Inline view
- Temporary view
- Materialized view (Correct answer)
Correct answer: Materialized view
A materialized view caches the result set on disk and is refreshed periodically.
Question 53: What does the WHERE clause do in an UPDATE statement?
- Sorts the results
- Groups the rows
- Restricts which rows are updated (Correct answer)
- Joins two tables
Correct answer: Restricts which rows are updated
WHERE limits an UPDATE to only the rows matching its condition.
Question 54: What does a CROSS JOIN produce?
- Only matching rows
- The Cartesian product of both tables (Correct answer)
- Only the left table's rows
- Distinct rows from both tables
Correct answer: The Cartesian product of both tables
A CROSS JOIN pairs every row of the first table with every row of the second.
Question 55: A developer wants to change the name of an existing table from `tbl_Users` to `Users`. Which of the following commands should be used?
- CREATE ALIAS Users FOR tbl_Users;
- RENAME TABLE tbl_Users TO Users; (Correct answer)
- MODIFY TABLE tbl_Users RENAME TO Users;
- UPDATE TABLE tbl_Users SET NAME = Users;
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 56: Which is valid MySQL shorthand for OFFSET 20 LIMIT 10?
- OFFSET 20, 10
- LIMIT 10, 20
- LIMIT 20 OFFSET 10
- LIMIT 20, 10 (Correct answer)
Correct answer: LIMIT 20, 10
MySQL's two-argument LIMIT takes offset first, then row count.
Question 57: A subquery returning multiple rows used with = will cause what?
- A silent truncation
- Automatic IN conversion
- A runtime error (Correct answer)
- A faster plan
Correct answer: A runtime error
Equality expects a single value, so a multi-row subquery triggers an error.
Question 58: How are NULL values typically sorted in PostgreSQL ascending order by default?
- NULLs cause an error
- NULLs are excluded
- NULLs appear first
- NULLs appear last (Correct answer)
Correct answer: NULLs appear last
In PostgreSQL ascending order, NULLs are treated as larger and sort last by default.
Question 59: Which scenario is best solved by LAG to compare consecutive periods?
- Finding the maximum value
- Counting distinct users
- Calculating month-over-month change (Correct answer)
- Removing duplicates
Correct answer: Calculating month-over-month change
LAG retrieves the prior period's value, enabling month-over-month difference calculations.
Question 60: What happens by default when you try to insert a NULL into a column defined as NOT NULL?
- The statement fails with an error (Correct answer)
- The row is skipped quietly
- The NULL is converted to zero
- The default value is silently used
Correct answer: The statement fails with an error
A NOT NULL column rejects NULL values, causing the insert to fail unless a value is provided.
Question 61: Which statement best describes the relationship between DDL and the data dictionary?
- DDL only reads metadata
- DDL modifies the metadata describing database objects (Correct answer)
- DDL has no effect on 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 62: What is the default transaction behavior in many databases like Oracle for DML statements?
- Changes roll back automatically
- Every statement auto-commits
- Changes are read-only
- Changes are uncommitted until COMMIT is issued (Correct answer)
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 63: In a query joining Orders o and Customers c, which expression correctly references the customer name?
- name.c
- Customers->name
- o.name
- c.name (Correct answer)
Correct answer: c.name
You qualify a column with its table alias using alias.column syntax.
Question 64: 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?
- UPDATE
- INSERT
- A series of INSERT, UPDATE, and DELETE statements
- MERGE (Correct answer)
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 65: Which function would you use to compute a running total of sales by date?
- SUM() OVER (ORDER BY date) (Correct answer)
- COUNT() with GROUP BY
- ROW_NUMBER()
- MAX() OVER ()
Correct answer: SUM() OVER (ORDER BY date)
SUM() with an ORDER BY in OVER produces a cumulative running total.
Question 66: A developer executes the following sequence of commands: 1. `START TRANSACTION;` 2. `INSERT INTO Logs (Message) VALUES ('Step 1');` 3. `SAVEPOINT A;` 4. `INSERT INTO Logs (Message) VALUES ('Step 2');` 5. `COMMIT;` What is the outcome of this sequence?
- The entire transaction is rolled back because the `COMMIT` invalidates the savepoint.
- Only 'Step 1' is saved because the savepoint was never used for a rollback.
- Both 'Step 1' and 'Step 2' are permanently saved to the Logs table. (Correct answer)
- An error occurs because a `SAVEPOINT` must be followed by a `ROLLBACK TO SAVEPOINT`.
Correct answer: Both 'Step 1' and 'Step 2' are permanently saved to the Logs table.
The `COMMIT` command finalizes the entire transaction, making all changes since the `START TRANSACTION` permanent. A `SAVEPOINT` is only a marker for a potential partial rollback. If `COMMIT` is issued, all savepoints within the transaction are disregarded, and all work is saved.
Question 67: Which clause correctly filters for names starting with the letter 'A'?
- WHERE name LIKE '_A'
- WHERE name = 'A%'
- WHERE name LIKE '%A'
- WHERE name LIKE 'A%' (Correct answer)
Correct answer: WHERE name LIKE 'A%'
The % wildcard after A matches any sequence of characters following A.
Question 68: This phrase refers to the process of forecasting, or simply identifying patterns in data that can lead to future predictions.
- histogram
- enterprise risk management
- customer relationship management
- data mining (Correct answer)
Correct answer: data mining
Data mining is the process of discovering patterns, anomalies, and correlations within large datasets to predict future outcomes or identify trends. It involves using various analytical techniques and algorithms to extract valuable insights and build predictive models from existing data, effectively forecasting or identifying patterns that lead to predictions.
Question 69: Which join returns only rows that have matching values in both tables?
- CROSS JOIN
- FULL OUTER JOIN
- LEFT JOIN
- INNER JOIN (Correct answer)
Correct answer: INNER JOIN
INNER JOIN returns only the rows where the join condition matches in both tables.
Question 70: A DELETE statement with a subquery in WHERE is used to do what?
- Delete rows based on values from another query (Correct answer)
- Sort the table
- Copy rows
- Create a new table
Correct answer: Delete rows based on values from another query
A subquery in WHERE lets DELETE target rows matching a related query's results.
Question 71: Where can a subquery used as a derived table appear?
- Only in ORDER BY
- Only in WHERE
- In the FROM clause (Correct answer)
- Only in GROUP 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 72: What is the result of joining a table to an empty table with an INNER JOIN?
- Zero rows (Correct answer)
- All rows from the non-empty table
- An error
- NULL-filled rows
Correct answer: Zero rows
With no matching rows available, an INNER JOIN returns nothing.
Question 73: What does the MIN function return for a group of dates?
- The count of dates
- The earliest date (Correct answer)
- The latest date
- NULL always
Correct answer: The earliest date
MIN returns the smallest value, which for dates is the earliest one.
Question 74: Which query returns rows where discount is NULL or zero?
- WHERE discount IN (NULL, 0)
- WHERE discount IS NULL OR discount = 0 (Correct answer)
- WHERE discount = 0 AND discount IS NULL
- WHERE discount = NULL OR discount = 0
Correct answer: WHERE discount IS NULL OR discount = 0
You must test IS NULL separately and combine it with the zero check using OR.
Question 75: 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
- data dictionary (Correct answer)
- Virtual File Allocation table
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 76: What does the ROLLBACK command do?
- Undoes changes since the last COMMIT or SAVEPOINT (Correct answer)
- Saves changes permanently
- Deletes a table
- Locks all rows
Correct answer: Undoes changes since the last COMMIT or SAVEPOINT
ROLLBACK reverses uncommitted changes back to the last commit or a specified savepoint.
Question 77: What is the purpose of the INCLUDE clause when creating a non-clustered index in SQL Server?
- To add non-key columns to the leaf level of the index without making them part of the key (Correct answer)
- To include the index in the query plan cache automatically
- To specify which stored procedures should use the index
- To include foreign key references in the index definition
Correct answer: To add non-key columns to the leaf level of the index without making them part of the key
The INCLUDE clause stores additional non-key columns at the leaf level of a non-clustered index, enabling more queries to be covered without widening the index key itself.
Question 78: To extract data from a database, which SQL statement is used?
- GET
- SELECT (Correct answer)
- EXTRACT
- OPEN
Correct answer: SELECT
The `SELECT` statement is the fundamental SQL command used to retrieve or extract data from one or more tables in a database. It allows users to specify which columns and rows they want to query, making it the primary tool for data retrieval operations. Other options like `EXTRACT` or `GET` are not standard SQL commands for this purpose.
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.
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
These practice questions were written with AI tools. They are not real exam questions. We have not yet matched this exam's facts to an official source, so confirm fees, dates and rules with the exam body. Not reviewed by a subject-matter expert. Editorial policy