Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

SQL UNION 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 salesperson and their cities and the cities there customers also lives. Return salesperson ID and city.

Sample table: Salesman


Sample table: Customer


Sample Solution: 

SELECT salesman_id, city
FROM customer
UNION
(SELECT salesman_id, city
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

Relational Algebra Expression:

Relational Algebra Expression: Display distinct salesman and their cities.

Relational Algebra Tree:

Relational Algebra Tree: Display distinct salesman and their cities.

Practice Online


Inventory database model

Query Visualization:

Duration:

Query visualization of Display distinct salesman and their working cities - Duration

Rows:

Query visualization of Display distinct salesman and their working cities - Rows

Cost:

Query visualization of Display distinct salesman and their working cities - Cost

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: From the following tables, write a SQL query to find all salespersons and customer who located in 'London' city.
Next: From the following tables, write a SQL query to find all those salespersons and customers who involved in inventory management system. Return salesperson ID, customer ID.

Test your Programming skills with w3resource's quiz.

What is the difficulty level of this exercise?



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3