w3resource
SQL exercises

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


Sample Solution:

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

Sample Output:

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

Practice Online


Inventory database model

Query Visualization:

Duration:

Query visualization of Appends strings to the selected fields, indicating whether or not a specified salesman was matched to a customer in his city - Duration

Rows:

Query visualization of Appends strings to the selected fields, indicating whether or not a specified salesman was matched to a customer in his city - Rows

Cost:

Query visualization of Appends strings to the selected fields, indicating whether or not a specified salesman was matched to a customer in his city - Cost

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?



New Content: Composer: Dependency manager for PHP, R Programming