Write an SQL query that reports the products that
were only sold in the first quarter of 2019. That is, between 2019-01-01 and
2019-03-31 inclusive.
Return the result table in any order.
table name: Product
table name: Sales
Solution:
select s.product_id, p.product_name
from sales s, product p
where s.product_id = p.product_id
group by s.product_id, p.product_name
having min(s.sale_date) >= '2019-01-01' AND max(s.sale_date) <= '2019-03-31'
select p.product_id,p.product_name from sales as s
join product as p on s.product_id = p.product_id
where sale_date between '2019-01-01' and '2019-03-31'
except
select p.product_id,p.product_name from sales as s
join product as p on s.product_id = p.product_id
where sale_date not between '2019-01-01' and '2019-03-31'
Output:
Execution Plan:
SQL Script:
CREATE TABLE [dbo].[Product](
[product_id]
[int] NOT NULL,
[product_name]
[varchar](50) NULL,
[unit_price]
[int] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[product_id]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Sales] Script Date: 20-03-2023 18:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sales](
[seller_id][int] NOT NULL,
[product_id][int] NULL,
[buyer_id][int] NULL,
[sale_date][date] NULL,
[quantity][int] NULL,
[price][int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Product] ([product_id], [product_name], [unit_price]) VALUES (1, N'SB', 1000)
GO
INSERT [dbo].[Product] ([product_id], [product_name], [unit_price]) VALUES (2, N'G4', 800)
GO
INSERT [dbo].[Product] ([product_id], [product_name], [unit_price]) VALUES (3, N'iPhone', 1400)
GO
INSERT [dbo].[Sales] ([seller_id], [product_id], [buyer_id], [sale_date], [quantity], [price]) VALUES (1, 1, 1, CAST(N'2019-01-21' AS Date), 2, 2000)
GO
INSERT [dbo].[Sales] ([seller_id], [product_id], [buyer_id], [sale_date], [quantity], [price]) VALUES (1, 2, 2, CAST(N'2019-02-17' AS Date), 1, 800)
GO
INSERT [dbo].[Sales] ([seller_id], [product_id], [buyer_id], [sale_date], [quantity], [price]) VALUES (2, 2, 3, CAST(N'2019-06-02' AS Date), 1, 800)
GO
INSERT [dbo].[Sales] ([seller_id], [product_id], [buyer_id], [sale_date], [quantity], [price]) VALUES (3, 3, 4, CAST(N'2019-05-13' AS Date), 2, 2800)
GO
ALTER TABLE [dbo].[Sales] WITH CHECK ADD CONSTRAINT [FK_Sales_Product] FOREIGN KEY([product_id])
REFERENCES [dbo].[Product] ([product_id])
GO
ALTER TABLE [dbo].[Sales] CHECK CONSTRAINT [FK_Sales_Product]
GO