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 salesperson_id having count(salesperson_id) > 1
Based on our tables, this SQL will return the names of Bob and Dan.
Click Next to check out the answers to part D.
|
