Sales Person
Salesforce
Problem Statement
Salesforce management wants to identify sales representatives who have no business history with a specific competitor or partner company named RED.
Write an SQL query to report the names of all the salespersons who did not have any orders related to the company with the name RED.
Rules:
- You must check the Orders table to see which salespersons are linked to which companies.
- A salesperson should be included in the output if they have no orders at all, or if none of their orders are linked to RED.
- Return the result table containing only the name column.
- Results must be sorted by name in ascending order.
Tests your understanding of
Basic SQL, Subquery, Joins and Filtering
Input Tables
salesperson
| sales_id(INTEGER) | name(VARCHAR) | salary(INTEGER) | commission_rate(INTEGER) | hire_date(DATE) |
|---|---|---|---|---|
| 1 | John | 100000 | 6 | 2006-04-01 |
| 2 | Amy | 12000 | 5 | 2010-05-01 |
| 3 | Mark | 65000 | 12 | 2008-12-25 |
| 4 | Pam | 25000 | 25 | 2005-01-01 |
| 5 | Alex | 5000 | 10 | 2007-02-03 |
company
| com_id(INTEGER) | name(VARCHAR) | city(VARCHAR) |
|---|---|---|
| 1 | RED | Boston |
| 2 | ORANGE | New York |
| 3 | YELLOW | Chicago |
| 4 | GREEN | Austin |
orders
| order_id(INTEGER) | order_date(DATE) | com_id(INTEGER) | sales_id(INTEGER) | amount(INTEGER) |
|---|---|---|---|---|
| 1 | 2014-01-01 | 3 | 4 | 10000 |
| 2 | 2014-02-01 | 4 | 5 | 5000 |
| 3 | 2014-03-01 | 1 | 1 | 50000 |
| 4 | 2014-04-01 | 1 | 4 | 25000 |
Expected Output
| name(VARCHAR) |
|---|
| Alex |
| Amy |
| Mark |
Tags
EasyBasic SQLSubqueryJoinsFiltering
10-15 min
66%