Write an SQL query that reports all product names of products in the “sales” table along with their selling year and price.
Input Table : sales
sale_id | product_id | sale_year | quantity | price |
20 | 80 | 2017 | 21 | 300 |
30 | 80 | 2018 | 23 | 400 |
40 | 13 | 2019 | 56 | 600 |
Input Table: product
product_id | product_name |
80 | Asus |
13 | Dell |
15 | Videocon |
Output Table:
product_name | sale_year | price |
Asus | 2017 | 300 |
Asus | 2018 | 400 |
Dell | 2019 | 600 |
Explanation:
- product_id is Primary Key of “product” table.
- product_id is Foreign Key and (sale_id, year) is primary key of “sales” table.
Fetch total quantity of units sold for each product collectively.
DDLs:
CREATE TABLE sales ( sale_id INT, product_id INT, sale_year INT, quantity INT, price INT ); INSERT INTO sales VALUES (20, 80, 2017, 21, 300); INSERT INTO sales VALUES (30, 80, 2018, 23, 400); INSERT INTO sales VALUES (40, 13, 2019, 56, 600); CREATE TABLE product ( product_id INT, product_name VARCHAR(10) ); INSERT INTO product VALUES (80, 'Asus'); INSERT INTO product VALUES (13, 'Dell'); INSERT INTO product VALUES (15, 'Videocon');
Solution:
SELECT A.product_name, B.sale_year, B.price FROM product A JOIN sales B ON A.product_id = B.product_id GROUP BY A.product_name, B.sale_year, B.price ORDER BY B.sale_year ;