SQL Report Course Project

Put yourself in the shoes of the database programmer. Your manager has asked for a listing of sales by sales associate, narrowed to only the associates who sold the single product with the highest overall quantity sold. This lesson walks through building that report from scratch, the same way an actual assignment like this would get approached: check whether the available data can even answer the question, then build the query up one layer at a time.

This is also the capstone of the module, and in a real sense of the course: every technique introduced across the previous four modules, joins, aggregation, subqueries, views, and the reporting formatting covered in this module's first lesson, gets applied together here rather than demonstrated in isolation. A request phrased in plain English from a manager, with no SQL in it at all, is exactly what all of that technique was for.

Before writing any SQL, it's worth checking whether the tables on hand actually contain what the question needs. The exercise provides five tables: Customer, OrderHeader, Associate, SalesDetail, and Inventory.

The report asks for two things: sales figures, attributed to each associate, and a filter down to only the associates who sold the best-selling item. Tracing what each table actually contributes:

• SalesDetail holds ItemQty and ItemPrice per order line, exactly what's needed to compute both a quantity total and a revenue total.
• OrderHeader links each order to the AssociateID who made it, the connection between a sale and the person who sold it.
• Associate supplies the actual names to display, rather than reporting on bare ID numbers.
• Customer and Inventory aren't actually load-bearing for this specific report. Customer details matter for plenty of other reports, but nothing here needs an address or a city; Inventory's vendor and description fields are similarly irrelevant to a report about sales performance by associate.

This kind of table-by-table trace is worth doing explicitly rather than skipping straight to writing joins, since it's the step that actually determines how many tables the finished query needs to touch. Getting it wrong in either direction causes real problems: missing a table that's genuinely needed means discovering partway through writing the query that some required piece of data simply isn't reachable, while including a table that isn't needed means carrying extra join complexity, and extra risk of introducing a duplicate row or a missed match, for columns the report was never going to display in the first place.

So yes, the schema supports the scenario, but only three of the five tables are actually doing work in the final query. Recognizing which tables are relevant and which aren't is itself part of the job; including Customer or Inventory in this report wouldn't just be unnecessary, it would add join complexity with nothing to show for it.

It's worth seeing how quickly that answer would change under a slightly different request. If the manager instead asked "which states are our top-selling associates' customers concentrated in," Customer would immediately become load-bearing, since CompanyState only exists on that table, and the query would need a fourth join to reach it. The schema-assessment step isn't a one-time check to run through mechanically; it's something to redo every time the actual question changes, since a table that's dead weight for one report can be the whole point of the next one.

The filtering half of this problem, identifying which associates qualify, was covered in the course project exercise: find the item with the highest total quantity sold, find the orders that included it, then find the associates behind those orders. That gives a list of qualifying AssociateID values, but a bare list of IDs, or even names, isn't a sales report; it never actually reports any sales.

Closing that gap means joining the three tables that matter and aggregating actual sales figures, filtered down to the same qualifying associates:

SELECT a.AssociateID, a.AssociateFirstName, a.AssociateLastName,
       SUM(sd.ItemQty) AS TotalQtySold,
       SUM(sd.ItemQty * sd.ItemPrice) AS TotalRevenue
FROM Associate a
JOIN OrderHeader oh ON a.AssociateID = oh.AssociateID
JOIN SalesDetail sd ON oh.OrderID = sd.OrderID
WHERE a.AssociateID IN (
    SELECT AssociateID
    FROM OrderHeader
    WHERE OrderID IN (
        SELECT DISTINCT OrderID
        FROM SalesDetail
        WHERE ItemID IN (
            SELECT ItemID
            FROM SalesDetail
            GROUP BY ItemID
            HAVING SUM(ItemQty) = (
                SELECT MAX(TotalQty)
                FROM (
                    SELECT SUM(ItemQty) AS TotalQty
                    FROM SalesDetail
                    GROUP BY ItemID
                )
            )
        )
    )
)
GROUP BY a.AssociateID, a.AssociateFirstName, a.AssociateLastName;

