SQL Exercises: Orders by the customers not located in the same cities where their salesmen live
SQL Query on Multiple Tables: Exercise-3 with Solution
From the following tables, write a SQL query to find those salespeople who generated orders for their customers but not located in the same city. Return ord_no, cust_name, customer_id (orders table), salesman_id (orders table).
Sample table: salesman
Sample table: customer
Sample table: orders
SELECT ord_no, cust_name, orders.customer_id, orders.salesman_id FROM salesman, customer, orders WHERE customer.city <> salesman.city AND orders.customer_id = customer.customer_id AND orders.salesman_id = salesman.salesman_id;
Output of the query:
ord_no cust_name customer_id salesman_id 70004 Geoff Cameron 3009 5003 70003 Geoff Cameron 3009 5003 70011 Jozy Altidor 3003 5007 70001 Graham Zusi 3005 5002 70007 Graham Zusi 3005 5002 70012 Julian Green 3008 5002
Relational Algebra Expression:
Relational Algebra Tree:
Pictorial presentation :
Note: The pictorial represetation above is based on hypothetical table for the purpose of explanation only. Your answer may not match.
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 the customers along with the salesperson who works for them. Return customer name, and salesperson name.
Next: From the following tables, write a SQL query to find those orders made by customers. Return order number, customer name.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework