Question No 2 [10 marks]

CREATE TABLE SALES

(

SALE_ID INTEGER,

PRODUCT_ID INTEGER,

YEAR INTEGER,

QUANTITY INTEGER,

PRICE INTEGER

);

1. Consider the product schema (given above) to answer the following questions.

1. Write a SQL query which retrieves product it and total quantity of bestselling product (i.e., largest quantity product) in each year.

2. Write a SQL query which retrieves three highest and three lowest price products in the sales table.

3. Write a SQL query using the analytic function to find the total sales of each product? Sample outcome of the SQL statement is given below.

PRODUCT_ID QUANTITY TOT_SALES

----------------------------------------------------------

100 12 22

100 10 22

200 15 25

200 10 25

4. Write a SQL query to find the cumulative sum of sales(QUANTITY) of each product? Sample outcome of the SQL statement is given below.

PRODUCT_ID QUANTITY CUM_SALES

----------------------------------------------------------

100 8 8

100 10 18

100 12 30

200 10 10

200 13 23

200 14 37