SQL VIEW Exercises: Create a view that shows the average and total orders for each salesman after his or her name
SQL VIEW : Exercise-11 with Solution
11. From the following table, create a view to compute average purchase amount and total purchase amount for each salesperson. Return name, average purchase and total purchase amount. (Assume all names are unique).
Sample table: salesman
Sample table: orders
Sample Solution:
CREATE VIEW norders
AS SELECT name, AVG(purch_amt), SUM(purch_amt)
FROM salesman, orders
WHERE salesman.salesman_id = orders.salesman_id
GROUP BY name;
output:
sqlpractice=# SELECT * sqlpractice-# FROM norders; name | avg | sum --------------+-----------------------+---------- Mc Lyon | 1152.7150000000000000 | 2305.43 James Hoog | 2817.8650000000000000 | 11271.46 Pit Alex | 270.6500000000000000 | 270.65 Lauson Hense | 1295.4500000000000000 | 2590.90 Paul Adam | 87.6450000000000000 | 175.29 Nail Knite | 466.3166666666666667 | 1398.95 (6 rows)
Inventory database model:

Contribute your code and comments through Disqus.
Previous:From the following table, create a view to count number of the salesperson in each city. Return city, number of salespersons.
Next: From the following tables, create a view to find those salespeople who handle more than one customer. Return all the fields of salesperson.
Test your Programming skills with w3resource's quiz.
What is the difficulty level of this exercise?
SQL: Tips of the Day
How to insert a line break in a SQL Server VARCHAR/NVARCHAR string?
DECLARE @text NVARCHAR(100) SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.' SELECT @text
This prints out the following:
This is line 1.
This is line 2.
Database: SQL Server
Ref: https://bit.ly/3ry1pBI
- 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