-- CSE 414 section 2: more SQL review

/*
 * New example with Population, GDP, Airport
 */
 
-- Schema for Population, GDP, Airport
CREATE TABLE Population ( 
  rank INTEGER,
  country VARCHAR(30),
  population INTEGER,
  percentage FLOAT		
);

CREATE TABLE GDP ( 
  rank INTEGER,
  country VARCHAR(30),
  gdp INTEGER	
);

CREATE TABLE Airport ( 
  code VARCHAR(30),
  name VARCHAR(30),
  country VARCHAR(30)
);


-- 1) change mode to csv if we want to import csv files
-- .mode csv
-- 2) import the table
-- .import ./population.csv Population
-- .import ./gdp.csv GDP
-- .import ./airport.csv Airport
-- 3) optional - change back to column mode, if preferred
-- .mode column
-- .header on

-- 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) AS Top_10_Percentage
FROM Population
WHERE rank <= 10;

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

-- What is the GDP ratio of the average top/bottom 10 countries?
SELECT 
 (SELECT AVG(gdp) FROM GDP WHERE rank <= 10) / 
 (SELECT AVG(gdp) FROM GDP WHERE rank >= (SELECT COUNT(*) FROM GDP) - 10 ) AS Ratio;

-- Which country over 10 million has the biggest GDP per capita -- give country name and gdp?
SELECT p.country, MAX(gdp) AS GDP_per_capita
FROM Population p, GDP g
WHERE p.country = g.country AND p.population >= 10000000; 

-- Order the top 10 countries by total GDP in billion dollars
SELECT p.country, (1.00 * g.gdp * p.population) / 1000000000 AS Total_GDP
FROM Population p, GDP g
WHERE p.country = g.country 
ORDER BY (g.gdp * p.population) DESC
LIMIT 10;

-- What percentage of the total GDP does USA have?
SELECT (100.00 * g.gdp * p.population) / (SUM (i.Total_GDP)) AS USA_percentage
FROM
 (SELECT p.country AS country, (g.gdp * p.population) AS Total_GDP
 FROM Population p, GDP g
 WHERE p.country = g.country ) AS i, Population p, GDP g
WHERE p.country = 'United States' AND g.country = p.country;

-- Top 10 countries with most international airports
SELECT country, COUNT(*) as Num_Airports
FROM Airport
GROUP BY country 
ORDER BY COUNT(*) DESC 
LIMIT 10; 

-- Top 10 countries with most international airports per population - give country name and number of airports
SELECT p.country, n.num_airports
FROM Population p, (SELECT country, COUNT(*) AS num_airports FROM Airport GROUP BY country) n
WHERE n.country = p.country
ORDER BY (1.00 * n.num_airports) / (p.population) DESC
LIMIT 10; 

-- How many countries more than 1000 airports?
SELECT COUNT(*) AS country_count
FROM (
  SELECT 1
  FROM Airport
  GROUP BY country
  HAVING COUNT(*) >= 1000
);
-- United States is the only country that has more than 1000 airports

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


/*
 * Database from last week
 * This part below is not covered in the section, but included for review.
 *
 * 	Class(dept, number, title)
 * 	Instructor(username, fname, lname, started_on)
 *	Teaches(username, dept, number)
 *
 */

-- Semantics of joins:
-- FROM clause takes the Cartesian product of all the named relations.
-- WHERE conditions that relate tuples in two tables implement the join by
-- filtering the Cartesian product to only those matchings of tuples that
-- meet the conditions.

/* 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;

-- How many classes are being taught by at least one instructor?

-- Special case: all classes have the same department
-- First attempt - wrong!
SELECT COUNT(*) AS class_count
FROM Teaches;
-- Second attempt - also wrong
SELECT COUNT(number) AS class_count
FROM Teaches;
-- Even if you specify a column name in an aggregate function,
-- it does not eliminate duplicate values of that column, only 
-- null values.  So try this:
SELECT COUNT(DISTINCT number) AS class_count
FROM Teaches;

-- General case: this is tricky because SQL does not allow aggregation
-- functions to take more than one column as argument, unless that
-- argument is * (which only works for COUNT()).
--
-- We'll solve using subqueries and grouping - first, we group the 
-- Teaches table by department and number in a subquery, then
-- we count the number of groups in the top-level query. 
--
-- Note that we don't care what the subquery tuples are, only how many 
-- tuples/groups there are, so we return dummy tuples containing only the
-- constant 1.
--
SELECT COUNT(*) AS class_count
FROM (
  SELECT 1
  FROM Teaches
  GROUP BY dept, number
);


/* 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;

-- Which instructors teach more than 1 class?
-- Without grouping -- uses subquery
SELECT i.username, i.fname, i.lname
FROM Instructor i
WHERE 1 < (
  SELECT COUNT(*)
  FROM Teaches t
  WHERE t.username = i.username
);
-- With grouping -- no subquery
SELECT i.username
FROM Instructor i, Teaches t
WHERE i.username = t.username
GROUP BY i.username
HAVING COUNT(*) > 1;
-- Notice that:
--  * conditions on groups go in the HAVING clause, not WHERE
--  * we dropped columns fname, lname -- how do we restore them?
-- Third version -- restores missing columns
SELECT i.username, i.fname, i.lname
FROM Instructor i, Teaches t
WHERE i.username = t.username
GROUP BY i.username, i.fname, i.lname
HAVING COUNT(*) > 1;
-- What if we omit fname, lname from GROUP BY?  Why is that so?


/* Indexes */

-- SQL queries run fast on small tables, but not on big ones.
-- To make them run faster, you can use "indexes".
-- These tell you where on disk to find tuple(s) with each possible
-- value of the indexed column(s). 

-- Here, give an example of a selection and how an index speeds this up

-- Create an index of table Class, ordered by department name.
CREATE INDEX class_ix_dept ON Class(dept);
-- Can see indexes on each table with .indices table_name
-- SQLite creates some indexes automatically, usually for PRIMARY KEY cols

-- Can index multiple columns in the same index:
CREATE INDEX instructor_ix_fname_lname ON Instructor(fname, lname);
-- Then index is ordered by first column listed first, then by second
-- column listed, and so on.  This is especially useful when 
-- queries select (filter with WHERE) using mostly that combination
-- of columns; otherwise would have to read all the tuples corresponding
-- to the first column to filter by the second column, w/o an index

