Practice SQL Interview Questions

 

Let’s now work on answering parts B and C of the original question. We present the tables below again for your convenience.

Here is part B: Find the names of all salespeople that do not have any orders with Samsonic.

This is part C: Find the names of salespeople that have 2 or more orders.

Salesperson Customer
ID Name Age Salary
1 Abe 61 140000
2 Bob 34 44000
5 Chris 34 40000
7 Dan 41 52000
8 Ken 57 115000
11 Joe 38 38000
ID Name City Industry Type
4 Samsonic pleasant J
6 Panasung oaktown J
7 Samony jackson B
9 Orange Jackson B
Orders
Number order_date cust_id salesperson_id Amount
10 8/2/96 4 2 540
20 1/30/99 4 8 1800
30 7/14/95 9 1 460
40 1/29/98 7 2 2400
50 2/3/98 6 7 600
60 3/2/98 6 7 720
70 5/6/98 9 7 150

Part B of the question asks for the names of the salespeople who do not have an order with Samsonic. A good way to approach this problem is to break it down: if we can first find the name of all the salespeople who do have an order with Samsonic. Then, perhaps we can work with that list and get all the salespeople who do not have an order with Samsonic.

So, let’s start by just getting a list of all the salespeople ID’s that have an order with Samsonic. We can get this list by doing a join with a condition that the customer is Samsonic. We can use both the Customer and Orders table. The SQL for this will look like: 

select Orders.salesperson_id from Orders, Customer where 
Orders.cust_id = Customer.ID and Customer.Name = 'Samsonic'

This will give us a list of all the salespeople ID’s that have an order with Samsonic. Now, we can get a list of the names of all the salespeople who do NOT have an order with Samsonic. SQL has a ‘NOT’ operator that easily allows us to exclude elements of the result set. We can use this to our advantage. Here is one possible answer to question B, and this is what the final SQL will look like: 

select Salesperson.Name from Salesperson 
where Salesperson.ID NOT IN(
select Orders.salesperson_id from Orders, Customer 
where Orders.cust_id = Customer.ID 
and Customer.Name = 'Samsonic')

Now, lets work on answering part C. As always, it’s best to break the problem down into more manageable pieces. So, lets focus on one table: the Orders table. Looking at that table we can find the ID’s that belong to the salespeople who have 2 or more orders. This will require use of the "group by" syntax in SQL, which allows us to group by whatever column we choose. In this case, the column that we would be grouping by is the salesperson_id column, because for a given salesperson ID we would like to find out how many orders were placed under that ID. With that said, we can write this SQL:

select salesperson_id from Orders group by 
salesperson_id having count(salesperson_id) > 1

Note how we used the having clause instead of the where clause because we are using the ‘count’ aggregate. Well, now we have a SQL statement that gives us the ID’s of the salespeople who have more than 1 order. But, what we really want is the names of the salespeople who have those ID’s. This is actually quite simple if we do a join on the Salesperson and Orders table, and use the SQL that we came up earlier. It would look like this:

SELECT name
FROM Orders, Salesperson
WHERE Orders.salesperson_id = Salesperson.id
GROUP BY name, salesperson_id
HAVING COUNT( salesperson_id ) >1

Based on our tables, this SQL will return the names of Bob and Dan. Click on the Next button below to check out the answer to part D.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.