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 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:
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'semployees.department
anddepartment.name
; - Then, we aggregate the data by
type
(since we have an aggregateSUM()
function in theSELECT
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, theJOIN
statement allows us to fully combine tables, not just the common columns between them. It's also worth noting that when using theJOIN
statement, we don't need to use subqueries with clauses, as we do with theUNION
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!
Thanks for your feedback!
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 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:
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'semployees.department
anddepartment.name
; - Then, we aggregate the data by
type
(since we have an aggregateSUM()
function in theSELECT
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, theJOIN
statement allows us to fully combine tables, not just the common columns between them. It's also worth noting that when using theJOIN
statement, we don't need to use subqueries with clauses, as we do with theUNION
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!
Thanks for your feedback!
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 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:
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'semployees.department
anddepartment.name
; - Then, we aggregate the data by
type
(since we have an aggregateSUM()
function in theSELECT
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, theJOIN
statement allows us to fully combine tables, not just the common columns between them. It's also worth noting that when using theJOIN
statement, we don't need to use subqueries with clauses, as we do with theUNION
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!
Thanks for your feedback!
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 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:
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'semployees.department
anddepartment.name
; - Then, we aggregate the data by
type
(since we have an aggregateSUM()
function in theSELECT
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, theJOIN
statement allows us to fully combine tables, not just the common columns between them. It's also worth noting that when using theJOIN
statement, we don't need to use subqueries with clauses, as we do with theUNION
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!