607 Sales Person

1. Description

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 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 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 for this table.
com_id is a foreign key to com_id from the Company table.
sales_id is a foreign key 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.

Write an SQL query to report the names of all the salesperson who did not have any orders related to the company with the name "RED".

 

2. Algorithms

The primary keys from the three tables are : sales_id, com_id, and order_id. com_id and sales_id are foreign keys of table Orders.

 

To check the names fo all the salespersons who did not ave any orders related to the company with the name "RED", we have to get name from SalesPerson and name of Company.

 

We will make integrated table by joining three tables and find rows with condition.

 

1. Get joined table with salesperson's name, company's name.
2. Get salesperson's name who related to the company with the name "RED".
3. SELECT salesperson not in previous table.

 

3. Codes

# Write your MySQL query statement below
WITH person_company AS ( 
    SELECT p.name AS person_name, 
           c.name AS company_name
    FROM Orders AS o 
    INNER JOIN Company AS c ON c.com_id = o.com_id 
    INNER JOIN SalesPerson AS p ON p.sales_id = o.sales_id 
), 
     person_red AS (
    SELECT person_name 
    FROM person_company 
    WHERE company_name IN ("RED")
)
SELECT name 
  FROM SalesPerson
 WHERE name NOT IN (SELECT person_name FROM person_red);

4. Conclusion