Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE users(
    usr_type VARCHAR(30),
    usr      VARCHAR(30)
);
BEGIN
  INSERT INTO users(usr_type,usr) VALUES ('Teacher',   'AAAA');
  INSERT INTO users(usr_type,usr) VALUES ('Principal', 'AAAA');
  INSERT INTO users(usr_type,usr) VALUES ('Employee',  'AAAA');
  INSERT INTO users(usr_type,usr) VALUES ('Admin',     'BBBB');
  INSERT INTO users(usr_type,usr) VALUES ('Cashier',   'CCCC'); -- CCCC doesn't belong to any group
  INSERT INTO users(usr_type,usr) VALUES ('Teacher',   'DDDD');
  INSERT INTO users(usr_type,usr) VALUES ('Principal', 'DDDD');
  INSERT INTO users(usr_type,usr) VALUES ('Employee',  'DDDD');
  INSERT INTO users(usr_type,usr) VALUES ('Admin',     'EEEE'); -- <-- this is the initial focus 
  INSERT INTO users(usr_type,usr) VALUES ('Cashier',   'EEEE'); -- <-- this is the initial focus
  INSERT INTO users(usr_type,usr) VALUES ('Admin',     'PPPP'); -- PPPP doesn't belong to any group
  INSERT INTO users(usr_type,usr) VALUES ('Employee',  'PPPP'); -- PPPP doesn't belong to any group
END;
/
1 rows affected

SELECT *
  FROM users;


USR_TYPE
USR


Teacher
AAAA

Principal
AAAA

Employee
AAAA

Admin
BBBB

Cashier
CCCC

Teacher
DDDD

Principal
DDDD

Employee
DDDD

Admin
EEEE

Cashier
EEEE

Admin
PPPP

Employee
PPPP


CREATE TABLE groups(
    usr_type VARCHAR(30),
    group_no VARCHAR(30)
);
BEGIN
  INSERT INTO groups(usr_type,group_no) VALUES ('Teacher',   'Group1');
  INSERT INTO groups(usr_type,group_no) VALUES ('Principal', 'Group1');
  INSERT INTO groups(usr_type,group_no) VALUES ('Employee',  'Group1');
  INSERT INTO groups(usr_type,group_no) VALUES ('Admin',     'Group2');
  INSERT INTO groups(usr_type,group_no) VALUES ('Admin',     'Group3');
  INSERT INTO groups(usr_type,group_no) VALUES ('Cashier',   'Group3');
  INSERT INTO groups(usr_type,group_no) VALUES ('Admin',     'Group4'); -- there are no users in group4
  INSERT INTO groups(usr_type,group_no) VALUES ('Cashier',   'Group4'); -- there are no users in group4
  INSERT INTO groups(usr_type,group_no) VALUES ('Astronaut', 'Group4'); -- there are no users in group4
END;
/
1 rows affected

SELECT *
  FROM groups;


USR_TYPE
GROUP_NO


Teacher
Group1

Principal
Group1

Employee
Group1

Admin
Group2

Admin
Group3

Cashier
Group3

Admin
Group4

Cashier
Group4

Astronaut
Group4


-- join of users and groups for user 'EEEE':
SELECT *
  FROM users  u
  JOIN groups g ON u.usr_type = g.usr_type
 WHERE usr = 'EEEE';


USR_TYPE
USR
USR_TYPE
GROUP_NO


Admin
EEEE
Admin
Group2

Admin
EEEE
Admin
Group3

Cashier
EEEE
Cashier
Group3

Admin
EEEE
Admin
Group4

Cashier
EEEE
Cashier
Group4


-- group by the group_no and count how many rows are in each group
SELECT g.group_no, count(g.usr_type)
  FROM users  u
  JOIN groups g ON u.usr_type = g.usr_type
 WHERE usr = 'EEEE'
 GROUP BY g.group_no;


GROUP_NO
COUNT(G.USR_TYPE)


Group4
2

Group2
1

Group3
2


-- add the condition that the count must be the same as the row count in the groups table
-- so if there are 3 rows in the groups table, then there also be a count of 3 for the join
-- this eliminates group4, because there are 3 of those in the groups table, but only 2 in the join
SELECT g.group_no, count(g.usr_type)
  FROM users  u
  JOIN groups g ON u.usr_type = g.usr_type
 WHERE usr = 'EEEE'
 GROUP BY g.group_no
HAVING count(g.usr_type) = (SELECT count(1)
                              FROM groups g2
                             WHERE g.group_no = g2.group_no);


GROUP_NO
COUNT(G.USR_TYPE)


Group2
1

Group3
2


-- add the conditaion that the count must be the same as the row count in the users table
-- since there are 2 rows of 'EEEE' un the users, not only the 1 found in previous SQL, group2 is now also gone:
SELECT g.group_no
  FROM users  u
  JOIN groups g ON u.usr_type = g.usr_type
 WHERE 1 = 1  -- always true
   AND usr = 'EEEE'
 GROUP BY g.group_no, u.usr
HAVING count(g.usr_type) = (SELECT count(1)
                              FROM groups g2
                             WHERE g.group_no = g2.group_no)
   AND count(g.usr_type) = (SELECT count(1)
                              FROM users u2
                             WHERE u.usr = u2.usr);


GROUP_NO


Group3


-- remove the where condition of usr = 'EEEE' to get the group for all users:
SELECT u.usr, g.group_no
  FROM users  u
  JOIN groups g ON u.usr_type = g.usr_type
 WHERE 1 = 1  -- always true
 GROUP BY g.group_no, u.usr
HAVING count(g.usr_type) = (SELECT count(1)
                              FROM groups g2
                             WHERE g.group_no = g2.group_no)
   AND count(g.usr_type) = (SELECT count(1)
                              FROM users u2
                             WHERE u.usr = u2.usr);


USR
GROUP_NO


AAAA
Group1

BBBB
Group2

DDDD
Group1

EEEE
Group3
USR_TYPE	USR
Teacher	AAAA
Principal	AAAA
Employee	AAAA
Admin	BBBB
Cashier	CCCC
Teacher	DDDD
Principal	DDDD
Employee	DDDD
Admin	EEEE
Cashier	EEEE
Admin	PPPP
Employee	PPPP
USR_TYPE	GROUP_NO
Teacher	Group1
Principal	Group1
Employee	Group1
Admin	Group2
Admin	Group3
Cashier	Group3
Admin	Group4
Cashier	Group4
Astronaut	Group4