Joining Data in SQL: Tricky Questions

INNER JOIN

(?) statement returns only those records or rows that have matching values and is used to retrieve data that appears in both tables.

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

*Self Join* Since you'll be joining the populations table to itself, you can alias populations first as p1 and again as p2. This is good practice whenever you are aliasing tables with the same first letter. Perform an inner join of populations with itself ON country_code, aliased p1 and p2 respectively. Select the country_code from p1 and the size field from both p1 and p2, aliasing p1.size as size2010 and p2.size as size2015 (in that order).

SELECT c.name AS country, continent, l.name AS language, official FROM countries AS c INNER JOIN languages AS l USING(code);

Inner join countries on the left and languages on the right with USING(code). Select the fields corresponding to: country name AS country, continent name, language name AS language, and whether or not the language is official. Remember to alias your tables using the first letter of their names.

SELECT * FROM cities INNER JOIN countries ON cities.country_code = countries.code;

Using... SELECT * FROM cities -Inner join the cities table on the left to the countries table on the right, keeping all of the fields in both tables. -You should match the tables on the country_code field in cities and the code field in countries. -Do not alias your tables here or in the next step. Using cities and countries is fine for now.

SELECT c.code, name, region, year, fertility_rate FROM countries AS c INNER JOIN populations AS p ON c.code = p.country_code

-Inner join countries (left) and populations (right) on the code and country_code fields respectively. -Alias countries AS c and populations AS p. -Select code, name, and region from countries and also select year and fertility_rate from populations (5 fields in total).

SELECT c.code AS country_code, name, year, inflation_rate FROM countries AS c INNER JOIN economies AS e ON c.code = e.code;

-Join the tables countries (left) and economies (right) aliasing countries AS c and economies AS e. -Specify the field to match the tables ON. -From this join, SELECT: c.code, aliased as country_code, name, year, and inflation_rate, not aliased (Hint: name, year, and inflation rate do not overlap in the two tables)

FULL JOIN

A (?) returns unmatched rows from both tables as well as the overlap between them.

inner join

An (?) only includes records in which the key is in both tables.

the SELECT statement (not the FROM statement)h

If you wanted to rearrange the order in which the columns appear, what would you change in your query?

left to right (this is one reason why right joins are not used as commonly as left joins)

Joins are typically typed from (?) to (?)

period ( . )

Notice that to select a field in your query that appears in multiple tables, you'll need to identify which table/table alias you're referring to by using a (?) in your SELECT statement.

LEFT JOIN

Remember that the (?) is a type of outer join: its result is not limited to only those records that have matches for both tables on the joining field.

SELECT * FROM countries INNER JOIN economies ON countries.code = economies.code

SELECT * FROM countries INNER JOIN economies USING(code) The USING clause is essentially the same thing as writing what?

Since code appears in both the countries and economies table, it has to be matched on in the ON clause. Similarly, since year appears in both the economies and populations table, it needs to be matched on as well.

SELECT c.code, name, region, e.year, fertility_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 What is wrong with this query?

SELECT c.code, name, region, year, fertility_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

SELECT c.code, name, region, year, fertility_rate FROM countries AS c INNER JOIN populations AS p ON c.code = p.country_code Add an additional INNER JOIN with economies to your previous query by joining on code. Include the unemployment_rate column that became available through joining with economies. Note that year appears in both populations and economies.

SELECT c.code, name, region, e.year, fertility_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 AND e.year = p.year;

SELECT c.code, name, region, year, fertility_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 Both code and year appear in multiple tables, fix this query to reflect this reality.

SELECT country_code, size, CASE WHEN size > 50000000 THEN 'large' WHEN size > 1000000 THEN 'medium' ELSE 'small' END AS popsize_group FROM populations WHERE year = 2015

SELECT country_code, size, CASE WHEN WHEN ELSE AS FROM WHERE Using the populations table focused only for the year 2015, create a new field aliased as popsize_group to organize population size into 'large' (> 50 million), 'medium' (> 1 million), and 'small' groups. Select only the country code, population size, and this new popsize_group as fields.

