Take Home Message

  • Basically, any object or value following a query command statement in SQL (including tables, conditions, and even values) can be replaced with another query.

Tables

  • What is the mass of the average species at the site?
    • Determine the average mass of the individuals in each species.
    • Take the average of the average masses of each species.
  • Write a query to determine the average mass of each species:
SELECT species, AVG(wgt) as spavgmass
FROM Main
WHERE species IS NOT NULL
GROUP BY species;
  • Use the results of that query as the table for another query that takes the average of those values:
SELECT AVG(spavgmass)
FROM (SELECT species, AVG(wgt) as spavgmass
      FROM Main
      WHERE species IS NOT NULL
      GROUP BY species);

Values

  • What is the relative abundance of the different species at the site?
    • Count how many individuals there are per species.
    • Divide the count per species by the total number of individuals.
  • Write a query to determine the number of individuals per species:
SELECT species, COUNT(*)
FROM Main
WHERE species IS NOT NULL
GROUP BY species;
  • Modify the query with a subquery to divide by the total number of individuals:
SELECT species, COUNT(*)/(SELECT COUNT(*) FROM Main);
SELECT species, COUNT(*) * 100.0/(SELECT COUNT(*) FROM Main);
SELECT species, COUNT(*) * 100.0/(SELECT COUNT(*) 
                                  FROM Main 
                                  WHERE species IS NOT NULL);
  • This finished version of the query even sorts based on the results of the subquery:
SELECT species, COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Main)
FROM Main
WHERE species IS NOT NULL
GROUP BY species
ORDER BY COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Main) DESC;

Conditions

  • List the sampling events for all plots that are not control plots:
SELECT yr, mo, dy, species
FROM Main
WHERE plot NOT IN (SELECT PlotID FROM Plots WHERE PlotTypeAlphaCode = 'CO');

Assign Exercise 5 - Nesting Queries.