Exercise 1. Getting to know the database using single table SELECT with WHERE clause

1. What databases exist in your environment?

2. Display the columns in the Tree table.

3. Display the first 10 rows of data in the Tree table.

4. Display only Trees where tag numbers are less than 5.

5. Display Tree where tag numbers are between 5 and 10.

6. Display Trees where SpeciesID is 1?

7. Display the columns in the Species table?

8. What is the mnemonic of the Species whose ID is 1?

9. What is its GenusID?

10. What is its Genus Name, Species Name?

11. What family does it belong to?

12. How many individuals of the species exist in the plot?

13. How many individuals are in the first Quadrat?

14. Of these, how many are of SpeciesID 1

15. How many Species in the database? Genera? Families?

16. What is the name of the Plot? How many hectares are there?

17. How many Quadrats?

18. How many times has it been censused?

Exercise 2. Getting to know the relationships joining two tables

1. Display Tree data for treeid = 1

2. How many stems does it have?

3. Use a JOIN to display the Tree tag, StemID and Stem tags for treeid=1

4. Display all the Measurement records for that Tree;

5. Use a JOIN to display the Tag and all the DBH measurements for the first census.

6. Use a join to create a species list with Genus Name and Species Names

Exercise 3. More joins

1. Add a family name to your species list

2. Create an abundance table

Exercise 4. Script writing permanent data

1. Create a script to make a species list. Call it species.sql. Modify the script to add a new species. Use a memory variable to collect the genus id.

2. Update the speciesid on tag XXXXXXX. Create a history record.

3. Create a temporary table temperrors with columns tag,stemtag,dbh.

4. Load a list of dbh corrections from the file dbhcorrections.csv

5. Update the appropriate table in the database making sure that you create history records as you go.

Exercise 4. Script writing screening

1. Compare the tags in tempOldTrees with the tags in the Tree table. Find the tags in tempOld Trees that are not in the Tree table. (hint you need to use LEFT OR RIGHT JOINS)

2. Find the tags in the Tree table that are not in tempOldTrees

3. Modify the script to put these tags into temp tables tempnotintree and tempnotintemp.

Exercise 6. Script writing temp tables

1. Create a script that selects distinct errors from each of the three temp tables tempOldTrees, tempNewPlants and tempMultStems. Save the script as temperrors.sql

2. Make error corrections as needed.

Exercise 7. Script writing populate Personnel tables

1. Create an excel table with a list of people. There should be two columns, first name and last name.

2. Load the table into the Personnel table in your database.