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. The answer to the interview puzzle uses a simple GROUP BY with HAVING. Here is the Part 2 puzzle solution with the full script.

Part 2 Puzzle Solution: 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 Part 2 puzzle solution 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.

Why This Answer Works

This pattern is known as relational division. The INNER JOIN keeps only the colors that appear in SelectedColors, so Brown drops out for everyone. GROUP BY then counts the matching colors for each person, and HAVING keeps only the people whose count reaches the number of selected colors. In the sample data, Tom and Joe are returned. Tom has all three selected colors plus Brown, and Joe has exactly the three. Mike, James and Matt each miss at least one.

If your table can hold the same person and color twice, change the count to COUNT(DISTINCT pc.ColorCode). Otherwise a duplicate row can push someone over the line without owning every color. That one small change keeps the answer correct even without the primary key.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.