1
+ /*
2
+ Table: Product
3
+
4
+ +--------------+---------+
5
+ | Column Name | Type |
6
+ +--------------+---------+
7
+ | product_id | int |
8
+ | product_name | varchar |
9
+ | unit_price | int |
10
+ +--------------+---------+
11
+ product_id is the primary key of this table.
12
+ Each row of this table indicates the name and the price of each product.
13
+ Table: Sales
14
+
15
+ +-------------+---------+
16
+ | Column Name | Type |
17
+ +-------------+---------+
18
+ | seller_id | int |
19
+ | product_id | int |
20
+ | buyer_id | int |
21
+ | sale_date | date |
22
+ | quantity | int |
23
+ | price | int |
24
+ +-------------+---------+
25
+ This table has no primary key, it can have repeated rows.
26
+ product_id is a foreign key to the Product table.
27
+ Each row of this table contains some information about one sale.
28
+
29
+
30
+ Required Query:
31
+
32
+ Report the products that were only sold in the first quarter of 2019. That is, between 2019-01-01 and 2019-03-31 inclusive.
33
+
34
+
35
+ SQL Schema:
36
+
37
+ Create table If Not Exists Product (product_id int, product_name varchar(10), unit_price int)
38
+ Create table If Not Exists Sales (seller_id int, product_id int, buyer_id int, sale_date date, quantity int, price int)
39
+ Truncate table Product
40
+ insert into Product (product_id, product_name, unit_price) values ('1', 'S8', '1000')
41
+ insert into Product (product_id, product_name, unit_price) values ('2', 'G4', '800')
42
+ insert into Product (product_id, product_name, unit_price) values ('3', 'iPhone', '1400')
43
+ Truncate table Sales
44
+ insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '1', '1', '2019-01-21', '2', '2000')
45
+ insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('1', '2', '2', '2019-02-17', '1', '800')
46
+ insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('2', '2', '3', '2019-06-02', '1', '800')
47
+ insert into Sales (seller_id, product_id, buyer_id, sale_date, quantity, price) values ('3', '3', '4', '2019-05-13', '2', '2800')
48
+
49
+
50
+ Example:
51
+
52
+ Input:
53
+ Product table:
54
+ +------------+--------------+------------+
55
+ | product_id | product_name | unit_price |
56
+ +------------+--------------+------------+
57
+ | 1 | S8 | 1000 |
58
+ | 2 | G4 | 800 |
59
+ | 3 | iPhone | 1400 |
60
+ +------------+--------------+------------+
61
+ Sales table:
62
+ +-----------+------------+----------+------------+----------+-------+
63
+ | seller_id | product_id | buyer_id | sale_date | quantity | price |
64
+ +-----------+------------+----------+------------+----------+-------+
65
+ | 1 | 1 | 1 | 2019-01-21 | 2 | 2000 |
66
+ | 1 | 2 | 2 | 2019-02-17 | 1 | 800 |
67
+ | 2 | 2 | 3 | 2019-06-02 | 1 | 800 |
68
+ | 3 | 3 | 4 | 2019-05-13 | 2 | 2800 |
69
+ +-----------+------------+----------+------------+----------+-------+
70
+ Output:
71
+ +-------------+--------------+
72
+ | product_id | product_name |
73
+ +-------------+--------------+
74
+ | 1 | S8 |
75
+ +-------------+--------------+
76
+ */
77
+
78
+ -- Solution:
79
+ SELECT Product .product_id , product_name
80
+ FROM Product JOIN Sales
81
+ ON Product .product_id = Sales .product_id
82
+ GROUP BY Product .product_id
83
+ HAVING MIN (sale_date) >= ' 2019-01-01' AND MAX (sale_date) <= ' 2019-03-31'
0 commit comments