ISYS2120 Chap.3 SQL Query Foundations
SQL Query Foundations
SQL Query Foundations builds a result relation clause by clause. SELECT specifies returned columns or expressions, FROM identifies source tables, and WHERE keeps only rows for which its predicate is true; ORDER BY affects presentation rather than membership.
The chapter then confronts the behaviours that make a plausible query wrong: SQL retains duplicates unless DISTINCT is requested, NULL produces unknown comparisons, and listing several tables creates a Cartesian product until a join predicate connects their rows. Aliases make self-joins and qualified column references readable.
Because the SQL tasks and online quiz execute queries against hidden data, every method here ends with a predicted result table and a boundary case, while examination practice requires writing the same logic clearly without relying on trial-and-error output.
What this chapter covers
- 01
Translate a request into query roles
- 02
Project only required columns
- 03
Filter rows with predicates
- 04
Treat NULL as unknown
- 05
Qualify ambiguous column names
- 06
Build an inner join from meaning
- 07
Distinguish duplicates from repeated facts
- 08
Compute derived output safely
- 09
Aggregate the right population
- 10
Group before comparing summaries
- 11
Test a query with diagnostic data
- 12
Read SQL as a logical pipeline
- 13
Explain an SQL result without the software
Filter trips and compute spare seats
- +1Use FROM Trip and write both required predicates: depart_date >= '2026-09-10' and capacity > booked.
- +1Select trip_id and the expression capacity - booked AS spare_seats so the result exposes the requested quantity.
- +1Apply the conjunction: T1 fails the date; T3 has no spare seat; T2 and T4 satisfy both conditions.
- +1Order by trip_id and compare the actual output with the two predicted rows before accepting the query.
Key terms
- SELECT clause
- The clause listing the columns and expressions returned by a query. Its output becomes the attributes of the result relation.
- FROM clause
- The clause naming the source relations. Several sources initially contribute combinations of rows that later conditions must connect.
- WHERE clause
- The clause whose logical expression controls which source rows enter the result. Only rows for which the predicate is true are retained.
- DISTINCT
- A SELECT modifier that eliminates duplicate result rows. Without it, SQL normally preserves duplicates.
- Target list
- The ordered list of attributes or computed expressions requested in SELECT, which determines the result's column structure.
- Alias
- A temporary name assigned to a relation, column or expression. Relation aliases distinguish separate roles when one table is used more than once.
- Predicate
- A logical condition evaluated for a candidate row, such as lecturer = 1011 AND credit_points > 4.
- Cartesian product
- Every possible pairing of rows from two source relations. It is produced by an unconnected multi-table FROM clause and is rarely the intended final answer.
- Equi-join
- A join whose condition uses equality between values in common-named columns; both matched columns appear redundantly in the result.
- Natural join
- A join that matches all same-named columns and keeps one copy of each matched column in the result.
- ORDER BY
- The clause that sorts a query result by specified expressions, ascending by default or descending when DESC is stated.
SQL Query Foundations FAQ
What is the fastest way to plan a SELECT query?
Write the required output columns, identify the relations that supply them, state the row connections, and then list the filtering conditions. This separates projection, joins and selection before clause syntax distracts from meaning.
Why does an omitted join predicate create too many rows?
Without the matching condition, FROM can combine each row from one input with every row from the other. The accidental Cartesian product multiplies facts, distorts aggregates and may still look credible on tiny data.
When should DISTINCT appear in a query?
Use DISTINCT only when the information need asks for unique projected values and duplicate result rows are semantically redundant. It should not conceal a faulty join or compensate for misunderstanding the multiplicity of underlying facts.
How do WHERE and HAVING divide the filtering work?
WHERE filters candidate rows before grouping, so it cannot normally test a group aggregate. HAVING filters completed groups after GROUP BY and aggregation. Place each condition at the stage whose population it describes.
Which test rows reveal SQL mistakes most effectively?
Include a matching row, an unmatched row, repeated projected values, a boundary value, a NULL where permitted and an empty group case. Predict intermediate rows by hand so the first divergence identifies the faulty clause.
Exam move
Practise SQL by predicting tables, not by guessing syntax until Ed accepts it. For each request, write the output columns first, identify the source relations, then translate every inclusion rule into a WHERE predicate. On a four-row sample, mark which rows survive and compute the exact target-list values before running the statement.
Maintain separate drills for duplicates, NULL and joins: compare SELECT with SELECT DISTINCT, test IS NULL against = NULL, and count the Cartesian-product rows before adding a join condition. Use aliases whenever one relation plays two roles, and qualify any ambiguous attribute. For SQL tasks and the online quiz, compare your predicted and actual tables at the first clause where they differ.
For the handwritten final examination, rehearse complete queries on paper, including quotes, parentheses and ORDER BY, then explain in one sentence why every returned row satisfies the request and every excluded boundary row fails it.
Working through SQL Query Foundations in ISYS2120? Sia is AskSia’s AI Information Technology tutor — ask any ISYS2120 SQL Query Foundations question and get a clear, step-by-step explanation grounded in how ISYS2120 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
