learnSQL/11-7-18.sql

-- aggregate functions

-- COUNT()

-- we're using the ZAGI database (and associated example commands)

-- number of rows (* can also be replaced with a primary key?)
COUNT(*)

-- number of entries
COUNT(field)

-- ZAGI 13
COUNT (*)
FROM product;

-- returns number or rows

-- 15

-- returns count, avg, min, max

-- 16

-- for each vendor id, get total number of product, and the average product price, sorted by vendor id.

-- returns vid, count, avg


-- GROUP BY xxxxx
-- must have xxxx in the select statement, otherwise we can get errors

-- 17 is highly un-recommended. it doesn't indicate what the data is sorted by (context)

-- 18

-- returns vid and count of products over 100$

-- 19

-- average product prices by category

-- 20

-- retunrs productid and the number of items sold

-- SUM()

-- shows a sum of the items

-- 21

-- shows number of transactions for each one, grouped by product

-- HAVING
-- similar to WHERE, used for a grouping feature, generally found after a GROUP BY statement

-- enhanced query based on 28

SELECT productid, productname, productprice, (
  SELECT AVG(productprice) FROM product
) AS avg_price
FROM product
WHERE productprice < (
  SELECT AVG (productprice)
  FROM product
);

-- notice that we can't just do WHERE productprice < avg_price. That's a bit derp. Some SQL db's can handle that, but ours (mySQL?) appears that it cannot
-- switching to SQL zoo after q30





-- dataset is now world(name, continent, area, population, gdp)

-- q2 show the countries with a per capita GDP greater than the UK
SELECT name
FROM world
WHERE continent = 'Europe'
AND gdp/population > (
  SELECT gdp/population AS percapitagdp
  FROM world
  WHERE name = 'United Kingdom'
);

-- he did this first:
SELECT gdp/population AS percapitagdp
FROM world
WHERE name = 'United Kingdom';

-- q3 List the names and continent of countries in the continents containng either Argentena or Australia, and order by country
SELECT name, continent
FROM world
WHERE continent IN (
  SELECT continent
  FROM world
  WHERE name IN ('Argentena', 'Australia')
)
ORDER BY name;

-- he did this first
SELECT continent
FROM world
WHERE name IN ('Argentena', 'Australia');

-- q4 Which country has a population that is more than Canada and less than poland? Show the name and the population.
SELECT name, country
FROM world
WHERE population > (
  SELECT population
  FROM world
  WHERE name = 'Canada'
)
AND population < (
  SELECT population
  FROM world
  WHERE name = 'Poland'
);

-- I came up with these first. He started with the external query first
SELECT population
FROM world
WHERE name = 'Canada';

SELECT population
FROM world
WHERE name = 'Poland';

-- show the inner query results for more context as to how the numbers compares
SELECT name, country, (
  SELECT population
  FROM world
  WHERE name = 'Canada'
) as popCanada, (
  SELECT population
  FROM world
  WHERE name = 'Poland'
) as popPoland
FROM world
WHERE population > (
  SELECT population
  FROM world
  WHERE name = 'Canada'
)
AND population < (
  SELECT population
  FROM world
  WHERE name = 'Poland'
);

-- q5 Germany, population 80 million has the largest population of the countries in Europe.
-- Show the name and population of each country in Europe
-- Show the population as a percentage of the population of Germany

-- I made this first
SELECT population
FROM world
WHERE name = 'Germany';

-- first round
SELECT name, population/(
  SELECT population
  FROM world
  WHERE name = 'Germany'
) AS popPctgOfGermany
FROM world
WHERE continent = 'Europe';

-- second round - add rounding statement and multiply by 100 to get percentage
SELECT name, ROUND (
  100 * population/(
    SELECT population
    FROM world
    WHERE name = 'Germany'
  ), 0 -- number of decimal places
) AS popPctgOfGermany
FROM world
WHERE continent = 'Europe';

-- third round - add percent symbol with || '%'
SELECT name, ROUND (
  100 * population/(
    SELECT population
    FROM world
    WHERE name = 'Germany'
  ), 0 -- number of decimal places
) || '%' AS popPctgOfGermany
FROM world
WHERE continent = 'Europe';

-- q6 Which countries have a GDP greater than every contry in Europe (name only, some countries may have NULL GDP values)
-- we need to find the greatest GDP in Europe as well
SELECT name
FROM world
WHERE gdp > (
  SELECT MAX(gdp)
  FROM world
  WHERE continent = 'Europe'
)


















--