Free Oracle 1Z0-071 Practice Questions: Restrict and Sort Data

Topic snapshot

Sample questions

These are original IT Mastery practice questions aligned to this topic area. They are not official Oracle questions, copied live-exam content, or exam dumps. Use them to preview question style and explanation depth before continuing with topic drills, mixed sets, and timed mocks in IT Mastery.

Question 1

Topic: Restrict and Sort Data

In Oracle SQL, which WHERE clause predicate correctly returns rows where manager_id has no value?

Options:

• A. manager_id IS NULL

• B. manager_id = NULL

• C. manager_id IN (NULL)

• D. manager_id <> NULL

Best answer: A

Explanation: In Oracle SQL, NULL is not tested with equality or inequality operators. To find rows where a column has no value, you must use the IS NULL predicate.

The core concept is that NULL means an unknown or missing value, so Oracle SQL does not treat it like an ordinary value in comparisons. Predicates such as = NULL or <> NULL do not correctly identify NULLs. Instead, Oracle provides the special predicates IS NULL and IS NOT NULL for NULL testing.

In this case, if manager_id has no value, the correct filter is manager_id IS NULL. Use IS NOT NULL when you want only rows that contain an actual non-NULL value. The key takeaway is that NULL checks require IS, not comparison operators.

• Equality confusion: The option using = NULL fails because equality comparisons do not work for NULL testing.
• Set-membership confusion: The option using IN (NULL) does not correctly test for missing values.
• Inequality confusion: The option using <> NULL also fails because NULL cannot be tested with standard inequality operators.

Question 2

Topic: Restrict and Sort Data

Which WHERE clause returns only rows where salary is greater than 5,000 and less than 10,000?

Options:

• A. WHERE salary >= 5000 AND salary <= 10000

• B. WHERE salary BETWEEN 5000 AND 10000

• C. WHERE salary > 5000 AND salary < 10000

• D. WHERE salary > 5000 OR salary < 10000

Best answer: C

Explanation: To exclude both boundary values, Oracle SQL must use > and < with AND. This limits the result to salaries strictly inside the range, not including 5,000 or 10,000.

The core concept is choosing the correct comparison operators for an exclusive range. In Oracle SQL, > means greater than and < means less than, so combining them with AND keeps only rows that satisfy both conditions at the same time.

• Use AND for an inside range.
• Use > and < for exclusive boundaries.
• BETWEEN is inclusive in Oracle SQL.

A common mistake is using BETWEEN or >=/<=, which would include the endpoints, or using OR, which makes the condition far too broad.

• Inclusive range using BETWEEN fails because Oracle includes both endpoints in a BETWEEN test.
• Too broad logic using OR matches almost every value, not only values inside the range.
• Wrong boundaries using >= and <= includes 5,000 and 10,000, which the requirement excludes.

Question 3

Topic: Restrict and Sort Data

Which statement returns employees in department 50 whose salary is greater than 5,000, sorted by salary from highest to lowest and then by last_name alphabetically?

Options:

• A. SELECT last_name, salary FROM employees WHERE department_id = 50 AND salary > 5000 AND salary DESC ORDER BY last_name;

• B. SELECT last_name, salary FROM employees WHERE department_id = 50 OR salary > 5000 ORDER BY salary ASC, last_name ASC;

• C. SELECT last_name, salary FROM employees ORDER BY salary DESC, last_name ASC WHERE department_id = 50 AND salary > 5000;

• D. SELECT last_name, salary FROM employees WHERE department_id = 50 AND salary > 5000 ORDER BY salary DESC, last_name ASC;

Best answer: D

Explanation: In Oracle SQL, row restriction is done in the WHERE clause, and sorting is done afterward with ORDER BY. The correct statement uses AND to limit rows to department 50 with salary above 5,000, then sorts by salary DESC and last_name ASC.

The core concept is the correct separation of filtering and sorting in a SELECT statement. In Oracle SQL, conditions that decide which rows are returned belong in the WHERE clause, while the presentation order of those rows belongs in the ORDER BY clause.

For this requirement, the logic is:

• keep only rows where department_id = 50
• keep only rows where salary > 5000
• sort first by salary descending
• break salary ties with last_name ascending

