SELECT * FROM presidents;

SELECT * FROM prime_ministers;

--Inner join of presidents and prime_ministers, joining on country SELECT prime_ministers.country, prime_ministers.continent, prime_minister, president FROM prime_ministers INNER JOIN presidents ON prime_ministers.country = presidents.country;

--Inner join of presidents and prime_ministers, joining on country SELECT p1.country, p1.continent, prime_minister, president FROM prime_ministers AS p1 INNER JOIN presidents AS p2 ON p1.country = p2.country;

--Inner join of presidents and prime_ministers, joining on country SELECT p1.country, p1.continent, prime_minister, president FROM prime_ministers AS p1 INNER JOIN presidents AS p2 USING(country);

SELECT * FROM cities;

SELECT * FROM cities -- Inner join to countries INNER JOIN countries -- Match on country codes ON cities.country_code = countries.code;

-- Select fields with aliases SELECT c.code AS country_code, name, year, inflation_rate FROM countries AS c -- Join to economies (alias e) INNER JOIN economies AS e -- Match on code field using table aliases ON c.code = e.code

SELECT c.name AS country, l.name AS language, official FROM countries AS c INNER JOIN languages AS l -- Match using the code column USING (code);

SELECT * FROM countries;

SELECT * FROM cities;

SELECT * FROM languages;

-- Select country and language names, aliased SELECT c.name AS country, l.name AS language -- From countries (aliased) FROM countries AS c -- Join to languages (aliased) INNER JOIN languages AS l -- Use code as the joining field with the USING keyword USING (code);

-- Rearrange SELECT statement, keeping aliases SELECT l.name AS language, c.name AS country FROM countries AS c INNER JOIN languages AS l USING(code) -- Order the results by language ORDER BY language

SELECT * FROM left_table INNER JOIN right_table ON left_table.id = right_table.id INNER JOIN another_table ON left_table.id = another_table.id;

SELECT * FROM prime_minister_terms;

SELECT p1.country, p1.continent, president, prime_minister FROM prime_ministers as p1 INNER JOIN presidents as p2 USING(country);

-- SQL query for chaining inner joins SELECT p1.country, p1.continent, president, prime_minister, pm_start FROM prime_ministers as p1 INNER JOIN presidents as p2 USING(country) INNER JOIN prime_minister_terms as p3 USING(prime_minister);

SELECT * FROM left_table INNER JOIN right_table ON left_table.id = right_table.id;

SELECT * FROM left_table INNER JOIN right_table ON left_table.id = right_table.id AND left_table.date = right_table.date;

-- Select fields SELECT name, e.year, fertility_rate, unemployment_rate FROM countries AS c INNER JOIN populations AS p ON c.code = p.country_code -- Join to economies (as e) INNER JOIN economies AS e -- Match on country code USING (code);

-- Select fields SELECT name, e.year, fertility_rate, unemployment_rate FROM countries AS c INNER JOIN populations AS p ON c.code = p.country_code -- Join to economies (as e) INNER JOIN economies AS e -- Match on country code USING (code) WHERE name = 'Albania'

SELECT name, e.year, fertility_rate, unemployment_rate FROM countries AS c INNER JOIN populations AS p ON c.code = p.country_code INNER JOIN economies AS e ON c.code = e.code -- Add an additional joining condition such that you are also joining on year AND p.year = e.year;

SELECT p1.country, prime_minister, president FROM prime_ministers AS p1 LEFT JOIN presidents AS p2 USING(country);

SELECT * FROM left_table RIGHT JOIN right_table ON left_table.id = right_table.id;

SELECT p1.country, prime_minister, president FROM prime_ministers AS p1 RIGHT JOIN presidents AS p2 USING(country);

SELECT c1.name AS city, code, c2.name AS country, region, city_proper_pop FROM cities AS c1 -- Perform an inner join with cities as c1 and countries as c2 on country code INNER JOIN countries AS c2 ON c1.country_code = c2.code ORDER BY code DESC;

SELECT c1.name AS city, code, c2.name AS country, region, city_proper_pop FROM cities AS c1 -- Join right table (with alias) LEFT JOIN countries AS c2 ON c1.country_code = c2.code ORDER BY code DESC;

SELECT name, region, gdp_percapita FROM countries AS c LEFT JOIN economies AS e -- Match on code fields Using (code) -- Filter for the year 2010 WHERE year = 2010;

-- Select region, and average gdp_percapita as avg_gdp SELECT region, AVG(gdp_percapita) AS avg_gdp FROM countries AS c LEFT JOIN economies AS e USING(code) WHERE year = 2010 -- Group by region GROUP BY region;

SELECT region, AVG(gdp_percapita) AS avg_gdp FROM countries AS c LEFT JOIN economies AS e USING(code) WHERE year = 2010 GROUP BY region -- Order by descending avg_gdp ORDER BY avg_gdp DESC -- Return only first 10 records LIMIT 10;

-- Modify this query to use RIGHT JOIN instead of LEFT JOIN SELECT countries.name AS country, languages.name AS language, percent FROM languages RIGHT JOIN countries USING(code) ORDER BY language;

