SQL Basics 03. GROUP BY ORDER BY. Database: WEATHER_STATIONS. SQLite.

Database: WEATHER_STATIONS

Table: STATION_DATA

CREATE TABLE STATION_DATA (

station_number INTEGER,

report_code VARCHAR,

year INTEGER,

month INTEGER,

day INTEGER,

dew_point DECIMAL,

station_pressure DECIMAL,

visibility DECIMAL,

wind_speed DECIMAL,

temperature DECIMAL,

precipitation DECIMAL,

snow_depth DECIMAL,

fog BOOLEAN,

rain BOOLEAN,

hail BOOLEAN,

thunder BOOLEAN,

tornado BOOLEAN

);

GROUP BY

Query 1

SELECT

COUNT(*) AS COUNT_ROWS

FROM STATION_DATA;

Query 2

SELECT

COUNT(*) AS COUNT_TORNADO_DAY

FROM STATION_DATA

WHERE TORNADO = 1

;

SELECT

COUNT(*) AS COUNT_NOTORNADO_DAY

FROM STATION_DATA

WHERE TORNADO = 0

;

Query 3

SELECT

YEAR, COUNT(*) AS COUNT_TORNADO_BYYEAR

FROM STATION_DATA

WHERE TORNADO = 1

GROUP BY YEAR

ORDER BY YEAR

;

Query 4

SELECT

YEAR, MONTH, COUNT(*) AS COUNT_TORNADO_INMONTH

FROM STATION_DATA

WHERE TORNADO = 1 AND YEAR >= 2000 AND YEAR <=2009

GROUP BY YEAR, MONTH

ORDER BY YEAR, MONTH

;

SELECT

YEAR, MONTH, DAY, TORNADO, *

FROM STATION_DATA

WHERE TORNADO = 1 AND YEAR = 2000

ORDER BY YEAR, MONTH, DAY

;

WARNING: investigation is required immediately to understand the meaning of having 3 reports in the same day.