123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220 |
- -- 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'
- )
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- --
|