1+ -- 1068. Product Sales Analysis I
2+ --
3+ -- Table: Sales
4+ --
5+ -- +-------------+-------+
6+ -- | Column Name | Type |
7+ -- +-------------+-------+
8+ -- | sale_id | int |
9+ -- | product_id | int |
10+ -- | year | int |
11+ -- | quantity | int |
12+ -- | price | int |
13+ -- +-------------+-------+
14+ -- (sale_id, year) is the primary key of this table.
15+ -- product_id is a foreign key to Product table.
16+ -- Note that the price is per unit.
17+ -- Table: Product
18+ --
19+ -- +--------------+---------+
20+ -- | Column Name | Type |
21
10000
+ -- +--------------+---------+
22+ -- | product_id | int |
23+ -- | product_name | varchar |
24+ -- +--------------+---------+
25+ -- product_id is the primary key of this table.
26+ --
27+ --
28+ -- Write an SQL query that reports all product names of the products in the Sales table along with their selling year and price.
29+ --
30+ -- For example:
31+ --
32+ -- Sales table:
33+ -- +---------+------------+------+----------+-------+
34+ -- | sale_id | product_id | year | quantity | price |
35+ -- +---------+------------+------+----------+-------+
36+ -- | 1 | 100 | 2008 | 10 | 5000 |
37+ -- | 2 | 100 | 2009 | 12 | 5000 |
38+ -- | 7 | 200 | 2011 | 15 | 9000 |
39+ -- +---------+------------+------+----------+-------+
40+ --
41+ -- Product table:
42+ -- +------------+--------------+
43+ -- | product_id | product_name |
44+ -- +------------+--------------+
45+ -- | 100 | Nokia |
46+ -- | 200 | Apple |
47+ -- | 300 | Samsung |
48+ -- +------------+--------------+
49+ --
50+ -- Result table:
51+ -- +--------------+-------+-------+
52+ -- | product_name | year | price |
53+ -- +--------------+-------+-------+
54+ -- | Nokia | 2008 | 5000 |
55+ -- | Nokia | 2009 | 5000 |
56+ -- | Apple | 2011 | 9000 |
57+ -- +--------------+-------+-------+
58+
59+ select p .product_name , s .year , s .price
60+ from Product p
61+ join Sales s
62+ on s .product_id = p .product_id
0 commit comments