Compose an SQL statement that lists the Order ID, Order Date, Product ID, and Order Quantity for all orders for which the order quantity is greater than the average order quantity for that product [Hint: This involves a correlated sub query].

Table1: Order_line_t

Order_line_t

Order_ID	Product_ID	Quantity
1001	1	2
1001	2	2
1001	4	1
1002	3	5
1003	3	1
1004	6	2
1004	8	2
1005	4	4
1006	4	1
1006	5	2
1006	7	2
1007	1	3
1007	2	2
1008	3	2
1008	8	3
1009	4	2
1009	7	3
1010	8	10

Table2 : Order_t

Order_t

Order_ID	Order_Date	Customer_ID
1001	10/21/2011	1
1002	10/25/2011	8
1003	10/26/2011	15
1004	10/27/2011	5
1005	11/24/2011	3
1006	11/27/2011	2
1007	11/28/2011	11
1008	12/3/2011	12
1009	12/5/2011	1
1010	1/16/2012	4
1011	1/1/2004	1

To understand the requirement for this query, let us use the following examples:

* If you open the Order Line table, you will see that Product ID = 3 was requested in three different orders: Order ID 1002 (Quantity = 5), Order ID 1003 (Quantity = 1), and Order ID 1008 (Quantity = 2). Thus, the average order quantity for Product ID = 3 was (5 + 1 + 2)/3 = 2.667. Consequently, in the result table, there will be a row that shows Order ID = 1002, Order Date = 10/25/2011 (note that in the Order table, Order ID 1002 was submitted on 10/25/2011), Product ID = 3, and Quantity = 5. The reason is because this row has Quantity = 5, which is greater than the average order quantity of 2.667.

* Similarly, Product ID = 1 was requested in two different orders: Order ID 1001 (Quantity = 2) and Order ID 1007 (Quantity =3). Thus, the average order quantity for Product ID = 1 was

(2 +3)/2 = 2.5. Consequently, in the result table, there will also be a row that shows Order ID = 1007,Order Date = 11/28/2011, Product ID = 1, and Quantity = 3 because this row has Quantity = 3, which is greater than the average order quantity of 2.5.

* In the result table, you will also see other rows. Each of these rows shows a product ID that has order quantity greater than the average order quantity for that product.