Take Home Message

Tables

SELECT species, AVG(wgt) as spavgmass
FROM Main
WHERE species IS NOT NULL
GROUP BY species;
SELECT AVG(spavgmass)
FROM (SELECT species, AVG(wgt) as spavgmass
      FROM Main
      WHERE species IS NOT NULL
      GROUP BY species);

Values

SELECT species, COUNT(*)
FROM Main
WHERE species IS NOT NULL
GROUP BY species;
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);
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

SELECT yr, mo, dy, species
FROM Main
WHERE plot NOT IN (SELECT PlotID FROM Plots WHERE PlotTypeAlphaCode = 'CO');

Assign Exercise 5 - Nesting Queries.