SQL Exercises: View to shows each salesman has more than one customer.

SQL VIEW : Exercise-12 with Solution

12. From the following table, create a view to identify salespeople who work with multiple clients. Return all the fields of salesperson.

Sample table: customer

Sample table: salesman

Sample Solution:

-- Creating a VIEW named 'mcustomer'
CREATE VIEW mcustomer

-- Selecting all columns from the 'salesman' table as 'a'
-- Filtering the rows where a salesman has more than one customer
-- Using a subquery to count the number of customers for each salesman and comparing it to 1
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
        5001 | James Hoog   | New York |       0.15
(2 rows)

Code Explanation:

The said statement in SQL creates a view named mcustomer, which returns all columns from the salesman table where the salesman has more than one customer associated with them.
This filters the results to only include records where the count of associated customers is greater than 1.
The subquery counts the number of customers associated with each salesman using the salesman_id foreign key in the customer table.

Inventory database model:

Contribute your code and comments through Disqus.

Previous SQL Exercise: View to show average and total orders for each salesman.
Next SQL Exercise: View to show all matches of customers with salesmen.