Lesson 1

This course assumes that you are familiar enough with SQL to write basic queries, including queries that take advantage of these SQL features:

1. Joining two or more tables
2. Grouping and summarizing data
3. Using sub-queries

This module covers some advanced SQL features, such as hierarchy queries and combining multiple queries with the UNION command. In addition, you will try out some query features that are exclusive to Oracle, such as the use of pseudocolumns^[1]. A pseudocolumn is a special column generated when Oracle executes the query. There are a number of pseudocolumns, as you will see in this module.

• Module Objectives
  By the end of this module, you will know how to:
  1. Use a variety of operators and conditions to write a query
  2. Write a query using the CONNECT BY feature
  3. Compare the INTERSECT, MINUS, and UNION commands
  4. Correctly place the HAVING DISTINCT or UNIQUE clauses in two queries
  5. Identify the uses of various pseudocolumns

Developers can write queries using the CONNECT BY feature in Oracle 19c to navigate and manipulate hierarchical data. Here's a breakdown:
Purpose:

• CONNECT BY is specifically designed to query hierarchical data structures represented in single tables.
• It iterates through the hierarchy, starting from a specified "root" node, and follows relationships defined by a specific column to identify parent-child connections.

Structure:
A basic CONNECT BY query includes three key clauses:

1. SELECT: Selects desired columns from the table.
2. FROM: Identifies the table containing the hierarchical data.
3. CONNECT BY: Defines the logic for traversing the hierarchy:
   • START WITH: Specifies the initial "root" node(s) for the traversal.
   • CONNECT BY: Defines the condition for identifying child nodes of the current row. This typically involves comparing a parent column with a child column using the `PRIOR` operator.

Additional functionalities:

• WHERE: Filters rows based on conditions other than hierarchical relationships.
• ORDER BY: Specifies the order in which rows are returned.
• LEVEL: A special pseudocolumn that indicates the level of each row within the hierarchy.

Example: Imagine a table `departments` storing department information with a `parent_id` column indicating hierarchical relationships.

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

This query retrieves department names along with their level in the hierarchy, starting from the department with no parent (likely the top-level department) and navigating through the connected child departments.
Important points:

• Understand the data structure and parent-child relationships before writing the query.
• Ensure the CONNECT BY condition accurately reflects the hierarchical relationships.
• Be cautious with potential loops in the hierarchy, which can lead to errors.

Remember, writing efficient and accurate CONNECT BY queries requires a good understanding of the specific data structure and the desired output.

The union, minus, and intersect clauses allow the results of multiple queries to be processed and compared.
Each of the functions has an associated operation, the names of the operations are UNION, MINUS, and INTERSECTION.
The following query selects all of the Title values from the BOOKSHELF table and from the BOOK_ORDER table:

select Title
from BOOKSHELF
UNION
select Title
from BOOK_ORDER;

When the preceding query is executed, the optimizer will execute each of the queries separately, and then combine the results. The first query is

select Title
from BOOKSHELF

There are no limiting conditions in the query, and the Title column is indexed, so the primary key index on the BOOKSHELF table will be scanned. The second query is

select Title
from BOOK_ORDER

There are no limiting conditions in the query, and the Title column is indexed, so the primary key index on the BOOK_ORDER table will be scanned. Since the query performs a union of the results of the two queries, the two result sets will then be merged via a UNION-ALL operation. Using the union operator forces Oracle to eliminate duplicate records, so the result set is processed by a SORT UNIQUE NOSORT operation before the records are returned to the user. If the query had used a union all clause in place of union, the SORT UNIQUE NOSORT operation would not have been necessary. The query would be

select Title
from BOOKSHELF
UNION ALL
select Title
from BOOK_ORDER;

When processing the revised query, the optimizer would perform the scans required by the two queries, followed by a UNION-ALL operation. No SORT UNIQUE NOSORT operation would be required, since a union all clause does not eliminate duplicate records. When processing the union query, the optimizer addresses each of the unioned queries separately. Although the examples shown in the preceding listings all involved simple queries with full table scans, the unioned queries can be very complex, with correspondingly complex execution paths. The results are not returned to the user until all of the records have been processed.
The next lesson discusses operators and conditions that are not usually covered in introductory SQL courses.

[1] Pseudocolumn: A pseudocolumn is data that Oracle makes available to you when it executes SQL commands. These are created for convenience. For example, the Oracle user name that is executing the SQL is contained in the pseudocolumn called USER.