SELECT Queries

The SELECT statement is the foundation of everything you do with data in Oracle. Every report, every dashboard, every application screen that displays data is built on a SELECT. This lesson covers the full structure of a SELECT statement — choosing columns, writing expressions, controlling output order, eliminating duplicates, and working with Oracle-specific syntax like DUAL, ROWNUM, and FETCH FIRST. All examples draw from the DataplexaStore and DataplexaHR schemas.

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

Basic SELECT Structure

A SELECT statement has a mandatory SELECT and FROM clause. Every other clause — WHERE, GROUP BY, HAVING, ORDER BY — is optional. Oracle evaluates clauses in a fixed internal order regardless of how they appear: FROM first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY last. Understanding this order explains why you cannot reference a column alias defined in SELECT inside a WHERE clause — the WHERE runs before SELECT has produced the alias.

The asterisk * selects all columns in the order they were defined at table creation. It is useful for exploration but should never appear in production queries or application code — when a column is added to the table, SELECT * returns it automatically, which can break applications that expect a fixed column count or order.

-- Select all columns — useful for exploration, avoid in production
SELECT * FROM customers;

-- Select specific columns — always preferred in application queries
SELECT customer_id, full_name, email, country
FROM   customers;

-- Column aliases — AS is optional but always include it for readability
SELECT customer_id          AS id,
       full_name            AS name,
       UPPER(country)       AS country_upper,
       LOWER(email)         AS email_lower
FROM   customers
ORDER BY customer_id;

-- DUAL — Oracle's built-in single-row dummy table
-- Use it when you need to evaluate an expression without reading from a real table
SELECT SYSDATE                             AS today,
       USER                               AS current_user,
       'DataplexaStore'                   AS schema_label,
       ROUND(1234.5678, 2)                AS rounded
FROM   DUAL;

• Oracle requires a FROM clause on every SELECT — unlike some databases there is no SELECT 1 + 1 without a table; use FROM DUAL when no real table is needed
• Column aliases defined with AS can be used in ORDER BY but not in WHERE, GROUP BY, or HAVING — those clauses execute before the alias is assigned
• UPPER() and LOWER() do not change the stored value — they only affect the output of that query

Column Expressions and Arithmetic

SELECT can compute new values from existing columns using arithmetic operators, string functions, date functions, and conditional expressions. The result appears as a derived column in the output — nothing is written back to the table. Expressions are evaluated row by row, so every row in the result set gets its own computed value.

-- Arithmetic on numeric columns
SELECT product_name,
       unit_price,
       unit_price * 1.2                    AS price_inc_vat,
       unit_price * 0.9                    AS discounted_10pct,
       ROUND(unit_price * 1.2, 2)          AS vat_rounded,
       stock_qty * unit_price              AS stock_value
FROM   products
WHERE  active_flag = 'Y'
ORDER BY unit_price DESC;

-- String expressions using Oracle's concatenation operator ||
SELECT employee_id,
       first_name || ' ' || last_name      AS full_name,
       INITCAP(first_name)                 AS first_initcap,
       LENGTH(last_name)                   AS last_name_length,
       SUBSTR(email, 1, INSTR(email,'@') - 1) AS email_username
FROM   employees
ORDER BY last_name;

-- Date expressions
SELECT order_id,
       order_date,
       TRUNC(order_date)                   AS date_only,
       order_date + 14                     AS due_date,
       ROUND(SYSDATE - order_date, 0)      AS days_old,
       TO_CHAR(order_date, 'Month YYYY')   AS month_label
FROM   orders
ORDER BY order_date;

• SUBSTR(str, start, length) extracts a substring — in Oracle positions are 1-based, not 0-based; SUBSTR(email, 1, 5) returns the first 5 characters starting from position 1
• INSTR(str, substr) returns the position of the first occurrence of substr in str — combining it with SUBSTR allows dynamic string parsing without knowing the exact position in advance
• Any arithmetic involving a NULL column returns NULL — if stock_qty were NULL then stock_qty * unit_price would produce NULL, not zero; use NVL(stock_qty, 0) to substitute a default before arithmetic

DISTINCT and Eliminating Duplicates

SELECT DISTINCT removes duplicate rows from the result set. It operates on the entire row — if two rows have identical values in every selected column they are collapsed into one. DISTINCT applies to all columns in the SELECT list, not just the first one.

DISTINCT has a cost — Oracle must sort or hash the result to identify duplicates. On large result sets this can be slow. If you find yourself using DISTINCT to remove duplicates from a join result, investigate whether the join itself is producing duplicates due to a missing or incorrect join condition.

-- All countries customers come from — without DISTINCT duplicates appear
SELECT country FROM customers ORDER BY country;

-- DISTINCT removes duplicate country values
SELECT DISTINCT country
FROM   customers
ORDER BY country;

-- DISTINCT across multiple columns — unique combinations of country and loyalty_tier
SELECT DISTINCT country,
                loyalty_tier
FROM   customers
ORDER BY country, loyalty_tier;

