SQL Exercises: List all salesmen with more than one customer

SQL SUBQUERY: Exercise-11 with Solution

11. From the following tables write a SQL query to find salespeople who had more than one customer. Return salesman_id and name.

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

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
5003         Lauson Hen  San Jose    0.12
5007         Paul Adam   Rome        0.13

Sample Solution:

-- Selecting the 'salesman_id' and 'name' columns from the 'salesman' table (aliased as 'a')
SELECT salesman_id, name 
-- Specifying the table to retrieve data from ('salesman' as 'a')
FROM salesman a 
-- Filtering the results based on the condition that the count of customers associated with the salesman is greater than 1
WHERE 1 < 
    (SELECT COUNT(*) 
     -- Subquery: Counting the number of rows in the 'customer' table where 'salesman_id' matches the outer query's 'salesman_id'
     FROM customer 
     WHERE salesman_id = a.salesman_id);

Output of the Query:

salesman_id	name
5001		James Hoog
5002		Nail Knite

Explanation:

In the said SQL query the subquery in the WHERE clause counts the number of rows in the 'customer' table where the salesman_id matches the salesman_id in the 'salesman' table. The outer query then selects the salesman_id and name columns from the 'salesman' table where the count returned by the subquery is greater than 1. This results in a list of salesman_id and name of all salesmen who have more than one customer.

Visual Explanation:

Practice Online

Sample Database: inventory

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Customers who ordered on August 17, 2012.
Next SQL Exercise: Find all orders with above-average amounts.