w3resource
SQL exercises

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


Sample Solution:

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

Sample Output:

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

Practice Online


Inventory database model

Query Visualization:

Duration:

Query visualization of List all the salesmen, and indicate those who do not have customers in their cities, as well as whose who do - Duration

Rows:

Query visualization of List all the salesmen, and indicate those who do not have customers in their cities, as well as whose who do - Rows

Cost:

Query visualization of List all the salesmen, and indicate those who do not have customers in their cities, as well as whose who do - Cost

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?



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