Lesson 1

This module pulls together the grouping techniques and functions covered throughout this course, aggregates, joins, views, date and string functions, and applies them toward a specific end: producing reports that are actually meaningful to read, not just correct. The module's class project asks for a listing of sales by sales associate, narrowed to only the associates who sold the single product with the highest overall quantity sold, a genuine combination of aggregation, filtering, and ranking rather than a simple SELECT.

Two genuinely different paths get covered in this lesson for turning that kind of query into something presentable: a hand-built approach using commands native to SQL*Plus, and a newer, natural-language path available on Autonomous Database specifically. Both are covered here because they solve the same underlying problem in different ways, and knowing when each one actually fits is as useful as knowing the syntax for either.

Oracle's native answer to formatted reporting isn't a separate server product; it's built directly into SQL*Plus, the command-line tool that ships with every Oracle Database installation. A handful of commands turn an ordinary query into a genuinely readable report: COLUMN for formatting individual columns, BREAK for grouping and suppressing repeated values, COMPUTE for subtotals and grand totals, and TTITLE/BTITLE for page headers and footers.

Consider an ordinary query listing employees earning over 12,000 by department:

SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_ID;

DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000
           80 Russell                        14000
           80 Partners                       13500
           90 King                           24000
           90 Kochhar                        17000
           90 De Haan                        17000

Functionally correct, but the repeated department numbers and lack of any summary make it a data dump rather than a report. Adding BREAK and COMPUTE changes that:

BREAK ON DEPARTMENT_ID SKIP 1
COMPUTE SUM OF SALARY ON DEPARTMENT_ID
/

Now department numbers stop repeating, a blank line separates each department, and a subtotal line prints automatically after each group:

DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000
                                         ----------
sum                                           13000

           80 Russell                        14000
              Partners                       13500
                                         ----------
sum                                           27500

           90 King                           24000
              Kochhar                        17000
              De Haan                        17000
                                         ----------
sum                                           58000

A grand total across the whole report, not just each group, adds one more BREAK/COMPUTE pair:

BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF SALARY ON REPORT

BREAK isn't limited to a single column. Grouping by department and then by job title within each department, with a subtotal at each level, chains two BREAK clauses together:

BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID SKIP 1 NODUP
COMPUTE SUM OF SALARY ON DEPARTMENT_ID

SKIP PAGE on the outer break starts each new department on a fresh page; SKIP 1 on the inner break just adds a blank line between job titles within the same department. NODUP suppresses a repeated department number the same way the single-column example already did, extended here to both grouping levels at once. The result nests naturally: department, then job title within it, then employee rows, with the subtotal computed at the department level regardless of how many job titles sit underneath it.

Sometimes a subtotal is wanted without the visible sum label cluttering the output, useful when a report needs a clean numeric total with no annotation. A dummy column, hidden with NOPRINT, accomplishes this:

COLUMN DUMMY NOPRINT
COMPUTE SUM OF SALARY ON DUMMY
BREAK ON DUMMY SKIP 1
SELECT DEPARTMENT_ID DUMMY, DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY > 12000
ORDER BY DEPARTMENT_ID;

DUMMY here is just DEPARTMENT_ID selected a second time under an alias that never actually prints; it exists purely to give BREAK and COMPUTE something to key off of, producing a clean subtotal line with no sum label attached to it.

TTITLE places a formatted title at the top of every page:

TTITLE LEFT 'Salary Report by Department'

A fixed title like this is fine when the whole report shares one heading, but a title can also change from page to page, reflecting whatever group of data appears on that specific page. Combining a hidden NEW_VALUE column with BREAK ON ... SKIP PAGE makes this possible:

COLUMN JOB_ID NEW_VALUE ji_nv NOPRINT
BREAK ON JOB_ID SKIP PAGE
TTITLE LEFT 'Employees in job: ' ji_nv
SELECT LAST_NAME, JOB_ID FROM EMPLOYEES ORDER BY JOB_ID;

NEW_VALUE captures JOB_ID's current value into the substitution variable ji_nv every time it changes; since that variable isn't wrapped in quotes or given an & prefix in the TTITLE command, SQL*Plus re-substitutes it fresh for each page rather than locking in whatever value it held when the title was first defined. The result is a title that reads "Employees in job: SA_REP" on one page and "Employees in job: ST_CLERK" on the next, generated automatically as the report moves from one job title's group to the next. BTITLE works identically, just anchored to the bottom of the page instead of the top.

This is the direct, executable equivalent of what SQL Server's Reporting Services (SSRS) accomplishes through a separate server product with a web-based designer: grouped, subtotaled, titled output, produced here entirely with commands native to SQL*Plus, no additional server or licensing required. The specific mechanics differ, SSRS is a standalone reporting platform with paginated report design, drill-down interactivity, and its own report server, while SQL*Plus reporting is script-based and runs anywhere the database itself does, but the underlying goal, turning raw query results into something a reader can actually use, is the same.

Oracle AI Database 26ai adds a genuinely different way to get from a question to a report: Select AI, which translates a natural language prompt into SQL, runs it, and can describe the result back in plain language. This is specific to Autonomous Database, Serverless, Dedicated Exadata Infrastructure, or Cloud@Customer, rather than something available on every Oracle installation, worth knowing before assuming it's available in whatever environment this course's exercises run against.

Three actions matter most for reporting purposes:

-- Show the SQL a natural language question would generate, without running it
SELECT AI showsql how many customers in San Francisco are married;

