30 Days Of LeetCode Database Day 21 - 607. Sales Person

1Table: SalesPerson
2 
3+-----------------+---------+
4| Column Name     | Type    |
5+-----------------+---------+
6| sales_id        | int     |
7| name            | varchar |
8| salary          | int     |
9| commission_rate | int     |
10| hire_date       | date    |
11+-----------------+---------+
12sales_id is the primary key (column with unique values) for this table.
13Each row of this table indicates the name and the ID of a salesperson alongside their salary, commission rate, and hire date.
14 
15 
16Table: Company
17 
18+-------------+---------+
19| Column Name | Type    |
20+-------------+---------+
21| com_id      | int     |
22| name        | varchar |
23| city        | varchar |
24+-------------+---------+
25com_id is the primary key (column with unique values) for this table.
26Each row of this table indicates the name and the ID of a company and the city in which the company is located.
27 
28 
29Table: Orders
30 
31+-------------+------+
32| Column Name | Type |
33+-------------+------+
34| order_id    | int  |
35| order_date  | date |
36| com_id      | int  |
37| sales_id    | int  |
38| amount      | int  |
39+-------------+------+
40order_id is the primary key (column with unique values) for this table.
41com_id is a foreign key (reference column) to com_id from the Company table.
42sales_id is a foreign key (reference column) to sales_id from the SalesPerson table.
43Each 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.
44 

1Example 1:
2 
3Input: 
4SalesPerson table:
5+----------+------+--------+-----------------+------------+
6| sales_id | name | salary | commission_rate | hire_date  |
7+----------+------+--------+-----------------+------------+
8| 1        | John | 100000 | 6               | 4/1/2006   |
9| 2        | Amy  | 12000  | 5               | 5/1/2010   |
10| 3        | Mark | 65000  | 12              | 12/25/2008 |
11| 4        | Pam  | 25000  | 25              | 1/1/2005   |
12| 5        | Alex | 5000   | 10              | 2/3/2007   |
13+----------+------+--------+-----------------+------------+
14Company table:
15+--------+--------+----------+
16| com_id | name   | city     |
17+--------+--------+----------+
18| 1      | RED    | Boston   |
19| 2      | ORANGE | New York |
20| 3      | YELLOW | Boston   |
21| 4      | GREEN  | Austin   |
22+--------+--------+----------+
23Orders table:
24+----------+------------+--------+----------+--------+
25| order_id | order_date | com_id | sales_id | amount |
26+----------+------------+--------+----------+--------+
27| 1        | 1/1/2014   | 3      | 4        | 10000  |
28| 2        | 2/1/2014   | 4      | 5        | 5000   |
29| 3        | 3/1/2014   | 1      | 1        | 50000  |
30| 4        | 4/1/2014   | 1      | 4        | 25000  |
31+----------+------------+--------+----------+--------+
32Output: 
33+------+
34| name |
35+------+
36| Amy  |
37| Mark |
38| Alex |
39+------+
40Explanation: 
41According to orders 3 and 4 in the Orders table, it is easy to tell that only salesperson John and Pam have sales to company RED, so we report all the other names in the table salesperson.
42 

1**SQL**
2 
3SELECT s.name FROM SalesPerson s
4 WHERE s.sales_id NOT IN (
5    SELECT o.sales_id FROM Orders o 
6`    JOIN Company c ON c.com_id = o.com_id
7    WHERE c.name = 'RED'
8);