Remember to

  • download portal_mammals.sqlite.
  • connect portal_mammals.sqlite to SQLite Manager.
  • display a fully joined version of the Portal data using SELECT * FROM surveys JOIN species ON surveys.species_id = species.species_id JOIN plots ON surveys.plot_id = plots.plot_id;

Why use multiple tables

Basic join

SELECT DISTINCT year, month, day, plot_type 
FROM surveys
JOIN species ON surveys.plot_id = plots.plot_id

Do Exercise 1 - Basic Join.

SELECT year, month, day, genus, species
FROM surveys
JOIN species USING (species_id);

Multi-table join

SELECT year, month, day, genus, species, plot_type
FROM surveys
JOIN species ON surveys.species_id = species.species_id
JOIN plots ON surveys.plot_id = plots.plot_id;

Multi-table join with abbreviations

SELECT surveys.year, surveys.month, surveys.day, species.genus, 
species.species, plots.plot_type
FROM surveys
JOIN species ON surveys.species_id = species.species_id
JOIN plots ON surveys.plot_id = plots.plot_id;
SELECT sv.year, sv.month, sv.day, sp.genus, sp.species, p.plot_type
FROM surveys sv
JOIN species sp  ON sv.species_id = sp.species_id
JOIN plots p ON sv.plot_id = p.plot_id;

Do Exercise 2 - Multi-table Join.

Combining joins with WHERE, ORDER BY, and aggregation

SELECT sp.genus, sp.species, COUNT(*) as number
FROM surveys sv
JOIN species sp  ON sv.species_id = sp.species_id
JOIN plots p ON sv.plot_id = p.plot_id
WHERE p.plot_type = 'Rodent Exclosure'
GROUP BY sp.genus, sp.species
HAVING number > 50
ORDER BY number;

Do Exercise 3 - Filtered Join.