SQL Exercises: Display distinct salesman and their cities
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 Tree:
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
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.