Difference between revisions of "MoL Chapter 8 Lab"

From SQLZOO
Jump to: navigation, search
Line 1: Line 1:
 
<div class='qu'>
 
<div class='qu'>
<div class=imper>List all details from the weather table for stornoway for 1887 so that you can see the NULL value</div>
+
<div class=imper>List all details from the weather table for dunstaffnage for 1974 so that you can see the NULL value</div>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT *
 
SELECT *
 
   FROM weather
 
   FROM weather
  WHERE station='stornoway' AND yr=1892
+
  WHERE station='dunstaffnage' AND yr=1974
 
</source>
 
</source>
 
<source lang='sql' class='ans'>
 
<source lang='sql' class='ans'>
 
SELECT *
 
SELECT *
 
   FROM weather
 
   FROM weather
  WHERE station='stornoway' AND yr=1892
+
  WHERE station='dunstaffnage' AND yr=1974
 
</source>
 
</source>
 
</div>
 
</div>

Revision as of 11:52, 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 | 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