SQL Challenges-1: List the items sold out within a specific period

Last update on December 20 2024 11:58:59 (UTC/GMT +8 hours)

From the following tables write a SQL query to get the description of items with 50 or more quantities sold out within January and February of 2020. Return item description and sale quantity.

Input:

Table: item

Structure:

Data:

Table: sales_info

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

CREATE TABLE item (item_code int not null unique, item_desc varchar(255), cost int);
INSERT INTO item VALUES(101,'mother board',	2700);
INSERT INTO item VALUES(102,'RAM',	800);
INSERT INTO item VALUES(103,'key board',300);
INSERT INTO item VALUES(104,'mouse',300);


CREATE TABLE sales_info (distributor_id int, item_code int, retailer_id int, date_of_sell date, quantity int, total_cost int);
INSERT INTO sales_info VALUES(5001,101,1001,'2020-01-12',30,8100);
INSERT INTO sales_info VALUES(5001,103,1002,'2020-01-15',25,4500);
INSERT INTO sales_info VALUES(5002,101,1001,'2019-01-30',25,5400);
INSERT INTO sales_info VALUES(5001,104,1003,'2019-02-17',75,2400);
INSERT INTO sales_info VALUES(5003,101,1003,'2020-03-07',55,13500);
INSERT INTO sales_info VALUES(5003,104,1002,'2020-05-27',100,3000);
INSERT INTO sales_info VALUES(5002,102,1001,'2020-05-18',65,9600);
INSERT INTO sales_info VALUES(5002,103,1004,'2020-01-30',45,2400);
INSERT INTO sales_info VALUES(5003,103,1001,'2020-03-12',30,900);



select item_desc, sum(quantity) as sale_quantity
from item a join sales_info b
on a.item_code = b.item_code
where month(date_of_sell) between  1 and 2
group by item_desc
having sale_quantity >= 50

Sample Output:

item_desc   |sale_quantity|
------------|-------------|
key board   |           70|
mother board|           55|
mouse       |           75|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Show running quantiry for each unit type of item.
Next: Find the order id and the item name for all companies who are not registered with the distributor.