DATA2001 · Data Science, Big Data and Data Variety
Declarative Data Analysis with SQL
Week 3 treats SQL as a declarative language for data analysis — you describe WHAT to retrieve, not how — building from filtering and joins up to set operations, nested subqueries, aggregation and grouping. This is the single most heavily assessed skill in DATA2001: it is the whole content of the Week 7 SQL Challenge (a 1-hour 'write SQL' task on Ed) and it recurs across the midterm and the 50% final exam.
What this chapter covers
- 01Set operations UNION / INTERSECT / EXCEPT (auto-dedup) and their ALL multiset versions (m+n, min(m,n), max(0,m-n))
- 02Nested/subqueries and set-comparison operators: IN, EXISTS, op ALL, op SOME/ANY, and correlated subqueries
- 03Aggregate functions COUNT, SUM, AVG, MAX, MIN and the effect of DISTINCT
- 04GROUP BY partitions rows; every non-aggregated SELECT/HAVING attribute must appear in GROUP BY
- 05HAVING filters groups after aggregation vs WHERE filters rows before grouping
- 06Conceptual clause evaluation order: FROM to WHERE to GROUP BY to HAVING to SELECT to ORDER BY
- 07Join varieties (inner, natural, theta, equi, left/right/full outer) as declarative building blocks for analysis
GROUP BY with HAVING on a small enrolment table
- +1Write the query: SELECT ucode, AVG(mark) FROM Enrolled GROUP BY ucode HAVING AVG(mark) > 60. The average is a group-level test, so it must go in HAVING, not WHERE.
- +1GROUP BY ucode forms three groups: DATA2001 {72, 55, 80}, INFO2222 {40, 58}, COMP2123 {85}.
- +1Compute each group's AVG: DATA2001 = (72 + 55 + 80) ÷ 3 = 207 ÷ 3 = 69; INFO2222 = (40 + 58) ÷ 2 = 98 ÷ 2 = 49; COMP2123 = 85 ÷ 1 = 85.
- +1Apply HAVING AVG(mark) > 60: DATA2001 (69) passes, INFO2222 (49) fails, COMP2123 (85) passes. The result is the two surviving groups.
Key terms
- Declarative query
- A query that specifies WHAT result you want, leaving the DBMS to decide how to compute it. SQL is declarative: you state the tables, conditions and grouping, and the query optimiser plans the execution.
- Set operation vs ALL variant
- UNION, INTERSECT and EXCEPT correspond to the relational operators and eliminate duplicates. The ALL versions keep duplicates: if a tuple occurs m times in R and n in S, UNION ALL gives m+n, INTERSECT ALL gives min(m,n), and EXCEPT ALL gives max(0, m-n).
- Correlated subquery
- A subquery that references a column from the outer query, so it is re-evaluated per outer row — often used with EXISTS or to compare a value against a per-group maximum (e.g. the top-mark student in each unit).
- Aggregate function
- COUNT, SUM, AVG, MAX or MIN collapse many rows into one value. All except COUNT operate on a single attribute and (unless DISTINCT is given) include duplicate values; COUNT(*) counts rows, COUNT(DISTINCT x) counts distinct non-null values.
- GROUP BY rule
- GROUP BY partitions the rows into groups by one or more attributes. Every attribute in SELECT or HAVING must be either inside an aggregate or listed in GROUP BY, because each output row must have a single value per column.
- HAVING vs WHERE
- WHERE filters individual rows before grouping; HAVING filters whole groups after aggregation using aggregate predicates. In the conceptual order FROM to WHERE to GROUP BY to HAVING to SELECT to ORDER BY, WHERE precedes grouping and HAVING follows it.
Declarative Data Analysis with SQL FAQ
What is the difference between WHERE and HAVING?
WHERE filters rows before any grouping happens, so it can only reference individual-row columns. HAVING filters groups after GROUP BY has aggregated them, so it can reference aggregates like AVG(mark) or COUNT(*). A query can use both: WHERE to keep only the rows of interest, then GROUP BY, then HAVING to keep only the groups that meet an aggregate condition.
How do the ALL (multiset) set operations count duplicates?
If a tuple appears m times in R and n times in S, then R UNION ALL S has m+n copies, R INTERSECT ALL S has min(m, n) copies, and R EXCEPT ALL S has max(0, m-n) copies. The plain UNION/INTERSECT/EXCEPT first remove duplicates from each input and produce a duplicate-free result. Knowing this counting rule is a favourite short-answer question.
How do I write 'students taking both unit A and unit B'?
Two clean idioms give the same answer. One uses a set operation inside a subquery: WHERE sid IN (SELECT sid FROM Enrolled WHERE ucode='A' INTERSECT SELECT sid FROM Enrolled WHERE ucode='B'). The other self-joins or uses two IN/EXISTS subqueries. Watch for duplicate rows in the subquery version and add DISTINCT if needed. The 'both / only one' pattern (COUNT(*) = 1 vs COUNT(DISTINCT ucode) = 1) is a recurring exam theme.
Can AI help me practise SQL for the SQL Challenge?
Yes, as a tutor. Sia can set you write-SQL problems in the same style as the Week 7 Ed task, explain the six-step clause evaluation order, and check your joins, grouping and HAVING conditions line by line. Because the SQL Challenge and exam are graded, Sia is for practice and understanding only — it will not sit the assessment for you, and the University of Sydney academic-integrity policy applies.
Exam move
SQL is worth more marks than any other single skill in DATA2001, so drill it on a live PostgreSQL database rather than on paper. Build a ladder of queries on a tutorial schema: simple SELECT-WHERE, then joins across two and three tables, then GROUP BY with HAVING, then set operations and nested subqueries. For every query, narrate the conceptual evaluation order (FROM to WHERE to GROUP BY to HAVING to SELECT to ORDER BY) so you place each condition in the right clause. Memorise the multiset counting rules and the 'taught both / only one' patterns, since they appear as short-answer questions. The 1-hour SQL Challenge in Week 7 is closed-book and timed, so rehearse writing correct SQL quickly from a schema you have not seen before. Ask Sia to generate fresh write-SQL drills and mark your working; confirm the SQL Challenge format on Canvas.
Working through Declarative Data Analysis with SQL in DATA2001? Sia is AskSia’s AI Data Science tutor — ask any DATA2001 Declarative Data Analysis with SQL question and get a clear, step-by-step explanation grounded in how DATA2001 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
