clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798673 fiddles created (41896 in the last week).

CREATE TABLE Employees ( Employee VARCHAR(30) NOT NULL PRIMARY KEY ); CREATE TABLE Roles ( Role VARCHAR(30) NOT NULL PRIMARY KEY ); CREATE TABLE AppSystemCategories ( Category VARCHAR(30) NOT NULL PRIMARY KEY ); CREATE TABLE AppSystems ( AppSystem VARCHAR(30) NOT NULL PRIMARY KEY, Category VARCHAR(30) NOT NULL REFERENCES AppSystemCategories(Category) ); CREATE TABLE EmployeeAppSystems ( Employee VARCHAR(30) NOT NULL REFERENCES Employees(Employee), AppSystem VARCHAR(30) NOT NULL REFERENCES AppSystems(AppSystem), PRIMARY KEY (Employee, AppSystem) ); CREATE TABLE RoleAppSystems ( Role VARCHAR(30) NOT NULL REFERENCES Roles(Role), AppSystem VARCHAR(30) NOT NULL REFERENCES AppSystems(AppSystem), PRIMARY KEY (Role, AppSystem) ); INSERT INTO Employees(Employee) VALUES ('bibi'),('tee'),('mimi'),('lee'), ('min'); INSERT INTO Roles(Role) VALUES ('Data Analyst'), ('Engineer'), ('Business'); INSERT INTO AppSystemCategories(Category) VALUES ('Tech'), ('Biz'); INSERT INTO AppSystems(AppSystem, Category) VALUES ('Redshift','Tech'), ('Redash','Tech'), ('Jira','Tech'), ('Tableau','Tech'), ('Figma','Tech'), ('Office','Tech'), ('MoneyForward','Biz'), ('Salesforce','Biz'), ('Notion','Biz'); INSERT INTO RoleAppSystems(Role, AppSystem) VALUES ('Data Analyst','Redshift'), ('Data Analyst','Redash'), ('Data Analyst','Tableau'), ('Data Analyst','MoneyForward'), ('Data Analyst','Notion'), ('Engineer','Figma'), ('Engineer','Jira'), ('Engineer','MoneyForward'), ('Engineer','Notion'), ('Business','Notion'), ('Business','MoneyForward'), ('Business','Office'); -- MoneyForward and Notion are used by all roles INSERT INTO EmployeeAppSystems(Employee, AppSystem) VALUES ('bibi','Redshift'), ('bibi','Redash'), ('bibi','MoneyForward'), ('bibi','Notion'), -- Partial match for Data Analyst, match for Biz systems ('tee','Redshift'), ('tee','Redash'), ('tee','Tableau'), ('tee','MoneyForward'), ('tee','Notion'), -- Perfect match for Data Analyst ('mimi','Figma'), ('mimi','Jira'), ('mimi','MoneyForward'), ('mimi','Notion'), ('mimi','Tableau'), -- Over qualified for Engineer (uses extra systems) ('lee','Notion'), ('lee','Salesforce'), -- No tech skills ('min','Redshift'), ('min','Redash'), ('min','Jira'), ('min','Tableau'), ('min','Figma'), ('min','Office'), ('min','MoneyForward'), ('min','Salesforce'), ('min','Notion'); -- min Has it all -- all candidates use notion
5 rows affected
3 rows affected
2 rows affected
9 rows affected
12 rows affected
25 rows affected
 hidden batch(es)

SELECT * FROM AppSystems; SELECT * FROM AppSystemCategories; SELECT * FROM Employees; SELECT * FROM EmployeeAppSystems; SELECT * FROM Roles; SELECT * FROM RoleAppSystems;
appsystem category
Redshift Tech
Redash Tech
Jira Tech
Tableau Tech
Figma Tech
Office Tech
MoneyForward Biz
Salesforce Biz
Notion Biz
employee appsystem
bibi Redshift
bibi Redash
bibi MoneyForward
bibi Notion
tee Redshift
tee Redash
tee Tableau
tee MoneyForward
tee Notion
mimi Figma
mimi Jira
mimi MoneyForward
mimi Notion
mimi Tableau
lee Notion
lee Salesforce
min Redshift
min Redash
min Jira
min Tableau
min Figma
min Office
min MoneyForward
min Salesforce
min Notion
Data Analyst
role appsystem
Data Analyst Redshift
Data Analyst Redash
Data Analyst Tableau
Data Analyst MoneyForward
Data Analyst Notion
Engineer Figma
Engineer Jira
Engineer MoneyForward
Engineer Notion
Business Notion
Business MoneyForward
Business Office
 hidden batch(es)

-- 全てのシステムを利用する社員 -- 利用するシステム以外の全てのシステムはない社員 select e.Employee from Employees AS e where NOT EXISTS ( -- 全ての利用されるシステム select a.AppSystem from AppSystems AS a EXCEPT -- 利用するシステム以外 select eas.AppSystem from EmployeeAppSystems AS eas where eas.Employee = e.Employee )
 hidden batch(es)