That means the statement must use WHERE department_id = 50 AND salary > 5000 followed by ORDER BY salary DESC, last_name ASC. A common mistake is placing ORDER BY before WHERE, or trying to use sort keywords such as DESC inside WHERE conditions.

• Clause order mistake placing ORDER BY before WHERE is invalid SQL syntax.
• Wrong clause purpose using DESC inside WHERE confuses sorting with row filtering.
• Wrong logic and sort using OR returns too many rows, and salary ASC reverses the required salary order.

Question 4

Topic: Restrict and Sort Data

The employees table has a salary column of type NUMBER. A report must return rows where salary is from 5,000 through 9,000, including both boundary values. Which WHERE clause correctly applies this Oracle SQL rule?

Options:

• A. WHERE salary BETWEEN 5000 AND 9000

• B. WHERE salary IN (5000, 9000)

• C. WHERE salary >= 5000 OR salary <= 9000

• D. WHERE salary > 5000 AND salary < 9000

Best answer: A

Explanation: BETWEEN is the Oracle SQL operator used for an inclusive range check. When the requirement says to include both the lower and upper limits, BETWEEN 5000 AND 9000 matches exactly that set of rows.

The core rule is that BETWEEN includes both endpoints of the range. In Oracle SQL, salary BETWEEN 5000 AND 9000 is equivalent to salary >= 5000 AND salary <= 9000. That makes it the correct choice when the requirement explicitly says to include 5,000 and 9,000.

A few points help confirm the rule:

• BETWEEN checks a continuous range.
• Both boundary values are included.
• Using AND with >= and <= would also be inclusive, but that is not what the other options do.
• OR changes the logic completely and makes the predicate far too broad.

The key takeaway is that BETWEEN is the clean Oracle SQL choice for inclusive lower-and-upper range filtering.

• Exclusive range using > and < fails because it excludes 5,000 and 9,000.
• Wrong logical operator using OR is true for almost every salary value, so it does not restrict to the intended range.
• Endpoints only using IN (5000, 9000) returns only exact matches for those two values, not the values between them.

Question 5

Topic: Restrict and Sort Data

Which predicate correctly returns rows where salary is from 3,000 through 5,000, including both boundary values?

Options:

• A. salary BETWEEN 3000 AND 5000

• B. salary IN (3000, 5000)

• C. salary > 3000 AND salary < 5000

• D. salary >= 3000 OR salary <= 5000

Best answer: A

Explanation: In Oracle SQL, BETWEEN low AND high is inclusive. That means values equal to 3,000 and 5,000 are both returned, which matches the requirement exactly.

The core concept is that Oracle SQL BETWEEN includes both endpoints. So salary BETWEEN 3000 AND 5000 is equivalent to salary >= 3000 AND salary <= 5000.

This is useful when a range filter must include the minimum and maximum values named in the requirement. If the requirement says “from 3,000 through 5,000,” BETWEEN is the direct and correct predicate.

The closest mistake is using > and <, which excludes rows whose salary is exactly 3,000 or exactly 5,000.

• Exclusive range using > and < fails because it leaves out exactly 3,000 and 5,000.
• Wrong operator using OR makes the condition true for nearly every salary value.
• Exact matches only using IN (3000, 5000) returns only salaries equal to 3,000 or 5,000, not the values in between.

Question 6

Topic: Restrict and Sort Data

A developer expects this query to return only employees in departments 10 or 20 whose salary is above 5,000, but it also returns some department 10 rows with lower salaries.

SELECT last_name, department_id, salary
FROM employees
WHERE department_id = 10
   OR department_id = 20
  AND salary > 5000;

Which Oracle SQL concept explains the unexpected rows?

Options:

• A. A correlated subquery changed the row filtering

• B. Logical operator precedence in the WHERE clause

• C. Implicit conversion changed the numeric comparison

• D. An outer join preserved unmatched department rows

Best answer: B

Explanation: The issue is logical operator precedence. In Oracle SQL, AND is evaluated before OR, so the condition is interpreted as department_id = 10 OR (department_id = 20 AND salary > 5000), which allows all department 10 rows through.

