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/08 13:45] qcbs [Exercise 3 - Importing data into SQLite in R] |
onlinedb [2025/04/08 18:34] (current) qcbs [Exercise 3 - SELECT statement] |
||
|---|---|---|---|
| Line 358: | 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", species_acro) | dbWriteTable(mydb, "species_acro", species_acro) | ||
| Line 449: | 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 473: | 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 714: | 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<- lakes |> group_by(lakes, province) |> summarise(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> | ||
