1068 Product Sales Analysis I

Description

Table: Sales

• (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

• 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 Statement

Write a solution to report the product_name, year, and price for each sale_id in the Sales table.

Return the resulting table in any order. The result format is in the following example.

Example 1:

Input:

• Sales table:

Product table:

Output:

Explanation:

• From sale_id = 1, we can conclude that Nokia was sold for 5000 in the year 2008.
• From sale_id = 2, we can conclude that Nokia was sold for 5000 in the year 2009.
• From sale_id = 7, we can conclude that Apple was sold for 9000 in the year 2011.

Solution

This is a relatively simple problem. The problem is asking us to report specific columns for each sale. So, it should have as many rows as the number of sales. For each sale, you need to output product_name, year and price columns. Out of these columns, product_name is a column from Product table and year and price are columns from Sales table.

To retrieve product_name for each sale, you will have to join Sales table with Product table. Both these tables have product_id which can act as a join column between these tables.

1SELECT p.product_name, s.year, s.price
2    FROM Sales s
3    JOIN Product p
4    ON s.product_id = p.product_id;