/dsc-join-statements-lab-wdc01-dtsc-ft-060120

Primary LanguageJupyter Notebook

Join Statements - Lab

Introduction

In this lab, you'll practice your knowledge of JOIN statements, using various types of joins and various methods for specifying the links between them.

Objectives

You will be able to:

  • Write SQL queries that make use of various types of joins
  • Compare and contrast the various types of joins
  • Discuss how primary and foreign keys are used in SQL
  • Decide and perform whichever type of join is best for retrieving desired data

CRM Schema

In almost all cases, rather than just working with a single table you will typically need data from multiple tables. Doing this requires the use of joins using shared columns from the two tables.

In this lab, you'll use the same customer relationship management (CRM) database that you saw from the previous lesson.

Connecting to the Database

Import the necessary packages and connect to the database 'data.sqlite'.

# Your code here

Display the names of all the employees in Boston

Hint: join the employees and offices tables.

# Your code here

Are there any offices that have zero employees?

Hint: Combine the employees and offices tables and use a group by.

# Your code here

Write 3 Questions of your own and answer them

# Answers will vary
# Example: Display the htmlDescription and employee's first and last name for each product that each employee has sold
# Your code here
# Your code here
# Your code here

Level Up: Display the names of every individual product that each employee has sold

# Your code here

Level Up: Display the Number of Products each employee has sold

# Your code here

Summary

Congrats! You practiced using join statements and leveraged your foreign keys knowledge!