/* 1.
Using a UNION query, create a report that lists the name and the district of the
cities in Canada (code CAN) and the Netherlands (code NLD). Note that this query
can be written without using a UNION. The purpose of this question is to use UNION.
(city table)*/

SELECT name, district, CountryCode
FROM city
WHERE CountryCode = "CAN"
UNION
SELECT name, district, CountryCode
FROM city
WHERE CountryCode = "NLD"
ORDER BY CountryCode, district;

/* 2.
Using an INNER JOIN..ON, create a report that list the name of cities, the country
and the indepyear of all countries whose indepYear field is not null. (city and
country tables).*/

SELECT cy.name, c.name, c.indepyear
FROM city cy INNER JOIN country c
ON (cy.countrycode = c.Code)
WHERE c.indepyear IS NOT NULL;

/* 3.
Rewrite the previous query using WHERE/FROM.*/

SELECT cy.name, c.name, c.indepyear
FROM city cy, country c
WHERE cy.countrycode = c.Code
AND c.indepyear IS NOT NULL;

/* 4.
Using a subquery, list the name of the city, the countrycode, the population and the
average city population of all cities with a population less than the average population.
(city table)*/

SELECT name, countrycode, population, (SELECT AVG(population) FROM city) as "Average Pop"
FROM City
GROUP BY population
HAVING population<(SELECT AVG(population) FROM city);

/* 5.
Using INNER JOIN ..ON, write a query that displays, for the city of Toronto, the name
of the city and the head of state of its country. (city and country tables)*/

SELECT cy.name, c.headofstate
FROM city cy INNER JOIN country c
ON (cy.countrycode = c.code)
WHERE cy.name = "Toronto";

/* 6.
Using INNER JOIN ..ON the three tables, write a query that displays the name of the
city, the city continent, the city head of state, the year of independance and the
percentage of people who speaks English in the city of Toronto. (city, countrylanguage
and country tables)*/

SELECT cy.name, c.continent, c.headofstate, c.indepyear, cl.percentage, cl.language
FROM city cy INNER JOIN countrylanguage cl
ON (cy.countrycode = cl.countrycode)
INNER JOIN country c
ON (c.code = cl.countrycode)
WHERE cy.name = "Toronto" AND cl.language = "English";

/* 7.
Rewrite the previous query using WHERE/FROM.*/

SELECT cy.name, c.continent, c.headofstate, c.indepyear, cl.percentage, cl.language
FROM city cy, countrylanguage cl, country c
WHERE (cy.countrycode = cl.countrycode) AND (c.code = cl.countrycode)
AND cy.name = "Toronto" AND cl.language = "English";

/*Querying table city and country.*/

/* 8.
Using a subquery, list the name of the city, the countrycode and the population of the
city with the largest population. (city table)*/

SELECT name, countrycode, MAX(population)
FROM city;

/* 9.
Using a subquery, list the name of the city, the countrycode and the population of all
the cities whose population is larger than the average population. (city table)*/

SELECT name, countrycode, population
FROM city
HAVING population>(SELECT AVG(population) FROM city);

/* 10.
What is wrong with the following subquery?
SELECT name,countrycode,population,avg(population)
FROM city
WHERE population > (SELECT avg(population) FROM city)*/

/*Selecting avg(population) instead of (SELECT avg(population) FROM city) will not return the desired results.
It will do the average of each field.
It also does not have a closing ;*/


/* 11.
Replace the first avg(population) with (SELECT AVG(population) from city)
What happens? Why?*/

SELECT name,countrycode,population,(SELECT avg(population) FROM city)
FROM city
WHERE population > (SELECT avg(population) FROM city);
/*The query now works because it is fetching the average of all city populations*/

/* 12.
Using a subquery, list the name of the country, the continent and the life expectancy
of all countries whose life expectancy is less than the average life expectancy.
(country table)*/

SELECT name, continent, lifeexpectancy
FROM country
HAVING lifeexpectancy < (SELECT avg(lifeexpectancy) FROM country)
ORDER BY lifeexpectancy;