Complex Queries for use against a SQL Engine

Building genuinely complex queries against a real database means drawing on the different clauses and techniques covered across this course. A plain SELECT * FROM ... stops being enough once the data underneath it gets larger and more varied; at that point, the questions become about logical breaks in the information, ways to group it, ways to filter it down to what actually matters. This lesson reviews the main topics covered so far, useful to skim quickly if the concepts are already solid, or to come back to directly while working through the class project.

GROUP BY breaks query results into logical subsets based on column values, and pairs naturally with the aggregate functions covered earlier in this course: COUNT() for counting records, SUM() for totals, AVG() for averages, and MAX()/MIN() for the highest and lowest values in a set. Where GROUP BY finds information about a particular record, aggregation summarizes across many of them at once.

Suppose a Sales table needs subtotaling by state:

SELECT State, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY State;

Every column in the SELECT list here is either the grouped column itself (State) or wrapped in an aggregate function (SUM(SalesAmount)); that pairing is a hard requirement, not a stylistic choice, since Oracle has no way to resolve what an ungrouped, unaggregated column should show once multiple rows have been collapsed into one group. SELECT * alongside a GROUP BY clause fails for exactly this reason, covered in more depth earlier in this course, along with the closely related ORA-00979 error and the NULL-grouping behavior worth remembering here too: a GROUP BY State against data containing a NULL state value produces one group specifically for that NULL, exactly as a real, distinct value would.

GROUP BY also answers questions that don't need aggregation at all. "Which states do club members live in?" doesn't need a full member list, just the distinct states represented:

SELECT State
FROM MemberDetails
GROUP BY State;

SELECT DISTINCT State would answer the identical question here; GROUP BY just happens to work too, since State is both the only selected column and the grouped one. The GROUP BY clause itself always goes after any FROM or WHERE clause in the statement, and every column being grouped by has to appear in its column list, comma-separated the same way a SELECT list would be.

One related clause worth remembering separately: filtering a grouped result down to only the groups meeting some condition, states with total sales over a threshold, for instance, is a job for HAVING, not WHERE. WHERE filters individual rows before grouping ever happens; HAVING filters the groups themselves, after aggregation:

SELECT State, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY State
HAVING SUM(SalesAmount) > 50000;

A view is a stored SELECT statement with a name attached, everything covered in depth earlier in this course, and one of its most practical uses is exactly this kind of reporting scenario: get a query refined and correct once, save it as a view, and reference that view by name from then on instead of reissuing the underlying SELECT every time.

Views serve a handful of recurring purposes for a DBA building them for other users:

1. Structuring data the way a particular user or group of users naturally thinks about it, rather than the way the normalized schema underneath actually stores it.
2. Restricting access, so a given user sees, and sometimes modifies, only what they specifically need.
3. Simplifying further development, since a well-built base view becomes a foundation other views can build on in turn.
4. Summarizing data for reporting, tying related tables together or pulling out just the rows and columns a specific report actually needs.

This matters most in a normalized database specifically, since normalization organizes tables around individual objects and events rather than around how any particular user wants to consume the data, and most users never need to understand that underlying structure at all; the view is what stands between them and it.

Two points about views are worth carrying into the project specifically. A view built on a single table, including that table's primary key, with no aggregation or joins, supports direct writes back to the underlying table; a view built on a join or a GROUP BY, the kind most reporting views end up being, generally doesn't, without an INSTEAD OF trigger explicitly resolving the ambiguity about which base table a given write belongs to. Since the class project is a reporting task, not a data-entry one, this distinction matters less directly, but it's worth keeping straight regardless: a view built to summarize sales by associate is a read-only reporting tool by its very shape, not an oversight.

A subquery, a query nested inside another query, is commonly used in a WHERE clause to filter the outer query's results using a condition that can't be expressed with a simple operator alone. Suppose a Sales table needs to surface only the transactions above the overall average:

SELECT transaction_id, product_id, sales_amount
FROM Sales
WHERE sales_amount > (SELECT AVG(sales_amount) FROM Sales);

The inner query, (SELECT AVG(sales_amount) FROM Sales), runs first, and its single value becomes the threshold the outer query filters against. Nothing about the average needs to be known in advance or hardcoded; it's calculated fresh from the data itself every time the query runs.

Subqueries show up in more than one shape. The IN form checks a column's value against a whole set of results:

SELECT columnNames
FROM tableName1
WHERE value IN (
    SELECT columnName
    FROM tableName2
    WHERE condition
);

A subquery can also stand in as a single computed column value in the outer SELECT list. Written with the standard AS alias, rather than an assignment-style syntax some other database products use:

SELECT (SELECT columnName FROM tableName WHERE condition) AS column1,
       columnNames
FROM tableName
WHERE condition;

A concrete, named example makes the IN form easier to place: given Titles and Publishers tables, "give me all the titles for publishers located in California" is a job for a subquery, not a join, since the actual goal is filtering Titles by a condition that lives entirely on Publishers:

SELECT Title
FROM Titles
WHERE Pub_ID IN (
    SELECT Pub_ID
    FROM Publishers
    WHERE State = 'CA'
);

The result is every title published by a California publisher, with the subquery doing nothing but narrowing the set of qualifying publisher IDs first.

Two related points worth remembering while working through the class project: a NOT IN subquery behaves unexpectedly the moment the subquery's result set contains even one NULL, since comparing anything against an unknown value can never definitively prove NOT IN true, the entire outer query can end up returning nothing at all rather than the rows a plain reading of the SQL would suggest. NOT EXISTS avoids this trap and is generally the safer choice whenever the subquery's column might contain nulls. And a subquery doesn't have to stand alone the way every example above does; a correlated subquery references a column from the outer query inside its own WHERE clause, re-evaluating once per outer row rather than running independently a single time, useful when the filtering condition itself depends on which outer row is currently being evaluated.

