w3resource

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:

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?



Share this Tutorial / Exercise on : Facebook and Twitter

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