-- Run the natural language question directly, returning rows
SELECT AI how many customers in San Francisco are married;

-- Run the question and have an LLM describe the result in plain language
SELECT AI narrate what are the top 3 customers in San Francisco;

Select AI works by augmenting the natural language prompt with the target schema's own metadata, table names, column names, and comments, before sending it to the configured LLM, which is what lets it generate schema-accurate SQL rather than a generic guess. Notably, for SQL generation specifically, only that metadata is sent, not the actual row or column values sitting in the tables; the narrate action is the exception, since narrating a result necessarily means sending that result's actual data to the LLM so it has something to describe. The narrate action specifically is aimed at exactly what this module cares about: taking a query's raw result set and turning it into a natural-language summary, the same underlying goal as a formatted report, arrived at through a different route than BREAK and COMPUTE. Oracle's own documentation is direct about the risk worth carrying forward: an LLM-generated query runs against the real database, and its accuracy is the user's responsibility to verify, not a guarantee of the feature itself.

A fourth action, explainsql, sits between showsql and narrate: rather than showing the generated SQL verbatim or narrating a result, it sends that generated SQL back to the LLM specifically to produce a plain-language explanation of what the query itself does, useful for understanding how an answer was derived, not just what the answer is.

These aren't competing solutions to the same problem so much as tools suited to different situations. BREAK and COMPUTE produce exact, deterministic, repeatable output: the same query with the same break and compute commands produces byte-for-byte identical formatting every time it runs, which matters for a report that gets generated on a schedule or that other systems depend on having a stable, predictable shape. Nothing about SQL*Plus reporting depends on an external service being available or a model's interpretation of a prompt; it's pure SQL and client-side formatting, running entirely within whatever session is already connected to the database.

Select AI trades that determinism for accessibility. Someone who doesn't know the schema, or doesn't know SQL at all, can still get an answer, and narrate can turn that answer into prose without anyone writing a single line of report-formatting code. That's a genuinely different value than BREAK/COMPUTE offers, but it comes with the tradeoffs already noted: it requires Autonomous Database specifically, it depends on an external LLM being configured and reachable, and the generated SQL needs the same scrutiny any unfamiliar query would get before being trusted. A recurring, scheduled report with a fixed structure is a natural fit for SQL*Plus; an ad hoc question from someone unfamiliar with the schema is a natural fit for Select AI.

Tuning an individual query means examining its structure, and any subqueries within it, along with the SQL syntax itself, to determine whether the underlying tables are designed to support fast data manipulation and whether the query itself is written in a way that lets the database engine work efficiently. Queries do several different kinds of work: adding new records, updating existing ones, and pulling data out of multiple related tables for reporting. It's this last category people most often mean when they talk about optimizing queries, but a database has to be treated as a whole system, not a collection of isolated queries; speeding up one piece can come at the expense of another.

Some tuning techniques are essentially free; others, adding a new index being the clearest example, come with a real cost. An index can dramatically speed up the query it was built for while simultaneously slowing down every INSERT, UPDATE, or DELETE against that same table, since the index itself has to be maintained on every write. Removing an index carries the same two-sided risk in reverse.

A concrete case makes this less abstract. Suppose a reporting query filters EMP_DETAILS_VIEW by DEPARTMENT_ID constantly, and an index on that column is added specifically to speed it up. That index genuinely helps every SELECT filtering or sorting by department, including the reporting queries built throughout this lesson. But the underlying EMPLOYEES table might also be the target of frequent INSERT statements from an unrelated hiring system, and every one of those inserts now has to update the new index in addition to writing the row itself, a cost that didn't exist before and that has nothing to do with reporting at all. Neither the reporting query nor the hiring system is wrong; they're simply pulling in opposite directions on the same table, and the index's actual cost only becomes visible once both sides of that tradeoff are considered together.

None of this is a reason to avoid making changes; it's a reason to think about a change's effects beyond the immediate query being tuned, to keep an ear out for reports that some other part of the application has gotten slower, and to record what changed so it can be reversed if it turns out to have caused more harm than good elsewhere.

Looking Ahead

This module builds toward the class project using both paths introduced here: hand-built reports using SQL*Plus's BREAK, COMPUTE, and title commands, and, where the environment supports it, natural language queries through Select AI. The tuning considerations covered briefly here become directly relevant once reports start pulling from larger tables or more complex joins, where a poorly structured query's cost becomes very apparent very quickly.

A few points from this lesson worth carrying forward:

• SQL*Plus's reporting commands, COLUMN, BREAK, COMPUTE, TTITLE/BTITLE, are Oracle's native, script-based equivalent to a standalone reporting platform, producing grouped, subtotaled, titled output with nothing beyond the database client itself.
• BREAK can chain multiple grouping levels together, and a hidden NOPRINT column can drive a subtotal without a visible label attached to it.
• A title can change per page automatically, using a hidden NEW_VALUE column to capture the current break value into a substitution variable SQL*Plus re-evaluates on every page.
• Select AI translates natural language into SQL using schema metadata, not table contents, except for narrate, which necessarily sends the query's actual result to the LLM to describe it.
• Select AI is an Autonomous Database feature specifically, not something available on every Oracle installation, and its generated SQL carries the same trust requirements as any unfamiliar query.
• Query tuning has to be evaluated against the whole system a table belongs to, not just the one query being sped up, since the same index that helps one query can measurably slow down every write against that table.