SELECT p1.country_code, p1.size AS size2010, p2.size AS size2015, *((p2.size - p1.size) / p1.size * 100.0) AS growth_perc FROM populations as p1 INNER JOIN populations as p2 ON p1.country_code = p2.country_code AND p1.year = p2.year - 5

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 AND p1.year = p2.year - 5 If we wanted to calculate the percent growth or increase (AS growth_perc) from 2010 to 2015, what should we add to the statement?

self join

Suppose you are interested in finding out how much the populations for each country changed from 2010 to 2015. This data lies in the same table. What kind of join should be used?

FULL JOIN

The (?) basically returns all records from the left table and also from the right table. For example, let's say, we have two tables, Table A and Table B. When (?) Join is applied on these two tables, it returns us all records from both Table A and Table B

INTO

The (?) clause creates a new table in the default file group and inserts the resulting rows from the query into it.

FULL OUTER JOIN

The (?) returns a result that includes rows from both left and right tables. In case, no matching rows exist for the row in the left table, the columns of the right table will have nulls. Correspondingly, the column of the left table will have nulls if there are no matching rows for the row in the right table.

RIGHT JOIN

The (?) selects data from the right table (Table B) and matches this data with the rows from the left table (Table A). The (?) returns a result set that includes all rows in the right table, whether or not they have matching rows from the left table. In case, a row in the right table does not have any matching rows in the left table, the column of the left table in the result set will have nulls.

true: this is one reason why right joins are not used as commonly as left joins

True/False: Right joins can always be re-written as left joins.

FULL JOIN

Unlike INNER JOIN , a (?) returns all the rows from both joined tables, whether they have a matching row or not.

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

Using... SELECT * FROM cities INNER JOIN countries ON cities.country_code = countries.code; -Modify the SELECT statement to keep only the name of the city, the name of the country, and the name of the region the country resides in. -Alias the name of the city AS city and the name of the country AS country.

CASE WHEN

What clause is similar to an if-then-else statement?

Full outer join

What kind of join is being displayed here?

INNER JOIN

What kind of join is this?

USING ( )

When joining tables with a common field name, e.g. SELECT * FROM countries INNER JOIN economies ON countries.code = economies.code You can use the (?) clause as a shortcut

tables must be aliased!

When performing self joins, what is one important thing to remember regarding aliasing?

after (SELECT CASE WHEN ______ THEN ELSE _____ END FROM)

When using a CASE WHEN and THEN clause, the FROM clause should go *before/after* the CASE WHEN and THEN clause.

ON (match on)

When writing a statement using an inner join clause, what comes after the inner join clause?

cross join

Which will likely return more records? An inner join or a cross join?

INNER JOIN requires a specification of the key field (or fields) in each table.

Why does the following code result in an error? SELECT c.name AS country, l.name AS language FROM countries AS c INNER JOIN languages AS l;

(B - A) / A * 100.0

With two numeric fields A and B, the percentage growth from A to B can be calculated as...?

Left join

You run a pizza delivery service with loyal clients. You want a table of clients and their weekly orders, with nulls if there are no orders. What kind of join should be used?

inner join

You sell houses and have two tables, listing_prices and price_sold. You want a table with sales prices and listing prices, only if you know both. What kind of join should be used?

Full join

You want a report of whether your patients have reached out to you, or you have reached out to them. You are fine with nulls for either condition. What kind of join should be used?

SELECT c.name AS country, region, life_expectancy AS life_exp FROM countries AS c LEFT JOIN populations AS p ON c.code = p.country_code WHERE year = 2010 ORDER BY life_exp LIMIT 5; Explanation: sql automatically sorts query results in descending order, this is how we get the lowest life expectancy

You will determine the names of the five countries and their respective regions with the lowest life expectancy for the year 2010. Use your knowledge about joins, filtering, sorting and limiting to create this list! -Complete the join of countries AS c with populations as p. -Filter on the year 2010. -Sort your results by life expectancy in ascending order. -Limit the result to five countries. (HINT: "name" appears in two different columns)

-INNER JOIN -LEFT JOIN

You'll use (?) when you want to return only records having pair on both sides, and you'll use (?) when you need all records from the "left" table, no matter if they have pair in the "right" table or not.

Key

_____ fields are those that can be used to reference one table to another.

OUTER JOIN

if you use an (?), it will retrieve not only the matching rows but also the unmatched rows as well.