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 (http://blog.SQLAuthority.com)

40 thoughts on “SQL SERVER – Interesting Interview Questions – Part 2 – Puzzle

  1. I haven’t checked this but I reckon it would do the job:

    select [name]
    from
    (select [name], count(colorcode) as c
    from
    (select *
    from (select distinct * from personcolor) a
    where [colorcode] in (select colorcode from selectedcolors)) b
    group by [name]) d
    where c = 3

    Like

    • Hi Magesh, your code is OK but who knows the max count i.e 3, it may be 4 /5/6/7….. so i think btr is to put the sub-query for COUNT(ColorCode)

      Thanks,
      Subha

      Like

  2. Pinal has provided script is for SQL 2008.

    If you want to create with SQL server 2005. Use this one:

    /*Create First Table PersonColors*/
    CREATE TABLE PersonColors (Name VARCHAR(100), ColorCode VARCHAR(100))
    GO
    INSERT INTO PersonColors (Name,ColorCode)
    SELECT’Tom’,’Red’
    UNION ALL
    SELECT
    ‘Tom’,’Green’
    UNION ALL
    SELECT
    ‘Tom’,’Brown’
    UNION ALL
    SELECT
    ‘Mike’,’Red’
    UNION ALL
    SELECT
    ‘Mike’,’Blue’
    UNION ALL
    SELECT
    ‘James’,’Green’
    UNION ALL
    SELECT
    ‘James’,’Brown’
    UNION ALL
    SELECT
    ‘Joe’,’Red’
    UNION ALL
    SELECT
    ‘Joe’,’Blue’
    UNION ALL
    SELECT
    ‘Joe’,’Green’
    UNION ALL
    SELECT
    ‘Matt’,’Brown’
    GO
    SELECT *
    FROM PersonColors
    GO
    /*Create Second Table SelectedColors*/
    CREATE TABLE SelectedColors (ColorCode VARCHAR(100))
    GO
    INSERT INTO SelectedColors (ColorCode)
    SELECT ‘Red’
    UNION ALL
    SELECT ‘Blue’
    UNION ALL
    SELECT ‘Green’
    GO
    SELECT *
    FROM SelectedColors
    GO

    Like

  3. Hi Pinal,

    Please find the simplest way to fix it..It also takes care if ColorCodes table is Dynamic..so that I did this check HAVING COUNT(*) = (SELECT COUNT(*) FROM SelectedColors).

    SELECT [Name]
    FROM PersonColors pc
    INNER JOIN SelectedColors sc ON pc.ColorCode = sc.ColorCode
    GROUP BY [Name]
    HAVING COUNT(*) = (SELECT COUNT(*) FROM SelectedColors)

    Tejas

    Like

  4. SELECT a.[Name]
    FROM PersonColors a INNER JOIN SelectedColors b ON a.[ColorCode] = b.[ColorCode]
    GROUP BY a.[Name]
    HAVING COUNT(a.[Name]) > = (SELECT COUNT(a.[ColorCode]) FROM SelectedColors a)

    Like

  5. SELECT [Name]
    from SelectedColors p
    left join PersonColors s on s.ColorCode = p.ColorCode
    group by [Name]
    having count(*) >= (select count(*) from SelectedColors )

    Like

  6. Hi Pinal,
    I have tried this one and got the result also but i could not be able to compare with your solution as the link was not working.can pls tell me whether it is correct or not

    Select distinct[name] from PersonColors
    group by [name]
    having count(colorcode)>=(select count(colorcode) from SelectedColors)

    Like

  7. Hi Pinal,

    Not sure about the optimization. But, I have this solution..

    SELECT distinct NAME FROM PERSONCOLORS where colorcode in (select colorcode from selectedcolors) group by name
    having COUNT(NAME)=(SELECT COUNT(*) FROM selectedcolors) . And this is my first reply to this blog as I am a front end developer, I am not much sure about the optimization. But Pival, your answer link doesn’t work for some reason. And I could not compare against your answer.

    Like

  8. Hi,

    It was really a good question. May be I have found the answer,

    select [Name] from PersonColors
    Group by [Name] having count(distinct(colorcode)) >= (
    select count(distinct(ColorCode)) from SelectedColors)

    Like

  9. Nice exercise Pinal :) Thanks :) It was quite simple and took me about 10 minutes. I don’t know how many minutes you give potential new employees ;)
    I see everybody has the same solution.

    Btw: I use Tejas Shah script to test on 2005 an there is ‘Tom’, ‘Blue’ record missed.

    Like

  10. Good Question, the answer is

    SELECT Name FROM PersonColors GROUP BY Name HAVING COUNT(ColorCode) >= (SELECT COUNT(*) FROM SelectedColors)

    It gave the desired answer.

    Like

  11. select distinct p.Name,count(p.Name)
    from personcolors p
    where exists (select * from selectedcolors sc where sc.colorcode=p.colorcode)
    group by p.Name
    having count(p.Name) = (select count(*) from selectedcolors )

    Like

  12. The ones that use

    COUNT(ColorCode) >= (SELECT COUNT(*) FROM SelectedColors)

    are probably wrong. THe reason is because there is no validation to make sure that 3 of the COlorCodes that the person has are all in the SelectedColors table.

    I mean, the results are coming up correct, but if you update the ColorCodes for the persons that have all three colors to have ColorCodes that are not in the SelectedColors table, the results will still show and one condition that Pinal specified was that they had to have all the colors in the other table.

    again this is a good excercise.

    Like

  13. Pingback: SQL SERVER - Interesting Interview Questions - Part 2 - Puzzle - Solution Journey to SQL Authority with Pinal Dave

  14. The only drawback to Tejas’ solution is that you must ensure that duplicate entries in the PersonColor table are prevented.

    See what happens if you add another row to the table with the values ‘Mike’, ‘Red’. Mike will now appear in the result query because his count is 3, even though he only has 2 colors.

    To address that issue, this seemed to work:

    SELECT [Name]
    FROM
    (select distinct [name],[colorcode] from PersonColors) pc
    INNER JOIN SelectedColors sc ON pc.ColorCode = sc.ColorCode
    GROUP BY [Name]
    HAVING COUNT(*) = (SELECT COUNT(*) FROM SelectedColors)

    Like

  15. Hi Pinal
    I solved this problem within 5 mins.answer given below
    select [Name] ,count(color_code) from
    (select [name],color_main1.color_code from color_main inner join color_main1
    on color_main.color_code=color_main1.color_code) cat1
    group by [Name]
    having count(color_code)=(select count(color_code) from color_main1)

    Like

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

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  22. 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)

    Like

  23. 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)

    Like

  24. 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)

    Like

  25. 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)

    Like

  26. 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)

    Like

  27. 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)

    Like

  28. 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
    )

    Like

  29. 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)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s