Course Content

Intermediate SQL

## Intermediate SQL

# 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:

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:

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.

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 adotand thecolumn name. When we use more than one table in a query, we need tospecify the tablefrom which we are taking the column to make the code readable. Also, in the case where tables havecolumns 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:

`SELECT department.type, SUM(employees.salary) AS total_salary FROM employees JOIN department ON employees.department = department.name GROUP BY department.type`

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 tofully 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 subquerieswith clauses, as we do with the`UNION`

clause.

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

Task

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!

Task

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!

Everything was clear?

# 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:

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:

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.

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 adotand thecolumn name. When we use more than one table in a query, we need tospecify the tablefrom which we are taking the column to make the code readable. Also, in the case where tables havecolumns 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:

`SELECT department.type, SUM(employees.salary) AS total_salary FROM employees JOIN department ON employees.department = department.name GROUP BY department.type`

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 tofully 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 subquerieswith clauses, as we do with the`UNION`

clause.

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

Task

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!

Task

`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!

Everything was clear?

# 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:

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:

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.

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 adotand thecolumn name. When we use more than one table in a query, we need tospecify the tablefrom which we are taking the column to make the code readable. Also, in the case where tables havecolumns 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:

`SELECT department.type, SUM(employees.salary) AS total_salary FROM employees JOIN department ON employees.department = department.name GROUP BY department.type`

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 tofully 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 subquerieswith clauses, as we do with the`UNION`

clause.

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

Task

`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!

Task

`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!

Everything was clear?

**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:

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

Here's what the `category`

table looks like:

We will work with these two tables.

**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.

**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

table name, then I put adotand thecolumn name. When we use more than one table in a query, we need tospecify the tablefrom which we are taking the column to make the code readable. Also, in the case where tables havecolumns with the same name, we need SQL to understand which table and column we are referring to.

**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`

:

**join tables** and get the desired result, even if the information is **scattered across multiple tables**.

Note

`UNION`

clause, the`JOIN`

statement allows us tofully 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 subquerieswith clauses, as we do with the`UNION`

clause.

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

Task

`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!