Demo entry 6716348

comp

   

Submitted by anonymous on Feb 18, 2018 at 22:15
Language: SQL. Code size: 5.3 kB.

# 1 Who has seen a flower at Alaska Flat 
select distinct person from sightings where location='Alaska Flat';

#2 Who has seen the same flower at both Moreland Mill and at Steve Spring
select s1.person from 
(select name, person from sightings where location='Moreland Mill') as s1,
(select name, person from sightings where location='Steve Spring') as s2
where s1.name = s2.name;

#3 WhatisthescientificnameforeachofthedifferentflowersthathavebeensightedbyeitherMichael or Robert above 8250 feet in elevation
#select * from sightings as si, features as fe where si.location=fe.location and fe.elev>8500 and (si.person = 'Michael' or si.person='Robert');

select fl.genus, fl.species
from flowers as fl, features as fe, sightings as si
where fl.comname = si.name and fe.location = si.location
		  and fe.elev > 8250 and (si.person = 'Michael' or si.person='Robert');

#4 Which maps hold a location where someone has seen Alpine penstemon in August

select fe.map from features as fe, sightings as si 
where fe.location = si.location and 
		   si.name='Alpine penstemon' and si.sighted like '%-08-%';

#5 Which genus have more than one species recorded in the SSWC database

select fc.genus
from (select genus, count(species) as sc from flowers group by genus) as fc
where fc.sc>1;

#6 What is the common name of the most commonly sighted flower (in terms of number of sightings)
select tmpTable.name as comName
from
(select name, count(sight_id) as sc 
from sightings 
group by name 
order by sc desc 
limit 1) tmpTable;

#7 Who has not seen a flower at a location of class Tower?

select person 
from people as pe
where pe.person not in (select si.person from sightings as si, features as fe where si.location=fe.location and fe.class='Tower');

#8 For each feature class, compute the total number of flower sightings?

select fe.class, count(si.sight_id) 
from features as fe left join sightings as si on fe.location=si.location
group by fe.class;

#9 For each month, compute the fraction of the various flower species that were observed
select count(distinct species) from flowers; # know that there are totally 47 species

select frac.month_name, frac.fraction_of_species
from 
	(select month(si.sighted) as month_num, DATE_FORMAT(si.sighted, '%M') as month_name, truncate(count(distinct fl.species)/47,2) as fraction_of_species
	from sightings as si
	left join flowers as fl on fl.comname=si.name
	group by month_num, month_name
	order by month_num) as frac;


#10 Who has seen a flower on every summit on the Sawmill Mountain map, except for Cerro Noroeste

drop view summitTab;
create view summitTab as 
select fe.location as sumLoc
from features as fe
where fe.map='Sawmill Mountain' and fe.class='Summit'
		and fe.location not in ('Cerro Noroeste');
select * from summitTab;
# 'Grouse Mountain'
# 'San Emigdio Mountain'

select distinct si.person
from sightings as si
where not exists (
		select st.sumLoc
        from summitTab as st
        where st.sumLoc not in (select si2.location from sightings as si2 where si2.person=si.person)
        );

#11 For those people who have seen all of the flowers in the SSWC database, what was the date at which they saw their last unseen flower

drop view peopleSeeAll;
create view peopleSeeAll as
select distinct si.person as sip
from sightings as si
where not exists (
		select distinct si2.name
        from sightings as si2
        where si2.name not in (select si3.name from sightings as si3 where si3.person=si.person)
        );
select sip from peopleSeeAll; 
#'Maria'

select datePerFlower.sip, datePerFlower.minPerFlower as eachLastDate
from
	(select si.person as sip, si.name as sin, min(si.sighted) as minPerFlower
	from sightings as si
	where si.person in (select sip from peopleSeeAll)
	group by sip, sin) as datePerFlower
order by eachLastDate desc
limit 1;

#12 Which latitude range (defined by a lower latitude and an upper latitude) having no more than 20 different locations inside of it had the most flower sightings, and how many sightings were there

#generate views with location and corresponding sighting number
drop view numPerLoc;
create view numPerLoc as
select si.location as siloc, count(si.sight_id) as sigCnt
from sightings as si
group by siloc;
select * from numPerLoc;

#generate view with location, sighting number and latitude using left join
drop view LocLat;
create view LocLat as
select fe.latitude as felat, fe.location as nps, COALESCE(np.sigCnt, 0) as npc
from features as fe 
left join numPerLoc as np  on np.siloc=fe.location;
select * from LocLat order by felat;

#generate view with differrent latitude combinations
drop view LocCom;
create view LocCom as
select fe1.latitude as lat1, fe2.latitude as lat2
from features as fe1 join features as fe2
where fe1.latitude <= fe2.latitude;
select * from LocCom;

#caculate  sighting count sum and corresponding latitude range
drop view sumTable;
create view sumTable as
select SUM(distinct ll.npc) as sigCnt, lc.lat1 as low, lc.lat2 as high, count(distinct ll.nps) as locCnt
from LocLat as ll, LocCom as lc
where ll.felat >= lc.lat1 and ll.felat <= lc.lat2
group by low, high
having locCnt <= 20
order by sigCnt desc;
select * from sumTable;

#get latitude range
select st.low, st.high, st.sigCnt
from sumTable as st
where st.sigCnt = (select max(st.sigCnt) from sumTable as st);

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).