In order to efficiently store data, we often spread related information across multiple tables.
For instance, imagine that we’re running a magazine company where users can have different types of subscriptions to different products. Different subscriptions might have many different properties. Each customer would also have lots of associated information.
We could have one table with all of the following information:
order_idcustomer_idcustomer_namecustomer_addresssubscription_idsubscription_descriptionsubscription_monthly_pricesubscription_lengthpurchase_date
However, a lot of this information would be repeated. If the same customer has multiple subscriptions, that customer’s name and address will be reported multiple times. If the same subscription type is ordered by multiple customers, then the subscription price and subscription description will be repeated. This will make our table big and unmanageable.
So instead, we can split our data into three tables:
1. orders would contain just the information necessary to describe what was ordered:
order_id,customer_id,subscription_id,purchase_date
2. subscriptions would contain the information to describe each type of subscription:
subscription_id,description,price_per_month,subscription_length
3. customers would contain the information for each customer:
customer_id,customer_name,address
In this lesson, we’ll learn the SQL commands that will help us work with data that is stored in multiple tables.
Instructions
Examine these tables by pasting the following code into the editor:
SELECT *
FROM orders
LIMIT 5;
SELECT *
FROM subscriptions
LIMIT 5;
SELECT *
FROM customers
LIMIT 5;