SQL Exercise: Salesmen work for one or more customers or yet to join
14. Salesmen List with Order and Grade Criteria
Write a SQL statement to make a list for the salesmen who either work for one or more customers or yet to join any of the customer. The customer, may have placed, either one or more orders on or above order amount 2000 and must have a grade, or he may not have placed any order to the associated supplier.
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 table: salesman
salesman_id | name | city | commission
-------------+------------+----------+------------
5001 | James Hoog | New York | 0.15
5002 | Nail Knite | Paris | 0.13
5005 | Pit Alex | London | 0.11
5006 | Mc Lyon | Paris | 0.14
5007 | Paul Adam | Rome | 0.13
5003 | Lauson Hen | San Jose | 0.12
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 Solution:
-- Selecting specific columns and renaming one column for clarity
SELECT a.cust_name, a.city, a.grade,
b.name AS "Salesman",
c.ord_no, c.ord_date, c.purch_amt
-- Specifying the tables to retrieve data from ('customer' as 'a', 'salesman' as 'b', and 'orders' as 'c')
FROM customer a
-- Performing a right outer join based on the salesman_id, including unmatched rows from 'salesman'
RIGHT OUTER JOIN salesman b
ON b.salesman_id = a.salesman_id
-- Performing a left outer join with the result of the previous join and the 'orders' table based on customer_id
LEFT OUTER JOIN orders c
ON c.customer_id = a.customer_id
-- Filtering the results based on two conditions (purchase amount greater than or equal to 2000 and grade is not null)
WHERE c.purch_amt >= 2000
AND a.grade IS NOT NULL;
Output of the Query:
cust_name |city |grade|Salesman |ord_no|ord_date |purch_amt| --------------|----------|-----|----------|------|----------|---------| Nick Rimando |New York | 100|James Hoog| 70002|2012-10-05| 65.26| Geoff Cameron |Berlin | 100|Lauson Hen| 70004|2012-08-17| 110.50| Brad Davis |New York | 200|James Hoog| 70005|2012-07-27| 2400.60| Nick Rimando |New York | 100|James Hoog| 70008|2012-09-10| 5760.00| Fabian Johnson|Paris | 300|Mc Lyon | 70010|2012-10-10| 1983.43| Geoff Cameron |Berlin | 100|Lauson Hen| 70003|2012-10-10| 2480.40| Jozy Altidor |Moscow | 200|Paul Adam | 70011|2012-08-17| 75.29| Nick Rimando |New York | 100|James Hoog| 70013|2012-04-25| 3045.60| Graham Zusi |California| 200|Nail Knite| 70001|2012-10-05| 150.50| Graham Zusi |California| 200|Nail Knite| 70007|2012-09-10| 948.50| Julian Green |London | 300|Nail Knite| 70012|2012-06-27| 250.45|
Explanation:
The said SQL query that is joining three tables: customer alias a, salesman alias b, and orders alias c. The query is using a RIGHT OUTER JOIN on salesman and customer tables on the 'salesman_id' column, and a LEFT OUTER JOIN on orders and customer tables on the 'customer_id' column.
The query is then selecting several columns from each table: 'cust_name', 'city', 'grade' from 'customer' table, 'name' aliased as 'Salesman' from salesman table, 'ord_no', 'ord_date', 'purch_amt' from orders table.
The query is also filtering the results based on the condition "c.purch_amt >=2000" and "a.grade IS NOT NULL" which means it will only return the rows where the purchase amount is greater than or equal to 2000 and grade is not null.
Visual Explanation:
Practice Online
For more Practice: Solve these Related Problems:
- Write a SQL query to list salesmen who either have one or more customers or no customers, and for those with customers, include only those where the customer has placed orders above 2000 or has a valid grade.
- Write a SQL query to retrieve a list of salesmen with associated customer details, filtering to include customers with orders ≥2000 or non-null grades, and include salesmen with no customers.
- Write a SQL query to display salesmen, ensuring that if a customer is associated then the customer must have placed an order of at least 2000 or have a grade, ordering the result by salesperson name.
- Write a SQL query to list salesmen based on customer order amounts and grade criteria, using a UNION to combine salesmen with and without customer assignments.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Salesmen who works either for one or more customer.
Next SQL Exercise: One or more customers ordered from the existing list
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.