easy Sales Person

Problem

Table: SalesPerson

+-----------------+---------+
| Column Name     | Type    |
+-----------------+---------+
| sales_id        | int     |
| name            | varchar |
| salary          | int     |
| commission_rate | int     |
| hire_date       | date    |
+-----------------+---------+
sales_id is the primary key (column with unique values) for this table.
Each row of this table indicates the name and the ID of a salesperson alongside their salary, commission rate, and hire date.

Table: Company

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| com_id      | int     |
| name        | varchar |
| city        | varchar |
+-------------+---------+
com_id is the primary key (column with unique values) for this table.
Each row of this table indicates the name and the ID of a company and the city in which the company is located.

Table: Orders

+-------------+------+
| Column Name | Type |
+-------------+------+
| order_id    | int  |
| order_date  | date |
| com_id      | int  |
| sales_id    | int  |
| amount      | int  |
+-------------+------+
order_id is the primary key (column with unique values) for this table.
com_id is a foreign key (reference column) to com_id from the Company table.
sales_id is a foreign key (reference column) to sales_id from the SalesPerson table.
Each row of this table contains information about one order. This includes the ID of the company, the ID of the salesperson, the date of the order, and the amount paid.

Problem Definition

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

Example

Output

Try It Yourself

Solution

In order to identify salespeople whose sales transactions are not linked to a company with the name 'RED,' we employ a structured SQL query on the "SalesPerson," "Orders," and "Company" tables.

The query utilizes a subquery to exclude sales IDs associated with the specified company, and the final result comprises the names of salespeople meeting the defined criteria.

SELECT s.name
FROM   salesperson s
WHERE  s.sales_id NOT IN (SELECT o.sales_id
                          FROM   orders o
                                 JOIN company c
                                   ON c.com_id = o.com_id
                          WHERE  c.name = 'RED') 

Let's break down the query step by step:

Step 1: Identify sales_ids associated with orders from the company named 'RED'

SELECT o.sales_id
FROM   orders o
       JOIN company c
         ON c.com_id = o.com_id
WHERE  c.name = 'RED'; 

This step involves selecting sales_ids from the Orders table where the associated company has the name 'RED'.

The query uses a JOIN clause to match orders with their respective companies based on the com_id.

The WHERE clause filters the results only to include orders from the company named 'RED'.

Output After Step 1:

+----------+
| sales_id |
+----------+
| 4        |
| 1        |
+----------+

Step 2: Use the sales_ids obtained from Step 1 to filter SalesPerson records

SELECT s.name
FROM   salesperson s
WHERE  s.sales_id NOT IN (SELECT o.sales_id
                          FROM   orders o
                                 JOIN company c
                                   ON c.com_id = o.com_id
                          WHERE  c.name = 'RED'); 

This step involves selecting the names of SalesPersons whose sales_ids are not present in the sales_ids obtained from Step 1.

The NOT IN clause is used to filter out SalesPersons whose sales_ids are associated with orders from the company named 'RED'.

Final Output:

+------+
| name |
+------+
| Amy  |
| Mark |
| Alex |
+------+