SQL Subquery Exercises: Find all orders with order amounts which are on or above-average amounts for their customers
SQL SUBQUERY : Exercise-13 with Solution
13. From the following tables write a SQL query to find those orders that are equal or higher than the average amount of the orders. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
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);
Output of the Query:
ord_no purch_amt ord_date customer_id salesman_id 70009 270.65 2012-09-10 3001 5005 70005 2400.60 2012-07-27 3007 5001 70008 5760.00 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.40 2012-10-10 3009 5003 70011 75.29 2012-08-17 3003 5007 70013 3045.60 2012-04-25 3002 5001 70007 948.50 2012-09-10 3005 5002 70012 250.45 2012-06-27 3008 5002
Explanation:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Contribute your code and comments through Disqus.
Previous: From the following tables, write a SQL query to find those orders, which are higher than average amount of the orders. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
Next: Write a query to find the sums of the amounts from the orders table, grouped by date, eliminating all those dates where the sum was not at least 1000.00 above the maximum order amount for that date.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework