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. Write a query to create a view that shows the average and total orders for each salesman after his or her name. (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: Write a query to create a view that shows the number of the salesman in each city.
Next: Write a query to create a view that shows each salesman with more than one customers.