Novels Sales

         

Write an SQL query to retrieve all the Novels that were only sold in the first half of the year 2022, i.e, between 2022-01-01 and 2022-06-31 inclusive.

Input :-

Novels table :-
Novel_ID is the Primary Key column for the Novels table.

Novel_ID	Novel_Name
1	The Great Gatsby
2	The Awakening
3	The Stranger

Sales table:-

There is no Primary Key column for the Sales table.

Novel_ID	Sale_Date	Quantity
1	2022-02-12	4
2	2022-04-17	5
2	2022-08-05	2
3	2022-10-21	3

Output :-

Novel_ID	Novel_Name
1	The Great Gatsby

Query :-

select n.Novel_ID, n.Novel_Name
from Novels n
join Sales s
on n.Novel_ID=s.Novel_ID
group by n.Novel_ID
having min(s.Sale_Date)>='2022-01-01' and max(s.Sale_Date)<='2022-06-31';

Explanation : - 

This query retrieves the ‘Novel_ID’ and ‘Novel_Name’ from the "Novels" table, which represents a collection of novels. It aims to find novels that were only sold in the first half of the year 2022.

To achieve this, the query joins the "Novels" table with the "Sales" table using the common column ‘Novel_ID’. The "Sales" table contains information about novel sales, including the sale date (represented by the ‘Sale_Date’ column).

The join operation links the novels in the "Novels" table with their corresponding sales in the "Sales" table. By grouping the result based on the Novel_ID, the query ensures that each novel appears only once in the output.

The having clause applies conditions to the grouped data. In this case, it checks for novels where the earliest sale date (min(s.Sale_Date)) is on or after January 1st, 2022, and the latest sale date (max(s.Sale_Date)) is on or before June 31st, 2022.

The result of this query will provide the ‘Novel_ID’ and ‘Novel_Name’ of the novels that meet these criteria, indicating the novels that were only sold exclusively within the first half of 2022.