I85PREP
⌘ K
Oracle 1Z0-182 study materials
Oracle 1Z0-182 study resources

Oracle 1Z0-182 practice questions

One question at a time. Build your Oracle 1Z0-182 understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Relational Database Concepts and SQL Fundamentals

In a relational database, which property must hold for a column declared as a primary key?

  1. It may contain NULL values as long as no duplicates exist
  2. It must be unique and must not contain NULL values Correct answer.
  3. It must be the first column defined in the table
  4. It must be of a character data type

Explanation

A primary key enforces two constraints at once: uniqueness across rows and mandatory values, so NULL is never allowed in a primary key column. The database rejects any insert or update that would duplicate an existing key or leave it empty.

Question 2Relational Database Concepts and SQL Fundamentals

A table has a foreign key referencing the primary key of a parent table. What happens by default when you attempt to insert a child row whose foreign key value does not exist in the parent table?

  1. The statement fails with a constraint violation Correct answer.
  2. The row is inserted but marked as invalid until the parent appears
  3. The row is inserted with the foreign key set to NULL
  4. The row is inserted and the parent row is created automatically

Explanation

A foreign key constraint requires every non-NULL child value to match an existing parent key, so the insert is rejected with an integrity constraint error. The database never silently creates parent rows or nulls out the value.

Question 3Relational Database Concepts and SQL Fundamentals

Which SQL statement is a data definition language (DDL) statement?

  1. DELETE FROM employees WHERE department_id = 10
  2. CREATE TABLE employees (id NUMBER PRIMARY KEY) Correct answer.
  3. UPDATE employees SET salary = salary * 1.1
  4. SELECT COUNT(*) FROM employees

Explanation

DDL statements define or alter database structures and include CREATE, ALTER, DROP, and TRUNCATE. Data manipulation statements such as UPDATE, DELETE, and SELECT operate on rows rather than on the schema.

Question 4Relational Database Concepts and SQL Fundamentals

Which clause is evaluated to filter individual rows before any grouping occurs?

  1. HAVING
  2. GROUP BY
  3. ORDER BY
  4. WHERE Correct answer.

Explanation

The WHERE clause filters rows before grouping, so aggregate functions cannot be used there. HAVING filters groups after aggregation, which is the key distinction between the two clauses.

Question 5Relational Database Concepts and SQL Fundamentals

What does the DISTINCT keyword do in a SELECT list?

  1. It returns only the first row of each group
  2. It sorts the rows in ascending order
  3. It excludes rows containing NULL values
  4. It removes duplicate rows from the result set Correct answer.

Explanation

DISTINCT eliminates duplicate combinations of the selected column values from the output. Ordering requires ORDER BY, and NULL handling requires an explicit predicate.

Question 6Relational Database Concepts and SQL Fundamentals

Which operator is used to test whether a column value falls within an inclusive range of values?

  1. EXISTS
  2. IN
  3. LIKE
  4. BETWEEN Correct answer.

Explanation

BETWEEN low AND high is inclusive of both boundary values and is shorthand for two comparison predicates joined with AND. LIKE performs pattern matching, IN tests membership in a list, and EXISTS tests subquery row existence.

Question 7Relational Database Concepts and SQL Fundamentals

A table SALES has columns REGION and AMOUNT. Which query returns total sales per region, showing only regions whose total exceeds 10000?

  1. SELECT region, SUM(amount) FROM sales WHERE SUM(amount) > 10000 GROUP BY region
  2. SELECT region, SUM(amount) FROM sales GROUP BY region WHERE SUM(amount) > 10000
  3. SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 10000 Correct answer.
  4. SELECT region, SUM(amount) FROM sales WHERE amount > 10000 GROUP BY region

Explanation

Aggregate conditions on grouped results must appear in HAVING, which is evaluated after GROUP BY. Filtering with WHERE on SUM is invalid because aggregates are not available at row-filtering time.

Question 8Relational Database Concepts and SQL Fundamentals

What is the result of comparing a column to NULL using the predicate salary = NULL?

  1. It returns TRUE for every row in the table
  2. It returns UNKNOWN for every row, so no rows are returned Correct answer.
  3. It returns TRUE for rows where salary is empty
  4. It raises a syntax error at parse time

Explanation

NULL represents an unknown value, so any ordinary comparison with NULL evaluates to UNKNOWN rather than TRUE. Rows are returned only when the predicate is TRUE, so the query returns nothing; IS NULL must be used instead.

Question 9Relational Database Concepts and SQL Fundamentals

Which join returns all rows from the left table plus matching rows from the right table, with NULLs in right-side columns where no match exists?

  1. RIGHT OUTER JOIN
  2. CROSS JOIN
  3. INNER JOIN
  4. LEFT OUTER JOIN Correct answer.

Explanation

A LEFT OUTER JOIN preserves every row of the left table and pads unmatched right-side columns with NULL. INNER JOIN discards unmatched rows, RIGHT OUTER JOIN preserves the right table, and CROSS JOIN produces a Cartesian product.

Question 10Relational Database Concepts and SQL Fundamentals

Which single-row function returns the number of characters in a string column?

  1. SUBSTR
  2. LENGTH Correct answer.
  3. INSTR
  4. TRIM

Explanation

LENGTH returns the character count of its argument, which is useful for validating or filtering string data. SUBSTR extracts a portion, INSTR locates a substring position, and TRIM removes pad characters.

Question 11Relational Database Concepts and SQL Fundamentals

The EMPLOYEES table contains 5 rows with department_id = 10, of which 2 have a NULL commission_pct. What does SELECT COUNT(commission_pct) FROM employees WHERE department_id = 10 return?

  1. 2
  2. 3 Correct answer.
  3. 0
  4. 5

Explanation

COUNT with a column argument ignores NULL values, so it counts only the three non-NULL entries. COUNT(*) would return 5 because it counts rows regardless of NULLs.

Question 12Relational Database Concepts and SQL Fundamentals

Which statement about the ORDER BY clause is correct?

  1. It guarantees that NULL values sort last in ascending order on every platform
  2. It must reference only columns that appear in the SELECT list
  3. It can sort by a column alias defined in the SELECT list Correct answer.
  4. It is evaluated before the WHERE clause

Explanation

ORDER BY is logically evaluated after SELECT, so it can reference column aliases and even expressions defined in the select list. NULL ordering is not portable across databases unless NULLS FIRST or NULLS LAST is specified.

Question 13Relational Database Concepts and SQL Fundamentals

A query uses WHERE last_name LIKE '_a%'. Which value satisfies the predicate?

  1. Smith
  2. Ada
  3. Ba Correct answer.
  4. A

Explanation

The underscore wildcard matches exactly one character and the percent wildcard matches any sequence, including none. 'Ba' has one leading character followed by 'a', which fits the pattern.

Question 14Relational Database Concepts and SQL Fundamentals

Which statement best describes the purpose of a view in a relational database?

  1. It permanently removes the underlying base tables
  2. It stores a named query whose result can be queried like a table Correct answer.
  3. It physically duplicates table rows to improve read performance
  4. It enforces uniqueness across multiple tables automatically

Explanation

A view is a stored query definition that presents a virtual table; it holds no data of its own unless materialized. It can simplify complex joins and restrict column exposure without duplicating rows.

Question 15Relational Database Concepts and SQL Fundamentals

Which constraint ensures that a column value is different for every row while still allowing NULLs?

  1. CHECK
  2. UNIQUE Correct answer.
  3. FOREIGN KEY
  4. PRIMARY KEY

Explanation

A UNIQUE constraint rejects duplicate non-NULL values but, unlike a primary key, generally permits multiple NULLs. PRIMARY KEY combines uniqueness with NOT NULL, FOREIGN KEY enforces referential integrity, and CHECK validates a condition.

Question 16Relational Database Concepts and SQL Fundamentals

