w3resource
SQL exercises

SQL Subquery Exercises: Find all orders with order amounts which are above-average amounts for their customers

SQL SUBQUERY: Exercise-12 with Solution

12. Write a query to find all orders with order amounts which are above-average amounts for their customers.

Sample table: Orders


Sample table: Customer


Sample Solution:

SELECT * 
FROM orders a
WHERE purch_amt >
    (SELECT AVG(purch_amt) FROM orders b 
     WHERE b.customer_id = a.customer_id);

Sample Output:

ord_no	purch_amt	ord_date	customer_id	salesman_id
70008	5760.00		2012-09-10	3002		5001
70003	2480.40		2012-10-10	3009		5003
70013	3045.60		2012-04-25	3002		5001
70007	948.50		2012-09-10	3005		5002

Explanation:

SQL Subqueries: Find all orders with order amounts which are above-average amounts for their customers.

Practice Online


Inventory database model

Query Visualization:

Duration:

Query visualization of Find all orders with order amounts which are above-average amounts for their customers - Duration

Rows:

Query visualization of Find all orders with order amounts which are above-average amounts for their customers - Rows

Cost:

Query visualization of Find all orders with order amounts which are above-average amounts for their customers - Cost

Contribute your code and comments through Disqus.

Previous: Write a query to find the name and numbers of all salesmen who had more than one customer.
Next: Write a queries to find all orders with order amounts which are on or above-average amounts for their customers.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming