← BIT 202 Database Systems
SQL: Queries, Joins and Aggregation
Notes
Selecting and combining records
Structured Query Language, or SQL, retrieves and manipulates relational data. SELECT identifies required expressions, FROM identifies source tables, and WHERE filters individual rows. ORDER BY provides an explicit result order; without it, row order is not guaranteed. An INNER JOIN returns matching rows according to its join condition. A LEFT JOIN retains every left-side row and supplies null values when no right-side match exists. Join using meaningful key relationships and qualify ambiguous column names to avoid accidental combinations and misleading results.
Summaries and query correctness
Aggregate functions summarise multiple rows: SUM adds values, AVG calculates an arithmetic mean, and COUNT measures occurrences. GROUP BY forms groups before aggregation, while HAVING filters the resulting groups. COUNT(*) counts result rows, whereas COUNT(column) excludes null values in that column. With a left join, counting a non-null child identifier avoids reporting one child for an unmatched parent. Joining two independent one-to-many tables can multiply rows and inflate totals, so aggregate each source first when necessary and inspect a small sample before trusting a financial report.
Practical example
A Mombasa mobile money agent records transactions with BranchID and Fee. Branch A has fees of KES 20, 30 and 50, while Branch B has KES 40 and 60. SELECT BranchID, SUM(Fee) FROM Transactions GROUP BY BranchID returns KES 100 for each branch. Adding HAVING SUM(Fee) > 100 returns no branches because equality does not satisfy the condition. A WHERE Fee >= 40 filter applied before grouping instead leaves A with KES 50 and B with KES 100. This difference demonstrates why row filters and group filters answer different management questions.
Quiz
1. Which clause filters groups after aggregation?
2. Why count a child identifier instead of COUNT(*) after a left join?
3. With fees 20, 30 and 50, what sum remains after WHERE Fee >= 40?
Sign in to submit and securely grade your quiz.