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 (https://blog.sqlauthority.com)
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
@Rahul
What exactly is the problem. Can you show a statement that generates an error?
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
@Rahul
That behavior is in SQL Server 2008, not 2005.
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)