DISTINCT removes duplicate values from a result set, useful whenever the same underlying value would otherwise appear once per matching row rather than once overall. Extending the publishers example, listing each qualifying publisher only once instead of once per title:

SELECT Title FROM Titles
WHERE Pub_ID IN (
    SELECT DISTINCT Pub_ID
    FROM Publishers
    WHERE State = 'CA'
);

DISTINCT also stands on its own, independent of any subquery:

SELECT DISTINCT City
FROM Student;

Filtering rows down to a meaningful subset, a select operation in relational terms, works by specifying a condition that's true or false for each row. Finding science students who enrolled in 2011 combines two conditions with AND:

SELECT *
FROM Student
WHERE Degree = 'Science' AND Year = 2011;

Finding both arts and commerce students, but no other degree, uses OR instead:

SELECT *
FROM Student
WHERE Degree = 'Arts' OR Degree = 'Commerce';

One point worth remembering from earlier in this course: a condition like Degree = 'Science' is neither true nor false for a row where Degree is NULL; it's unknown, and SQL only returns rows where a condition evaluates to true. Filtering for Degree = 'Science' and separately for Degree <> 'Science' will together miss every row where Degree was never set at all, since neither condition can be confirmed true against an unknown value. Finding those rows specifically requires WHERE Degree IS NULL, not a comparison operator.

The functions covered throughout this course fall into three broad categories: string functions (extracting substrings, concatenating values, changing case), numeric functions (summarizing, averaging, and calculating based on table values or literals), and date functions (adding intervals to a date, finding the difference between two dates). Date functions in particular tend to produce the natural break points GROUP BY later organizes a report around, a month or a quarter derived from a raw date column, for instance.

A computed-column example ties several of these together, discounting book prices by 10 percent:

SELECT title_id, price,
       0.10 AS "Discount",
       price * (1 - 0.10) AS "New price"
FROM titles;

title_id price Discount New price
-------- ----- -------- ---------
T01      21.99 0.10     19.79
T02      19.95 0.10     17.95
T03      39.95 0.10     35.96
...
T10      NULL  0.10     NULL
T11      7.99  0.10     7.19

T10's result is worth pausing on. Its price is NULL, and NULL * (1 - 0.10) also comes out NULL, since ordinary arithmetic operators propagate NULL rather than skipping over it. This is worth distinguishing clearly from ||, the string concatenation operator covered earlier in this course, which in Oracle specifically treats a NULL operand as if it were an empty string rather than propagating it. The two operators behave differently on purpose: * and + follow standard NULL-propagation, while || is Oracle's specific, well-known exception to that rule. Assuming one implies the other, in either direction, is an easy mistake to make without having seen both demonstrated concretely.

This same three-category breakdown, string, numeric, date, is worth having as a quick mental checklist while planning the class project's query. Turning a raw sale date into a month or quarter for grouping purposes is a date function's job; formatting an associate's name consistently for display is a string function's job; and the running totals and comparisons the project actually asks for are numeric functions layered on top of GROUP BY. None of the three categories does the others' job, and reaching for the wrong one is usually the fastest way to end up with a query that looks close but produces the wrong shape of result.

Oracle requires a FROM clause in every SELECT statement, even one that's only evaluating a literal expression with no real table involved. DUAL, Oracle's built-in one-row, one-column table, exists specifically for this:

SELECT 2 + 3
FROM DUAL;

Other database products solve the identical problem their own way, worth knowing if this course's material or its source material ever gets compared against another engine. DB2 uses a comparable dummy table, SYSIBM.SYSDUMMY1:

SELECT 2 + 3
FROM SYSIBM.SYSDUMMY1;

PostgreSQL doesn't require a FROM clause for a literal expression at all; a plain SELECT 2 + 3; runs on its own. Where PostgreSQL differs is in an older-versions quirk around floating-point arithmetic in a calculation like the discount example above, historically requiring an explicit cast to DECIMAL to avoid floating-point rounding artifacts:

price * CAST((1 - 0.10) AS DECIMAL)

None of this changes anything about how Oracle itself behaves; it's simply useful context for recognizing that a requirement as basic as "does SELECT need a FROM clause" isn't universal across products, the same way DATEDIFF, SUBSTR versus SUBSTRING, and column-alias syntax turned out not to be universal either.

Looking Ahead

Every technique reviewed in this lesson, grouping and aggregation, views, subqueries, DISTINCT, computed columns, is meant to combine in the class project ahead: a listing of sales by sales associate, narrowed to only those who sold the single product with the highest overall quantity sold. None of these techniques solves that problem in isolation; the project is exactly the kind of layered, multi-step query this lesson's review was meant to prepare for.

A few points worth carrying forward from this review specifically:

• GROUP BY requires every selected column to be either grouped or aggregated; SELECT * alongside it is a reliable way to trigger an error, not a shortcut.
• HAVING filters groups after aggregation; WHERE filters rows before it. The two aren't interchangeable, and using one where the other belongs is a common source of confusion.
• A view's read-only or writable status follows directly from its shape, single table and primary key versus join or aggregation, not from any special permission granted to the view itself.
• NOT IN and NULLs are a genuinely dangerous combination; NOT EXISTS is the safer default whenever a subquery's result might contain one.
• Ordinary arithmetic operators propagate NULL; Oracle's || concatenation operator is the specific, well-known exception, not the rule.
• A requirement as basic as whether SELECT needs a FROM clause turns out not to be universal across database products, a useful reminder that assumptions worth double-checking aren't always the obscure ones.