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 31-35 #62

Closed VincentSteil closed 11 years ago

wgenerous commented 11 years ago

33

select count(country) from Organization where (country="United States" or country = "United States of America" or country = "US" or country = "USA");

wgenerous commented 11 years ago

34

Select count(kind) From Person Where (kind = 'Singer');

wgenerous commented 11 years ago

32

Select name, start_date From Crisis Where start_date <= all (Select start_date From Crisis) order by name;

wgenerous commented 11 years ago

31

(im not exactly sure on this one)

Select name From Crisis natural join Location Where count(country) >= all (Select count(country) From Crisis natural join Location) order by name;

VincentSteil commented 11 years ago

this is what I would do for 31:

two tables due to the fact that you can't call the same temp table twice in one query

create temporary table Crisis_Location_count as select entity_id, count(distinct country) as count from Location where entity_type = 'C' group by entity_id;

create temporary table Crisis_Location_count as select entity_id, count(distinct country) as count from Location where entity_type = 'C' group by entity_id;

select entity_id from Crisis_Location_count where not exists ( select * from Crisis_Location_count2 where Crisis_Location_count.count < Crisis_Location_count2.count ) ;

VincentSteil commented 11 years ago

for 32, this is what works: select id from Crisis as C11 where not exists ( select * from Crisis as C12 where C11.start_date > C12.start_date );

@wgenerous @caisbalderas I'm currently going through all of your sql queries and verifying them against the database and rewriting as needed thanks for helping me out :)