Differences
This shows you the differences between two versions of the page.
| 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] (current) 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: | ||
