SQL Subquery Exercises: Display the rows of all salesmen who have customers with more than one orders

SQL SUBQUERY: Exercise-18 with Solution

18. From the following tables write a SQL query to find the salespeople who deal the customers with more than one order. Return salesman_id, name, city and commission.

Sample table: Salesman

Sample table: Orders

Sample table: Customer

Sample Solution:

SELECT * 
FROM salesman a 
WHERE EXISTS     
   (SELECT * FROM customer b     
    WHERE a.salesman_id=b.salesman_id     
	 AND 1<             
	     (SELECT COUNT (*)              
		  FROM orders             
		  WHERE orders.customer_id =            
		  b.customer_id));

Output of the Query:

salesman_id	name	city		commission
5001		James Hoog	New York	0.15
5002		Nail Knite	Paris		0.13
5003		Lauson Hen	San Jose	0.12

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous: From the following tables write a SQL query to find the salespeople who deal a single customer. Return salesman_id, name, city and commission.
Next: From the following tables write a SQL query to find the salespeople who deals those customers who live in the same city. Return salesman_id, name, city and commission.