-- Command to run file: sqlite3 < demoqueries.sql

.open demotables.db
.header on
.mode column
.width 20 20 20 20 20

-- [Basic Queries] Show the entire table
-- SELECT * 
-- FROM cities;

-- [Basic Queries] Show only the name and state
-- SELECT Name, State 
-- FROM cities;

-- [Sorting] Order alphabetically
-- SELECT * 
-- FROM cities
-- ORDER BY Name ASC;

-- [Sorting] Order by population (highest to lowest)
-- SELECT * 
-- FROM cities
-- ORDER BY Population DESC;

-- [Sorting] Order by state, tiebreak alphabetically
-- SELECT * 
-- FROM cities
-- ORDER BY State ASC, Name ASC;

-- [Sorting] Order by founding (most recent to least), tiebreak alphabetically
-- SELECT * 
-- FROM cities
-- ORDER BY Founding DESC, Name ASC;

-- [Sorting] Order by length of name
-- SELECT * 
-- FROM cities
-- ORDER BY LENGTH(Name);

-- [Top X] Get the 5 cities with the highest altitude
-- SELECT *
-- FROM cities
-- ORDER BY Elevation DESC
-- LIMIT 5;

-- [Top X] Get the 10 cities that were founded first
-- SELECT *
-- FROM cities
-- ORDER BY Founding ASC
-- LIMIT 10;

-- [Unique Items] Get a list of unique states
-- SELECT DISTINCT State
-- FROM cities
-- ORDER BY State ASC;

-- [Unique Items] Get a list of unique starting letters of city names
-- SELECT DISTINCT SUBSTR(Name, 1, 1)
-- FROM cities
-- ORDER BY Name;

-- [Unique Items] Get a list of unique city name lengths
-- SELECT DISTINCT LENGTH(Name)
-- FROM cities
-- ORDER BY LENGTH(Name);

-- [Filtering] Get the cities where the elevation is at most 50 feet
-- SELECT *
-- FROM cities
-- WHERE Elevation <= 50;

-- [Filtering] Get the cities founded in the 1700s
-- SELECT *
-- FROM cities
-- WHERE Founding BETWEEN 1700 AND 1799

-- [Filtering] Get the cities in California
-- SELECT *
-- FROM cities
-- WHERE State = "CA";

-- [Filtering] Get the cities with population above 1000000 and elevation below 500
-- SELECT *
-- FROM cities
-- WHERE Population > 1000000 AND Elevation < 500;

-- [Patterns] Get the cities from a state that starts with N
-- SELECT *
-- FROM cities
-- WHERE State LIKE "N_";

-- [Patterns] Get the cities where the name starts with San
-- SELECT *
-- FROM cities
-- WHERE State LIKE "San%";

-- [Patterns] Get the cities where the name contains at least two O's
-- SELECT *
-- FROM cities
-- WHERE Name LIKE "%o%o%";

-- [Patterns] Get the cities that were founded at the start of a decade
-- SELECT *
-- FROM cities
-- WHERE Founding LIKE "%0";

-- [Operations] Extract the century of each city's founding
-- SELECT Name, 100*(Founding/100)
-- FROM cities;

-- [Operations] Same as above, with the columns renamed
-- SELECT Name AS City_Name, 100*(Founding/100) AS Founding_Century
-- FROM cities
-- ORDER BY Founding_Century;

-- [Operations] Get the sum of the populations, and the average elevation
-- SELECT SUM(Population) AS Total_Population, ROUND(AVG(Elevation)) AS Average_Elevation
-- FROM cities;

-- [Groupings] Group the cities by state
-- SELECT *
-- FROM cities
-- GROUP BY State;

-- [Groupings] Count the number of cities per state
-- SELECT State, Count(*) as Num_Cities
-- FROM cities
-- GROUP BY State;

-- [Groupings] Count the number of cities and total population by state starting letter
-- SELECT SUBSTR(State, 1, 1) AS Letter, COUNT(*) AS Count, SUM(Population) AS Pop
-- FROM cities
-- GROUP BY Letter;

-- [Groupings] Pick the largest city from within each state
-- SELECT Name, State, MAX(Population) As Population
-- FROM cities
-- GROUP BY State;

-- [NEW TABLE] the phone_book table
-- SELECT *
-- FROM phone_book
-- ORDER BY City, Zipcode;

-- [Joins] Combine cities & phone_book to map each person to their state
-- SELECT phone_book.First, phone_book.Last, cities.state
-- FROM cities
-- INNER JOIN phone_book
-- ON cities.Name = phone_book.city
-- ORDER BY cities.State;

-- [Joins] Combines cities & phone_book to count the number of people per state
-- SELECT cities.State, Count(*) AS Num_People
-- FROM cities
-- INNER JOIN phone_book
-- ON cities.Name = phone_book.city
-- GROUP BY cities.state;

-- [NEW TABLE] the foods table
-- SELECT *
-- FROM foods;

-- [NEW TABLE] the order table
-- SELECT *
-- FROM orders;

-- [Joins] Combine phone_book and orders to count the number of orders per city
-- SELECT phone_book.City, COUNT(*) as Num_Orders
-- FROM phone_book
-- INNER JOIN orders
-- ON phone_book.First = orders.First and phone_book.Last = orders.Last
-- GROUP BY phone_book.City
-- ORDER BY Num_Orders DESC;

-- [Joins] Combine foods and orders to find how many pounds of each food was ordered
-- SELECT foods.Name, SUM(Amount) AS Total_Pounds
-- FROM foods
-- INNER JOIN orders
-- ON foods.Id = orders.Id
-- GROUP BY foods.Name;

-- [Joins] Combine cities, phone_book and orders to find out how many pounds were ordered in each state
-- SELECT T1.State, SUM(orders.Amount) As Total_Pounds
-- FROM (
--         SELECT phone_book.First AS First, phone_book.Last AS Last, cities.State AS State
--         FROM cities
--         INNER JOIN phone_book
--         ON cities.Name = phone_book.city
--         ORDER BY cities.State) AS T1
-- INNER JOIN orders
-- ON T1.First = orders.First and T1.Last = orders.Last
-- GROUP BY T1.State;

-- [Joins] Combine cities, phone_book, foods and orders to find out how many times each food was ordered in each state
-- SELECT T1.State, T2.Name, COUNT(*) As Num_Orders
-- FROM (
--         SELECT phone_book.First AS First, phone_book.Last AS Last, cities.State AS State
--         FROM cities
--         INNER JOIN phone_book
--         ON cities.Name = phone_book.city
--         ORDER BY cities.State) AS T1
-- INNER JOIN (
--         SELECT foods.Name AS Name, orders.First, orders.Last
--         FROM foods
--         INNER JOIN orders
--         ON foods.Id = orders.ID) AS T2
-- ON T1.First = T2.First and T1.Last = T2.Last
-- GROUP BY T1.State, T2.Name;