Very challenging SQL interview (can't use stored procedure)

Question

You have a SQL table table with two columns: name and pen. Both columns are text strings.

name  | pen
---------------    
mike  | red
mike  | red
mike  | blue
mike  | green
steve | red
steve | yellow
anton | red
anton | blue
anton | green
anton | black
alex  | black
alex  | green
alex  | yellow
alex  | red

Person's name is given as the input argument.

Please write a SQL statement (not a stored procedure) which returns names of persons having unique set of pens which is equivalent or wider/bigger than the set of pens of given person.

Examples:

• input: mike
• output: anton

Mike has (red, blue, green).
Anton has more gadgets (red, blue, green) + black.

• input: steve
• output: alex

Steve has (red, yellow).
Alex has (red, yellow) + green+ black.
Mike, Anton aren't printed - they do not have yellow.

• input: alex
• output:

Answer 1

Here's one way (Online Demo), assuming input name "steve".

This can be rephrased as "Looking for all users for which there does not exist a pen owned by steve that they do not own"

SELECT DISTINCT name
FROM   table t1
WHERE  NOT EXISTS (SELECT *
                   FROM   table t2
                   WHERE  name = 'steve'
                          AND NOT EXISTS (SELECT *
                                          FROM   table t3
                                          WHERE  t2.pen = t3.pen
                                                 AND t1.name = t3.name))  
AND t1.name <> 'steve' /*Exclude input name from results*/

See this article for other techniques