MoL Chapter 8 Lab

From SQLZOO
Revision as of 14:49, 22 December 2015 by Andr3w (talk | contribs)
Jump to: navigation, search
List all details from the weather table for stornoway for 1887 so that you can see the NULL value
SELECT *
  FROM weather
 WHERE station='stornoway' AND yr=1892
SELECT *
  FROM weather
 WHERE station='stornoway' AND yr=1892
What output do you expect from this query? Notice that some values are missing in the tmin column for this year and station.
+-----------+------+------+------+------+------+-------+------+
| station   | yr   | mnth | tmax | tmin | af   | rain  | sun  |
+-----------+------+------+------+------+------+-------+------+
| stornoway | 1892 |    1 |  5.4 | NULL |   15 |  79.9 | NULL |
| stornoway | 1892 |    2 |  5.7 | NULL |   13 |  50.4 | NULL |
| stornoway | 1892 |    3 |  5.5 | NULL |   18 |  30.9 | NULL |
| stornoway | 1892 |    4 |  9.1 |  1.2 |    9 |    29 | NULL |
| stornoway | 1892 |    5 | 11.6 |  4.7 |    1 |  87.3 | NULL |
| stornoway | 1892 |    6 | 13.5 |  6.3 |    0 |  50.5 | NULL |
| stornoway | 1892 |    7 | 14.4 |  8.5 |    0 |  48.3 | NULL |
| stornoway | 1892 |    8 | 14.6 |    9 |    0 | 103.2 | NULL |
| stornoway | 1892 |    9 | 12.6 |  6.1 |    0 | 114.8 | NULL |
| stornoway | 1892 |   10 |  8.6 |    3 |    3 | 110.6 | NULL |
| stornoway | 1892 |   11 |  9.3 |  3.6 |    3 | 146.4 | NULL |
| stornoway | 1892 |   12 |  5.3 |  0.4 |   14 |  64.8 | NULL |
+-----------+------+------+------+------+------+-------+------+
SELECT COUNT(tmin), COUNT(tmax)
  FROM weather
 WHERE station='stornoway' AND yr=1892
SELECT COUNT(tmin), COUNT(tmax)
  FROM weather
 WHERE station='stornoway' AND yr=1892
List all details from the weather table for stornoway for 1887 so that you can see the NULL value
SELECT yr,AVG(tmin),COUNT(tmin)
  FROM weather
 WHERE station='stornoway' AND yr=1900
GROUP BY yr
SELECT *
  FROM weather
 WHERE station='stornoway' AND yr=1892