CREATE TABLE SALES

(

SALE_ID INTEGER,

PRODUCT_ID INTEGER,

YEAR INTEGER,

QUANTITY INTEGER,

PRICE INTEGER

);

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

5. Write a SQL query to find the sum of sales of current row and previous 2 rows in a product group? Sample outcome of the SQL statement is given below.

PRODUCT_ID QUANTITY CALC_SALES

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

100 25 25

100 16 41

100 12 53

200 20 20

200 15 35

200 14 49

PART 2 [5 marks]

For the following vectors, X and Y, calculate the indicated similarity or distance measure.

X = (1, 0, 1, 0), Y = (0, 0, 1, 1), Cosine, Correlation, Euclidean, Jaccard, and Hamming.

PART 3 [6 marks]

A transaction database from one grocery shop server is as follows:

Transaction Id	Items
1	Item1, Item2, Item5
2	Item2, Item4
3	Item2, Item3
4	Item1, Item2, Item4
5	Item1, Item3
6	Item2, Item3
7	Item1, Item3
8	Item1, Item2, Item3, Item5
9	Item1, Item2, Item3
10	Item1, Item2, Item3, Item4, Item 5

Assuming a minimum support of 20% (i.e., 2 transactions out of 10).

• Calculate the frequent 1-itemset (L1).
• Calculate the frequent 2-itemset (L2).
• Generate the possible association rules and calculate their level of confidence.