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.

  • Shree

    Q.d You can use this in sql server

    Select Name,Age into highAchiever
    From [dbo].[Salesperson]
    Where Salary >= 100000

  • Deepak Indrapuri

    select Name from Salesperson where ID in
    (
    select salesperson_id from orders
    group by salesperson_id
    having COUNT(salesperson_id)>=2
    )

  • Dhanu

    Q: The names of salespeople that have 2 or more orders.

    A:select name from salesperson where ID IN ( select salesperson_id from Orders group by salesperson_id having count(salesperson_id) >=2)

    As per the question number 3 ,above answer is Correct,Please correct me if i am wrong

  • Shilpa M B

    i am asking.. i shud have written first line this way.. “wont this also be ans for part b”, i am asking if mine is correct

  • Rubbish O’mally

    Do you want to explain why it won’t be the answer or is this your natural train of thought which involves no reasoning or clarification?

  • neelu

    yes u r right.. i got the same

  • Shilpa M B

    this wont be ans for part b:
    select Salesperson.Name from
    Salesperson, Orders where Salesperson.ID
    = Orders.salesperson_id and cust_id ‘4’;