All tracks

Foundations

4 modules, 6 hours. Work through them in order: each module lists the ones it needs first.

1. f1 Select and filter

   • Browser
   • 1 hour

   Skills: SELECT, WHERE, ORDER BY, NULL

   • Which animals have no photo?
   • Which mammals does critwire list, by common name?
   • Why doesn't = NULL find a missing value?

2. f2 Group and aggregate

   • Browser
   • 1.5 hours

   Prerequisites: Select and filter (f1)

   Skills: GROUP BY, HAVING, CASE, COUNT(DISTINCT …)

   • How many records does the sample hold per year, and by basis of record?
   • Which species have more than 500 records?
   • How many datasets contributed?

3. f3 Joins

   • Browser
   • 2 hours

   Prerequisites: Group and aggregate (f2)

   Skills: Inner, left and anti joins; many-to-many; reading an ERD; light spatial in plain SQL (bounding boxes, 10° bins)

   • Which animals were recorded in Brazil?
   • Which have no records at all?
   • How many records fall in each 10° box?

4. f4 Window functions

   • Browser
   • 1.5 hours

   Prerequisites: Joins (f3)

   Skills: CTEs, subqueries, window functions

   • Which dataset contributed most of each species' records, and what share is that?
   • How does each year rank against the others?