SQL SERVER – Interesting Interview Questions – Part 2 – Puzzle

In the recent time of recession my company is able to continue its progress and we are hiring. It is very surprising to me that many developers who have experience with SQL Server could not get following simple question right. There were nearly 40 candidates I interviewed but none of the candidate was able to solve this problem. When I displayed final answer they could not believe that it is that simple.

When I asked some of the MCITP or Oracle certified candidate about why they can not get this simple question, they smiled and answered that I did not have that on my blog or on my interview questions list. Let us go over the interview question.

Let us create two tables. Once Created SELECT values from both the tables.

`USE AdventureWorks GO /*Create First Table PersonColors*/ CREATE TABLE PersonColors (Name VARCHAR(100), ColorCode VARCHAR(100)) GO INSERT INTO PersonColors (Name,ColorCode) VALUES ('Tom','Red'),('Tom','Blue'),('Tom','Green'),('Tom','Brown'), ('Mike','Red'),('Mike','Blue'), ('James','Green'),('James','Brown'), ('Joe','Red'),('Joe','Blue'),('Joe','Green'), ('Matt','Brown') GO SELECT * FROM PersonColors GO /*Create Second Table SelectedColors*/ CREATE TABLE SelectedColors (ColorCode VARCHAR(100)) GO INSERT INTO SelectedColors (ColorCode) VALUES ('Red'),('Blue'),('Green') SELECT * FROM SelectedColors GO `

The resultset of created table is as following.

Now the question is find all the persons from table PersonColor who have all the ColorCode mentioned in the table ColorCode. From the example it is clear that final result will contain Tom and Joe as answer. Tom has Red, Blue and Green as well Brown colorcode. If any person have Red, Blue and Green color they should qualify in our resultset. Joe has all the same colorcode as ColorCode table so it should also qualify in our resultset. Other person Mike and James did not have same kind of colorcode as table ColorCode.

In summary, Select all the person from table PersonColor who have same color as ColorCode or have more colors than table ColorCode.

When you look at this question it is very simple and answer is very simple as well. One of my employee was able to solve it in 3 mins. However, all the 40 interview candidates found it very difficult to write the query for the same.

Now before looking at the answer, please try to find the solution by yourself. Once you find the solution compare to the solution I have and see if you got it correct.

Expected Resultset should look like this.

Again, it may look very simple I would strongly suggest you try to solve it by yourself. If you can not solve it then try to look solution here.

UPDATE: I have used SQL Server 2008 script to generate the same table. I thank Tejas Shah for providing the script of SQL Server 2005 here.

Click here for Solution. (Go live on 12/11/2008)

