Homework 5 (SQL) -- due Nov. 6

This homework will use the following SQLite databases (same as we used in class):

• Several tables of UN data, from UNdata.

  Each table contains data for all (or almost all) countries for a range of years. The tables can be linked through the country and year fields. All fields are in the obvious units, except for precip, which is in millions of cubic meters.

• 100k movie ratings, from MovieLens. The database link will be removed after the course is over, but this script will generate the sqlite database from the original MovieLens data set.

  The database contains tables movies, ratings, and users. Each user may have ranked many movies. The ratings table is linked to the others through the movie_id and user_id fields.

See the week 10 agenda for information on what software we used to query the databases.

Using the UN database, answer the following questions in a text file. Include the SQL statements you used to find the answer. The questions don't state this explicitly, but assume they always mean "given the available data" (since some countries have data missing in some years).

1. What was the smallest total population in the data set? Which country?
2. What were the top 3 countries in terms of precipitation in 2000?
3. Which country starting with "A" had the largest population in 1995?
4. Which countries in the year 2000 had a total population of over 1 million and a ratio of males to females of over 1.2? Note: to compute the ratio, you'll have to use the expression 1.0 * male / female, because otherwise the fractional part of the ratio is dropped due to integer division.

Use the movie database to answer the next set of questions:

1. What are all the movie titles that were classified as both a thriller and a musical?
2. What movies are listed with titles that start with the word "Dead"?
3. How many males and females provided ratings?
4. What occupation was the most common among the users?

5. What were the top 3 movies in terms of average rating? Your query should look like (you have to fill in the ??? with the correct field names):

   select ???, avg(???) as avg_rating from movies natural join ratings 
       group by ??? order by avg_rating desc limit 3;
   

6. What were the top 3 movies in terms of average rating that had at least 10 ratings? Your query should look like (you have to fill in the ??? with the correct field names):

   select ???, avg(???) as avg_rating, count(*) as num_ratings 
       from movies natural join ratings 
       group by ??? having num_ratings >= 10 order by avg_rating desc limit 3;