SQL UNION Exercises: List all the salesmen, and indicate those who do not have customers in their cities, as well as whose who do
SQL UNION : Exercise-6 with Solution
6. Write a query to list all the salesmen, and indicate those who do not have customers in their cities, as well as whose who do.
Sample table: Salesman
Sample table: Customer
SELECT salesman.salesman_id, name, cust_name, commission FROM salesman, customer WHERE salesman.city = customer.city UNION (SELECT salesman_id, name, 'NO MATCH', commission FROM salesman WHERE NOT city = ANY (SELECT city FROM customer)) ORDER BY 2 DESC
salesman_id name cust_name commission 5005 Pit Alex Julian Green 0.11 5005 Pit Alex Brad Guzan 0.11 5007 Paul Adam NO MATCH 0.13 5002 Nail Knite Fabian Johnson 0.13 5006 Mc Lyon Fabian Johnson 0.14 5003 Lauson Hen NO MATCH 0.12 5001 James Hoog Nick Rimando 0.15 5001 James Hoog Brad Davis 0.15
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a query to make a report of which salesman produce the largest and smallest orders on each date and arranged the orders number in smallest to the largest number.
Next: Write a query to that appends strings to the selected fields, indicating whether or not a specified salesman was matched to a customer in his city.
What is the difficulty level of this exercise?