Sample TRIM Database Queries

As TRIM now allows the user to write model output to MySQL databases, the user needs to have some basic understanding of how to access these data.  Data stored in databases are accessed using queries.  There are a number of books and websites devoted to writing database queries.  One site that we've found to be particularly useful is http://www.sqlcourse.com.  This is a free site that provides a basic and advanced tutorial on working with MySQL databases.  To supplement the information provided on the web and in the literature, we've provided a few example queries for two of the TRIM databases (TRIM.FaTE and TRIM.Expo-Inhalation).  These example queries can be entered using MySQL in command line mode or using one of the available MySQL database viewers, such as iSQL.  Instructions for installing iSQL are provided in the User Guide.

TRIM.Expo-Inhalation Database Queries

This database is comprised of four tables: RunParams, Counties, Chemicals, and Persons.

Example 1:

SELECT * FROM RunParams;

This query will create a table containing all of the columns and rows (there is one row per run in this table) in the RunParams table.

Example 2:

SELECT Runid, location, scenario FROM RunParams;

This query will create a table containing the Runid, location, and scenario columns for all of the runs in the RunParams table.

Example 3:

SELECT Runid, person, AvgExp FROM Persons WHERE AvgExp > 0.5

This query will create a table containing the Runid, person, and AvgExp columns for all of the rows in the Results table where the AvgExp value is greater than 0.5.

Example 4:

SELECT Runid, person, AvgExp FROM Persons WHERE AvgExp > 0.5 AND Gender = "Female"

This query will create a table containing the Runid, person, and AvgExp columns for all of the rows in the Results table where the AvgExp value is greater than 0.5 and the Gender is female.

Example 5:

SELECT Persons.Runid, Persons.person, Chemicals.pollutant, Persons.AvgExp FROM Persons,Chemicals WHERE Persons.chemical_num = Chemicals.chemical_num AND AvgExp > 0.5 AND Gender = "Female"

This query will create a table containing the Runid, person, and AvgExp columns from the Persons table and the pollutant field from the Chemicals table for all of the rows in the Results table where the AvgExp value is greater than 0.5 and the Gender is female.

TRIM.FaTE Database

This database is comprised of six tables: Results, Variables, Objects, ObjectProps, Chemicals, and ChemProps.

Example 1.

SELECT chemid FROM chemicals

This query would produce a list of the values in the "chemid" field of the Chemicals table.

Example 2.

SELECT Results.value, Chemicals.Name FROM Results,Chemicals,Variables WHERE Results.varid = Variables.varid AND Variables.chemid = Chemicals.chemid AND Results.runid = 1 AND time = '01-01-1987 00:00' AND varid = 12345

This query would select the values from the "value" field and the chemical name associated with each of these values in the Results table where the "runid" field has a value of "1", the "time" field has a value of "01-01-1987 00:00", and the "varid" field has a value of "12345".