Sales Person - Problem

You are given three tables: SalesPerson, Company, and Orders.

The SalesPerson table contains information about salespeople including their ID, name, salary, commission rate, and hire date.

The Company table contains company information including ID, name, and city location.

The Orders table contains order information linking salespeople to companies with order details.

Write a SQL query to find the names of all salespeople who did not have any orders related to the company with the name "RED".

Return the result table in any order.

Table Schema

SalesPerson
Column Name Type Description
sales_id PK int Primary key - unique identifier for salesperson
name varchar Name of the salesperson
salary int Salesperson's salary
commission_rate int Commission rate percentage
hire_date date Date when salesperson was hired
Primary Key: sales_id
Company
Column Name Type Description
com_id PK int Primary key - unique identifier for company
name varchar Company name
city varchar City where company is located
Primary Key: com_id
Orders
Column Name Type Description
order_id PK int Primary key - unique identifier for order
order_date date Date when order was placed
com_id int Foreign key referencing Company.com_id
sales_id int Foreign key referencing SalesPerson.sales_id
amount int Order amount
Primary Key: order_id

Input & Output

Example 1 — Standard Case
Input Tables:
SalesPerson
sales_id name salary commission_rate hire_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 name city
1 RED Boston
2 ORANGE New York
3 YELLOW Boston
4 GREEN Austin
Orders
order_id order_date com_id sales_id amount
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
Output:
name
Amy
Mark
Alex
💡 Note:

Looking at the orders, we can see that:

  • John (sales_id=1) has an order with RED company (com_id=1)
  • Pam (sales_id=4) has an order with RED company (com_id=1)
  • Amy, Mark, and Alex have no orders with RED company

Therefore, Amy, Mark, and Alex are returned as they never had orders related to the RED company.
Example 2 — No RED Orders
Input Tables:
SalesPerson
sales_id name salary commission_rate hire_date
1 Alice 95000 8 2020-01-15
2 Bob 87000 7 2019-03-10
Company
com_id name city
1 BLUE Seattle
2 GREEN Portland
Orders
order_id order_date com_id sales_id amount
1 2021-01-01 1 1 15000
2 2021-02-01 2 2 8000
Output:
name
Alice
Bob
💡 Note:

Since there is no company named 'RED' in the Company table, none of the salespeople have any orders related to RED company. Therefore, all salespeople (Alice and Bob) are returned.

Constraints

  • 1 ≤ sales_id ≤ 10^5
  • 1 ≤ com_id ≤ 10^4
  • 1 ≤ order_id ≤ 10^6
  • Company names and salesperson names are non-empty strings
  • salary and amount are positive integers

Visualization

Tap to expand
Sales Person Problem: Exclude RED Company OrdersInput TablesSalesPersonJohnAmyMarkPamAlexCompanyREDORANGEYELLOWGREENOrdersJohn→REDPam→REDPam→YELAlex→GRNNOT EXISTS / LEFT JOINFind salespeople WITHOUT orders to REDJohn - has RED orderPam - has RED orderAmy - no ordersMark - no ordersAlex - no RED ordersResultnameAmyMarkAlex
Understanding the Visualization
1
Input
Three tables with sales and order data
2
Filter
Exclude salespeople with RED orders
3
Output
Names of salespeople without RED orders
Key Takeaway
🎯 Key Insight: Use NOT EXISTS or LEFT JOIN with NULL to find missing relationships between tables

Related Problems

Asked in
Amazon 12 Microsoft 8 Apple 6
25.6K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen