Changing and summarising data

Changing and deleting rows safely, and turning many rows into counts, totals and averages, per group.

About 5 hours of study

Learning objectives

• Update and delete rows with the right WHERE
• Use aggregate functions
• Group rows with GROUP BY
• Filter groups with HAVING

Lessons

1. 1

   Changing and deleting rows

   UPDATE changes values and DELETE removes rows, both only where the WHERE condition holds. Without WHERE they touch every row.

2. 2

   Counting and totals: GROUP BY and HAVING

   COUNT, SUM, AVG, MIN and MAX sum up many rows; GROUP BY gives one result row per group; HAVING keeps only some groups.

Module practice

• Restock dayEasy
• A pay rise and a clear-outMedium
• Archive, then deleteHard
• Statistics for the whole classEasy
• Department summaryMedium
• Big customersMedium
• The months that reached 1000 takaHard

‹ Tables and basic queriesKeys and joins ›