Try 10 focused Oracle 1Z0-071 questions on Restrict and Sort Data, with explanations, then continue with IT Mastery.
Open the matching IT Mastery practice page for timed mocks, topic drills, progress tracking, explanations, and full practice.
Try Oracle 1Z0-071 on Web View full Oracle 1Z0-071 practice page
| Field | Detail |
|---|---|
| Exam route | Oracle 1Z0-071 |
| Topic area | Restrict and Sort Data |
| Blueprint weight | 7% |
| Page purpose | Focused sample questions before returning to mixed practice |
Use this page to isolate Restrict and Sort Data for Oracle 1Z0-071. Work through the 10 questions first, then review the explanations and return to mixed practice in IT Mastery.
| Pass | What to do | What to record |
|---|---|---|
| First attempt | Answer without checking the explanation first. | The fact, rule, calculation, or judgment point that controlled your answer. |
| Review | Read the explanation even when you were correct. | Why the best answer is stronger than the closest distractor. |
| Repair | Repeat only missed or uncertain items after a short break. | The pattern behind misses, not the answer letter. |
| Transfer | Return to mixed practice once the topic feels stable. | Whether the same skill holds up when the topic is no longer obvious. |
Blueprint context: 7% of the practice outline. A focused topic score can overstate readiness if you recognize the pattern too quickly, so use it as repair work before timed mixed sets.
These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.
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.
= NULL fails because equality comparisons do not work for NULL testing.IN (NULL) does not correctly test for missing values.<> NULL also fails because NULL cannot be tested with standard inequality operators.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.
AND for an inside range.> 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.
BETWEEN fails because Oracle includes both endpoints in a BETWEEN test.OR matches almost every value, not only values inside the range.>= and <= includes 5,000 and 10,000, which the requirement excludes.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:
department_id = 50salary > 5000salary descendinglast_name ascendingThat 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.
ORDER BY before WHERE is invalid SQL syntax.DESC inside WHERE confuses sorting with row filtering.OR returns too many rows, and salary ASC reverses the required salary order.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.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.
> and < fails because it excludes 5,000 and 9,000.OR is true for almost every salary value, so it does not restrict to the intended range.IN (5000, 9000) returns only exact matches for those two values, not the values between them.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.
> and < fails because it leaves out exactly 3,000 and 5,000.OR makes the condition true for nearly every salary value.IN (3000, 5000) returns only salaries equal to 3,000 or 5,000, not the values in between.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.
Topic: Restrict and Sort Data
A developer needs an Oracle SQL query against the employees table.
employee_id and last_name for department 80 employeescommission_pct is not recordedlast_nameWhich 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:
WHERE clauseIS NULLORDER BY last_nameUsing 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.
= NULL never matches NULL values in a WHERE predicate.IS NOT NULL returns rows with a stored commission value, not missing commission.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.
IN fails because IN does not replace mixed logical tests like a numeric comparison plus a character equality.HAVING fails because HAVING filters grouped results, and this query has no aggregation requirement.DISTINCT fails because the issue is incorrect filtering logic, not duplicate result rows.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.
OR before AND.DISTINCT fails because it removes duplicates, not logically incorrect matches.ORDER BY affects presentation, not which rows satisfy the WHERE clause.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.
IN option returns only totals equal to 100 or 500, not the full range.>= 100 OR <= 500 matches almost all rows instead of restricting the set.Use the Oracle 1Z0-071 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.
Try Oracle 1Z0-071 on Web View Oracle 1Z0-071 Practice Test
Read the Oracle 1Z0-071 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.