Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.JOIN 2 Tables | Joining Tables
Intermediate SQL
course content

Course Content

Intermediate SQL

Intermediate SQL

1. Grouping
GROUP BY ClauseOrder of StatementsChallenge: Total Ride Time for Each Subway LineAverage Student's Grade ChallengeSearching for the Top Math Students ChallengeHAVING ClauseSeveral Aggregate Functions in the QueryMore Complex Filtering
2. Nested Subqueries
Nested QueriesChallenge: Non-tech Department EmployeesUsing Inner Subqueries in the FROM SectionChallenge: Employees With More Than Average SalaryUNION ClauseChallenge: Combining Table Results
3. Joining Tables
JOIN 2 TablesChallenge: Get Category By Product PriceChallenge: Get Minimum Price By CategoryChallenge: Get Product Amount By CategoryLEFT, RIGHT and INNER JOINsChallenge: Table JoinChallenge: FULL JOIN Usage
4. DDL and DML in SQL
CREATE and ConstraintsALTER and INSERT OperationsDROP and TRUNCATEUPDATE and DELETE

book
JOIN 2 Tables

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:

Our first task is to join these two tables to find out how many products are in each category. We'll use a JOIN statement to achieve this.

Before diving into the task, let's understand what a JOIN statement is and how it works.

To join two tables, they need to share a common column. Let's see how JOIN works using the employees and department tables. Their common column is employees.department and departments.name.

Note

When writing columns from these tables, start with the table name, add a dot, and then the column name. This helps keep the code clear, especially when tables have columns with the same name. It tells SQL exactly which table and column you mean.

Here is 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:


              
1234

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

Let's break down how we used JOIN in our query:

  1. In the SELECT part, we list the columns we want from both tables, making sure to include the table name for clarity;
  2. In the JOIN part, we specify the table to join and the common column that links them. Here, it's employees.department and department.name;
  3. We then group the data by type to calculate the total salary using the SUM() function.

If this seems complex, here's a simple syntax for using JOIN:

Note

Unlike the UNION clause, the JOIN statement lets us combine entire tables, not just the columns they share. Plus, with JOIN, there's no need for subqueries like with UNION.

Task
test

Swipe to show code editor

Your need 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.

Brief Instructions

  • Retrieve the category.name column and the sum of the product.amount column from the product table.
  • Assign the alias total_amount to the second column.
  • Join the category table using a JOIN statement.
  • Match the tables on the common column product.category_id = category_id.
  • Group the results by category.name.
  • Sort the results by total_amount.

Solution

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 3. Chapter 1
toggle bottom row

book
JOIN 2 Tables

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:

Our first task is to join these two tables to find out how many products are in each category. We'll use a JOIN statement to achieve this.

Before diving into the task, let's understand what a JOIN statement is and how it works.

To join two tables, they need to share a common column. Let's see how JOIN works using the employees and department tables. Their common column is employees.department and departments.name.

Note

When writing columns from these tables, start with the table name, add a dot, and then the column name. This helps keep the code clear, especially when tables have columns with the same name. It tells SQL exactly which table and column you mean.

Here is 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:


              
1234

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

Let's break down how we used JOIN in our query:

  1. In the SELECT part, we list the columns we want from both tables, making sure to include the table name for clarity;
  2. In the JOIN part, we specify the table to join and the common column that links them. Here, it's employees.department and department.name;
  3. We then group the data by type to calculate the total salary using the SUM() function.

If this seems complex, here's a simple syntax for using JOIN:

Note

Unlike the UNION clause, the JOIN statement lets us combine entire tables, not just the columns they share. Plus, with JOIN, there's no need for subqueries like with UNION.

Task
test

Swipe to show code editor

Your need 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.

Brief Instructions

  • Retrieve the category.name column and the sum of the product.amount column from the product table.
  • Assign the alias total_amount to the second column.
  • Join the category table using a JOIN statement.
  • Match the tables on the common column product.category_id = category_id.
  • Group the results by category.name.
  • Sort the results by total_amount.

Solution

Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
Everything was clear?

How can we improve it?

Thanks for your feedback!

Section 3. Chapter 1
Switch to desktopSwitch to desktop for real-world practiceContinue from where you are using one of the options below
We're sorry to hear that something went wrong. What happened?
some-alt