Code Academy SQ L

MULTIPLE TABLES
Combining Tables Manually

Let’s return to our magazine company. Suppose we have the three tables described in the
previous exercise – shown in the browser on the right (we are going to try something new!):
 orders
 subscriptions
 customers
If we just look at the orders table, we can’t really tell what’s happened in each order. However,
if we refer to the other tables, we can get a complete picture.
Let’s examine the order with an order_id of 2. It was purchased by the customer with
a customer_id of 2.
To find out the customer’s name, we look at the customers table and look for the item with
a customer_id value of 2. We can see that Customer 2’s name is ‘Jane Doe’ and that she lives at
‘456 Park Ave’.
Doing this kind of matching is called joining two tables.
Instructions
1.
Using the tables displayed, what is the description of the magazine ordered in order_id 1?
Type your answer on line 1 of the code editor.
Be sure to capitalize it the same as in the table.

Hint
First, we look at the orders table.
The order with an order_id of 1 is in the first row. Its subscription_id is 3.
Then we look at the subscriptions table.
The subscription with a subscription_id of 3 is in the third row. Its description is Sports

Magazine.
Answer: Sports Magazine
Don’t write a query for this exercise!

2.
Using the tables displayed, what is the customer_name of the customer
in order_id 3?
Type your answer on line 2 of the code editor.
Be sure to capitalize it the same as in the table.
MULTIPLE TABLES
Combining Tables with SQL
Combining tables manually is time-consuming. Luckily, SQL gives us an easy
sequence for this: it’s called a JOIN.
If we want to combine orders and customers, we would type:
SELECT *
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;
Let’s break down this command:
1. The first line selects all columns from our combined table. If we only
want to select certain columns, we can specify which ones we want.
2. The second line specifies the first table that we want to look in, orders
3. The third line uses JOIN to say that we want to combine information
from orders with customers.
4. The fourth line tells us how to combine the two tables. We want to
match orders table’s customer_id column
with customers table’s customer_id column.
Because column names are often repeated across multiple tables, we use the
syntax table_name.column_name to be sure that our requests for columns are
unambiguous. In our example, we use this syntax in the ON statement, but we
will also use it in the SELECT or any other statement where we refer to column
names.
For example: Instead of selecting all the columns using *, if we only wanted to

select orders table’s order_id column
and customers table’s customer_name column, we could use the following query:
SELECT orders.order_id,
customers.customer_name
FROM orders
JOIN customers
ON orders.customer_id = customers.customer_id;
Instructions
1.
Join orders table and subscriptions table and select all columns.
Make sure to join on the subscription_id column.

Checkpoint 2 Passed
Hint
Suppose we do:
SELECT *
FROM orders
LIMIT 10;
SELECT *
FROM subscriptions
LIMIT 10;
You will notice that both orders table and subscriptions table have
a subscription_id column.
And we want to match these two columns:
 orders.subscription_id
 subscriptions.subscription_id
SELECT *
FROM orders
JOIN subscriptions
ON orders.subscription_id = subscriptions.subscription_id;
Notice that in the column subscriptions.subscription_id, the table name has a
‘s’ in the end.
2.
Don’t remove the previous query.
Add a second query after your first one that only selects rows from the join
where description is equal to ‘Fashion Magazine’.
Hint
The WHERE clause goes after the JOIN!
Answer:
-- Checkpoint 1
SELECT *
FROM orders
JOIN subscriptions
ON orders.subscription_id = subscriptions.subscription_id;
-- Checkpoint 2
SELECT *
FROM orders
JOIN subscriptions
ON orders.subscription_id = subscriptions.subscription_id
WHERE subscriptions.description = 'Fashion Magazine';

Code Academy SQ L

Uploaded by

Copyright:

Available Formats

Code Academy SQ L

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Code Academy SQ L

Uploaded by

Copyright:

Available Formats

MULTIPLE TABLES

Combining Tables Manually

Doing this kind of matching is called joining two tables.

Type your answer on line 1 of the code editor.

Be sure to capitalize it the same as in the table.

The order with an order_id of 1 is in the first row. Its subscription_id is 3.

Then we look at the subscriptions table.

The subscription with a subscription_id of 3 is in the third row. Its description is Sports

Don’t write a query for this exercise!

Be sure to capitalize it the same as in the table.

If we want to combine orders and customers, we would type:

For example: Instead of selecting all the columns using *, if we only wanted to

Make sure to join on the subscription_id column.

And we want to match these two columns:

You might also like