Update: answers posted below each problem.
Due: Thursday Nov 8, 11:59PM.
To submit: Send an email to me at email@example.com with subject CSC 110 HW6 with your work in the body of an email, or attached in a plain text file. Note: no Microsoft Word documents will be accepted for this or future assignments.
This assignment will use the example SQLite databases linked in this post. Include the SQL statements you used to find the answer.
Using the UN database, answer the following questions. 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).
What was the largest total population in the data set? Which country?
select country, male + female as total from population order by total desc limit 1;
Which 3 countries had the largest male population in 1999?
select country, male from population where year = 1999 order by male desc limit 3;
United States of America
Explain what the following query does. Can you explain why Montserrat shows up in the results? How many rows appear to indicate bad data?
select first.year, first.male + first.female as pop_orig, next.male + next.female as pop_next_year, first.country, 1.0 * (next.male + next.female) / (first.male + first.female) - 1.0 as percent_change from population first join population next on first.year = next.year - 1 and first.country = next.country order by percent_change limit 10;
Here are the results:
The join operation is linking the same table to itself, but lining up rows that differ in year by 1. This is allowing us to compute a year-to-year population change. So we're finding the countries and years where a country lost the most population from one year to the next.
The top three results are most likely errors. However, Serbia and Montserrat have serious explanations for the decreases. In 1995, a volcano began erupting on Montserrat that forced evacuations. Conflict in Serbia can explain some decreases (although it also probably caused difficulty in gathering population estimates).
Use the movie database to answer the next set of questions:
How many users listed an occupation of "student"?
select count(*) as num_students from users where occupation = 'student';
What are all the movie titles that were classified as both romance and sci-fi?
select title from movies where genre_romance = 1 and genre_sci_fi = 1;
Star Wars (1977)
Empire Strikes Back, The (1980)
Return of the Jedi (1983)
Nutty Professor, The (1996)
Kid in King Arthur's Court, A (1995)
What movies are listed with titles that start with the word "Big"?
select title from movies where title like 'Big%';
Big Night (1996)
Big Sleep, The (1946)
Big Squeeze, The (1996)
Big Lebowski, The (1998)
Big Blue, The (Grand bleu, Le) (1988)
Big Green, The (1995)
Big Bully (1996)
Big Bang Theory, The (1994)
Big One, The (1997)
What was the most common rating (number) given?
select rating, count(*) as count from ratings group by rating order by count desc;
Which movie did lawyers rate the most times? Hint: the template below is one possible solution, where you have to fill in the ??? sections.
select ??? from ratings natural join users natural join movies where ??? group by movie_id order by ??? limit ???;
select title, count(*) as rating_count from ratings natural join users natural join movies where occupation = 'lawyer' group by movie_id order by rating_count desc limit 1;
Use the Chinook database to answer the next set:
Which 5 artists have the most albums listed?
select Name, count(*) as album_count from Album natural join Artist group by ArtistID order by album_count desc limit 5;
Which customer has ordered the largest total in dollars?
select CustomerId, FirstName, LastName, count(*) as orders, sum(Total) as total from Customer natural join Invoice group by CustomerId order by total desc limit 1;