Lesson 1

This lesson introduces a module that builds on three core SELECT skills you should already know:

1. Joining two or more tables
2. Grouping and summarizing data
3. Using subqueries

If you can already write basic SELECT statements that join tables, calculate aggregates with GROUP BY, and incorporate subqueries in the SELECT, FROM, or WHERE clauses, you are ready for the material in this module.

From that foundation, this module extends your skills in three directions:

• Richer joins and conditions: You will refine the way you join tables, control filter conditions, and place predicates in WHERE or HAVING correctly.
• Grouping and summarizing: You will review grouping rules, learn how HAVING works with aggregates, and see where DISTINCT and UNIQUE belong in a SELECT statement.
• Subqueries in real scenarios: You will write single-row, multi-row, and correlated subqueries and compare subquery solutions with join-based solutions.

In addition, you will explore Oracle-specific SELECT features that build on these basics: hierarchical queries, set operators, and pseudocolumns.

• Module Objectives
  By the end of this module, you will be able to:
  1. Use a variety of operators and conditions to write clear, maintainable SELECT statements.
  2. Write hierarchical queries using the CONNECT BY feature.
  3. Compare and apply the UNION, UNION ALL, INTERSECT, and MINUS operators.
  4. Place DISTINCT, UNIQUE, and HAVING in the correct position within a SELECT statement.
  5. Identify and use common Oracle pseudocolumns in queries.

Many business structures are naturally hierarchical: organizations, chart-of-account trees, folders and subfolders, and parent–child relationships in product or category tables. Oracle supports these structures with hierarchical queries using the CONNECT BY clause.

A basic hierarchical query identifies:

1. The table containing parent and child rows.
2. The root of the hierarchy using START WITH.
3. The parent–child relationship using CONNECT BY and the PRIOR keyword.

Example: Navigating a department hierarchy.

SELECT department_name,
       LEVEL          AS hierarchy_level
FROM   departments
START WITH parent_id IS NULL
CONNECT BY PRIOR department_id = parent_id
ORDER BY LEVEL, department_name;
  

In this query:

• LEVEL is a pseudocolumn indicating how deep each row sits in the hierarchy.
• START WITH finds the top-level department rows.
• CONNECT BY PRIOR department_id = parent_id tells Oracle how to walk from parent rows to child rows.

Later lessons in the module show how hierarchical queries relate back to joins and subqueries, and how to avoid common pitfalls such as cycles or missing root rows.

Set operators combine the results of multiple SELECT statements. In this module you will compare:

• UNION – merges two result sets and removes duplicates.
• UNION ALL – merges two result sets and preserves duplicates.
• INTERSECT – returns only rows that appear in both result sets.
• MINUS – returns rows from the first query that do not appear in the second.

Oracle Database SQL

The following query selects all book titles from the BOOKSHELF and BOOK_ORDER tables and removes duplicates by using UNION:

SELECT Title
FROM   BOOKSHELF
UNION
SELECT Title
FROM   BOOK_ORDER;
  

When this statement executes, Oracle runs each SELECT separately, then combines the results. Because UNION removes duplicates, Oracle must sort and compare the combined rows before returning them to the user.

If your business requirement allows duplicate titles, you can avoid that extra work by using UNION ALL:

SELECT Title
FROM   BOOKSHELF
UNION ALL
SELECT Title
FROM   BOOK_ORDER;
  

In this case Oracle still executes each SELECT and concatenates the results, but it does not need an extra sort step to remove duplicates. Part of working effectively with set operators is deciding when deduplication is necessary and when it simply adds overhead for no benefit.

Later lessons use similar examples to show how set operators compare with joins and subqueries when you need combined or filtered result sets from multiple tables.

This introductory lesson sets the stage for the rest of the module. You will:

• Review and extend join techniques across two or more tables.
• Group and summarize data with aggregates, GROUP BY, and HAVING.
• Use subqueries in practical business questions and compare them with join-based solutions.
• Apply Oracle-specific features such as hierarchical queries, set operators, and pseudocolumns.

The next lesson starts by revisiting operators and conditions that are often skipped in introductory SQL courses and shows how they influence join logic, grouping, and subqueries in real systems.

[1]Pseudocolumn: A pseudocolumn is data that Oracle makes available when it executes a SQL statement. Examples include USER (the name of the current Oracle user), ROWNUM (a row sequence number), and LEVEL in hierarchical queries.