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

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

SQL Challenges-1: Exercise-49 with Solution

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:

FieldTypeNullKeyDefaultExtra
item_codeint(11)NOPRI
item_descvarchar(255)YES
costint(11)YES

Data:

item_codeitem_desccost
101mother board2700
102RAM800
103key board300
104mouse300

Table: sales_info

Structure:

FieldTypeNullKeyDefaultExtra
distributor_idint(11)YES
item_code int(11)YES
retailer_idint(11)YES
date_of_selldateYES
quantityint(11)YES
total_costint(11)YES

Data:

distributor_iditem_code retailer_iddate_of_sellquantitytotal_cost
500110110012020-01-12308100
500110310022020-01-15254500
500210110012019-01-30255400
500110410032019-02-17752400
500310110032020-03-075513500
500310410022020-05-271003000
500210210012020-05-18659600
500210310042020-01-30452400
500310310012020-03-1230900

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: