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.