# 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```

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```

Reference : Pinal Dave (https://blog.sqlauthority.com)

## SQL SERVER – Network Port Used

• Rahul Bhargava
December 17, 2009 5:55 pm

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

• 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