Examples of queries that can be used with the EMBSeCBIO version 2.0

database

By: Esmeralda Cruz-Silva

#USE embsecbio database

USE embsecbio_v2_0;

#select all pollen count from the site 'Lake Van'

SELECT * FROM site JOIN entity using (ID_SITE) JOIN sample USING

(ID_ENTITY) JOIN pollen_data using (ID_SAMPLE) where site_name= 'Lake

Van';

#select all pollen count from the entity 'Van - Wick Core'

SELECT site_name, entity_name, avg_depth, taxon_clean, taxon_count

FROM site JOIN entity using (ID_SITE) JOIN sample USING (ID_ENTITY) JOIN

pollen_data using (ID_SAMPLE) where entity_name = 'Van - Wick Core';

#Pollen counts for entities, joined with samples depths and the estimated

ages

SELECT ID_ENTITY, entity_name, ID_SAMPLE, avg_depth,

est_age_provided, est_age_bacon_intcal20_mean, est_age_original,

taxon_clean,taxon_count

FROM entity JOIN sample USING(ID_ENTITY) JOIN age_model using (ID_SAMPLE)

JOIN pollen_data using (ID_SAMPLE);

#Ages of the samples dated for each entity

SELECT

ID_ENTITY, entity_name, avg_depth, DATED_AGE,

ERROR_POSITIVE, ERROR_NEGATIVE, ID_DATE_TYPE, DATE_COMMENTS

FROM entity JOIN date_info USING (ID_ENTITY);

#Query to get the publication or publications for each entity

SELECT * FROM site JOIN entity USING (ID_SITE) JOIN entity_pub USING

(ID_ENTITY)

JOIN pub USING (ID_PUB);

#Information fo sites for the entities

SELECT ID_SITE, SITE_NAME, ID_SITE_TYPE, ID_BASIN_SIZE,

ID_ENTITY, ENTITY_NAME, avg_depth, ID_SAMPLE

FROM site JOIN entity USING (ID_SITE) JOIN sample USING (ID_ENTITY);

#Select all entities from an specific gegraphic interval

SELECT * FROM entity WHERE entity.latitude <40 AND entity.longitude >25;

#Select all sites from medium basin size

SELECT * FROM site WHERE ID_BASIN_SIZE = 'MEDI';