JOIN 2 Tables

Great! We've got the attention of a company that owns a small online store. They have 2 tables that are related to each other. The first table contains information about the products sold in the online store.

Here's what the product table looks like:

id	name	amount	price	category_id
1	Shrimp	50	450.00	1
2	Salmon	30	300.00	1
3	Tuna	40	350.00	1
4	Rice	100	200.00	2
...	...	...	...	...
62	Frozen Pie	25	300.00	10

The second table contains product categories on the website, along with a brief description for each category.

Here's what the category table looks like:

id	name	description
1	Seafood	Products from the sea, including fish and shellfish.
2	Grains	Grain-based foods such as rice, wheat, and quinoa.
3	Vegetables	Fresh vegetables including leafy greens, roots, and tubers.
4	Fruits	Fresh fruits such as apples, bananas, and berries.
...	...	...
10	Frozen Foods	Frozen products including meals, desserts, and vegetables.

We will work with these two tables.

We have the first task: join these 2 tables and see the amount of products in each category. To do this, we will use a JOIN statement.

Let's take a break from the main task and understand what a JOIN statement is and how to use it.

JOIN in SQL is an operation that combines rows from two or more tables based on a related column between them. JOIN allows obtaining data from multiple tables in one query, which simplifies the analysis and processing of related data.

To join 2 tables, they must have a common column between them. Let's consider the usage of JOIN using the example of the employees and department tables. I'll remind you that their common column is employees.department and departments.name.

Note
Notice how I wrote the columns from these tables. First, I write the table name, then I put a dot and the column name. When we use more than one table in a query, we need to specify the table from which we are taking the column to make the code readable. Also, in the case where tables have columns with the same name, we need SQL to understand which table and column we are referring to.

Let's set ourselves the task of retrieving the total salary for employees in each department type (tech/non-tech).

The query to accomplish this task will look like this:

Let's step by step understand what we did and how we used JOIN:

In the SELECT section, we specify the columns we need to retrieve from two tables, indicating the table name to which the column belongs;
In the JOIN section, we specify the table we want to join, followed by specifying the common column. In our case, it's employees.department and department.name;
Then, we aggregate the data by type (since we have an aggregate SUM() function in the SELECT section to find the total salary) and get the result we're interested in.

The query might look complex, so let's look at the general syntax for using JOIN:

This way, we can join tables and get the desired result, even if the information is scattered across multiple tables.

Note
Unlike the UNION clause, the JOIN statement allows us to fully combine tables, not just the common columns between them. It's also worth noting that when using the JOIN statement, we don't need to use subqueries with clauses, as we do with the UNION clause.

Now let's get back to the task at hand and reinforce our knowledge with practice:

Tarefa

Your task is to join the two tables: category and product. The common columns for these two tables are product.category_id and category.id. Your task is to find the total amount of products in each category. To do this, you need to calculate the sum of the product.amount column. Use the alias total_amount for this column! At the end of your query, sort the result by the total_amount column in ascending order.

In the response, you should have 2 columns: category.name and total.amount.

Good luck!

Tudo estava claro?

Seção 3. Capítulo 1

JOIN 2 Tables

Here's what the product table looks like:

id	name	amount	price	category_id
1	Shrimp	50	450.00	1
2	Salmon	30	300.00	1
3	Tuna	40	350.00	1
4	Rice	100	200.00	2
...	...	...	...	...
62	Frozen Pie	25	300.00	10

The second table contains product categories on the website, along with a brief description for each category.

Here's what the category table looks like:

id	name	description
1	Seafood	Products from the sea, including fish and shellfish.
2	Grains	Grain-based foods such as rice, wheat, and quinoa.
3	Vegetables	Fresh vegetables including leafy greens, roots, and tubers.
4	Fruits	Fresh fruits such as apples, bananas, and berries.
...	...	...
10	Frozen Foods	Frozen products including meals, desserts, and vegetables.

We will work with these two tables.

We have the first task: join these 2 tables and see the amount of products in each category. To do this, we will use a JOIN statement.

Let's take a break from the main task and understand what a JOIN statement is and how to use it.

Note
Notice how I wrote the columns from these tables. First, I write the table name, then I put a dot and the column name. When we use more than one table in a query, we need to specify the table from which we are taking the column to make the code readable. Also, in the case where tables have columns with the same name, we need SQL to understand which table and column we are referring to.

Let's set ourselves the task of retrieving the total salary for employees in each department type (tech/non-tech).

The query to accomplish this task will look like this:

Let's step by step understand what we did and how we used JOIN:

In the SELECT section, we specify the columns we need to retrieve from two tables, indicating the table name to which the column belongs;
In the JOIN section, we specify the table we want to join, followed by specifying the common column. In our case, it's employees.department and department.name;
Then, we aggregate the data by type (since we have an aggregate SUM() function in the SELECT section to find the total salary) and get the result we're interested in.

The query might look complex, so let's look at the general syntax for using JOIN:

This way, we can join tables and get the desired result, even if the information is scattered across multiple tables.

Note
Unlike the UNION clause, the JOIN statement allows us to fully combine tables, not just the common columns between them. It's also worth noting that when using the JOIN statement, we don't need to use subqueries with clauses, as we do with the UNION clause.

Now let's get back to the task at hand and reinforce our knowledge with practice:

Tarefa

In the response, you should have 2 columns: category.name and total.amount.

Good luck!

Tudo estava claro?

Seção 3. Capítulo 1

Intermediate SQL

JOIN 2 Tables

Tarefa

Intermediate SQL

JOIN 2 Tables

Tarefa