-- Warm-up Query: Countries in Asia
-- List all the full names, population, and gnp of all countries in Asia, ordered in descending
-- order by gnp, breaking ties ordering by population in ascending order. 
SELECT name, population, gnp
FROM countries
WHERE name = 'Asia'
ORDER BY gnp, population;

-- English-Speaking Countries
-- List the full names of all countries where English is spoken as an official language. Do this as
-- a single query.
SELECT c.name
FROM countries c
JOIN languages l on l.country_code = c.code
WHERE l.language = 'English' AND l.official = 'T';

-- Languages in France
-- List all of the languages that are spoken in France, as well as the expected number of speakers
-- for that language (population * percentage / 100.0). 
SELECT language, official, percentage, 
       percentage * population as estimated_speakers
FROM countries c, languages l
WHERE l.country_code = c.code
AND c.name = "France";

-- Cities in China
-- List all of the cities in China, whose population is more than 0.2% of China's population. The
-- results should include the name of the city, the district, the population, and the percentage of
-- the city's population based on the total population of China. Organize results by percentage
-- where the city with the highest population ratio comes first in the result. 
SELECT c.name, c.district, c.population, 
       c.population / countries.population as percentage
FROM countries, cities c
WHERE c.code = countries.country_code
AND countries.name = "China"
AND c.population / countries.population > 0.002
ORDER BY percentage DESC;

-- High Population
-- List the full names of all countries that contain at least 2 cities of at least 5,000,000 people.
SELECT DISTINCT c.name
FROM countries c, cities ci1, cities ci2
WHERE ci1.country_code = c.code
AND ci2.country_code = c.code
AND ci1.id < ci2.id 
AND ci1.population >= 5000000 
AND ci2.population >= 5000000;

-- Roles in Pi
-- Show all roles played in the movie named Pi. To achieve this, join the movies table with the
-- roles table and filter out all records except those in Pi. You shouldn't need to know the movie
-- ID of Pi ahead of time to do the query.
SELECT role
FROM roles, movies
WHERE movie_id = movies.id 
AND name = 'Pi';

-- Actors in Pi
-- Show the first/last names of all actors who appeared in Pi, 
-- along with their roles. You will need to join all three tables.
SELECT first_name, last_name, role
FROM roles, actors, movies
WHERE roles.movie_id = movies.id
AND roles.actor_id = actors.id
AND movies.name = 'Pi';
    
-- Actors and Genres
-- List all roles in a Horror or Sci-Fi movie with the first and last name of the actor who played
-- the role, as well as the movie name. Order the results by last name alphabetically, breaking ties
-- by first name alphabetically, further breaking ties by movie name alphabetically.
SELECT DISTINCT a.first_name, a.last_name, r.role, m.name
FROM roles r, actors a, movies m, genres g
WHERE r.movie_id = m.id
AND r.actor_i = a.id
AND (g.genre = 'Sci-Fi' OR g.genre = 'Horror')
ORDER BY last_name, first_name, m.name;

-- CSE 143 Grades
-- List all of the grades given in the course Computer Science 143. Do this as a single query and do
-- not hard-code 143's ID number in the query.
SELECT g.grade
FROM grades g, course c
WHERE c.id = g.course_id
AND c.name = 'Computer Science 143';

-- CSE 143 Grades and Names
-- Modify your previous query to list the names and grades of all students that took Computer
-- Science 143 and got a B- or better. Do this as a single query and do not hard-code 143's ID
-- number in the query.
SELECT DISTINCT s.name, g.grade
FROM students s, grades g, courses c
WHERE g.student_id = s.id
AND c.id = g.course_id
AND c.name = 'Computer Science 143' 
AND g.grade <= 'B-';

-- Popular classes
-- List the names of all courses that have been taken by 2 or more students. Do this as a single
-- query and do not hard-code any ID numbers in the query. Don't show duplicates.
SELECT DISTINCT c.name
FROM courses c, grades g1, students s1, grades g2, students s2
WHERE g1.course_id = c.id
AND g1.student_id = s1.id
AND g2.course_id = c.id
AND g2.student_id = s2.id
AND s1.id < s2.id;
