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 - SQL Problem INPUT TABLES SalesPerson sales_id | name 1 | Alice 2 | Bob 3 | Carol Company com_id | name 1 | RED 2 | BLUE Orders order_id|sales_id|com_id 1 | 1 | 1 2 | 2 | 2 3 | 3 | 2 Find salespeople with NO orders to "RED" company ALGORITHM STEPS 1 Find RED company Get com_id for "RED" com_id = 1 (RED) 2 Find RED orders Join Orders with Company Orders WHERE com_id=1 sales_id: 1 (Alice) 3 Use NOT IN / NOT EXISTS Exclude those sales_ids WHERE sales_id NOT IN (SELECT sales_id FROM subquery) 4 Return names SELECT name from remaining Bob, Carol (no RED orders) FINAL RESULT SQL Query: SELECT name FROM SalesPerson WHERE sales_id NOT IN ( SELECT o.sales_id FROM Orders o, Company c WHERE c.name='RED') Output: name Bob Carol OK - 2 salespeople found Neither has orders to RED Key Insight: Use a subquery to first identify all sales_ids who have orders with the "RED" company, then use NOT IN or NOT EXISTS to exclude them from the SalesPerson table. This anti-join pattern efficiently finds records that DON'T match a condition. TutorialsPoint - Sales Person | Optimal Solution

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