sql playground
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

220 lines
4.3 KiB

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