SQL Exercises: Salesmen, customer involved in inventory management

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

3. From the following tables, write a SQL query to find all those salespeople and customers who are involved in the inventory management system. Return salesperson ID, customer ID.

Sample table: orders

Sample table: customer

Sample Solution:

-- Selecting specific columns (salesman_id, customer_id) from the 'customer' table
SELECT salesman_id, customer_id

-- Performing a UNION operation with the result set of a subquery that selects specific columns (salesman_id, customer_id) from the 'orders' table
FROM customer

UNION 

(SELECT salesman_id, customer_id

-- Selecting specific columns (salesman_id, customer_id) from the 'orders' table
FROM orders)

Sample Output:

salesman_id	customer_id
5005		3001
5007		3003
5001		3007
5002		3008
5006		3004
5003		3009
5001		3002
5002		3005

Code Explanation:

The said query in SQL which selects the distinct set of salesman IDs and customer IDs from two tables - customer and orders - and combines them using the UNION operator.
The UNION operator combines the results of two SELECT statements into a single table. It automatically removes any duplicate rows, so the resulting table will have a distinct set of salesman_id and customer_id pair.

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 distinct salesman and their cities.
Next SQL Exercise: Largest and smallest orders are produced on each date.