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

SQL SERVER - Interesting Interview Questions - Part 2 - Puzzle - Solution int2

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 (https://blog.sqlauthority.com)

,
Previous Post
SQLAuthority News – SQL SERVER 2008 Upgrade Technical Reference Guide Download
Next Post
SQLAuthority News – Wedding Day of Author – 800th Article of Blog

Related Posts

25 Comments. Leave new

  • 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

    Reply
  • 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

    Reply
  • 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)

    Reply

Leave a Reply

Menu