SQL UNION Exercises: Appends strings to the selected fields, indicating whether or not a specified salesman was matched to a customer in his city
SQL UNION : Exercise-7 with Solution
7. 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.
Sample table: Salesman
Sample table: Customer
SELECT a.salesman_id, name, a.city, 'MATCHED' FROM salesman a, customer b WHERE a.city = b.city UNION (SELECT salesman_id, name, city, 'NO MATCH' FROM salesman WHERE NOT city = ANY (SELECT city FROM customer)) ORDER BY 2 DESC
salesman_id name city ?column? 5005 Pit Alex London MATCHED 5007 Paul Adam Rome NO MATCH 5002 Nail Knite Paris MATCHED 5006 Mc Lyon Paris MATCHED 5003 Lauson Hen San Jose NO MATCH 5001 James Hoog New York MATCHED
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: 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.
Next: Write a query to create a union of two queries that shows the names, cities, and ratings of all customers. Those with a rating of 300 or greater will also have the words High Rating, while the others will have the words Low Rating.
What is the difficulty level of this exercise?