Lesson: Relational Data with Joins

RELATIONAL_DATA_WITH_JOINS.md

Relational Data With Joins

Learning Objectives

Agenda

• Review SQL commands for CRUD
• Introduce:
  • count
  • order by
  • limit

--

Exercise:

15 min

https://gist.github.com/mdang/7e68c9473de0c0363d9b778d7ca02c2b

--

• Add cohort table to users in wdi database
• Add new column to users table
• Set up foreign keys
• Explain relationships between tables
  • https://camo.githubusercontent.com/21f01e4fb4c8af8a32afecb64d15e8220b997637/687474703a2f2f7777772e766976656b6d636861776c612e636f6d2f636f6e74656e742f696d616765732f323031332f4465632f4552445f52656c6174696f6e736869705f53796d626f6c735f517569636b5f5265666572656e63652d312e706e67

Joins

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. There are different types of joins available in SQL:

• INNER JOIN: returns rows when there is a match in both tables
• LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table

https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

• Load sample world database

  • https://dev.mysql.com/doc/index-other.html

• Alter database table names

• Show joins in action with sample database

Exercise

20 min

https://gist.github.com/mdang/9a4a8063ebea3b829b8025746643ade1