Database Access

Usage

LIMIT statement is not allowed and will result in a syntax error

your request is limited to the 10 first entries but you can download the remaining entries as .csv file

you are only allowed to use SELECT, DESC and SHOW statement

quick sql tutorial (http://www.w3schools.com/sql/sql_quickref.asp)

SQL example queries

At first, you usually want to get an overview over the database and list all available tables:
"SHOW TABLES;"

Often you want to check whether a certain table contains the information you are interestd in. Therefore you want to have a quick overlook over the columns in the table of interest::
"DESC agro_pheno_def;

Stations and observations are uniquely referenced to by identifiers. Therefore, it is safer and more efficient to access phenological observations by their identifiers rather than by names.
The identifiers of all stations with a name similar to "Geisenheim" can be retrieved with the query:
"select stat_id, stat_name from pheno_stations where stat_name like '%Geisenheim%';"

In the DWD- and HIS-database a combination of plant and phase is referenced by a single unique identifier, which is handy:
"select * from dwd_pheno_def where plant_name_en like '%chestnut%' and phase_name_en like '%flowering%';"

With a station-id and a phase-id where can efficiently retrieve time series, e.g. flowering of chestnut at Geisenheim(DWD):
"select obs_day, obs_year, source_db from all_pheno_obs where stat_id=2430 and phase_id=8;"

We can also ask more complex questions, e.g. which of the following plants flowered earliest after 1951 on average, hazelnut, chestnut or birch?:
"select avg(obs_day) mean, b.plant_name_en plant, b.phase_name_en phase from dwd_pheno_obs a, dwd_pheno_def b where a.phase_id=b.phase_id and a.phase_id in (1,8,220) group by a.phase_id order by mean"

or, e.g. how many single station time series are there that have a certain length including all lengths and and phases (see Figure 1 in the documentation)?:
"select c, count(c) from (select stat_id as sid , phase_id as pid , count(distinct obs_year) as c from all_pheno_obs where phase_id != 0 group by stat_id,phase_id) as sq group by c"