This now genuinely answers both halves of the manager's request in one statement: the WHERE ... IN clause is the same qualifying-associate logic already built in the exercise, and the join across Associate, OrderHeader, and SalesDetail, aggregated with GROUP BY, is what actually produces sales totals rather than just names. TotalQtySold answers "how much did they sell," and TotalRevenue answers the closely related "how much did that amount to," two different but both reasonable readings of "based on what" a report about sales performance should actually measure.

Running this against a small set of sample data makes the shape of the result concrete:

ASSOCIATEID FIRSTNAME  LASTNAME   TOTALQTYSOLD TOTALREVENUE
----------- ---------- ---------- ------------ ------------
2004        Priya      Nair               48         2160.00
2011        Marcus     Webb               48         2304.00

Two associates qualify here, both having sold the same best-selling item, and the query correctly returns a row for each, with their own totals rather than a single combined figure. Nothing about the qualifying-associate subquery collapses multiple qualifying rows into one; each associate who meets the condition gets their own line, exactly what "a listing of sales by sales associate" actually asks for.

Wrapping this as a view, exactly as the exercise already demonstrated, means this entire query only needs to be built once:

CREATE VIEW TopAssociateSales AS
SELECT a.AssociateID, a.AssociateFirstName, a.AssociateLastName,
       SUM(sd.ItemQty) AS TotalQtySold,
       SUM(sd.ItemQty * sd.ItemPrice) AS TotalRevenue
FROM Associate a
JOIN OrderHeader oh ON a.AssociateID = oh.AssociateID
JOIN SalesDetail sd ON oh.OrderID = sd.OrderID
WHERE a.AssociateID IN (
    SELECT AssociateID FROM OrderHeader WHERE OrderID IN (
        SELECT DISTINCT OrderID FROM SalesDetail WHERE ItemID IN (
            SELECT ItemID FROM SalesDetail GROUP BY ItemID
            HAVING SUM(ItemQty) = (
                SELECT MAX(TotalQty) FROM (
                    SELECT SUM(ItemQty) AS TotalQty
                    FROM SalesDetail GROUP BY ItemID
                )
            )
        )
    )
)
GROUP BY a.AssociateID, a.AssociateFirstName, a.AssociateLastName;

This is exactly the point where the SQL*Plus reporting toolkit introduced earlier in this module becomes directly useful, rather than a separate, disconnected topic. Querying the finished view and adding formatting turns a correct result set into an actual titled, subtotaled report:

COLUMN TotalRevenue FORMAT $999,999.99
BREAK ON REPORT
COMPUTE SUM LABEL 'Grand Total' OF TotalQtySold TotalRevenue ON REPORT
TTITLE LEFT 'Top Associate Sales Report'

SELECT * FROM TopAssociateSales
ORDER BY TotalRevenue DESC;

Against the two-associate result shown earlier, this produces something closer to an actual deliverable rather than a raw query result:

Top Associate Sales Report

ASSOCIATEID FIRSTNAME  LASTNAME   TOTALQTYSOLD TOTALREVENUE
----------- ---------- ---------- ------------ ------------
2011        Marcus     Webb               48     $2,304.00
2004        Priya      Nair               48     $2,160.00
                                    ------------ ------------
Grand Total                                96     $4,464.00

None of this required anything beyond what this module already covered: BREAK, COMPUTE, and TTITLE from the first lesson, applied here to the view built specifically for the course project. ORDER BY TotalRevenue DESC is doing real work here too, not just cosmetic sorting; presenting the higher-revenue associate first is a small choice, but it's the kind of choice that makes a report actually easy to read at a glance, which was the entire premise this module opened with.

If more than two associates ever qualify, BREAK ON AssociateID could introduce a subtotal per associate before the report-wide grand total, though with each associate already appearing on exactly one row here, since the query is grouped by associate already, a per-associate break wouldn't add anything a plain row listing doesn't already show. The report-level COMPUTE, providing one grand total across every qualifying associate, is the genuinely useful piece for this particular shape of result.

A handful of errors account for most of the trouble building a project like this one.

Filtering before checking what the schema can actually answer. Writing the qualifying-associate subquery first and only later noticing the report never mentions a single sales figure, exactly what happened in the exercise's own first attempt, is a natural trap. Confirming what the finished report actually needs to display, before writing the first line of SQL, would have caught that gap immediately.

Reintroducing the nested-aggregate error from the exercise. SUM(sd.ItemQty * sd.ItemPrice) is one aggregate function wrapped around one expression; it's not the same shape as the illegal MAX(SUM(...)) corrected earlier, but it's worth double-checking that any new aggregate added while extending this query stays that same one-layer-deep shape, rather than accidentally nesting a second aggregate inside it.

Joining tables that don't need to be there. As covered above with Customer and Inventory, adding a join "just in case" costs real query complexity for a specific report that has no use for the columns it would introduce. Every join in the finished query should be traceable to a column that report actually displays or filters on.

Forgetting that a view's GROUP BY makes it read-only. This particular view, built on a join and an aggregate, is a reporting tool by its very structure, not something anyone would expect to write through. That's fine here, since the whole point is reading a report, but it's worth remembering as a general rule rather than being surprised by it on a future project with different requirements.

One older strand of relational theory objects to the term "materialized view" specifically, arguing that a view, by definition, is never physically stored, so a "materialized" one is a contradiction in terms, and that the correct word for a stored, refreshable result is a snapshot. This is a genuine position with real academic history behind it, associated particularly with C.J. Date's writing on relational theory.

It's not, however, how Oracle's own documentation uses the term, and it's not how this course has used it either. Oracle's official terminology, used consistently since materialized views were introduced earlier in this course, is exactly "materialized view": a view-like object with its own physical storage, refreshed on a schedule, distinct from a regular view that has no storage of its own. That's the terminology worth using going forward, both because it's what Oracle's actual documentation calls the feature and because switching vocabulary mid-course would contradict everything already established. The theoretical objection is worth knowing exists, but it describes a preference some relational theorists hold, not the term this course, or Oracle, actually uses.

The distinction is worth restating plainly, since TopAssociateSales itself is a good example of the difference. It's a regular view, exactly the kind of view this course has covered throughout, recomputed fresh every time it's queried, with no data physically stored anywhere except the query text that defines it. Had the goal instead been to freeze this report's results at a specific point in time and refresh them only periodically, a materialized view, not a regular one, would have been the right tool, and that decision would have nothing to do with which term is more theoretically pure and everything to do with whether the report genuinely needs to reflect live data or a periodic snapshot of it.

Looking Back

The path from "list our best sales associates" to a finished, titled report retraces most of what this course covered: identifying which tables in a real schema actually matter for a given question, filtering with nested subqueries the same way WORKS_ON1 and the California-publishers examples did earlier, aggregating with GROUP BY and SUM the same way the very first grouping lessons did, wrapping the result in a view so the work never has to be repeated, and finally formatting that view's output using the SQL*Plus commands introduced at the start of this module. None of these pieces is new by this point; what's new is seeing all of them asked for at once, in the same loose, informal phrasing a real manager would actually use, with no hint in the request itself about which specific SQL techniques would end up being needed.

That gap, between a plain-language business question and the layered SQL that actually answers it, is the skill this whole course has been building toward, and it's worth recognizing that the hardest part of this project was never the syntax. Every individual piece, the subquery, the join, the aggregate, the view, the report formatting, was already covered well before this lesson. The genuinely new work was recognizing which pieces the question actually required, in which order, and confirming along the way that the data on hand could support the answer at all.

Complete the exercise below to build this report yourself, step by step.
Course Project - Exercise