Oppiskele JOIN 2 Tables | Taulujen Yhdistäminen

Osio 3. Luku 1

single

Pyyhkäise näyttääksesi valikon

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.

Definition

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 two tables, they need to share a common column. For our tables, this shared link is product.category_id and category.id.

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.

Let's see how JOIN works in action. Imagine we want to retrieve the average price of products for each category name.

The query to accomplish this looks like this:


              1234
            
SELECT category.name, AVG(product.price) AS average_price
FROM product
JOIN category ON product.category_id = category.id
GROUP BY category.name

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

In the SELECT part, we list the columns we want from both tables, making sure to include the table name for clarity;
In the JOIN part, we specify the table to join (category) and the common columns that link them (product.category_id = category.id);
We then group the data by category.name to calculate the average price using the AVG() function.

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

SELECT table1.column, table2.column
FROM table1
JOIN table2 ON table1.common_column = table2.common_column

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.

Tehtävä

Pyyhkäise aloittaaksesi koodauksen

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.

Ratkaisu

Vaihda työpöytään todellista harjoitusta vartenJatka siitä, missä olet käyttämällä jotakin alla olevista vaihtoehdoista

Oliko kaikki selvää?

Kiitos palautteestasi!

Osio 3. Luku 1

single

Kysy tekoälyä

Kysy mitä tahansa tai kokeile jotakin ehdotetuista kysymyksistä aloittaaksesi keskustelumme