In the recent time of recession my company is able to continue its progress and we are hiring. It is very surprising to me that many developers who have experience with SQL Server could not get following simple question right. There were nearly 40 candidates I interviewed but none of the candidate was able to solve this problem. When I displayed final answer they could not believe that it is that simple.
When I asked some of the MCITP or Oracle certified candidate about why they can not get this simple question, they smiled and answered that I did not have that on my blog or on my interview questions list. Let us go over the interview question.
Let us create two tables. Once Created SELECT values from both the tables.
/*Create First Table PersonColors*/
CREATE TABLE PersonColors (Name VARCHAR(100), ColorCode VARCHAR(100))
INSERT INTO PersonColors (Name,ColorCode)
/*Create Second Table SelectedColors*/
CREATE TABLE SelectedColors (ColorCode VARCHAR(100))
INSERT INTO SelectedColors (ColorCode)
The resultset of created table is as following.
Now the question is find all the persons from table PersonColor who have all the ColorCode mentioned in the table ColorCode. From the example it is clear that final result will contain Tom and Joe as answer. Tom has Red, Blue and Green as well Brown colorcode. If any person have Red, Blue and Green color they should qualify in our resultset. Joe has all the same colorcode as ColorCode table so it should also qualify in our resultset. Other person Mike and James did not have same kind of colorcode as table ColorCode.
In summary, Select all the person from table PersonColor who have same color as ColorCode or have more colors than table ColorCode.
When you look at this question it is very simple and answer is very simple as well. One of my employee was able to solve it in 3 mins. However, all the 40 interview candidates found it very difficult to write the query for the same.
Now before looking at the answer, please try to find the solution by yourself. Once you find the solution compare to the solution I have and see if you got it correct.
Expected Resultset should look like this.
Again, it may look very simple I would strongly suggest you try to solve it by yourself. If you can not solve it then try to look solution here.
UPDATE: I have used SQL Server 2008 script to generate the same table. I thank Tejas Shah for providing the script of SQL Server 2005 here.
Reference : Pinal Dave (http://blog.SQLAuthority.com)