5. Product Sales Analysis I

Problem

Table: Sales


+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.
Note that the price is per unit.

Table: Product


+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id is the primary key (column with unique values) of this table.
Each row of this table indicates the product name of each product.

Problem Definition

Write a solution to report the product_name, year, and price for each sale_id in the Sales table. Also, return the output in the order of year.

Example

1706519125482770 Image scaled to 90%

Output

1706519170833291 Image scaled to 45%

Try It Yourself

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

Solution

We can utilize SQL query that uses the INNER JOIN clause to retrieve information from two tables, namely “Sales” and “Product.” This type of query is used when you want to combine rows from both tables based on a related column.


SELECT product_name,
       year,
       price
FROM   Sales
       INNER JOIN Product
               ON Sales.product_id = Product.product_id
ORDER BY year;

Let’s break down the query step by step:

Step 1: INNER JOIN


INNER JOIN Product ON Sales.product_id = Product.product_id;

This is the INNER JOIN clause that combines rows from the “Sales” table and the “Product” table. The condition for the join is that the “product_id” column in the “Sales” table must match the “product_id” column in the “Product” table.

Output After Step 1:


+---------+------------+------+----------+-------+-------------+
| sale_id | product_id | year | quantity | price |product_name |
+---------+------------+------+----------+-------+-------------+
| 1       | 100        | 2008 | 10       | 5000  | Nokia       |
| 2       | 100        | 2009 | 12       | 5000  | Apple       |
| 7       | 200        | 2011 | 15       | 9000  | Samsung     |
+---------+------------+------+----------+-------+-------------+

Step 2: Select specific columns


SELECT product_name,
       year,
       price
FROM   Sales
       INNER JOIN product
               ON Sales.product_id = Product.product_id;

This part of the query specifies the columns that you want to retrieve in the result set. In this case, it’s the product name, year, and price.

Step 3: Order the table raw by year


ORDER BY year;

Final Output:


+--------------+-------+-------+
| product_name | year  | price |
+--------------+-------+-------+
| Nokia        | 2008  | 5000  |
| Nokia        | 2009  | 5000  |
| Apple        | 2011  | 9000  |
+--------------+-------+-------+