This query returns unexpected rows because the Boolean conditions are not grouped as intended. Oracle evaluates AND before OR unless parentheses explicitly change that order. So the predicate is processed as department_id = 10 OR (department_id = 20 AND salary > 5000), not as (department_id = 10 OR department_id = 20) AND salary > 5000.

If the requirement is to apply the salary filter to both departments, write the predicate with parentheses:

WHERE (department_id = 10 OR department_id = 20)
  AND salary > 5000

The key takeaway is that unexpected rows often come from predicate grouping, not from joins, subqueries, or datatype conversion.

• Correlated subquery confusion does not apply because the statement has no subquery at all.
• Outer join confusion does not apply because the query reads from only one table and uses no join.
• Implicit conversion confusion does not apply because the comparisons shown are numeric-to-numeric and the problem is the logical grouping.

Question 7

Topic: Restrict and Sort Data

A developer needs an Oracle SQL query against the employees table.

• Return employee_id and last_name for department 80 employees
• Include only rows whose commission_pct is not recorded
• Sort by last_name

Which statement is the best solution?

Options:

• A. SELECT employee_id, last_name FROM employees WHERE department_id = 80 AND commission_pct IS NOT NULL ORDER BY last_name

• B. SELECT employee_id, last_name FROM employees WHERE department_id = 80 AND commission_pct IS NULL ORDER BY last_name

• C. SELECT employee_id, last_name FROM employees WHERE department_id = 80 HAVING commission_pct IS NULL ORDER BY last_name

• D. SELECT employee_id, last_name FROM employees WHERE department_id = 80 AND commission_pct = NULL ORDER BY last_name

Best answer: B

Explanation: In Oracle SQL, NULL is not tested with = or <>; it must be tested with IS NULL or IS NOT NULL. The best query uses commission_pct IS NULL in the WHERE clause, keeps only department 80 rows, and sorts by last_name.

Oracle treats NULL as an unknown or missing value, so ordinary equality comparisons do not work for NULL testing. A condition like commission_pct = NULL does not evaluate to TRUE for any row, so it cannot correctly return employees whose commission is missing.

To meet the requirements, the query must do three things in the right places:

• filter department 80 rows in the WHERE clause
• test missing commission values with IS NULL
• sort the final result with ORDER BY last_name

Using IS NOT NULL would return the opposite set, and moving this row filter to HAVING is inappropriate because the query is not doing grouped reporting. The key takeaway is simple: in Oracle, use IS NULL and IS NOT NULL whenever you need to test for NULL.

• Equality comparison fails because = NULL never matches NULL values in a WHERE predicate.
• HAVING misuse fails because this is row filtering, not aggregate filtering for a grouped query.
• Opposite test fails because IS NOT NULL returns rows with a stored commission value, not missing commission.

Question 8

Topic: Restrict and Sort Data

A developer wants only employees from department 50 who either earn more than 5,000 or have job ID SA_REP. However, this query returns extra rows, including SA_REP employees from other departments:

SELECT employee_id, department_id, salary, job_id
FROM employees
WHERE department_id = 50
  AND salary > 5000
   OR job_id = 'SA_REP';

What is the best fix?

Options:

• A. Replace OR with IN in the WHERE clause.

• B. Move department_id = 50 to a HAVING clause.

• C. Group the OR conditions with parentheses.

• D. Add DISTINCT to remove the extra rows.

Best answer: C

Explanation: The problem is operator precedence in the WHERE clause. In Oracle SQL, AND is evaluated before OR, so the query is interpreted differently from the stated requirement unless the two alternative conditions are grouped with parentheses.

This query is evaluated as (department_id = 50 AND salary > 5000) OR job_id = 'SA_REP' because AND has higher precedence than OR. That means any employee with job ID SA_REP is returned, even if the employee is not in department 50.

To match the requirement, first restrict rows to department 50, then apply the two allowed alternatives:

WHERE department_id = 50
  AND (salary > 5000 OR job_id = 'SA_REP')

The key takeaway is that unexpected row counts often come from incorrectly grouped logical conditions, not from duplicate rows or aggregate behavior.

