Learning Objectives

Following this session students should be able to:

• describe the advantages of relational databases
• write basic queries including aggregation and join statements
• set up a local MySQL database and access it through a client
• add a table, and create a view in the MySQL database
• understand the data -> MySQL -> R workflow

Reading

• Tidy data

Lecture Notes

1. Configure MySQL Workbench
2. Discussion: why (or why not) SQL?
3. Configure local MAMP database - your own data server!
4. Import the Bee Lab database to your local MySQL
5. SQL queries revisited
   • Basic queries
   • Aggregation
   • Joins
6. Exercise 1: join & aggregate
7. Datatypes and table structure in MySQL
8. Views, Constraints, and Indexes
9. Creating a table from CSV
10. Transactions & MySQL Dump files
11. Full circle: query local db from R

---

Exercises

1. -- Aggregated Join --

   Using the BeeLab database, write a query that displays the total number of bee individuals sampled on each soil type. Save this query file as individuals_per_soil_type.

   [click here for output]