SQL Exercises: Salesmen, customer involved in inventory management
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
ord_no purch_amt ord_date customer_id salesman_id ---------- ---------- ---------- ----------- ----------- 70001 150.5 2012-10-05 3005 5002 70009 270.65 2012-09-10 3001 5005 70002 65.26 2012-10-05 3002 5001 70004 110.5 2012-08-17 3009 5003 70007 948.5 2012-09-10 3005 5002 70005 2400.6 2012-07-27 3007 5001 70008 5760 2012-09-10 3002 5001 70010 1983.43 2012-10-10 3004 5006 70003 2480.4 2012-10-10 3009 5003 70012 250.45 2012-06-27 3008 5002 70011 75.29 2012-08-17 3003 5007 70013 3045.6 2012-04-25 3002 5001
Sample table: Customer
customer_id | cust_name | city | grade | salesman_id
-------------+----------------+------------+-------+-------------
3002 | Nick Rimando | New York | 100 | 5001
3007 | Brad Davis | New York | 200 | 5001
3005 | Graham Zusi | California | 200 | 5002
3008 | Julian Green | London | 300 | 5002
3004 | Fabian Johnson | Paris | 300 | 5006
3009 | Geoff Cameron | Berlin | 100 | 5003
3003 | Jozy Altidor | Moscow | 200 | 5007
3001 | Brad Guzan | London | | 5005
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:
Go to:
PREV : Display distinct salesman and their cities.
NEXT : Largest and smallest orders are produced on each date.
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.