Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
onlinedb [2022/03/09 11:27]
qcbs [Exercise 3 - SELECT statement]
onlinedb [2022/03/09 11:38]
qcbs [Exercise 4 - GROUPING]
Line 406: Line 406:
  
 **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| 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 ​Ecozone ​(use the avg() operator)?​ +**Question 2** :?: -  What is the average Elevation (mean_ele column) of all lakes in the Montane Cordillera ​Ecozones ​(use the avg() operator)?​ 
-\\ ++answer| 1364.36 ++ \\+\\ ++answer| ​SELECT avg(mean_ele) 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.+Note: the % operator is a wildcard used to replace the beginning or the end of a string ​in a query using LIKE.
 \\ \\
 Select all lake names that contain the word '​Small'​ Select all lake names that contain the word '​Small'​
Line 431: Line 431:
 \\ ++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| 17 (21 have the word '​Daphnia'​ somewhere in their name) ++ \\+\\ ++answer| ​SELECT count(*) FROM species WHERE full_name like '​Daphnia%'; ​17 (21 have the word '​Daphnia'​ somewhere in their name) ++ \\
  
 ===== Exercise 4 - GROUPING ===== ===== Exercise 4 - GROUPING =====
Line 451: Line 451:
  
 **Question 5** :?: -  Which province has the lake with the highest maximum temperature (column tmax_ann)? **Question 5** :?: -  Which province has the lake with the highest maximum temperature (column tmax_ann)?
-\\ ++answer| BRITISH COLUMBIA 14.60 ++ \\+\\ ++answer| ​SELECT province, max(tmax_ann) FROM lakes GROUP BY province ORDER BY max DESC LIMIT 1; 
 +BRITISH COLUMBIA 14.60 ++ \\
  
 Update statement: ​ Update statement: ​