SQL Exercises: Display distinct salesman and their cities
SQL UNION: Exercise-2 with Solution
2. From the following tables, write a SQL query to find distinct salespeople and their cities. Return salesperson ID and city.
Sample table: Salesman
Sample table: Customer
Sample Solution:
-- Selecting specific columns (salesman_id, city) from the 'customer' table
SELECT salesman_id, city
-- Performing a UNION operation with the result set of a subquery that selects specific columns (salesman_id, city) from the 'salesman' table
FROM customer
UNION
(SELECT salesman_id, city
-- Selecting specific columns (salesman_id, city) from the 'salesman' table
FROM salesman)
Sample Output:
salesman_id city 5001 New York 5002 London 5002 California 5006 Paris 5007 Rome 5002 Paris 5005 London 5003 Berlin 5007 Moscow 5003 San Jose
Code Explanation:
The said query in SQL that retrieves a list of unique pairs of salesman_id and city from two different tables, customer and salesman. The UNION operator is used to combine the results of two separate SELECT statements into a single result set.
The resulting table will contain all the unique combinations of salesman_id and city that appear in either the customer or salesman tables.
Relational Algebra Expression:
![Relational Algebra Expression: Display distinct salesman and their cities.](https://www.w3resource.com/w3r_images/sql-union-relational-algebra-2.png)
Relational Algebra Tree:
![Relational Algebra Tree: Display distinct salesman and their cities.](https://www.w3resource.com/w3r_images/sql-union-relational-algebra-tree-diagram-2.png)
Practice Online
![Inventory database model](https://www.w3resource.com/w3r_images/inventory.png)
Query Visualization:
Duration:
![Query visualization of Display distinct salesman and their working cities - Duration](https://www.w3resource.com/w3r_images/sql-union-exercise-2-duration.png)
Rows:
![Query visualization of Display distinct salesman and their working cities - Rows](https://www.w3resource.com/w3r_images/sql-union-exercise-2-rows.png)
Cost:
![Query visualization of Display distinct salesman and their working cities - Cost](https://www.w3resource.com/w3r_images/sql-union-exercise-2-cost.png)
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Display all salesmen and customer located in London.
Next SQL Exercise: Salesmen, customer involved in inventory management.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics