/*CSE 344 Section 2: Solutions*/

-- What is the total population of earth?
SELECT SUM (population) AS Total_Population
FROM Population;

-- What is the percentage of the population from the top 10 populated countries?
SELECT SUM (percentage)
FROM Population
WHERE rank <= 10;

-- How many countries do have less than 1,000,000 population?
SELECT COUNT(*) AS Small_Countries
FROM Population
WHERE population < 1000000;

-- How many countries have airports?
SELECT COUNT (DISTINCT country) AS Airport_Count
FROM Airport;

-- Top 10 countries with most airports, in descending order
SELECT country, COUNT(*) AS Count
FROM Airport
GROUP BY country
ORDER BY COUNT(*) DESC
LIMIT 10;

-- Order the top 10 countries by total GDP (gdp / population)
SELECT p.country,  g.gdp * 1.0 / p.population AS GrossDomesticProduct
FROM Population p, GDP g
WHERE p.country = g.country
ORDER BY GrossDomesticProduct DESC
LIMIT 10;

-- Who teaches CSE 451?
SELECT i.fname, i.lname
FROM Teaches AS t, Instructor AS i
WHERE t.username = i.username AND
    t.dept = 'CSE' AND t.number = 451;

/* CSE 344 Section 2: Solutions */
-- What courses does zahorjan teach?
SELECT dept, number
FROM Teaches
WHERE username = 'zahorjan';

-- Which courses do both levy and zahorjan teach?
SELECT c.dept, c.number, c.title
FROM Class c, Teaches t1, Teaches t2
WHERE c.dept = t1.dept AND c.dept = t2.dept AND
    c.number = t1.number AND c.number = t2.number AND
    t1.username = 'levy' AND
    t2.username = 'zahorjan';

-- We can use the same table multiple times in the same query.
-- In fact, in this query, we can't use Teaches just once.
-- Why? Because with just one, we'd be asking for tuples
-- where the uid is levy and zahorjan in the same tuple.

/* Queries using aggregation functions */

-- How many classes are there in the course catalog?
SELECT COUNT(*)
FROM Class;

-- What are the highest and lowest class numbers?
SELECT MIN(number), MAX(number)
FROM Class;

/* Queries with both grouping and aggregation */

-- How many instructors teach each class?
SELECT dept, number, COUNT(DISTINCT username) AS teacher_count
FROM Teaches
GROUP BY dept, number;

-- Order the instructors by who teaches in the most departments
SELECT username, COUNT(DISTINCT dept) AS Department_Count
FROM Teaches
GROUP BY username
ORDER BY COUNT(DISTINCT dept) DESC;
