Skip to content

Joining Data with SQL

Here you can access every table used in the course. To access each table, you will need to specify the world schema in your queries (e.g., world.countries for the countries table, and world.languages for the languages table).

Note: When using sample integrations such as those that contain course data, you have read-only access. You can run queries, but cannot make any changes such as adding, deleting, or modifying the data (e.g., creating tables, views, etc.).

Take Notes

Add notes about the concepts you've learned and SQL cells with queries you want to keep.

Add your notes here

Spinner
DataFrameas
world_info
variable
-- Add your own queries here
SELECT *
FROM world.languages 
LIMIT 5
Spinner
DataFrameas
df
variable
-- Select name fields (with alias) and region 
SELECT  cities.name as city, countries.name as country, region
FROM cities
INNER JOIN countries
ON cities.country_code = countries.code;cinema


-- SQL code
SELECT c.code AS country_code, c.name, e.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 relevant fields
Select name, year, fertility_rate
-- Inner join countries and populations, aliased, on code
from countries as c
Inner join populations as p
on c.code = p.country_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)

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 e.year = p.year;

-- SQL code
SELECT c.name AS country, l.name AS language
FROM countries AS c
INNER JOIN languages AS l
USING(code)


-- Select language and country names, aliased
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;

-- SQL code
Select *
FROM presidents;  

-- SQL code
From prime_ministers
INNER JOIN president
ON prime_ministers.country = presidents.country

Alias Tables
SELECT p1.country, p1.continent, prime_minister, president
FROM prime_ministers AS p1
INNER JOIN presidents AS p2
ON p1.country = p2.country;

SELECT cities.name AS city, countries.name AS country, countries.region
FROM cities
INNER JOIN countries
ON cities.country_code = countries.code;

SELECT c.code AS country_code, c.name, e.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 - syntax error at or near "Inner"

SELECT name, region, gdp_percapita
FROM countries AS c
LEFT JOIN economies AS e
USING(code)
WHERE year = 2010;


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