Consider SELECT department_id, AVG(salary) FROM employees GROUP BY department_id HAVING COUNT(*) > 3 ORDER BY 2 DESC. What does ORDER BY 2 DESC accomplish?

  1. It sorts by the second column of the result, the average salary, in descending order Correct answer.
  2. It sorts by the second row of the employees table
  3. It sorts by department_id because position 2 is the second grouping key
  4. It raises an error because ORDER BY cannot use a numeric position with GROUP BY

Explanation

A numeric literal in ORDER BY refers to the ordinal position of the select list, so 2 means the AVG(salary) column. The result is ordered from highest to lowest average salary.

Question 17Relational Database Concepts and SQL Fundamentals

A subquery used with the IN operator returns three rows, one of which is NULL. A row whose comparison column is non-NULL and matches none of the other values will be returned by the outer query?

  1. Yes, because IN ignores NULL entries entirely
  2. No, because the comparison against the NULL element yields UNKNOWN and the row is not selected Correct answer.
  3. No, because the statement fails at execution time
  4. Yes, because the outer query treats UNKNOWN as TRUE

Explanation

IN is equivalent to a series of OR comparisons; comparing a value to NULL yields UNKNOWN, and a row is returned only if the overall predicate is TRUE. NOT IN with a NULL in the list therefore returns no rows at all, a classic pitfall.

Question 18Relational Database Concepts and SQL Fundamentals

Two tables are joined with a condition that matches multiple rows on both sides. If table A has 3 matching rows and table B has 4 matching rows for the same key, how many rows does the inner join produce for that key?

  1. 7
  2. 12 Correct answer.
  3. 3
  4. 4

Explanation

An inner join produces the Cartesian product of the matching rows for each key, so 3 multiplied by 4 yields 12 rows. This row multiplication is a common cause of inflated aggregate totals.

Question 19Relational Database Concepts and SQL Fundamentals

Which statement about the execution order of a query containing WHERE, GROUP BY, HAVING, and ORDER BY is correct?

  1. GROUP BY is evaluated before WHERE so that aggregates are available for filtering
  2. ORDER BY is evaluated before HAVING to reduce the rows that must be aggregated
  3. WHERE is evaluated before GROUP BY, and HAVING is evaluated after GROUP BY Correct answer.
  4. HAVING is evaluated before GROUP BY so that groups can be pre-filtered

Explanation

The logical processing order is FROM, WHERE, GROUP BY, HAVING, SELECT, then ORDER BY. Row filtering therefore precedes grouping, and group filtering follows it.

Question 20Relational Database Concepts and SQL Fundamentals

A table has a CHECK constraint defined as salary > 0. What happens when an UPDATE attempts to set salary to NULL?

  1. The update succeeds but the constraint is disabled
  2. The update fails with a foreign key violation
  3. The update succeeds because UNKNOWN satisfies a CHECK constraint Correct answer.
  4. The update fails because NULL is not greater than zero

Explanation

A CHECK constraint is violated only when its condition evaluates to FALSE; UNKNOWN, which results from comparing NULL, is accepted. To forbid NULLs as well, the column must be declared NOT NULL.

Question 21Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

You run SELECT * FROM employees WHERE last_name LIKE 'S%'; Which rows are returned?

  1. Rows whose last_name is exactly one character long
  2. Rows whose last_name contains the letter S anywhere
  3. Rows whose last_name ends with the letter S
  4. Rows whose last_name begins with the letter S Correct answer.

Explanation

The percent sign in a LIKE pattern matches any sequence of zero or more characters, so 'S%' anchors the letter S at the start and allows anything after it. Without a leading wildcard the match must begin at the first character of the column value.

Question 22Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which operator lets you test whether a column value falls within an inclusive range of two bounds in a single condition?

  1. EXISTS
  2. LIKE
  3. BETWEEN Correct answer.
  4. IN

Explanation

BETWEEN low AND high is a shorthand for two comparisons joined with AND and includes both endpoint values. It is typically used with numbers or dates to express an inclusive range.

Question 23Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

