Tables – product and sales are provided.
Write SQL query to identify products which were sold only in first quarter of year 2019. If a product is sold in any other quarter then that is not supposed to be present in final output.
Output should be product_id and product_name.
Input Table: sales
Input Table: product
- product_id = 10 is the only product which was sold in first quarter of 2019.
- product_id = 20 was sold in first quarter as well as in third quarter, thats why it was excluded from final result.
CREATE TABLE IF NOT EXISTS product ( product_id INT, product_name VARCHAR(10), unit_price INT ); INSERT INTO product VALUES (10, 'S10', 2000); INSERT INTO product VALUES (20, '7Pro', 600); INSERT INTO product VALUES (30, 'iPhone', 3300); CREATE TABLE IF NOT EXISTS sales ( seller_id INT, product_id INT, buyer_id INT, sale_date DATE, quantity INT, price INT ); INSERT INTO sales VALUES (11, 10, 71, '2019-02-22', 3, 6000); INSERT INTO sales VALUES (11, 20, 72, '2019-03-27', 1, 600); INSERT INTO sales VALUES (22, 20, 73, '2019-07-04', 1, 900); INSERT INTO sales VALUES (33, 30, 74, '2019-06-21', 2, 6600);
SELECT C.product_id, C.product_name FROM product C WHERE product_id NOT IN ( SELECT B.product_id FROM product A JOIN sales B ON A.product_id = B.product_id WHERE B.sale_date < '2019-01-01' OR B.sale_date > '2019-03-31' ) ;