SELECT left_table.id AS L_id, right_table.id AS R_id, left_table.val AS L_val, right_table.val AS R_val FROM left_table FULL JOIN right_table USING (id);

SELECT p1.country AS country, prime_minister, president FROM prime_ministers AS p1 FULL JOIN presidents AS p2 ON p1.country = p2.country LIMIT 10;

SELECT name AS country, code, region, basic_unit FROM countries -- Join to currencies FULL JOIN currencies USING (code) -- Where region is North America or name is null WHERE region = 'North America' OR name IS NULL ORDER BY region;

SELECT name AS country, code, region, basic_unit FROM countries -- Join to currencies LEFT JOIN currencies USING (code) WHERE region = 'North America' OR name IS NULL ORDER BY region;

SELECT name AS country, code, region, basic_unit FROM countries -- Join to currencies INNER JOIN currencies USING (code) WHERE region = 'North America' OR name IS NULL ORDER BY region;

SELECT c1.name AS country, region, l.name AS language, basic_unit, frac_unit FROM countries as c1 -- Full join with languages (alias as l) FULL JOIN languages AS l USING (code) -- Full join with currencies (alias as c2) FULL JOIN currencies AS c2 USING (code) WHERE region LIKE 'M%esia';

SELECT id1, id2 FROM table1 CROSS JOIN table2;

SELECT prime_minister, president FROM prime_ministers AS p1 CROSS JOIN presidents AS p2 WHERE p1.continent IN ('Asia') AND p2.continent IN ('South America');

SELECT c.name AS country, l.name AS language -- Inner join countries as c with languages as l on code FROM countries AS c INNER JOIN languages AS l USING (code) WHERE c.code IN ('PAK','IND') AND l.code in ('PAK','IND');

SELECT c.name AS country, l.name AS language FROM countries AS c -- Perform a cross join to languages (alias as l) CROSS JOIN languages AS l WHERE c.code in ('PAK','IND') AND l.code in ('PAK','IND');

SELECT c.name AS country, region, life_expectancy AS life_exp FROM countries AS c -- Join to populations (alias as p) using an appropriate join INNER JOIN populations AS p ON c.code = p.country_code -- Filter for only results in the year 2010 WHERE year = 2010 -- Sort by life_exp ORDER BY life_exp -- Limit to five records LIMIT 5;

SELECT * FROM prime_ministers;

SELECT p1.country AS country1, p2.country AS country2, p1.continent FROM prime_ministers AS p1 INNER JOIN prime_ministers AS p2 ON p1.continent = p2.continent LIMIT 10;

SELECT p1.country AS country1, p2.country AS country2, p1.continent FROM prime_ministers AS p1 INNER JOIN prime_ministers AS p2 ON p1.continent = p2.continent AND p1.country <> p2.country;

-- Select aliased fields from populations as p1 SELECT p1.country_code, p1.size AS size2010, p2.size AS size2015 -- Join populations as p1 to itself, alias as p2, on country code FROM populations AS p1 INNER JOIN populations AS p2 ON p1.country_code = p2.country_code

SELECT p1.country_code, p1.size AS size2010, p2.size AS size2015 FROM populations AS p1 INNER JOIN populations AS p2 ON p1.country_code = p2.country_code WHERE p1.year = 2010 -- Filter such that p1.year is always five years before p2.year AND p1.year = p2.year - 5

SELECT * FROM left_table UNION SELECT * FROM right_table;

SELECT * FROM left_table UNION ALL SELECT * FROM right_table;

SELECT * FROM monarchs;

SELECT monarch AS leader, country FROM monarchs UNION SELECT prime_minister, country FROM prime_ministers ORDER BY country, leader LIMIT 10;

SELECT monarch AS leader, country FROM monarchs UNION ALL SELECT prime_minister, country FROM prime_ministers ORDER BY leader, country LIMIT 10;

SELECT * FROM languages UNION SELECT * FROM currencies;

SELECT code FROM languages UNION ALL SELECT code FROM currencies;

SELECT code FROM languages UNION SELECT curr_id FROM currencies;

-- Select all fields from economies2015 SELECT * FROM economies2015 -- Set operation UNION -- Select all fields from economies2019 SELECT * FROM economies2019 ORDER BY code, year;

-- Query that determines all pairs of code and year from economies and populations, without duplicates SELECT code, year FROM economies UNION SELECT country_code, year FROM populations;

SELECT code, year FROM economies -- Set theory clause UNION ALL SELECT country_code, year FROM populations ORDER BY code, year;

SELECT id, val FROM left_table INTERSECT SELECT id, val FROM right_table;

SELECT id, val FROM left_table INTERSECT SELECT id, val FROM right_table;

SELECT * FROM left_table INNER JOIN right_table ON left.id = right.id AND left.val = right.val

SELECT country as intersect_country FROM prime_ministers INTERSECT SELECT country FROM presidents;

SELECT country, prime_minister AS leader FROM prime_ministers INTERSECT SELECT country, president FROM presidents;

SELECT country, prime_minister AS leader FROM prime_ministers INTERSECT SELECT country, monarch FROM monarchs;

