Description
Write a SQL query to find salespeople who didn't have any orders from company 'RED'. Return their names.
Table:
salesperson| Column | Type |
|---|---|
| sales_id | INT |
| name | TEXT |
Table:
company| Column | Type |
|---|---|
| com_id | INT |
| name | TEXT |
Table:
orders| Column | Type |
|---|---|
| order_id | INT |
| com_id | INT |
| sales_id | INT |
Examples
Input:
CREATE TABLE salesperson (sales_id INT, name TEXT); CREATE TABLE company (com_id INT, name TEXT); CREATE TABLE orders (order_id INT, com_id INT, sales_id INT); INSERT INTO salesperson VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'); INSERT INTO company VALUES (1, 'RED'), (2, 'BLUE'); INSERT INTO orders VALUES (1, 1, 1), (2, 2, 2);Output:
Bob
CharlieExplanation:
Alice sold to RED (order 1). Bob sold to BLUE (not RED). Charlie has no orders. Both Bob and Charlie never sold to RED.
Input:
CREATE TABLE salesperson (sales_id INT, name TEXT); CREATE TABLE company (com_id INT, name TEXT); CREATE TABLE orders (order_id INT, com_id INT, sales_id INT); INSERT INTO salesperson VALUES (1, 'Diana'), (2, 'Eve'), (3, 'Frank'), (4, 'Grace'); INSERT INTO company VALUES (1, 'RED'), (2, 'GREEN'), (3, 'YELLOW'); INSERT INTO orders VALUES (1, 1, 1), (2, 1, 2), (3, 2, 3), (4, 3, 3);Output:
Frank
GraceExplanation:
Diana and Eve sold to RED. Frank sold to GREEN and YELLOW (not RED). Grace has no orders. Both Frank and Grace never sold to RED.
Input:
CREATE TABLE salesperson (sales_id INT, name TEXT); CREATE TABLE company (com_id INT, name TEXT); CREATE TABLE orders (order_id INT, com_id INT, sales_id INT); INSERT INTO salesperson VALUES (1, 'John'), (2, 'Kate'), (3, 'Luke'); INSERT INTO company VALUES (1, 'RED'), (2, 'ORANGE'), (3, 'PURPLE'); INSERT INTO orders VALUES (1, 2, 1), (2, 3, 1), (3, 2, 2), (4, 3, 3);Output:
John
Kate
LukeExplanation:
None of the salespeople sold to RED company. John sold to ORANGE and PURPLE, Kate sold to ORANGE, and Luke sold to PURPLE. All qualify.
Constraints
- •
Use NOT IN or NOT EXISTS