SQL Exercises: Show all salesmen with more than one order

Last update on June 08 2024 12:31:45 (UTC/GMT +8 hours)

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

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 table: Orders

ord_no      purch_amt   ord_date    customer_id  salesman_id
----------  ----------  ----------  -----------  -----------
70001       150.5       2012-10-05  3005         5002
70009       270.65      2012-09-10  3001         5005
70002       65.26       2012-10-05  3002         5001
70004       110.5       2012-08-17  3009         5003
70007       948.5       2012-09-10  3005         5002
70005       2400.6      2012-07-27  3007         5001
70008       5760        2012-09-10  3002         5001
70010       1983.43     2012-10-10  3004         5006
70003       2480.4      2012-10-10  3009         5003
70012       250.45      2012-06-27  3008         5002
70011       75.29       2012-08-17  3003         5007
70013       3045.6      2012-04-25  3002         5001

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 Solution:

-- Selecting all columns from the 'salesman' table (aliased as 'a')
SELECT * 
-- Specifying the table to retrieve data from ('salesman' as 'a')
FROM salesman a 
-- Checking the existence of records in a subquery
WHERE EXISTS     
   -- Subquery: Selecting any record from the 'customer' table (aliased as 'b') where 'salesman_id' matches the outer query's 'salesman_id'
   (SELECT * FROM customer b     
    -- Specifying the table to retrieve data from ('customer' as 'b')
    WHERE a.salesman_id = b.salesman_id     
	 -- Checking the condition that the count of orders for the customer is greater than 1
	 AND 1 <             
	     -- Subquery: Counting the number of rows in the 'orders' table where 'customer_id' matches the outer query's 'customer_id'
	     (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

Explanation:

The said SQL query that selects all columns from the 'salesman' table (aliased as 'a') where there exists at least one record in the 'customer' table (aliased as 'b') where the "salesman_id" value in 'a' is equal to the "salesman_id" value in 'b' and the subquery returns a count of the number of records in the 'orders' table that have a "customer_id" value equal to the "customer_id" value in 'b' and this count is greater than 1.
It is using the EXISTS clause to check if there is at least one record in the customer table, where the salesman id of the salesman table and customer table match and the customer has more than one order in the orders table. The subquery returns a count of the number of orders for each customer, and the main query retrieves the rows from the salesman table for the salesman who have at least one customer with more than one order.