Difference between revisions of "MoL Chapter 8 Lab"

From SQLZOO
Jump to: navigation, search
Line 58: Line 58:
 
<div>You should notice that the columns '''station''', '''yr''' and '''mnth''' are never NULL.</div>
 
<div>You should notice that the columns '''station''', '''yr''' and '''mnth''' are never NULL.</div>
 
<div class=imper>Show the number readings in the whole data set together with the number of missing values for ewach of the columns '''tmin''', '''tmax''', '''rain''' and '''sun'''.</div>
 
<div class=imper>Show the number readings in the whole data set together with the number of missing values for ewach of the columns '''tmin''', '''tmax''', '''rain''' and '''sun'''.</div>
<div>You can use the expression '''COUNT(station)-COUNT(tmin)''' to find the number of missing values in '''tmin'''. You output should look like this:</div>
+
<div>You can use the expression '''COUNT(station)-COUNT(tmin)''' to find the number of missing values in '''tmin'''. Your output should look like this:</div>
 
  +----------+--------------+--------------+--------------+-------------+
 
  +----------+--------------+--------------+--------------+-------------+
 
  | readings | tmin missing | tmax missing | rain missing | sun missing |
 
  | readings | tmin missing | tmax missing | rain missing | sun missing |
Line 75: Line 75:
 
       COUNT(station)-COUNT(sun)  AS 'sun missing'
 
       COUNT(station)-COUNT(sun)  AS 'sun missing'
 
   FROM weather
 
   FROM weather
 +
</source>
 +
</div>
 +
 +
<div class='qu'>
 +
<div class=imper>List the station, the year and the month for each reading where tmin is NULL.</div>
 +
<source lang='sql' class='def'>
 +
SELECT station, yr, mnth
 +
  FROM weather
 +
WHERE tmin IS NULL AND yr>=2000
 +
</source>
 +
<source lang='sql' class='ans'>
 +
SELECT station, yr, mnth
 +
  FROM weather
 +
WHERE tmin IS NULL AND yr>=2000
 
</source>
 
</source>
 
</div>
 
</div>

Revision as of 12:22, 23 December 2015

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 readings in the whole data set together with the number of missing values for ewach 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. Your output should look like this:
+----------+--------------+--------------+--------------+-------------+
| readings | tmin missing | tmax missing | rain missing | sun missing |
+----------+--------------+--------------+--------------+-------------+
|     7756 |          104 |          105 |           79 |        1931 |
+----------+--------------+--------------+--------------+-------------+
SELECT COUNT(station),COUNT(yr)
  FROM weather
SELECT COUNT(station) AS readings,
       COUNT(station)-COUNT(tmin) AS 'tmin missing',
       COUNT(station)-COUNT(tmax) AS 'tmax missing',
       COUNT(station)-COUNT(rain) AS 'rain missing',
       COUNT(station)-COUNT(sun)  AS 'sun missing'
  FROM weather
List the station, the year and the month for each reading where tmin is NULL.
SELECT station, yr, mnth
  FROM weather
WHERE tmin IS NULL AND yr>=2000
SELECT station, yr, mnth
  FROM weather
WHERE tmin IS NULL AND yr>=2000