Compose an SQL statement that lists the Order ID, Order Date, Product ID, and Or

Question

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

Table2 : Order_t

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.

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

Explanation / Answer

Answer)

select Order_ID, Product_ID, Quantity, (select Order_Date from Order_t where Order_line_t.Order_ID=Order_t.Order_ID) as Order_Date from Order_line_t where Quantity > (select avg(Quantity) from Order_line_t);

The above query selects all orders that lists 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.

We calculate the average of the Quantity in the sub-query and list all th items for which the above value is greater than that.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.