w3resource
SQL exercises

SQL VIEW Exercises: Create a view that shows each salesman with more than one customers

SQL VIEW : Exercise-12 with Solution

12. Write a query to create a view that shows each salesman with more than one customers.

Sample table: customer


Sample table: salesman


Sample Solution:

CREATE VIEW mcustomer
AS SELECT *
FROM salesman a
WHERE 1 <
   (SELECT COUNT(*)
     FROM customer b
     WHERE a.salesman_id = b.salesman_id);

output:

sqlpractice=# SELECT *
sqlpractice-# FROM mcustomer;
 salesman_id |     name     |   city   | commission
-------------+--------------+----------+------------
        5002 | Nail Knite   | Paris    |       0.13
        5006 | Mc Lyon      | Paris    |       0.14
        5003 | Lauson Hense |          |       0.12
        5001 | James Hoog   | New York |       0.15
(4 rows)

Inventory database model:

Inventory database model

Contribute your code and comments through Disqus.

Previous: Write a query to create a view that shows the average and total orders for each salesman after his or her name. (Assume all names are unique)
Next: Write a query to create a view that shows all matches of customers with salesman such that at least one customer in the city of customer served by a salesman in the city of the salesman.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming