w3resource

SQL Challenges-1: Sale history of items for a particular period

SQL Challenges-1: Exercise-37 with Solution

From the following table write a SQL query to display those items that were only sold in the 2nd quarter of a year, i.e. April 1st to June end for the year 2020. Return item code and item description.

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_codeint(11)YES
retailer_id int(11)YES
date_of_selldateYES
quantityint(11)YES
total_costint(11)YES

Data:

distributor_iditem_coderetailer_iddate_of_sellquantitytotal_cost
500110110012020-02-1238100
500110310022020-03-15154500
500210110012019-06-2425400
500110410032019-09-1182400
500310110032020-10-21513500
500310410022020-12-27103000
500210210012019-05-18129600
500210310042020-06-1782400
500310310012020-04-123900

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-02-12',3,8100);
INSERT INTO sales_info VALUES(5001,103,1002,'2020-03-15',15,4500);
INSERT INTO sales_info VALUES(5002,101,1001,'2020-06-24',2,5400);
INSERT INTO sales_info VALUES(5001,104,1003,'2020-09-11',8,2400);
INSERT INTO sales_info VALUES(5003,101,1003,'2020-10-21',5,13500);
INSERT INTO sales_info VALUES(5003,104,1002,'2020-12-27',10,3000);
INSERT INTO sales_info VALUES(5002,102,1001,'2020-05-18',12,9600);
INSERT INTO sales_info VALUES(5002,103,1004,'2020-06-17',8,2400);
INSERT INTO sales_info VALUES(5003,103,1001,'2020-04-12',3,900);

SELECT item_code, item_desc
FROM item
WHERE item_code IN (
SELECT item_code
FROM sales_info
WHERE date_of_sell BETWEEN '2020-04-01' AND '2020-06-30'
);

Sample Output:

item_code|item_desc   |
---------|------------|
      101|mother board|
      102|RAM         |
      103|key board   |

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Retailers buying history.
Next: Highest purchase for each customer.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8