A column commission_pct contains NULL for many employees. Which predicate returns only rows where a commission value is actually present?

  1. WHERE commission_pct = NULL
  2. WHERE commission_pct <> ''
  3. WHERE commission_pct != NULL
  4. WHERE commission_pct IS NOT NULL Correct answer.

Explanation

NULL represents an unknown value, and any comparison using = or <> against NULL evaluates to unknown, so those rows are never returned. Only IS NULL and IS NOT NULL reliably test for the presence or absence of a value.

Question 24Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which clause restricts the rows returned by a query before they are sorted or limited?

  1. GROUP BY
  2. ORDER BY
  3. HAVING
  4. WHERE Correct answer.

Explanation

The WHERE clause applies row-level filtering as rows are read, which happens before grouping, sorting, and row limiting in the logical processing order. HAVING filters groups after aggregation instead.

Question 25Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

You need the ten highest salaries from employees. Which query is correct?

  1. SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 10 ROWS ONLY; Correct answer.
  2. SELECT * FROM employees WHERE ROWNUM <= 10 ORDER BY salary DESC;
  3. SELECT TOP 10 * FROM employees ORDER BY salary DESC;
  4. SELECT * FROM employees FETCH FIRST 10 ROWS ONLY ORDER BY salary DESC;

Explanation

Row limiting with FETCH FIRST must follow the ORDER BY clause so the sort is applied before the rows are truncated. Placing the limiting clause after ORDER BY DESC guarantees the ten largest salaries are kept.

Question 26Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which ORDER BY clause sorts employees by department_id ascending, then by salary descending within each department?

  1. ORDER BY salary DESC, department_id ASC
  2. ORDER BY department_id ASC, salary DESC Correct answer.
  3. ORDER BY department_id, salary
  4. ORDER BY department_id DESC, salary DESC

Explanation

Multiple sort keys are listed in priority order, so department_id is the primary key and salary the tiebreaker. ASC is the default for the first key, and DESC must be stated explicitly for the second.

Question 27Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

In a default Oracle database, how are NULLs positioned in the result of ORDER BY salary ASC?

  1. They appear first because NULL is treated as the smallest value.
  2. Their position is unpredictable and varies per execution.
  3. They are excluded from the result set entirely.
  4. They appear last because NULLs sort high by default in ascending order. Correct answer.

Explanation

Oracle treats NULLs as larger than any non-null value, so ascending sorts place them last and descending sorts place them first. The NULLS FIRST and NULLS LAST keywords override this default explicitly.

Question 28Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

You want rows where job_id is either 'IT_PROG' or 'SA_REP'. Which WHERE clause is the most direct and correct choice?

  1. WHERE job_id IN ('IT_PROG', 'SA_REP') Correct answer.
  2. WHERE job_id LIKE 'IT_PROG' AND 'SA_REP'
  3. WHERE job_id = 'IT_PROG' OR 'SA_REP'
  4. WHERE job_id BETWEEN 'IT_PROG' AND 'SA_REP'

Explanation

The IN operator tests a column against an explicit list of values and is equivalent to a chain of OR comparisons. It is concise and avoids repeating the column name for each value.

Question 29Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which statement about the underscore character in a LIKE pattern is true?

  1. It escapes the following character literally.
  2. It matches zero or more characters.
  3. It matches exactly one character. Correct answer.
  4. It matches only numeric digits.

Explanation

In a LIKE pattern the underscore wildcard matches exactly one character, while the percent sign matches any sequence including none. To match a literal underscore you must escape it with the ESCAPE clause.

Question 30Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

You write WHERE salary > 5000 AND department_id = 20 OR department_id = 30. How does Oracle evaluate the condition?

  1. The statement raises an error because mixing AND and OR requires parentheses.
  2. salary > 5000 AND department_id = (20 OR 30)
  3. (salary > 5000 AND department_id = 20) OR department_id = 30 Correct answer.
  4. salary > 5000 AND (department_id = 20 OR department_id = 30)

Explanation

AND has higher precedence than OR, so the two AND-linked predicates are evaluated together first and the result is OR-ed with the remaining predicate. Parentheses are needed to force the other grouping.

Question 31Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which query returns employees hired during the year 2020, assuming hire_date is a DATE column?

  1. WHERE hire_date BETWEEN TO_DATE('2020-01-01','YYYY-MM-DD') AND TO_DATE('2020-12-31','YYYY-MM-DD') Correct answer.
  2. WHERE hire_date LIKE '2020%'
  3. WHERE hire_date = '2020'
  4. WHERE EXTRACT(YEAR FROM hire_date) = '2020'

Explanation

BETWEEN with explicit date literals for January 1 and December 31 includes every hire date in the calendar year. Using TO_DATE avoids dependence on session date-format settings.

Question 32Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which clause is used to filter groups after aggregation, for example to keep only departments whose average salary exceeds 8000?

  1. FETCH FIRST 8000 ROWS ONLY
  2. HAVING AVG(salary) > 8000 Correct answer.
  3. WHERE AVG(salary) > 8000
  4. ORDER BY AVG(salary) > 8000

Explanation

HAVING is evaluated after GROUP BY and can reference aggregate functions such as AVG. WHERE is evaluated before grouping and therefore cannot contain group functions.

Question 33Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

You issue SELECT * FROM employees ORDER BY 2 DESC; What does the numeric literal mean here?

  1. It limits the output to two rows.
  2. It sorts by the second column of the select list. Correct answer.
  3. It sorts by the value 2 in every row.
  4. It is invalid because ORDER BY accepts only column names.

Explanation

A positional integer in ORDER BY refers to the ordinal position of an expression in the select list, so 2 means the second selected column. This is convenient but fragile if the select list changes.

Question 34Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which predicate returns employees whose salary is not in the range 3000 through 5000, including employees whose salary is NULL?

  1. WHERE salary NOT BETWEEN 3000 AND 5000
  2. WHERE salary NOT BETWEEN 3000 AND 5000 OR salary IS NULL Correct answer.
  3. WHERE salary < 3000 AND salary > 5000
  4. WHERE salary <> 3000 AND salary <> 5000

Explanation

NOT BETWEEN excludes NULL rows because comparisons with NULL yield unknown, so an explicit OR salary IS NULL is required to include them. The other predicates either omit NULLs or describe an impossible condition.

Question 35Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

What is the effect of ORDER BY last_name ASC NULLS FIRST?

  1. NULL rows are removed before sorting.
  2. The NULLS FIRST keyword is ignored in ascending sorts.
  3. Rows with NULL last_name appear at the top, followed by names in ascending order. Correct answer.
  4. Rows with NULL last_name appear at the bottom, followed by names in ascending order.

Explanation

NULLS FIRST explicitly overrides Oracle's default placement and puts rows with a null sort key ahead of all non-null values. The remaining rows are still sorted ascending as specified.

Question 36Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which query returns exactly the second through fifth rows of employees ordered by employee_id?

  1. SELECT * FROM employees WHERE ROWNUM BETWEEN 2 AND 5 ORDER BY employee_id;
  2. SELECT * FROM employees ORDER BY employee_id OFFSET 1 ROWS FETCH NEXT 4 ROWS ONLY; Correct answer.
  3. SELECT * FROM employees ORDER BY employee_id FETCH FIRST 5 ROWS ONLY;
  4. SELECT * FROM employees ORDER BY employee_id OFFSET 2 ROWS FETCH NEXT 4 ROWS ONLY;

Explanation

OFFSET skips a specified number of rows and FETCH NEXT returns the following rows, so skipping 1 and taking 4 yields rows 2 through 5. OFFSET counts rows to skip, not the starting row number.

Question 37Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which comparison correctly finds names containing a literal percent sign, for example 'A%1'?

  1. WHERE name LIKE 'A_%1'
  2. WHERE name LIKE 'A%1'
  3. WHERE name LIKE 'A\%1' ESCAPE '\' Correct answer.
  4. WHERE name = 'A%1'

Explanation

To match a wildcard literally you must escape it and declare the escape character with the ESCAPE clause. The backslash before the percent sign makes it a literal percent rather than a wildcard.

Question 38Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which statement about using a column alias in ORDER BY is correct?

  1. A select-list alias can be referenced in ORDER BY. Correct answer.
  2. Aliases are only valid in the FROM clause.
  3. A select-list alias can be referenced in WHERE.
  4. Aliases must be enclosed in single quotes in ORDER BY.

Explanation

ORDER BY is evaluated after the select list, so it can reference column aliases defined there. WHERE is evaluated before the select list and therefore cannot see those aliases.

Question 39Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which condition returns employees whose manager_id is NULL using a comparison that behaves as intended?

  1. WHERE manager_id IS NULL Correct answer.
  2. WHERE manager_id IN (NULL)
  3. WHERE NVL(manager_id, 0) > 0
  4. WHERE manager_id = NULL

Explanation

IS NULL is the only predicate designed to detect the absence of a value, because any = or IN comparison against NULL evaluates to unknown. The NVL test would actually select non-null managers.

Question 40Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

You need to filter on a column with the condition UPPER(job_id) = 'IT_PROG'. What happens to the use of an ordinary index on job_id?

  1. A regular index on job_id generally cannot be used, so a function-based index would be needed. Correct answer.
  2. The query fails because functions are not allowed in WHERE.
  3. The index is used only when the column contains NULLs.
  4. The index is used normally because UPPER is transparent to the optimizer.

Explanation

A plain B-tree index stores the raw column values, so wrapping the column in a function prevents a normal index range scan. A function-based index on UPPER(job_id) restores index access for that predicate.

Question 41Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which query lists distinct department_id values in descending order?

  1. SELECT department_id FROM employees ORDER BY DISTINCT department_id DESC;
  2. SELECT UNIQUE department_id FROM employees GROUP BY department_id DESC;
  3. SELECT DISTINCT department_id FROM employees ORDER BY 1 ASC;
  4. SELECT DISTINCT department_id FROM employees ORDER BY department_id DESC; Correct answer.

Explanation

DISTINCT removes duplicate values from the result and ORDER BY then sorts the surviving values descending. The DISTINCT keyword belongs immediately after SELECT, not inside ORDER BY.

Question 42Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

What does the following return: SELECT * FROM employees WHERE salary > 1000 AND ROWNUM <= 5;

  1. A syntax error because ROWNUM cannot be combined with AND
  2. All employees earning more than 1000
  3. The five highest-paid employees
  4. Up to five arbitrary employees whose salary exceeds 1000 Correct answer.

Explanation

ROWNUM is assigned to rows as they are produced, before any ORDER BY is applied, so the query stops after five qualifying rows in whatever order they are read. To get the top five salaries you must sort first, for example with a subquery or FETCH FIRST.

Question 43Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which operator would you use to match a value against a pattern where only the first character is known and the rest is variable?

  1. IN
  2. BETWEEN
  3. IS NULL
  4. LIKE Correct answer.

Explanation

LIKE supports wildcards, so a pattern such as 'A%' matches values that start with A and have any suffix. The other operators compare against fixed values or ranges rather than character patterns.

Question 44Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which clause order is valid in an Oracle SELECT statement that filters, groups, sorts, and limits rows?

  1. SELECT ... FROM ... WHERE ... HAVING ... GROUP BY ... ORDER BY ... FETCH FIRST ...
  2. SELECT ... FROM ... ORDER BY ... WHERE ... GROUP BY ... HAVING ... FETCH FIRST ...
  3. SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... FETCH FIRST ... Correct answer.
  4. SELECT ... FROM ... GROUP BY ... WHERE ... HAVING ... ORDER BY ... FETCH FIRST ...

Explanation

The logical clause order is FROM, WHERE, GROUP BY, HAVING, ORDER BY, and then the row-limiting FETCH clause. WHERE always precedes GROUP BY, and HAVING follows the grouping it filters.

Question 45Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

You want employees whose last_name starts with 'Mc' or 'Mac'. Which WHERE clause is correct?

  1. WHERE last_name LIKE 'Mc%' AND last_name LIKE 'Mac%'
  2. WHERE last_name IN ('Mc', 'Mac')
  3. WHERE last_name LIKE 'Mc%' OR last_name LIKE 'Mac%' Correct answer.
  4. WHERE last_name BETWEEN 'Mc' AND 'Mac'

