Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
onlinedb [2025/04/04 21:31] qcbs [Exercise 3 - Importing data into SQLite in R] |
onlinedb [2025/04/08 18:34] (current) qcbs [Exercise 3 - SELECT statement] |
||
|---|---|---|---|
| Line 344: | Line 344: | ||
| </file> | </file> | ||
| - | You can now load the CSV files in R as data frames | + | Set this to where you have downloaded the files |
| - | + | <file> | |
| - | <file rsplus> | + | |
| - | #Set this to where you have downloaded the files | + | |
| setwd('C:\\User\MyName\Workshop\') | setwd('C:\\User\MyName\Workshop\') | ||
| + | </file> | ||
| + | You can now load the CSV files in R as data frames | ||
| + | <file> | ||
| lakes <- read.csv('lakes.csv') | lakes <- read.csv('lakes.csv') | ||
| lakes_species <- read.csv('lakes_species.csv') | lakes_species <- read.csv('lakes_species.csv') | ||
| Line 357: | Line 358: | ||
| And then load those dataframes into the database | And then load those dataframes into the database | ||
| <file rsplus> | <file rsplus> | ||
| - | dbWriteTable(mydb, "lakes", lakes, overwrite=TRUE) | + | dbWriteTable(mydb, "lakes", lakes) |
| dbWriteTable(mydb, "lakes_species", lakes_species) | dbWriteTable(mydb, "lakes_species", lakes_species) | ||
| - | dbWriteTable(mydb, "species_acro.csv", species_acro) | + | dbWriteTable(mydb, "species_acro", species_acro) |
| dbListTables(mydb) | dbListTables(mydb) | ||
| + | </file> | ||
| + | |||
| + | You can then run most queries below with dbGetQuery. For example: | ||
| + | <file rsplus> | ||
| + | lake_prairies<-dbGetQuery(mydb, "SELECT * FROM lakes WHERE ecozone='Prairies'") | ||
| </file> | </file> | ||
| ===== REFERENCES ===== | ===== REFERENCES ===== | ||
| Line 443: | Line 449: | ||
| **Question 1** :?: - How many lakes in British Columbia receive more than 3000 mm of precipitation (totp_ann column)? | **Question 1** :?: - How many lakes in British Columbia receive more than 3000 mm of precipitation (totp_ann column)? | ||
| \\ ++answer| SELECT count(*) FROM lakes WHERE province='BRITISH COLUMBIA' AND totp_ann>3000; 12 ++ \\ | \\ ++answer| SELECT count(*) FROM lakes WHERE province='BRITISH COLUMBIA' AND totp_ann>3000; 12 ++ \\ | ||
| - | **Question 2** :?: - What is the average Elevation (mean_ele column) of all lakes in the Montane Cordillera Ecozones (use the avg() operator)? | + | **Question 2** :?: - What is the average Elevation (mean_elev column) of all lakes in the Montane Cordillera Ecozones (use the avg() operator)? |
| - | \\ ++answer| SELECT avg(mean_ele) FROM lakes WHERE ecoprov LIKE '%Montane Cordillera'; 1364.36 ++ \\ | + | \\ ++answer| SELECT avg(mean_elev) FROM lakes WHERE ecoprov LIKE '%Montane Cordillera'; 1364.36 ++ \\ |
| \\ | \\ | ||
| Note: the % operator is a wildcard used to replace the beginning or the end of a string in a query using LIKE. | Note: the % operator is a wildcard used to replace the beginning or the end of a string in a query using LIKE. | ||
| Line 467: | Line 473: | ||
| \\ ++answer| SELECT max(latitude) FROM lakes WHERE ecozone like 'Taiga%'; 68.8 ++ \\ | \\ ++answer| SELECT max(latitude) FROM lakes WHERE ecozone like 'Taiga%'; 68.8 ++ \\ | ||
| **Question 4** :?: - How many species of Daphnia are there in the species table? | **Question 4** :?: - How many species of Daphnia are there in the species table? | ||
| - | \\ ++answer| SELECT count(*) FROM species WHERE full_name like 'Daphnia%'; 17 (21 have the word 'Daphnia' somewhere in their name) ++ \\ | + | \\ ++answer| SELECT count(*) FROM species_acro WHERE full_name like 'Daphnia%'; 17 (21 have the word 'Daphnia' somewhere in their name) ++ \\ |
| ===== Exercise 4 - GROUPING ===== | ===== Exercise 4 - GROUPING ===== | ||
| Line 708: | Line 714: | ||
| lakes <- tbl(src, "lakes") # Define lakes table | lakes <- tbl(src, "lakes") # Define lakes table | ||
| lakes_qc<-filter(lakes, province %=% 'QUEBEC') # Select lakes in Quebec | lakes_qc<-filter(lakes, province %=% 'QUEBEC') # Select lakes in Quebec | ||
| - | prov_tmean<-summarise(group_by(lakes, province), mean(tmean_an)) # Mean annual temperature per province | + | prov_tmean<- lakes |> group_by(province) |> summarise(mean(tmean_an)) # Mean annual temperature per province |
| - | prov_tmean=collect(prov_tmean) # Transfer result to standard R data frame | + | prov_tmean <- collect(prov_tmean) # Transfer result to standard R data frame |
| - | lakes_qc2<-tbl(src, sql("SELECT * FROM lakes WHERE province='QUEBEC'")) #Perform any SQL statement | + | lakes_qc2 <- tbl(src, sql("SELECT * FROM lakes WHERE province='QUEBEC'")) #Perform any SQL statement |
| </file> | </file> | ||