• Using IN fails because IN does not replace mixed logical tests like a numeric comparison plus a character equality.
• Using HAVING fails because HAVING filters grouped results, and this query has no aggregation requirement.
• Using DISTINCT fails because the issue is incorrect filtering logic, not duplicate result rows.

Question 9

Topic: Restrict and Sort Data

A developer wants all orders for sales rep 10 that are either NEW or PENDING, but the query returns extra rows for other reps.

SELECT order_id, sales_rep_id, status
FROM orders
WHERE sales_rep_id = 10
  AND status = 'NEW'
   OR status = 'PENDING';

What is the best fix?

Options:

• A. Leave the filter as written because OR is evaluated first.

• B. Add parentheses so the rep filter applies to both statuses.

• C. Add DISTINCT to remove the unintended rows.

• D. Sort the rows first so only rep 10 remains.

Best answer: B

Explanation: Oracle evaluates AND before OR. Without parentheses, the predicate is treated as (sales_rep_id = 10 AND status = 'NEW') OR status = 'PENDING', so any PENDING row is returned. Grouping the two status conditions fixes the row count.

This is an operator-precedence problem in the WHERE clause. In Oracle SQL, AND is evaluated before OR, so the original filter does not mean “rep 10 and either of these statuses.” It means “rep 10 with NEW, or any row with PENDING.”

The intended logic should be written so the status alternatives are evaluated together:

WHERE sales_rep_id = 10
  AND (status = 'NEW' OR status = 'PENDING')

Parentheses make the intended grouping explicit and prevent extra rows from being returned. The key takeaway is that when AND and OR are mixed, add parentheses whenever the business rule depends on a specific grouping.

• Wrong precedence fails because Oracle does not evaluate OR before AND.
• Using DISTINCT fails because it removes duplicates, not logically incorrect matches.
• Sorting first fails because ORDER BY affects presentation, not which rows satisfy the WHERE clause.

Question 10

Topic: Restrict and Sort Data

A developer is troubleshooting a report that should return only rows where order_total is from 100 through 500, inclusive. The current query is:

SELECT order_id, order_total
FROM orders
WHERE order_total > 100
AND order_total < 500;

Users report that orders with totals exactly 100 and exactly 500 are missing. What is the best fix?

Options:

• A. Replace the predicate with order_total >= 100 OR order_total <= 500.

• B. Replace the predicate with order_total BETWEEN 100 AND 500.

• C. Convert order_total to character data before comparing it.

• D. Replace the predicate with order_total IN (100, 500).

Best answer: B

Explanation: The issue is that > and < exclude the boundary values. For an inclusive range in Oracle SQL, BETWEEN 100 AND 500 correctly returns rows with totals equal to 100, 500, and everything in between.

This is a range-filter problem in a WHERE clause. The current predicate uses strict comparison operators, so values equal to 100 or 500 do not satisfy the condition. In Oracle SQL, BETWEEN low AND high is inclusive of both endpoints, making it a correct fix for a requirement stated as “from 100 through 500, inclusive.”

You can think of it as equivalent to using >= 100 and <= 500 together with AND. Using OR would make the condition far too broad, because almost every value is either at least 100 or at most 500.

The key takeaway is to match the comparison operator to the business rule, especially when endpoint values must be included.

• Exact values only: the IN option returns only totals equal to 100 or 500, not the full range.
• Wrong logical connector: using >= 100 OR <= 500 matches almost all rows instead of restricting the set.
• Unnecessary conversion: converting numeric data to character data does not solve an inclusive-range problem and can make comparisons misleading.

Continue in the web app

Use IT Mastery for interactive Oracle 1Z0-071 practice with mixed sets, timed mocks, topic drills, explanations, and progress tracking.

Try Oracle 1Z0-071 on Web

Related focused pages

• Free Oracle 1Z0-071 Full-Length Practice Exam
• Relational Database Concepts
• SQL SELECT
• Single-Row Functions
• Conversion and CASE
• Group Functions
• Display Data from Multiple Tables
• Use Subqueries to Solve Queries
• Use Set Operators
• Manage Tables by Using DML Statements
• Indexes and Sequences
• DDL Tables and Relationships
• Manage Views
• Control User Access
• Data Dictionary Views
• Manage Data in Different Time Zones