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

Last update on January 12 2026 12:48:23 (UTC/GMT +8 hours)

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

Sample table: Salesman

 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12

Sample table: Customer

 customer_id |   cust_name    |    city    | grade | salesman_id 
-------------+----------------+------------+-------+-------------
        3002 | Nick Rimando   | New York   |   100 |        5001
        3007 | Brad Davis     | New York   |   200 |        5001
        3005 | Graham Zusi    | California |   200 |        5002
        3008 | Julian Green   | London     |   300 |        5002
        3004 | Fabian Johnson | Paris      |   300 |        5006
        3009 | Geoff Cameron  | Berlin     |   100 |        5003
        3003 | Jozy Altidor   | Moscow     |   200 |        5007
        3001 | Brad Guzan     | London     |       |        5005

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.

Go to:

PREV : View to show average and total orders for each salesman.
NEXT : View to show all matches of customers with salesmen.

Inventory database model:

Contribute your code and comments through Disqus.