MoL Chapter 8 Lab

From SQLZOO
Revision as of 12:13, 23 December 2015 by Andr3w (talk | contribs)
Jump to: navigation, search
List all details from the weather table for dunstaffnage for 1974 so that you can see the NULL value
SELECT *
  FROM weather
 WHERE station='dunstaffnage' AND yr=1974
SELECT *
  FROM weather
 WHERE station='dunstaffnage' AND yr=1974
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 | tmin | tmax |
+--------------+------+------+------+------+
| dunstaffnage | 1974 |    1 |  3.8 |  8.7 |
| dunstaffnage | 1974 |    2 |  3.7 |    8 |
| dunstaffnage | 1974 |    3 |  2.8 |  8.9 |
| dunstaffnage | 1974 |    4 |  3.9 |   14 |
| dunstaffnage | 1974 |    5 |  7.3 | NULL |
| dunstaffnage | 1974 |    6 |  8.3 | 16.8 |
| dunstaffnage | 1974 |    7 | 10.2 | 15.9 |
| dunstaffnage | 1974 |    8 | 10.7 | 16.9 |
| dunstaffnage | 1974 |    9 |    8 |   14 |
| dunstaffnage | 1974 |   10 |  5.4 | 10.9 |
| dunstaffnage | 1974 |   11 |  3.7 |  8.9 |
| dunstaffnage | 1974 |   12 |  4.6 |  9.1 |
+--------------+------+------+------+------+
SELECT COUNT(tmin), COUNT(tmax)
  FROM weather
 WHERE station='dunstaffnage' AND yr=1974
SELECT COUNT(tmin), COUNT(tmax)
  FROM weather
 WHERE station='dunstaffnage' AND yr=1974
Find the number of non-NULL values for station and the number of non-NULL values for tmin over the whole data set.
SELECT COUNT(station),COUNT(yr)
  FROM weather
SELECT COUNT(station),COUNT(tmin)
  FROM weather
You should notice that the columns station, yr and mnth are never NULL.
Show the number of NULL values in each of the columns tmin, tmax, rain and sun. You can use the expression COUNT(station)-COUNT(tmin) to find the number of missing values in tmin.
SELECT COUNT(station),COUNT(yr)
  FROM weather
SELECT COUNT(station),COUNT(tmin)
  FROM weather