Explanation

Two LIKE patterns joined with OR match names beginning with either prefix, since a name cannot satisfy both prefixes at once. The IN and BETWEEN forms compare whole values or ranges rather than prefixes.

Question 46Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which ORDER BY clause returns employees sorted by commission_pct so that rows with a commission come first, and employees without a commission appear last?

  1. ORDER BY commission_pct DESC Correct answer.
  2. ORDER BY commission_pct NULLS LAST
  3. ORDER BY commission_pct ASC
  4. ORDER BY commission_pct DESC NULLS LAST

Explanation

In Oracle, ORDER BY ... DESC places NULL values first by default, because NULLs are treated as larger than any non-NULL value in a descending sort. Therefore ORDER BY commission_pct DESC puts the employees who have a commission at the top and the NULL commission rows at the bottom. No extra NULLS clause is needed to achieve that ordering.

Question 47Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

You run SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5 ORDER BY department_id. Which statement describes the order in which Oracle processes these clauses?

  1. GROUP BY is evaluated before WHERE so that rows are grouped before filtering
  2. HAVING is evaluated before GROUP BY so that only qualifying rows are grouped
  3. ORDER BY is evaluated before HAVING so that groups are sorted before filtering
  4. WHERE is evaluated before GROUP BY, HAVING filters groups after grouping, and ORDER BY sorts the surviving groups Correct answer.

Explanation

The logical processing order for a grouped query is FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, and finally ORDER BY. WHERE removes individual rows before grouping, while HAVING removes whole groups after aggregation. ORDER BY is applied last, so it sorts only the groups that survived HAVING.

Question 48Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which query returns employees whose salary is greater than 5000 and whose hire_date falls in the first quarter of 2021, using hire_date as a DATE column?

  1. SELECT * FROM employees WHERE salary > 5000 AND TO_CHAR(hire_date) = '2021-01-01'
  2. SELECT * FROM employees WHERE salary > 5000 AND hire_date = '2021-Q1'
  3. SELECT * FROM employees WHERE salary > 5000 AND hire_date LIKE '2021-01%'
  4. SELECT * FROM employees WHERE salary > 5000 AND hire_date BETWEEN DATE '2021-01-01' AND DATE '2021-03-31' Correct answer.

Explanation

BETWEEN with two DATE literals is inclusive, so DATE '2021-01-01' through DATE '2021-03-31' covers the whole first quarter. Using explicit DATE literals avoids dependence on session date-format settings. The AND combines the salary and date conditions so both must be true.

Question 49Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which query returns the five highest salaries from employees without duplicates caused by ties in salary?

  1. SELECT ALL salary FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY
  2. SELECT salary FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS WITH TIES
  3. SELECT DISTINCT salary FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY Correct answer.
  4. SELECT salary FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC

Explanation

DISTINCT removes duplicate salary values before the row-limiting clause is applied, so the result contains up to five distinct salaries. FETCH FIRST 5 ROWS ONLY limits the rows after ORDER BY, which is the correct order of evaluation. This avoids duplicate salaries appearing when several employees share the same value.

Question 50Retrieving Data with SELECT: Filtering, Sorting, and Row Limiting

Which predicate returns employees whose last_name does not start with the letter 'A', including rows where last_name is NULL?

  1. WHERE NOT (last_name LIKE 'A%')
  2. WHERE NVL(last_name, ' ') NOT LIKE 'A%' Correct answer.
  3. WHERE last_name NOT LIKE 'A%'
  4. WHERE last_name <> 'A'

Explanation

NOT LIKE and NOT(...) both evaluate to UNKNOWN when last_name is NULL, so NULL rows are filtered out. Wrapping the column in NVL gives a non-NULL value, so the NOT LIKE test returns TRUE for NULL names and those rows are kept. The inequality test on the whole name is a different comparison and does not implement a prefix test.