SQL VIEW Exercises: Create a view that shows the number of the salesman in each city

SQL VIEW : Exercise-10 with Solution

10. From the following table, create a view to count number of the salesperson in each city. Return city, number of salespersons.

Sample table: salesman

Sample Solution:

CREATE VIEW citynum
AS SELECT city, COUNT (DISTINCT salesman_id)
FROM salesman
GROUP BY city;

output:

sqlpractice-# FROM citynum;
   city   | count
----------+-------
 London   |     1
 New York |     1
 Paris    |     2
 Rome     |     1
          |     1
(5 rows)

Inventory database model:

Contribute your code and comments through Disqus.

Previous: From the following table, create a view to find all the customers who have the highest grade. Return all the fields of customer.
Next: 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).