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.