### 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.