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).
Test your Programming skills with w3resource's quiz.
What is the difficulty level of this exercise?
SQL: Tips of the Day
MySQL export schema without data
mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql
Ref: https://bit.ly/3xzB9dS
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework