Sales Person is the question asked in many interviews and listed on leetcode at 596. 


Let's start with SQL Schema for the table named salesPerson, company, and orders:


Table SalesPerson:


CREATE TABLE salesPerson (sales_id int, name varchar(255), salary int, commission_rate int, hire_date date)

INSERT INTO SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('1', 'John', '100000', '6', '4/1/2006')

INSERT INTO SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('2', 'Amy', '12000', '5', '5/1/2010')

INSERT INTO SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('3', 'Mark', '65000', '12', '12/25/2008')

INSERT INTO SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('4', 'Pam', '25000', '25', '1/1/2005')

INSERT INTO SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('5', 'Alex', '5000', '10', '2/3/2007')


The table will look like this based on the data of salesPerson:

sales_id name salary commission_rate hire_date
1 John 100000 6 4/1/2006
2 Any 12000 5 5/1/2010
3 Mark 65000 12 12/25/2008
4 Pam 25000 25 1/1/2005


Table Company:

CREATE TABLE company (com_id int, name varchar(255), city varchar(255))
INSERT INTO Company (com_id, name, city) values ('1', 'RED', 'Boston')
INSERT INTO Company (com_id, name, city) values ('2', 'ORANGE', 'New York')
INSERT INTO Company (com_id, name, city) values ('3', 'YELLOW', 'Boston')
INSERT INTO Company (com_id, name, city) values ('4', 'GREEN', 'Austin')

The table will look like this based on the data of company:

com_id name city
1 RED Boston
2 ORANGE New York
3 YELLOW Boston
4 GREEN Austin


Table Orders:

CREATE TABLE orders (order_id int, order_date date, com_id int, sales_id int, amount int)
INSERT INTO Orders (order_id, order_date, com_id, sales_id, amount) values ('1', '1/1/2014', '3', '4', '10000')
INSERT INTO Orders (order_id, order_date, com_id, sales_id, amount) values ('2', '2/1/2014', '4', '5', '5000')
INSERT INTO Orders (order_id, order_date, com_id, sales_id, amount) values ('3', '3/1/2014', '1', '1', '50000')
INSERT INTO Orders (order_id, order_date, com_id, sales_id, amount) values ('4', '4/1/2014', '1', '4', '25000')

The table will look like this based on the data of Order:

order_id order_date com_id sales_id amount
1 1/1/2014 3 4 10000
2 2/1/2014 4 5 5000
3 3/1/2014 1 1 5000
4 4/1/2014 1 4 25000


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

Return the result table in any order.

The query result will be,


name
Amy
Mark
Alex


You have the table and question to find all the classes that have at least five students.

Now, let's start with the Algorithm:


If we know all the persons who have sales in this company 'RED', it will be fairly easy to know who do not have.

To start, we can query the information of sales in company 'RED' as a temporary table. And then try to build a connection between this table and the salesperson table since it has the name information.



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


The output of the above query will look like:


order_id order_date com_id sales_id amount com_id name city
3 3/1/2014 1 1 5000 1 RED Boston
4 4/1/2014 1 4 25000 1 RED Boston


Obviously, the column sales_id exists in table salesperson so we may use it as a subquery, and then utilize the NOT IN to get the target data.

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


This is the final query to get the required output. 

I hope you understood the algorithm, if you have any query or doubt please let us know in comment section.