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.


 

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
;

Leave a Comment