-- Return all cities with the same name as a country SELECT name FROM countries INTERSECT SELECT name FROM cities;

SELECT monarch, country FROM monarchs EXCEPT SELECT prime_minister, country FROM prime_ministers;

-- Return all cities that do not have the same name as a country SELECT name FROM cities EXCEPT SELECT name FROM countries ORDER BY name;

SELECT * FROM left_table INNER JOIN right_table ON left_table.id = right_table.id;

SELECT country, continent, president FROM presidents;

SELECT country FROM states WHERE indep_year < 1800;

SELECT president, country, continent FROM presidents WHERE country IN (SELECT country FROM states WHERE indep_year < 1800);

SELECT country, president FROM presidents WHERE continent LIKE '%America' AND country NOT IN (SELECT country FROM states WHERE indep_year < 1800);

-- Select country code for countries in the Middle East SELECT code FROM countries WHERE region = 'Middle East';

-- Select unique language names SELECT DISTINCT(name) FROM languages -- Order by the name of the language ORDER BY name ASC;

SELECT DISTINCT name FROM languages -- Add syntax to use bracketed subquery below as a filter WHERE code IN (SELECT code FROM countries WHERE region = 'Middle East') ORDER BY name;

SELECT c1.code, name, basic_unit AS currency FROM countries AS c1 INNER JOIN currencies AS c2 ON c1.code = c2.code WHERE c1.continent = 'Oceania';

-- Select code and name of countries from Oceania SELECT code, name FROM countries WHERE continent = 'Oceania';

SELECT code, name FROM countries WHERE continent = 'Oceania' -- Filter for countries not included in the bracketed subquery AND code NOT IN (SELECT code FROM currencies);

SELECT * FROM some_table WHERE some_numeric_field IN (4, 8, 12);

SELECT * FROM some_table WHERE some_field IN (SELECT some_numeric_field FROM another_table WHERE field2 = some_condition);

SELECT DISTINCT continent FROM states;

SELECT DISTINCT continent, (SELECT COUNT(*) FROM monarchs WHERE states.continent = monarchs.continent) AS monarch_count FROM states;

-- Select average life_expectancy from the populations table SELECT AVG(life_expectancy) FROM populations -- Filter for the year 2015 WHERE year = 2015;

SELECT * FROM populations -- Filter for only those populations where life expectancy is 1.15 times higher than average WHERE life_expectancy > 1.15 * (SELECT AVG(life_expectancy) FROM populations WHERE year = 2015) AND year = 2015;

-- Select relevant fields from cities table SELECT name, country_code, urbanarea_pop FROM cities -- Filter using a subquery on the countries table WHERE name IN (SELECT capital FROM countries) ORDER BY urbanarea_pop DESC;

-- Find top nine countries with the most cities SELECT countries.name AS country, COUNT(*) AS cities_num FROM countries LEFT JOIN cities ON countries.code = cities.country_code GROUP BY country -- Order by count of cities as cities_num ORDER BY cities_num DESC, country LIMIT 9;

SELECT countries.name AS country, -- Subquery that provides the count of cities (SELECT count(*) FROM cities WHERE countries.code = cities.country_code) AS cities_num FROM countries ORDER BY cities_num DESC, country LIMIT 9;

SELECT continent, MAX(indep_year) AS most_recent FROM states GROUP BY continent;

SELECT left_table.id, left_val FROM left_table, right_table WHERE left_table.id = right_table.id;

SELECT DISTINCT left_table.id, left_val FROM left_table, right_table WHERE left_table.id = right_table.id;

-- Query to return continents with monarchs and the year the most recent country gained independence SELECT DISTINCT monarchs.continent, sub.most_recent FROM monarchs, (SELECT continent, MAX(indep_year) AS most_recent FROM states GROUP BY continent) AS sub WHERE monarchs.continent = sub.continent ORDER BY continent;

-- Select code, and language count as lang_num SELECT code, COUNT(*) AS lang_num FROM languages GROUP BY code;

-- Select local_name and lang_num from appropriate tables SELECT con.local_name, sub.lang_num FROM countries AS con, (SELECT code, COUNT(*) AS lang_num FROM languages GROUP BY code) AS sub -- Where codes match WHERE con.code = sub.code ORDER BY lang_num DESC;

-- Select relevant fields SELECT code, inflation_rate, unemployment_rate FROM economies WHERE year = 2015 AND code NOT IN -- Subquery returning country codes filtered on gov_form (SELECT code FROM countries WHERE gov_form LIKE '%Monarchy%' OR gov_form LIKE '%Republic%') ORDER BY inflation_rate;

-- Select fields from cities SELECT name, country_code, city_proper_pop, metroarea_pop, city_proper_pop / metroarea_pop * 100 AS city_perc FROM cities -- Use subquery to filter city name WHERE name IN (SELECT capital FROM countries WHERE (continent = 'Europe' OR continent LIKE '%America')) -- Add filter condition such that metroarea_pop does not have null values AND metroarea_pop IS NOT NULL -- Sort and limit the result ORDER BY city_perc desc limit 10;