Week 15: SQL

See the bottom of this page for this weeks Challenge. The start page for the DataCarpentry lesson is here.

Preparation

This week's videos and exercises are based on educational materials from DataCarpentry. Create a directory to hold all your work for this week. We will also use this directory next week. It doesn't matter what it is called; I called mine learn_sql so that's what you'll see in the videos. The data files needed for the challenges can be downloaded here. You want the CSV files surveys.csv, plots.csv and species.csv. I put these files in a subdirectory called data.

Introduction to SQL

• Install SQLite Manager for Firefox
• Reading for introduction. This has a bit of motivational text and instructions for creating the database by importing the CSV files.
• Short video demonstrating data import

Basic Queries

• What is a Query?
• More complex queries
• Reading and challenges
• Video with challenge solutions in case you get stuck! The final challenge is not here; it is part of this week's assignment.

Aggregating data

• Counting and grouping rows
• Ordering and filtering aggregated results, and saving queries
• Reading and challenges
• Video with challenge solutions in case you get stuck! The final challenge is not here; it is part of this week's assignment.

Joining tables

Warning! The second challenge on this page is hard. Don't wrestle with it too long before looking at the solution video. Here's a hint: a query saved as a view can be used in the FROM clause of another query.

• Joining tables
• Reading and challenges
• Joining tables challenge solutions

Extra practice (optional!)

• Downloading and using a database from the command line
• Extra readings and challenges

Assignment

There are 3 challenges to turn in this week. Although I am not requiring you to turn in most of the challenges in the readings, I recommend that you attempt them to practice.

1. Challenge at the bottom of the Basic Queries page.
2. Challenge at the bottom of the SQL Aggregation page.
3. Write a query that finds the average weight of each rodent species (i.e., only include species with Rodent in the taxa field). You will need to join the surveys and species tables. This challenge is in the middle of the joins and aliases page

Copy and paste the SQL statement for each challenge into a text file saved as week15_yourlastname.txt and attach that file to your submission. This Assignment is due on Friday of Week 15 (April 22) at 5 pm. Late assignments will receive a score of zero unless prior approval is granted.