Remember to

  • download portal_mammals.sqlite.
  • open portal_mammals.sqlite in SQLite Manager.
  • make sure the copy you are going to use in class does not have the SpeciesCounts table or view.


portalDB <- "portal_mammals.sqlite"
conn <- dbConnect(SQLite(), portalDB)

Check out database structure

dbListFields(conn, "plots")
dbListFields(conn, "surveys")

Run queries

query <- "SELECT genus, species, COUNT(*)
          FROM surveys JOIN species
          ON surveys.species_ID = species.species_ID
          GROUP BY genus, species;"
species_counts <- dbGetQuery(conn, query)

Do Exercise 1 - Connect and Query.

Write new information to database

Show the original portal_mammals.sqlite in SQLite Manager.

dbWriteTable(conn, "SpeciesCounts", species_counts)

Show SpeciesCounts table in portal_mammals.sqlite.

viewquery <- paste("CREATE VIEW SpeciesCounts AS", query)
dbSendQuery(conn, viewquery)

Show the SpeciesCounts view in portal_mammals.sqlite.

Do Exercise 3 - Export to Database.

Assign remaining exercises.