Recursive one-to-many
drop table employee;
create table employee(
employee_id int generated always as identity start with 100 primary key,
first_name varchar(20),
last_name varchar(30) not null,
title varchar(20),
supervisor_id int references employee not null
);
insert into employee (first_name, last_name, title, supervisor_id) values('Joseph','Quimby','Mayor',100);
insert into employee (first_name, last_name, title, supervisor_id) values('Gary','Chalmers','Superintendent',100);
insert into employee (first_name, last_name, title, supervisor_id) values('Clancy','Wiggum','Chief',100);
insert into employee (first_name, last_name, title, supervisor_id) values('Lou','Azaria','Sergeant',102);
insert into employee (first_name, last_name, title, supervisor_id) values('Eddie','Shearer','Officer',102);
insert into employee (first_name, last_name, title, supervisor_id) values('Otto','Mann','Mr.',101);
insert into employee (first_name, last_name, title, supervisor_id) values('Seymour','Skinner','Principal',101);
insert into employee (first_name, last_name, title, supervisor_id) values('Dustin','Bergstrom','Mr.',101);
insert into employee (first_name, last_name, title, supervisor_id) values('Edna','Krabappel','Mrs.',106);
insert into employee (first_name, last_name, title, supervisor_id) values('Hank','Krupt','Coach',106);
insert into employee (first_name, last_name, title, supervisor_id) values('Dewy','Largo','Mr.',106);
insert into employee (first_name, last_name, title, supervisor_id) values('William','MacDougal','Groundskeeper',106);
select * from employee;
select emp.title,
emp.first_name,
emp.last_name,
sup.title || ' ' || sup.last_name as Supervisor
from employee emp
join employee sup
on emp.SUPERVISOR_ID = sup.EMPLOYEE_ID