The keys to the database

Exploratory Data Analysis in SQL

Christina Maimone

Data Scientist

Foreign keys

Foreign key example linking films to actors

Exploratory Data Analysis in SQL

Foreign keys

  • Reference another row
    • In a different table or the same table
    • Via a unique ID

                   >> Primary key column containing unique, non-NULL values

  • Values restricted to values in referenced column OR NULL
Exploratory Data Analysis in SQL

Entity relationship diagram

Exploratory Data Analysis in SQL

Entity relationship diagram showing link between tag_company and company tables

Exploratory Data Analysis in SQL

Entity relationship diagram showing self-reference in company table

Exploratory Data Analysis in SQL

Entity relationship diagram

Exploratory Data Analysis in SQL

Entity relationship diagram with primary keys

Exploratory Data Analysis in SQL

Coalesce function

coalesce(value_1, value_2 [, ...])
  • Operates row by row
  • Returns first non-NULL value
Exploratory Data Analysis in SQL

Coalesce function

SELECT *
  FROM prices;

 column_1 | column_2 
----------+----------
          |       10
          |         
       22 |         
        3 |        4

SELECT coalesce(column_1, column_2)
  FROM prices;

 coalesce 
----------
       10

       22
        3
Exploratory Data Analysis in SQL

Time to keep exploring!

Exploratory Data Analysis in SQL

Preparing Video For Download...