w3resource

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:

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