Run to view results
Run to view results
Run to view results
Run to view results
Run to view results
SELECT *
FROM presidents;
Run to view results
SELECT *
FROM prime_ministers;
Run to view results
Run to view results
Run to view results
--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;
Run to view results
--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;
Run to view results
--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);
Run to view results
SELECT *
FROM cities;
Run to view results
SELECT *
FROM cities
-- Inner join to countries
INNER JOIN countries
-- Match on country codes
ON cities.country_code = countries.code;
Run to view results
-- 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
Run to view results
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);
Run to view results
Run to view results
Run to view results
Run to view results
Run to view results
Run to view results
SELECT *
FROM countries;
Run to view results
SELECT *
FROM cities;
Run to view results
SELECT *
FROM languages;
Run to view results
-- 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);
Run to view results
-- 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
Run to view results
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;
Run to view results
SELECT p1.country, p1.continent,
president, prime_minister
FROM prime_ministers as p1
INNER JOIN presidents as p2
USING(country);
Run to view results
Run to view results
-- 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);
Run to view results
SELECT *
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id;
Run to view results
SELECT *
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id
AND left_table.date = right_table.date;
Run to view results
-- 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);
Run to view results
-- 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'
Run to view results
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;
Run to view results
Run to view results
Run to view results
Run to view results
SELECT p1.country, prime_minister, president
FROM prime_ministers AS p1
LEFT JOIN presidents AS p2
USING(country);
Run to view results
Run to view results
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);
Run to view results
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;
Run to view results
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;
Run to view results
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;
Run to view results
-- 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;
Run to view results
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;
Run to view results
-- 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;
Run to view results
Run to view results
Run to view results
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;
Run to view results
Run to view results
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;
Run to view results
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;
Run to view results
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;
Run to view results
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';
Run to view results
Run to view results
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');
Run to view results
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');
Run to view results
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');
Run to view results
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;
Run to view results
SELECT *
FROM prime_ministers;
Run to view results
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;
Run to view results
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;
Run to view results
-- 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
Run to view results
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
Run to view results
Run to view results
Run to view results
Run to view results
Run to view results
SELECT *
FROM left_table
UNION
SELECT *
FROM right_table;
SELECT *
FROM left_table
UNION ALL
SELECT *
FROM right_table;
Run to view results
SELECT *
FROM monarchs;
Run to view results
SELECT monarch AS leader, country
FROM monarchs
UNION
SELECT prime_minister, country
FROM prime_ministers
ORDER BY country, leader
LIMIT 10;
Run to view results
SELECT monarch AS leader, country
FROM monarchs
UNION ALL
SELECT prime_minister, country
FROM prime_ministers
ORDER BY leader, country
LIMIT 10;
Run to view results
SELECT *
FROM languages
UNION
SELECT *
FROM currencies;
Run to view results
SELECT code FROM
languages
UNION ALL
SELECT code FROM
currencies;
Run to view results
SELECT code
FROM languages
UNION
SELECT curr_id
FROM currencies;
Run to view results
-- Select all fields from economies2015
SELECT *
FROM economies2015
-- Set operation
UNION
-- Select all fields from economies2019
SELECT *
FROM economies2019
ORDER BY code, year;
Run to view results
-- 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;
Run to view results
SELECT code, year
FROM economies
-- Set theory clause
UNION ALL
SELECT country_code, year
FROM populations
ORDER BY code, year;
Run to view results
Run to view results
Run to view results
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;
Run to view results
SELECT *
FROM left_table
INNER JOIN right_table
ON left.id = right.id
AND left.val = right.val
Run to view results
SELECT country as intersect_country
FROM prime_ministers
INTERSECT
SELECT country
FROM presidents;
Run to view results
SELECT country, prime_minister AS leader
FROM prime_ministers
INTERSECT
SELECT country, president
FROM presidents;
Run to view results
SELECT country, prime_minister AS leader
FROM prime_ministers
INTERSECT
SELECT country, monarch
FROM monarchs;
Run to view results
-- Return all cities with the same name as a country
SELECT name
FROM countries
INTERSECT
SELECT name
FROM cities;
Run to view results
Run to view results
Run to view results
SELECT monarch, country
FROM monarchs
EXCEPT
SELECT prime_minister, country
FROM prime_ministers;
Run to view results
-- 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;
Run to view results
Run to view results
Run to view results
SELECT *
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id;
Run to view results
Run to view results
Run to view results
Run to view results
SELECT country, continent, president
FROM presidents;
Run to view results
SELECT country
FROM states
WHERE indep_year < 1800;
Run to view results
SELECT president, country, continent
FROM presidents
WHERE country IN
(SELECT country
FROM states
WHERE indep_year < 1800);
Run to view results
Run to view results
Run to view results
SELECT country, president
FROM presidents
WHERE continent LIKE '%America'
AND country NOT IN
(SELECT country
FROM states
WHERE indep_year < 1800);
Run to view results
-- Select country code for countries in the Middle East
SELECT code
FROM countries
WHERE region = 'Middle East';
Run to view results
-- Select unique language names
SELECT DISTINCT(name)
FROM languages
-- Order by the name of the language
ORDER BY name ASC;
Run to view results
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;
Run to view results
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';
Run to view results
-- Select code and name of countries from Oceania
SELECT code, name
FROM countries
WHERE continent = 'Oceania';
Run to view results
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);
Run to view results
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;
Run to view results
SELECT DISTINCT continent,
(SELECT COUNT(*)
FROM monarchs
WHERE states.continent = monarchs.continent) AS monarch_count
FROM states;
Run to view results
-- Select average life_expectancy from the populations table
SELECT AVG(life_expectancy)
FROM populations
-- Filter for the year 2015
WHERE year = 2015;
Run to view results
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;
Run to view results
-- 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;
Run to view results
-- 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;
Run to view results
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;
Run to view results
SELECT continent, MAX(indep_year) AS most_recent
FROM states
GROUP BY continent;
Run to view results
SELECT left_table.id, left_val
FROM left_table, right_table
WHERE left_table.id = right_table.id;
Run to view results
SELECT DISTINCT left_table.id, left_val
FROM left_table, right_table
WHERE left_table.id = right_table.id;
Run to view results
-- 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;
Run to view results
-- Select code, and language count as lang_num
SELECT code, COUNT(*) AS lang_num
FROM languages
GROUP BY code;
Run to view results
-- 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;
Run to view results
-- 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;
Run to view results
-- 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;
Run to view results
Run to view results