4. Using Structured Query Language

Learning Objectives

1. Store, retrieve, and use data from important data models used in Hydrology such as ArcHydro, NetCDF, and the Observations Data Model (ODM)
2.  Design and use relational databases to organize, store, and manipulate data
3. Query, aggregate, and pivot data using Structured Query Language (SQL), Excel, R, and other software systems

Computer and Data Requirements

1. You will need Microsoft SQL Server Management Studio Express to complete this assignment.  You can download Microsoft SQL Server Express from http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/express.aspx Links to an external site.
2. The data needed for this assignment are stored in a Micrsoft SQL Server database on the class HydroServer.  You can connect using Microsoft SQL Server Management Studio.  Connection information is below:

Server Name:  hydroserver.uwrl.usu.edu
Database Name:  LittleBearRiverODM
User Name: Hydroinformatics
Password: F4ll2013!!

The Problem

You are working on a water quality study in the Little Bear River.  One aspect of this study is to assess water temperature for potential impacts on cold-water fish species. The water quality standards for the State of Utah list the Little Bear River as Class 3A - protected for cold water species of game fish and other cold water aquatic life, including the necessary aquatic organisms in their food chain (see http://www.rules.utah.gov/publicat/code/r317/r317-002.htm).  Given this, state standards require that water temperatures in the river not exceed 20 degrees C to protect aquatic organisms.  Your job is to perform some exploratory data analysis using the water temperature datasets in the Little Bear River ODM database and perform analyses that may identify potential water temperature impairment.  In your analysis, you should write SQL queries on the database to assemble the following:

1. A table listing the period of record for water temperature measurements (e.g., begin and end date), the number of observations, and the overall minimum, maximum, and average values for each site at which quality controlled (QualityControlLevelID = 1) water temperature (VariableID = 36) data have been collected.
2. A table listing the total number of temperature observations, the number of observations greater than the water quality criterion value (i.e., 20 degrees C), and the overall percent exceedence of the water quality criterion value for each site at which quality controlled water temperature data have been collected. 
3. A table for the Little Bear River at Mendon Road (SiteID = 1) listing the percent exceedence of the water quality standard for each month of the year.
4. A table listing the percent exceedence of the water quality standard for each site during the month of July, which is generally a critical period with low flows and elevated temperatures.

At which site(s) was the overall percent exceedence the greatest? At which site(s) is there most likely to be temperature related effects on fish populations? How does the percent exceedence change from month to month at the Mendon Road site?  How does the percent exceedence in August compare to the overall percent exceedence?

Deliverable

Submit a one-page briefing that introduces the problem and addresses the questions above. Provide your tables as appendices to to your one page write up.  Additionally, provide an appendix that lists the SQL queries that you used to generate your tables (indicate which query(ies) go with which tables). In your write up, please discuss what you learned. 

A PDF version of this assignment for printing purposes is here Download here