SQL SERVER – Interesting Interview Questions – Part 2 – Puzzle – Solution

Yesterday we looked at Puzzle and I did got great response to this question. Very interestingly not many got it right. First go through the puzzle first and then come back here and read answer.

Read Original Interview Question and Puzzle.

Question: Select all the person from table PersonColor who have same color as ColorCode or have more colors than table ColorCode.

UPDATE: Following solution is written with assumption that in SelectedColors table Name and ColorCode are Primary Key. This requirement was not specified in original question.

/*Answer to Interview Question*/
SELECT Name
FROM PersonColors pc
INNER JOIN SelectedColors sc ON sc.ColorCode = pc.ColorCode
GROUP BY pc.Name
HAVING COUNT(pc.ColorCode) >= (SELECT COUNT(ColorCode) FROM SelectedColors)
GO

If you want to download complete script for this interview question. Please download it from here.

Complete script of the puzzle is also listed here.

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
/*Answer to Interview Question*/
SELECT Name
FROM PersonColors pc
INNER JOIN SelectedColors sc ON sc.ColorCode = pc.ColorCode
GROUP BY pc.Name
HAVING COUNT(pc.ColorCode) >= (SELECT COUNT(ColorCode) FROM SelectedColors)
GO
*Clean up DATABASE*/
DROP TABLE PersonColors
DROP TABLE SelectedColors
GO

Let me know your opinion about this puzzle.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

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

  1. Your solution is incorrect, as there’s no unique constraint. If you insert a person with a sufficient number of rows with colors not in SelectedColors, they will be in your result set but should not be according to your specifications.

  2. Hi Dave,

    I just looked into the site and recognized the style of insertion into the table like this

    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’)

    As all in my past I used to insert only one record at a time. And I was amazed at looking this that we can insert multiple records with in a single insert statement. But sadly, my it failed in my machine. The more interesting is I accidentally inserted a duplicate record into the SelectedColors table with the value ‘Red’ two times. I tried to delete only the duplicate. But cannot do that without deleting the ‘Red’ items. I tried in the GUI grid also to delete only one record. That failed to do this. Is there any work around for this rather than deleting all the records.

  3. Wow, I’m just excited that I got the same answer! :)

    That is an interesting puzzle, I’ve never had that sort of problem before “in real life” but it was fun.

    I’m a newer reader to your blog, but do you have additional T-SQL puzzles like that (or in general)?

    Thank you!

  4. The posted answer will fail if there are multiple identical rows i.e. three rows that say Mark, Green. Mark will appear even though he only has one of the colors…

  5. SELECT distinct Name
    FROM #PersonColors PC
    INNER JOIN #SelectedColors SC on
    SC.ColorCode=PC.ColorCode
    Group by Name
    HAving Count(PC.ColorCode) >=
    (Select Count(1) from #SelectedColors)

  6. wow.. that’s very good question.

    i got same answer, please give me some more this type of question so that i can improve my knowledge.

  7. i try this query

    select name from PersonColors
    where colorcode in(select colorcode from SelectedColors)
    group by name

    is it correct please reply…

  8. If there is a primary key on Name and ColorCode I’m having trouble understanding why the having clause uses greater than or equal. Shouldn’t it be just equal?

  9. DECLARE @cnt int
    SELECT @cnt = COUNT(distinct colorcode) FROM selectedcolors
    SELECT NAME FROM (
    SELECT NAME,COUNT(DISTINCT scl.ColorCode) ColorCode FROM PersonColors n
    INNER JOIN SelectedColors scl ON n.ColorCode = scl.ColorCode
    GROUP BY NAME
    HAVING COUNT(DISTINCT scl.ColorCode) = @cnt

    ) Q

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

  11. select P.Name from personcolors P inner join selectedcolors S on P.colorcode=s.colorcode
    group by P.Name
    having COUNT(*)= (select COUNT(*) from selectedcolors)

  12. HI.

    pinaldave sorry to say but your answer is not correct…

    Take a look:

    If you run this:

    delete from personcolors where name=’Tom’ and ColorCode=’Red’;
    insert into personcolors values(‘Tom’, ‘Blue’);

    The person Tom has three colors: green, brown and blue. Right now this user should not appear on the result set because he’s missing red color. Guess what? Running your query he is still shown on the result set (because he has two blue lines) :(

    For this query to work it should be written like this:

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

    The only difference is that i’m using COUNT(distinct pc.ColorCode).

    My first solution was pretty much like yours, but after awhile I relized that it was not correct :-D

    I’m a great fan of your blog
    Greetings from Portugal ;-)

    Leandro Nunes

  13. @Leandro

    “Following solution is written with assumption that in SelectedColors table Name and ColorCode are Primary Key. This requirement was not specified in original question.”

  14. @Brian Tkatch

    Sorry, I missed that. But assuming that there ia no need to have the greater or equal comparison here: COUNT(pc.ColorCode) >= (SELECT COUNT(ColorCode) FROM SelectedColors). Assuming that “hidden” key (:-p) we can use just the equal comparison like this: COUNT(pc.ColorCode) = (SELECT COUNT(ColorCode) FROM SelectedColors).

    Just a tinny little tune up…

    Cheers,
    Leandro Nunes

  15. If you use distinct in the having count then you can avoid duplicates in both tables.

    SELECT Name

    FROM PersonColors pc

    INNER JOIN SelectedColors sc ON sc.ColorCode = pc.ColorCode

    GROUP BY pc.Name

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

  16. If you insert data as mentioned below
    (‘Tom’,’Red’),(‘Tom’,’Blue’), (‘Tom’,’Red’),(‘Tom’,’Blue’)
    you will get Tom in output which is wrong…

  17. @Surya,

    You are correct, we will get TOM in that case.

    But please read post, there is already note like:

    UPDATE: Following solution is written with assumption that in SelectedColors table Name and ColorCode are Primary Key. This requirement was not specified in original question.

    Thanks,

    Tejas

  18. Hi,
    I am using SQL Server 2005 and many times i have tried with single insert statements having multiple values always gives error .
    what can be the reason?

    To insert i used your way

    CREATE TABLE PersonColors (Name VARCHAR(100), ColorCode VARCHAR(100))
    GO
    INSERT INTO PersonColors ([Name],ColorCode)
    SELECT ‘Tom’,’Blue’
    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’
    SELECT *
    FROM PersonColors
    SELECT *
    FROM SelectedColors
    GO

  19. Hi Brian,
    When i executes
    CREATE TABLE PersonColors (Name VARCHAR(100), ColorCode VARCHAR(100))
    GO
    Then table creation is successfull but when i inserts data in

    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

    This way it gives error

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘,’.

    Thanks
    Rahul

  20. SELECT Name
    FROM PersonColors pc
    INNER JOIN SelectedColors sc ON sc.ColorCode = pc.ColorCode
    GROUP BY pc.Name
    HAVING COUNT(distinct pc.ColorCode) >= (SELECT COUNT(ColorCode) FROM SelectedColors)

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