SQL - Basic Queries

For this and many of the following problems you will create queries that retrieve the relevant information from the Portal small mammal survey database. You can create the SQLite version of this database using the EcoData Retriever by first installing the software and then running:

retriever install sqlite PortalMammals

from the command line. Or you can download an already assembled copy of the database.

You will need to know some details regarding what is in this database in order to answer the questions. For example, you may need to know what species is associated with the two character species ID or you may need to know the units for the individual’s weight. This type of information associated with data is called metadata and the metadata for this dataset is available online at Ecological Archives.

1. Write a query that displays all of the records for all of the fields in the main table. Save it as a view named All Survey Data.
2. We want to generate data for an analysis of body size differences between males and females of each species. We have decided that we can ignore the information related to when and where the individuals were trapped. Create a query that returns all of the necessary information, but nothing else. Save this as Size Differences Among Sexes Data.