Difference between revisions of "MoL Chapter 8 Lab"

From SQLZOO
Jump to: navigation, search
Line 15: Line 15:
 
<div class='qu'>
 
<div class='qu'>
 
<div class=imper>What output do you expect from this query? Notice that some values are missing in the tmin column for this year and station.</div>
 
<div class=imper>What output do you expect from this query? Notice that some values are missing in the tmin column for this year and station.</div>
 +
+-----------+------+------+------+------+------+-------+------+
 +
| 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 |
 +
+-----------+------+------+------+------+------+-------+------+
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT COUNT(tmin), COUNT(tmax)
 
SELECT COUNT(tmin), COUNT(tmax)

Revision as of 14:43, 22 December 2015

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