Reference : Pinal Dave (https://blog.sqlauthority.com)

Winners and Solution – DELETE Qualified Rows From Multiple Tables

40 Comments.Leave new

• For duplicate entries we just need to change the having clause in tejas’ query

HAVING COUNT(DISTINCT pc.ColorCode) >= (SELECT COUNT(ColorCode) FROM SelectedColors)

Reply
• Good post pinal
thanks once again

Reply
• I came up with the following:

SELECT Name
FROM dbo.PersonColors P
LEFT JOIN dbo.SelectedColors S
ON P.ColorCode = S.ColorCode
GROUP BY Name
HAVING COUNT(*) =
(
SELECT DISTINCT COUNT(*)
FROM dbo.SelectedColors
)

Then I read the below comments. I was happy to note that Tejas Shah was close to your answer.

I will say this took me more than 3 minutes to answer, but I am happy that I completed the exercise.

Reply
• select distinct(name) from
PersonColors a
where (select count(colorcode) from PersonColors b where a.name=b.name)>=(select count(colorcode) from SelectedColors)

Reply
• Hi Pinal,

I am a beginner in SQL Server 2005.
Kindly suggest me the way of improving the same as I am very much interested in SQL server.

Regards,
Jerry

Reply
• Hi Pinal,

How about

select name, count(*)
from #personcolors
group by name having count(*) >= 3

since I know there is a maximum of 3 colors in the table SelectedColors

Reply
• Or even this one

declare @ln_count int
set @ln_count = (select count(*) from #tabb)
select name, count(*)
from #taba
group by name
having count (*) >= @ln_count

Reply
• select distinct name from personcolors p where not exists ( select colorcode from selectedcolors where colorcode not in
(select colorcode from personcolors p2 where p.Name=p2.name)
)

Another long solution

Reply
• Hi Pinal

Here is my kind of solution:

SELECT NAME
FROM
PERSONCOLORS PC,SELECTEDCOLORS SC
WHERE PC.COLORCODE=SC.COLORCODE GROUP BY PC.NAME HAVING COUNT(DISTINCT(PC.COLORCODE))>=(SELECT COUNT(*) FROM SELECTEDCOLORS)

Reply
• Using the INNER JOIN this one is only processing PersonColor records that meet the SelectedColors criteria. You don’t necessarily need the DISTINCT keyword, but depending on the source of your data, you never know if you’re going to get duplicates in your table (no constraints on the table DDL).

SELECT Name FROM PersonColors A
INNER JOIN SelectedColors B ON A.ColorCode = B.ColorCode
GROUP BY Name
HAVING COUNT(DISTINCT A.ColorCode) = (SELECT COUNT(DISTINCT ColorCode) FROM SelectedColors)

Reply
• JOBY CHERIYAN
June 9, 2009 6:14 pm

SELECT P.[name] FROM PersonColors P, SelectedColors C
WHERE P.colorcode = C.colorcode
GROUP BY P.[name] HAVING count(P.[name]) >= (SELECT count(c2.colorcode) FROM SelectedColors c2)

Reply
• JOBY CHERIYAN
June 9, 2009 6:29 pm

Guys,

My Advice for you all / Correct me if I’m wrong
———————————————————
1. Join Query will have Perfomance issue when data is huge.
2. If you use COUNT(*) this also Affect the Query perfomace.
3. name is already a SQL key word so use Tbl.[name] to improve perfomance insted just use name in a query.

Query can be like this
—————————-

SELECT P.[name] FROM PersonColors P, SelectedColors C
WHERE P.colorcode = C.colorcode
GROUP BY P.[name] HAVING count(P.[name]) >= (SELECT count(c2.colorcode) FROM SelectedColors c2)

Reply
• Atin Srivastava
August 10, 2009 4:32 pm

How about this–
What I noticed some blogs above given by bloggers doenst have dynamic queries means their queries will fail if we insert or update more data in either tables… Queries should be general that goes write no matter what data table contains

select name from personcolors a, selectedcolors b where a.colorcode=b.colorcode
group by [name] having count(*)= (select count(*) from selectedcolors)

Reply
• Rakesh Shrivastava
August 25, 2009 12:55 pm

select p.name from Personcolors p
left outer join SelectedColors s on p.colorcode=s.colorcode
group by p.name
having count(s.colorcode)=(select count(d.colorcode) from SelectedColors d)

Reply
• select distinct name from PersonColors where Name not in (
select b.name from (
select a.Name, SelectedColors.ColorCode
from (Select distinct name from PersonColors) a
cross join SelectedColors
) b
left join PersonColors pc on b.Name=pc.Name and pc.ColorCode=b.ColorCode
where pc.Name is null
)

Reply
• Shakshi Pradhan
August 19, 2011 2:09 am

Select Name from Person_Color WHERE Color_Code=’Red’ AND ‘Blue’ AND ‘Green’

Reply
• Vijay Labhaniya
March 21, 2014 11:11 am

SELECT * FROM (SELECT *,ROW_NUMBER() OVER ( partition by name ORDER BY colorcode) a FROM PersonColors)
main
where main.a = (Select COUNT(*) FROM SelectedColors)
and main.colorcode IN (Select colorcode FROM SelectedColors)

Reply
• with cte
as
(
select pc.name, pc.colorcode from PersonColors pc
inner join
SelectedColors sc
on pc.colorcode = sc.colorcode
)

select cte.Name, COUNT(cte.name)
from cte
group by cte.name
having COUNT(cte.name) > 2

Reply