Difference between revisions of "White Christmas"

From SQLZOO
Jump to: navigation, search
(hadcet)
(preteen Dickens)
Line 22: Line 22:
  
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
SELECT yr-1811 FROM hadcet
+
SELECT yr-1811 as age FROM hadcet
 
   WHERE yr BETWEEN 1812 and 1812+12 AND dy=25
 
   WHERE yr BETWEEN 1812 and 1812+12 AND dy=25
 
</source>
 
</source>
  
 
<source lang='sql' class='ans'>
 
<source lang='sql' class='ans'>
SELECT yr-1811, m12/10 FROM hadcet
+
SELECT yr-1811 as age, m12/10 as temp FROM hadcet
   WHERE yr BETWEEN 1812 and 1812+12 AND dy=25
+
   WHERE yr BETWEEN 1812 and 1812+11 AND dy=25
 
</source>
 
</source>
 
</div>
 
</div>

Revision as of 21:57, 13 March 2017

hadcet

The table hadcet from the UK Met Office contains the average temperatures for central England since 1772, the units are 10th of a degree Celcius. The columns are yr and dy for year and day of month. The next twelve columns are for January through to December.

Show the average temperature for August 10th 1964

SELECT m5/10 FROM hadcet
  WHERE yr=1962 AND dy=20
SELECT m8/10 FROM hadcet
  WHERE yr=1964 AND dy=10

preteen Dickens

Charles Dickens is said to be responsible for the tradition of expecting snow at Christmas. Show the temperature on Christmas day (25th December) for each year of his childhood. He was born in February 1812 - so he was 1 (more or less) in December 1812.

Show the twelve temperatures.

SELECT yr-1811 as age FROM hadcet
  WHERE yr BETWEEN 1812 and 1812+12 AND dy=25
SELECT yr-1811 as age, m12/10 as temp FROM hadcet
  WHERE yr BETWEEN 1812 and 1812+11 AND dy=25