## 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.
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 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:
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:
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:
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
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. |