w3resource
SQL exercises

SQL Subquery Exercises: Find all the salesmen worked for only one customer

SQL SUBQUERY: Exercise-17 with Solution

17. Write a query to find all the salesmen who worked for only one customer.

Sample table: Customer


Sample table: Salesman


Sample Solution:

SELECT * 
FROM salesman 
WHERE salesman_id IN (
   SELECT DISTINCT salesman_id 
   FROM customer a 
   WHERE NOT EXISTS (
      SELECT * FROM customer b 
      WHERE a.salesman_id=b.salesman_id 
      AND a.cust_name<>b.cust_name));

Sample Output:

salesman_id	name		city	commission
5005		Pit Alex	London	0.11
5006		Mc Lyon		Paris	0.14
5007		Paul Adam	Rome	0.13
5003		Lauson Hen	San Jose0.12

Explanation:

SQL Subqueries:  Find all the salesmen worked for only one customer.

Practice Online


Inventory database model

Query Visualization:

Duration:

Query visualization of Find all the salesmen worked for only one customer - Duration

Rows:

Query visualization of Find all the salesmen worked for only one customer - Rows

Cost:

Query visualization of Find all the salesmen worked for only one customer - Cost

Contribute your code and comments through Disqus.

Previous: Write a query to find the salesmen who have multiple customers.
Next: Write a query that extract the rows of all salesmen who have customers with more than one orders.

What is the difficulty level of this exercise?



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