Product Sales Analysis – 1

Write an SQL query that reports all product names of products in the “sales” table along with their selling year and price.

---

 

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
;