Oracle SQL Certification 1Z0-071 Course | Section 6: Restricting and Sorting Data

How to filter and limit data in your queries

This section covers one of the most heavily tested areas of the Oracle SQL 1Z0-071 certification exam: controlling which rows are returned and how results are ordered. Almost every real exam includes multiple questions based on these concepts, often combined into a single query with subtle logic traps.

You will learn how Oracle processes query results, how filtering happens, and how ordering rules affect the final output.

Column Aliases

We begin with column aliases, a simple but extremely important feature when retrieving data.

Column aliases allow you to rename columns in the query output. They improve readability and are commonly used in reports, calculations, and ordered results.

Key exam points to remember:

  • Column aliases affect only the query output, not the table definition
  • The AS keyword is optional in Oracle
  • Without double quotes, aliases are not case sensitive and cannot contain spaces
  • With double quotes, aliases become case sensitive and may include spaces or reserved words
  • Aliases defined in the SELECT clause can be used in ORDER BY

Oracle allows this behavior, and it is frequently tested.

Sorting Data with ORDER BY

Sorting data correctly is a fundamental SQL skill and a frequent exam topic.

Important concepts covered in this section:

  • SQL result sets have no default order
  • ORDER BY is always evaluated last
  • Ascending order is the default
  • DESC must be explicitly specified
  • Sorting does not change stored data

You learn how to sort by:

  • Column names
  • Column aliases
  • Expressions
  • Multiple columns

You also see how ambiguity can cause errors when aliases conflict with column names.

Positional Sorting and Advanced ORDER BY Rules

Oracle supports positional sorting, where numbers represent column positions in the SELECT list. This feature is Oracle specific and appears regularly on the exam.

Critical rules covered:

  • Positional sorting uses column positions from the SELECT clause
  • You can mix column names, positions, and aliases in ORDER BY
  • You can sort by columns that are not in the SELECT list
  • You cannot use positional sorting for columns that are not selected
Case Sensitivity and Sorting

Oracle sorts uppercase characters before lowercase ones. This can change expected alphabetical order.

To avoid this, functions such as UPPER are used in ORDER BY clauses. This behavior is a common exam trick.

Sorting and NULL Values

NULL values behave consistently across data types:

  • NULL is treated as the highest value
  • In ascending order, NULL appears last
  • In descending order, NULL appears first

This rule is frequently tested and often misunderstood.

WHERE Clause Part 1: Basic Filtering

The WHERE clause is one of the most important SQL topics in the entire exam.

In Part 1, you learn:

  • WHERE clause position in a query
  • How rows are filtered before results are returned
  • Comparison operators for numbers, characters, and dates
  • Multiple valid NOT EQUAL operators in Oracle
  • Implicit data type conversion

Oracle will attempt to convert data types automatically when possible. This can succeed or fail depending on the data, which affects both correctness and performance.

WHERE Clause Part 2: Boolean Logic

This lesson focuses on combining conditions using logical operators.

Key exam rules:

  • AND is evaluated before OR
  • Parentheses override operator precedence
  • Small logic changes can drastically change results

Many exam questions are designed to test your understanding of precedence, not syntax.

WHERE Clause Part 3: LIKE, IN, BETWEEN, and NULL

This lesson introduces advanced filtering techniques that appear constantly on the exam.

Important behaviors to remember:

  • LIKE supports wildcards for pattern matching
  • BETWEEN is inclusive on both ends
  • IN replaces multiple OR conditions
  • NOT IN behaves differently when NULL values are involved
  • NULL cannot be compared using equals
  • IS NULL and IS NOT NULL must be used instead

Incorrect NULL handling is one of the most common causes of wrong exam answers.

Substitution Variables

Substitution variables allow queries and scripts to accept input at runtime.

This topic is especially relevant for SQL scripts and exam questions involving dynamic SQL execution.

Key concepts:

  • Substitution variables are resolved at runtime
  • They can replace values, column names, and clauses
  • Character input must be quoted
  • DEFINE prevents repeated prompts
  • ACCEPT and PROMPT are used mainly in SQL*Plus
  • VERIFY controls variable substitution output

This feature is commonly tested in scenario based questions.

Row Limiting with FETCH

Oracle provides modern row limiting syntax using FETCH.

You learn how to:

  • Limit results to a fixed number of rows
  • Fetch a percentage of rows
  • Fetch a single row
  • Combine FETCH with ORDER BY

Key exam rules:

  • FETCH appears at the end of the query
  • FIRST and NEXT are interchangeable
  • ROW and ROWS are interchangeable
  • Without ORDER BY, fetched rows are not deterministic
FETCH WITH TIES

WITH TIES allows Oracle to return additional rows when values are equal to the last fetched row.

Important exam points:

  • ORDER BY is required for meaningful results
  • Rows with the same ordered value are included
  • Without ORDER BY, WITH TIES behaves like FETCH ONLY

This feature is commonly tested in ranking and reporting scenarios.

OFFSET

OFFSET allows you to skip rows before fetching results.

You learn how to:

  • Skip a fixed number of rows
  • Combine OFFSET with FETCH
  • Handle edge cases such as zero, negative, or excessive offsets

OFFSET is frequently used for pagination and appears in exam questions combined with ORDER BY and FETCH.

Exam Strategy for This Section

This section is guaranteed to appear on the 1Z0-071 exam.

To succeed, you must be able to:

  • Predict query results mentally
  • Understand evaluation order
  • Spot missing WHERE clauses
  • Recognize logic errors with AND and OR
  • Handle NULL values correctly
  • Understand sorting behavior precisely

Practice combining WHERE, ORDER BY, FETCH, WITH TIES, and OFFSET in a single query. That is exactly how the exam tests these concepts.

Now already involved in this SQL journey, you can subscribe to the YouTube channel and maybe subscribe to newsletter below?

Join ForgeFuse Newsletter