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