﻿ SQL VIEW: Create a view that shows the average and total orders for each salesman after his or her name - 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:

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