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.
table name: SalesPerson
table name: Company
table name: Orders
Solution:
select name from SalesPerson
except
select s.name from SalesPerson as s
left join Orders2 as o on s.sales_id = o.sales_id
left join Company as c on o.com_id = c.com_id
where c.name = 'RED'
select s.name from SalesPerson s where s.sales_id not in
(select o.sales_id from Company c right join Orders2 o on c.com_id=o.com_id where c.name='RED');
Output:
Execution Plan:
SQL Script:
Create table
SalesPerson (sales_id int, name varchar(255), salary int,
commission_rate int, hire_date date)
Create table
Company (com_id int, name varchar(255), city varchar(255))
Create table
[Orders2] (order_id int, order_date date, com_id int, sales_id int, amount int)
Truncate table SalesPerson
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')
Truncate table Company
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')
Truncate table Orders
insert into Orders2 (order_id, order_date, com_id, sales_id, amount) values ('1', '1/1/2014', '3', '4', '10000')
insert into Orders2 (order_id, order_date, com_id, sales_id, amount) values ('2', '2/1/2014', '4', '5', '5000')
insert into Orders2 (order_id, order_date, com_id, sales_id, amount) values ('3', '3/1/2014', '1', '1', '50000')
insert into Orders2 (order_id, order_date, com_id, sales_id, amount) values ('4', '4/1/2014', '1', '4', '25000')