code

Basic SQL Notes

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

Use JOIN or INNER 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

Use 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

Use 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 FROM) keyword.

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

When you SELECT a column from a table with a RIGHT or LEFT 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 CASE statement.

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.

Matt has worked as a data analyst, writer, counselor, and business owner for a total of 20 years. Since the start of his career he's been fascinated by technology and passionate about helping people use modern technology to hack their work and their lives.

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *