In today’s blog post, we will discuss a simple yet often misunderstood SQL Server interview question. This question has stumped many experienced developers, including those with SQL Server certifications. Let’s dive into the question and its solution.

First, let’s create two tables: PersonColors and SelectedColors. The PersonColors table contains the names of individuals and their corresponding color codes. The SelectedColors table contains a list of color codes.

USE AdventureWorks

/* Create First Table PersonColors */
CREATE TABLE PersonColors (
    Name VARCHAR(100),
    ColorCode VARCHAR(100)
)

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')

/* Create Second Table SelectedColors */
CREATE TABLE SelectedColors (
    ColorCode VARCHAR(100)
)

INSERT INTO SelectedColors (ColorCode)
VALUES
    ('Red'),
    ('Blue'),
    ('Green')

Now, the question is to find all the persons from the PersonColors table who have all the color codes mentioned in the SelectedColors table.

In this example, the expected result should include Tom and Joe. Tom has the color codes Red, Blue, Green, and Brown. Joe also has all the color codes mentioned in the SelectedColors table.

The solution to this question is quite simple. We need to select all the persons from the PersonColors table who have the same color codes as the SelectedColors table or have more colors than the SelectedColors table.

SELECT DISTINCT pc.Name
FROM PersonColors pc
WHERE NOT EXISTS (
    SELECT sc.ColorCode
    FROM SelectedColors sc
    WHERE NOT EXISTS (
        SELECT pc2.ColorCode
        FROM PersonColors pc2
        WHERE pc2.Name = pc.Name
        AND pc2.ColorCode = sc.ColorCode
    )
)

It’s important to note that while this question may seem simple, it tests your understanding of SQL Server concepts such as subqueries and the use of the EXISTS keyword.

Before we conclude, I encourage you to try solving this question on your own. If you’re unable to find the solution, you can refer to the provided solution.

Thank you for reading this blog post on understanding SQL Server concepts. Stay tuned for more informative articles!