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  |
+
  | station      | yr  | mnth | tmin | tmax |
  +--------------+------+------+------+------+------+-------+------+
+
  +--------------+------+------+------+------+
  | dunstaffnage | 1974 |    1 |  8.7 3.8 |    0 | 321.1 | NULL |
+
  | dunstaffnage | 1974 |    1 |  3.8 |  8.7 |
  | dunstaffnage | 1974 |    2 |    8 |  3.7 |    0 |  121 | NULL |
+
  | dunstaffnage | 1974 |    2 |  3.7 |    8 |
  | dunstaffnage | 1974 |    3 |  8.9 |  2.8 |    4 61.9 | NULL |
+
  | dunstaffnage | 1974 |    3 |  2.8 |  8.9 |
  | dunstaffnage | 1974 |    4 |  14 |  3.9 |   0 |  16.2 | NULL |
+
  | dunstaffnage | 1974 |    4 |  3.9 |   14 |
  | dunstaffnage | 1974 |    5 | NULL |  7.3 |    1 |  71.8 | NULL |
+
  | dunstaffnage | 1974 |    5 |  7.3 | NULL |
  | dunstaffnage | 1974 |    6 | 16.8 |  8.3 |   0 |  82.4 | NULL |
+
  | dunstaffnage | 1974 |    6 |  8.3 | 16.8 |
  | dunstaffnage | 1974 |    7 | 15.9 | 10.2 |   0 | 120.4 | NULL |
+
  | dunstaffnage | 1974 |    7 | 10.2 | 15.9 |
  | dunstaffnage | 1974 |    8 | 16.9 | 10.7 |   0 | 138.5 | NULL |
+
  | dunstaffnage | 1974 |    8 | 10.7 | 16.9 |
  | dunstaffnage | 1974 |    9 |  14 |    8 |   2 | 155.4 | NULL |
+
  | dunstaffnage | 1974 |    9 |    8 |   14 |
  | dunstaffnage | 1974 |  10 | 10.9 |  5.4 |   0 |  82.1 | NULL |
+
  | dunstaffnage | 1974 |  10 |  5.4 | 10.9 |
  | dunstaffnage | 1974 |  11 |  8.9 |  3.7 |   2 | 210.2 | NULL |
+
  | dunstaffnage | 1974 |  11 |  3.7 | 8.9 |
  | dunstaffnage | 1974 |  12 |  9.1 |  4.6 |   0 | 306.5 | NULL |
+
  | dunstaffnage | 1974 |  12 |  4.6 | 9.1 |
  +--------------+------+------+------+------+------+-------+------+
+
  +--------------+------+------+------+------+
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
SELECT COUNT(tmin), COUNT(tmax)
 
SELECT COUNT(tmin), COUNT(tmax)

Revision as of 11:54, 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='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