In class exercises

Part I

1. Return all the observations for Central Park in Dec 2015.

SELECT *
FROM nyc.weather_daily
WHERE station_id='WBAN:94728' AND year=2015 AND month=12;

rows=1014

2. What was the windiest reading at JFK airport in 2015?

SELECT MAX(windspeed_mph) AS highwind
FROM nyc.weather_daily
WHERE station_id='WBAN:94789' AND year=2015;

rows=1 (37mph)

Alt:

SELECT station_id, MAX(windspeed_mph) AS highwind
FROM nyc.weather_daily
WHERE station_id='WBAN:94789' AND year=2015
GROUP BY station_id;

Alt:

SELECT station_id, thedate, windspeed_mph AS highwind
FROM nyc.weather_daily
WHERE station_id='WBAN:94789' AND year=2015
ORDER BY highwind DESC
LIMIT 1;

3. What were the windiest readings for the three major airports in 2015?

SELECT station_id, MAX(windspeed_mph) AS highwind
FROM nyc.weather_daily
WHERE station_id IN ('WBAN:94789','WBAN:14732', 'WBAN:14734')
AND year=2015
GROUP BY station_id;

rows=4

4. How many records are there for each station?

SELECT station_id, count(station_id) AS stations
FROM nyc.weather_daily
GROUP BY station_id;

rows=10

5. How many records are there for each station where the temperature is null?

SELECT station_id, count(station_id) AS stations
FROM nyc.weather_daily
WHERE drybulb_temp_f IS NULL
GROUP BY station_id;

rows=10

6. What is the avg dry bulb temperature in F for each month and year?

SELECT month, year, avg(drybulb_temp_f) AS avg_temp
FROM nyc.weather_daily
GROUP BY month, year
ORDER BY year, month;

rows=36

7. Which months had more than 5 inches of rain at Newark airport in 2015?

SELECT station_id, month, sum(precip_in) AS rain
FROM nyc.weather_daily
WHERE station_id='WBAN:14734' AND year=2015
GROUP by station_id, month
HAVING sum(precip_in)>5
ORDER BY rain DESC;

rows=5

8. How many overcast readings were there at JFK airport between Jan and Mar of 2015?

SELECT COUNT(weather_id) AS overcast
FROM nyc.weather_daily
WHERE month BETWEEN 1 AND 3 AND year=2015
AND station_id='WBAN:94789'
AND skycondition LIKE '%OVC%';

row=1

9. What was the avg temperature in Central Park on Dec 25, 2015?

SELECT station_id, avg(drybulb_temp_f) AS avgtemp
FROM nyc.weather_daily
WHERE station_id='WBAN:94728'
AND EXTRACT (YEAR FROM thedate)=2015
AND EXTRACT (MONTH FROM thedate)=12
AND EXTRACT (DAY FROM thedate)=25
GROUP BY station_id;

rows=1

10. For the stations table, return all the records with an additional column where the elevation is reported in feet

SELECT station_id, station_name, elevation, 
ROUND(elevation*3.281,0) AS elev_ft
FROM nyc.weather_station
ORDER BY elevation DESC

rows=10

Part II

1. Join all the fields from the stations table and the date and temps for the observations table for all airports in NJ with observations in 2015

SELECT s.*, d.thedate, d.drybulb_temp_f
FROM nyc.weather_station AS s, nyc.weather_daily AS d
WHERE s.station_id=d.station_id
AND s.station_name LIKE '%NJ%'
AND d.year=2015
ORDER BY thedate;

rows=70682

2. Modify this table to exclude Null temperature observations

SELECT s.*, d.thedate, d.drybulb_temp_f
FROM nyc.weather_station AS s, nyc.weather_daily AS d
WHERE s.station_id=d.station_id
AND s.station_name LIKE '%NJ%'
AND d.year=2015
AND d.drybulb_temp_f IS NOT NULL
ORDER BY thedate;

rows=70578

3. Do a join of the reports and observations table and count the number of non-null temperature observations by report type

SELECT r.reptype, r.repname, count(d.drybulb_temp_f) AS temps
FROM nyc.weather_reptype AS r, nyc.weather_daily AS d
WHERE r.reptype=d.reptype
AND d.year=2015
AND d.drybulb_temp_f IS NOT NULL
GROUP BY r.reptype, r.repname;

rows=4

ALT:

SELECT r.reptype, r.repname, count(d.drybulb_temp_f) AS temps 
FROM nyc.weather_reptype r
INNER JOIN nyc.weather_daily d ON (d.reptype=r.reptype)
WHERE d.year=2015
AND d.drybulb_temp_f IS NOT NULL
GROUP BY r.reptype, r.repname;