Question

Write a query that returns all of the main information that are associated with the purchase orders that have an amount greater than 70% of the average amount of all purchase orders, the output should be sorted by the largest purchase order amount

Answer 1

I have answered this based on a dummy database table but should work on a different schema as well.  

Database table

PurchaseOrders

ID	OrderName	OrderAmount	OrderItems	OrderDate	OrderPerson
1	ABC	1000	10	Oct-18	DEF
2	XYZ	1500	4	Nov-19	DEF
3	TUV	5000	6	Dec-20	WXY
4	XUV	10000	100	Jan-19	ZUH
5	KUV	4000	1	Mar-18	DUH

Snapshot:

It is called "PurchaseOrders" and has 6 fields (ID being the primary key)

To do the above question, I have used two SQL queries:

Average order amount query:

This calculates the average amount of the the order from the above table.

SELECT avg(PurchaseOrders.OrderAmount) AS AverageOrder
FROM PurchaseOrders;

Snapshot:

Query output:

In this particular example, the average comes out to be 4,300. It would change with the values in the table.

Final query:

This will extract the records from the PurchaseOrders table where the order amount is greater than 70% of the average order amount (in this case - 3,010).

SELECT *
FROM PurchaseOrders, [Average order]
WHERE (((PurchaseOrders.OrderAmount)>0.7*[Average order].AverageOrder))
ORDER BY PurchaseOrders.OrderAmount DESC;

Snapshot:

Description:

1st line - selects all the fields from the table. However, if you wish to select only a few you can replace * with PurchaseOrders.ID, PurchaseOrders.OrderAmount so on and so forth.

2nd line - Extracts the information from PurchaseOrders table and Average order query

3rd line - Checks the condition if the order amount in PurchaseOrders table is more than 70% of the average amount of all the orders

4th line - Sorts the output by the largest purchase order amount

Output snapshot:

Let me know if anything is not clear.