Programming for Biologists

Teaching biologists the tools they need to use computers to do cool science

Creative Commons License

SQL for Access Users

Side by Side Demonstrations

Selecting

SELECT day, month, year, species FROM surveys;

SELECT * FROM surveys;

SELECT DISTINCT year FROM surveys;

Filtering

SELECT * FROM surveys WHERE species="DM";

SELECT * FROM surveys WHERE (year >= 2000) AND (species = "DM");

Sorting

SELECT * FROM species ORDER BY Genus ASC;
SELECT * FROM species ORDER BY Genus DESC;
SELECT * FROM species ORDER BY genus ASC, species ASC;

Aggregation

Get the combined mass of all individuals in each year.

SELECT year, SUM(wgt)
FROM surveys
GROUP BY year;

Joins

SELECT surveys.year, surveys.month, surveys.day, species.genus, species.species
FROM surveys
JOIN species ON surveys.species = species.species_id

Combining it all

SELECT surveys.year, species.genus, species.species, SUM(wgt / 1000.0)
FROM surveys
JOIN species ON surveys.species = species.species_id
WHERE species.taxa="Rodent"
GROUP BY surveys.year, species.genus, species.species