Click here to Skip to main content
15,037,168 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
This is a interview SQL test on which stumped me. Here is the question: In every office branch, there is a sales quota to be fulfilled by each salesperson. The SQL query is to display the number of salesperson in each branch that equal or exceed the sales quota. There are 2 tables, Branch and SalesPerson. SalesPerson linked to Branch through BranchID. Branch table has a Quota field to be fulfilled by each SalesPerson's Sales Field.
The SQL commands to create the tables are provided.
SQL
CREATE TABLE [dbo].[Branch](
	[ID] [int] NOT NULL,
	[Quota] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SalesPerson](
	[ID] [int] NOT NULL,
	[Name] [nchar](10) NOT NULL,
	[Sales] [int] NOT NULL,
	[BranchID] [int] NOT NULL
) ON [PRIMARY]
GO

The SQL commands to populate the tables are provided below.
SQL
INSERT INTO Branch (ID, Quota) VALUES (1, 500);
INSERT INTO Branch (ID, Quota) VALUES (2, 600);

INSERT INTO SalesPerson (ID, Name, Sales, BranchID) VALUES (1, 'John', 500, 1);
INSERT INTO SalesPerson (ID, Name, Sales, BranchID) VALUES (2, 'Jane', 400, 2);


What I have tried:

SQL
SELECT B.ID AS BranchNum, COUNT(*) AS EligibleSalesPersonCount FROM 
SalesPerson AS S INNER JOIN Branch AS B ON (S.BranchID = B.ID)
WHERE S.Sales >= B.Quota
GROUP BY B.ID;


My Output
BranchNum	EligibleSalesPersonCount
1	        1

Expected Output
BranchNum	EligibleSalesPersonCount
1	        1
2	        0


My question is how to display the result when the EligibleSalesPersonCount is 0?
Posted
Updated 26-Jul-20 23:25pm
v2

If i understand you well...

If you would like to display all branches and the number of employees where the sale is bigger-than or equal-to quota for that branch, you need to use different join.

SQL
SELECT B.ID AS BranchNum, COUNT(S.ID) AS EligibleSalesPersonCount
FROM Branch AS B
    LEFT JOIN SalesPerson AS S ON B.BranchID = B.ID  AND S.Sales >= B.Quota
GROUP BY B.ID;


For further details, please see: Visual Representation of SQL Joins[^]

To display corresponding sales-persons:
SQL
SELECT B.ID AS BranchNum, S.*
FROM Branch AS B
    LEFT JOIN SalesPerson AS S ON B.BranchID = B.ID  AND S.Sales >= B.Quota
GROUP BY B.ID;
   
v2
Comments
Sandeep Mewara 27-Jul-20 5:31am
   
+5 :thumbsup:
Maciej Los 27-Jul-20 5:32am
   
Thank you, Sandeep.
Shao Voon Wong 27-Jul-20 5:52am
   
Thanks Maciej! You saved my day again!
Maciej Los 27-Jul-20 5:53am
   
You're very welcome.
We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.
And interview questions are homework on steroids: it's even less fair for us to answer them as if disadvantages people who can do the job you have applied for.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you can't do this simple task, you really aren't ready to get this job and would fail the interview badly if you did get through this stage.
Good luck with the job hunt, but you need to know the subject before you apply, not after!
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900