Here are my notes on SQL. This Notes series is meant to be light on narrative but heavy on code samples that you can copy and paste. At some point, if there is demand I will provide posts that explain what is actually happening.
Datasets are located here https://data.world/mattjcamp/wiche-enrollment-projections and you can follow along right in the Data World web app. This project has two data tables: wiche_graduate_projections.csv and state_table.csv.
Select All Columns From Table
Essentially the simplest SQL query that you can write that returns records from a table.
SELECT * FROM state_table
Join Two Tables
JOIN to combine two tables when you only want records that appear in both tables. Selecting the alias and column names lets you control what appears in the final table.
SELECT S.name ,S.census_region_name ,W.year ,W.grade ,W.n FROM state_table S JOIN wiche_graduate_projections W ON S.abbreviation = W.location
LEFT JOIN when you want all records in the first table (that appears after the
FROM keyword) and the matching records that appear in the second table (after the
LEFT JOIN) keyword.
SELECT S.name ,S.census_region_name ,W.year ,W.grade ,W.n FROM wiche_graduate_projections W LEFT JOIN state_table S ON S.abbreviation = W.location
RIGHT JOIN when you want all records in the second table (that appears after the
RIGHT JOIN keyword) and the matching records that appear in the first table (after the
SELECT S.name ,S.census_region_name ,W.year ,W.grade ,W.n FROM state_table S RIGHT JOIN wiche_graduate_projections W ON S.abbreviation = W.location
Conditionally SELECT Columns
SELECT a column from a table with a
JOIN you will end up with a
NULL value in those instances where the query cannot find a matching row. If you need a value to appear in place of
NULL you can use a
SELECT CASE WHEN S.name IS NULL THEN 'United States' ELSE S.name END as name ,W.year ,W.grade ,W.n FROM wiche_graduate_projections W LEFT JOIN state_table S ON S.abbreviation = W.location
This puts returns a United States in place of
NULL since we know that there is no entry for the entire country in the states table. You can also apply more complex categorization rules here.