SQL Exercises: Display distinct salesman and their cities

Last update on December 20 2024 11:46:05 (UTC/GMT +8 hours)

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.