search

VincentSteil / World_Crisis_DB

Creating a database for world crises similar to what IMDB is for movies that can be navigated in a similar manner. Parsed and accessed through python and initially stored in XML and validated against an XSD. The XML is converted in MySQL later on.
1 stars 0 forks source link

sql queries 41-45 #64

Closed VincentSteil closed 11 years ago

caisbalderas commented 11 years ago

Ill take these, @VincentSteil what format do you want them in and by what time

VincentSteil commented 11 years ago

Just write a comment with the number and English query above each SQL query. All in one file.

caisbalderas commented 11 years ago

41

Select sum(number) From HumanImpact Where crisis_id in (Select id From Crisis Where kind="TA");

caisbalderas commented 11 years ago

42

select id from Crisis where kind = "HU" and id in(select entity_id from Location where country="United States" or country = "United States of America" or country = "US" or country = "USA") and id in(select id_crisis from CrisisOrganization where id_organization in (select id_organization from OrganizationPerson where id_person = "WStickney"));

caisbalderas commented 11 years ago

44

select count(id_crisis) from CrisisOrganization where id_organization in (select id from Organization where kind = "IA");

caisbalderas commented 11 years ago

43

select id  from Crisis where kind = "HU" and id in(select entity_id   from Location where country="United States" or country = "United States of America" )       and id not in (select distinct id_crisis                  from CrisisOrganization                 where id_organization = "FEMA");

caisbalderas commented 11 years ago

stuck on 45... Gonna get a few cups of coffee and try again

VincentSteil commented 11 years ago

I've done 45. All of them are done now and have been sanity checked by me on the db. Just need to make the html when @mduban updates the db to its final form. Going to push the queries now.