w3resource

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.

Sample table : Customer
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
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
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:

SQL Subqueries: Display the names and numbers of all salesmen who had more than one customer.

Practice Online


Sample Database: inventory

Inventory database model

Query Visualization:

Duration:

Query visualization of Display the names and numbers of all salesmen who had more than one customer - Duration

Rows:

Query visualization of Display the names and numbers of all salesmen who had more than one customer - Rows

Cost:

Query visualization of Display the names and numbers of all salesmen who had more than one customer - 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.