SQL for Data Analytics · Table Relationships

Table Relationships

Table Relationships13 min

Table Relationships13 min

Run queries here while you watch. When I tell you in the video to open Workbench and try something, use this box. This is for your own practice. It isn't recorded or part of your unit submission.
⌘/Ctrl + Enter
One-to-many in Northwind

In the video I used roommates and fruits, and drivers and cars. Northwind is full of the same kind of relationship.

Every order in the Orders table has a CustomerID column. There's only one CustomerID on each order, so an order belongs to one customer. But nothing stops the same CustomerID from showing up on lots of orders. Run this to see every order placed by customer 5.

Every order placed by customer 5.
⌘/Ctrl + Enter

Customer 5 has three orders. One customer can have many orders, and each order has one customer. That's a one-to-many relationship.

The foreign key, CustomerID, is in the Orders table, on the "many" side. That's where it has to go. A customer can have any number of orders, and there's no way to give the Customers table a column for every one of them. Each order only ever needs to hold one customer.

Look at the Products table. Which of these describes the relationship between suppliers and products?

SELECT *
FROM Products
LIMIT 5;
Employees with no orders

Run these two. The first lists the employees. The second counts the orders each employee has handled.

Every employee's ID and name.
⌘/Ctrl + Enter
The number of orders each employee has handled.
⌘/Ctrl + Enter

Employee 10, Adam West, isn't in the second result. Adam hasn't handled a single order.

Is the relationship between employees and orders still one-to-many, if one employee has no orders at all?

How I'd answer it

Yes. One-to-many describes what the tables allow. Each order has one spot for an EmployeeID, and any number of orders can have the same EmployeeID. Adam could handle fifty orders tomorrow without anything about the tables changing. It's the same as in the video, where Mei ended up owning zero cars.