SQL question - How to get employees with the same skills as a specific employee

Question

I have a table called Employee with columns:

• EMPID INT (primary key)
• EMPNAME VARCHAR2(100)

With sample data as follows:

empid|empname|
-----|-------|
    1|Mary   |
    2|Bob    |

I have another table Employee_Skills with columns:

• EMPID INT
• SKILL VARCHAR2(100)

A sample of which can be represented as follows:

empid|skill |
-----|------|
    1|SKILL |
    1|Java  |
    1|C++   |
    1|Python|
    2|C++   |
    2|Python|

In the 2nd table, both the columns together form the primary key. And EMPID has a referential constraint pointing to Employee(EMPID).

I want to get all the employees who have all the skills that Mary has.

For example,

select t2.skill 
from Employee t1 
join Employee_Skills t2 on (t1.EMPID = t2.EMPID) 
where t1.EMPNAME = 'Mary'

This query returned these rows:

SKILL
------------
Java
C++
Python
... (a few more)

Now I want to select all employees that have the above skills (can have more skills, but should have at least the ones that Mary has).

I have made a few attempts but nothing gives me exactly what I need.

Here's one attempt that didn't work:

select t1.EMPID, t1.EMPNAME
from Employees t1 
join Employee_Skills t2 on (t1.EMPID = t2.EMPID)
where t2.SKILL = ALL (select t4.skill 
                      from Employee t3  
                      join Employee_Skills t4 on (t3.EMPID = t4.EMPID) 
                      where t3.EMPNAME = 'Mary');

Answer 1

This has worked. Sharing it for everyone. Basically, you take Mary's skills. Do a MINUS of the current employee's skills. If you get a blank, you show this employee in the result.

Select t4.empid, t4.empname from employee t4
Where not exists 
(Select t1.skill from employee_skills t1 join employee t2 on (t1.empid = t2.empid) where t2.empname = 'Mary'
Minus
Select t3.skill from employee_skills t3 where t3.empid = t4.empid)
and t4.empname <> 'Mary';