-- Count of distinct values — DISTINCT inside COUNT
SELECT COUNT(*)            AS total_customers,
       COUNT(DISTINCT country)  AS unique_countries,
       COUNT(DISTINCT loyalty_tier) AS unique_tiers
FROM   customers;

• DISTINCT is placed immediately after SELECT and applies to the entire row — SELECT DISTINCT a, b deduplicates on the combination of a and b, not on a alone
• COUNT(DISTINCT column) counts unique non-null values in a column — a useful pattern for data profiling and cardinality checks
• If duplicates appear in a query result unexpectedly, the cause is almost always a one-to-many join — check the join conditions before reaching for DISTINCT

ORDER BY and Sorting

ORDER BY controls the sequence in which rows appear in the result. Without ORDER BY, Oracle returns rows in no guaranteed order — the physical storage order, index access order, or parallel execution order may all produce different sequences across runs. If row order matters, always specify it explicitly.

Oracle sorts NULL values to the end in ascending order and to the beginning in descending order by default. This can be overridden with NULLS FIRST or NULLS LAST.

-- Single column sort — ascending is the default
SELECT product_name, unit_price, stock_qty
FROM   products
ORDER BY unit_price;

-- Descending sort
SELECT product_name, unit_price, stock_qty
FROM   products
ORDER BY unit_price DESC;

-- Multi-column sort — primary sort by category, secondary sort by price within each category
SELECT category, product_name, unit_price
FROM   products
ORDER BY category ASC, unit_price DESC;

-- Sort by column position (1-based) — works but reduces readability
SELECT product_name, unit_price, stock_qty
FROM   products
ORDER BY 2 DESC;

-- NULL handling in sort — manager_id is nullable
-- By default NULLs sort last in ASC, first in DESC
SELECT employee_id, full_name, manager_id
FROM   (SELECT employee_id,
               first_name || ' ' || last_name AS full_name,
               manager_id
        FROM   employees)
ORDER BY manager_id ASC NULLS LAST;

-- Sorting by a column alias defined in SELECT
SELECT product_name,
       unit_price * stock_qty  AS stock_value
FROM   products
ORDER BY stock_value DESC;

• Sorting by column position (ORDER BY 2) is fragile — if the SELECT column list is reordered, the sort column changes silently; always sort by name in production code
• NULLS LAST is often the right choice for nullable columns in ascending sorts — it pushes unknown values to the end rather than letting them appear first
• Column aliases defined in SELECT can be used in ORDER BY — this is one of the few places an alias is available, because ORDER BY is the last clause Oracle evaluates

Limiting Rows — ROWNUM and FETCH FIRST

Oracle provides two ways to limit the number of rows returned. ROWNUM is the traditional approach — it assigns a row number to each row as it is retrieved, before sorting. This means WHERE ROWNUM <= 5 on a query with an ORDER BY does not return the top 5 sorted rows — it returns 5 arbitrary rows and then sorts them. To correctly limit after sorting, wrap the sorted query in a subquery and apply ROWNUM to the outer query.

FETCH FIRST n ROWS ONLY, introduced in Oracle 12c, applies the limit after sorting and is the correct modern approach. Always prefer it over ROWNUM for new code.

-- ROWNUM — traditional approach, applied BEFORE sorting
-- This does NOT return the 3 most expensive products
SELECT product_name, unit_price
FROM   products
WHERE  ROWNUM <= 3
ORDER BY unit_price DESC;

-- Correct ROWNUM pattern — sort in a subquery, limit in the outer query
SELECT product_name, unit_price
FROM   (SELECT product_name, unit_price
        FROM   products
        ORDER BY unit_price DESC)
WHERE  ROWNUM <= 3;

-- FETCH FIRST — Oracle 12c syntax, correct and readable
-- Returns the 3 most expensive products after sorting
SELECT product_name, unit_price
FROM   products
ORDER BY unit_price DESC
FETCH FIRST 3 ROWS ONLY;

-- OFFSET — skip rows and return the next n
-- Page 2 of results: skip the first 3, return the next 3
SELECT product_name, unit_price
FROM   products
ORDER BY unit_price DESC
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

-- FETCH FIRST with PERCENT
SELECT product_name, unit_price
FROM   products
ORDER BY unit_price DESC
FETCH FIRST 25 PERCENT ROWS ONLY;

• ROWNUM is assigned before ORDER BY — this is the most common beginner mistake in Oracle; the subquery pattern or FETCH FIRST must be used to get correct top-N results
• FETCH FIRST ... ROWS ONLY requires an ORDER BY to be deterministic — without it Oracle can return any n rows; always pair the two together
• OFFSET n ROWS FETCH NEXT m ROWS ONLY is Oracle's pagination syntax — use it for API endpoints or UI pages that return results in pages

Summary

Practice Questions

Quiz

Next up — WHERE Clause & Filtering — Learn how to filter rows using comparison operators, BETWEEN, IN, LIKE, IS NULL, and compound conditions with AND, OR, and NOT.