SQL Exercises: Alphabetically list the salesmen below their customers

SQL SUBQUERY: Exercise-21 with Solution

21. From the following tables write a SQL query to find all those salespeople whose names appear alphabetically lower than the customer’s name. Return salesman_id, name, city, commission.

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

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 the 'name' in the outer query is less than 'cust_name' in the subquery
   (SELECT *
	FROM CUSTOMER b
	WHERE  a.name  < b.cust_name);

Output of the Query:

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

Explanation:

The said SQL query is selecting all columns (*) from the table 'salesman' alias as table 'a' where there is at least one row in the table 'customer' alias as table 'b' that has a "cust_name" value greater than the "name" value of the row in table 'a'. It is using the EXISTS keyword to check for the existence of this condition in the subquery.

Visual Explanation:

Practice Online

Sample Database: inventory

Query Visualization:

Duration:

Rows:

Cost:

Contribute your code and comments through Disqus.

Previous SQL Exercise: Display the salesmen with customers following them.
Next SQL Exercise: Customers with the highest grade in alphabetical order.