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
Last revision Both sides next revision
onlinedb [2022/03/09 11:27]
qcbs [Exercise 3 - SELECT statement]
onlinedb [2022/03/09 11:35]
qcbs [Exercise 3 - SELECT statement]
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 =====