CREATE TABLE branches OF branch_type(
branch_ID PRIMARY KEY);

CREATE TYPE BRANCH_TYPE AS OBJECT(
branch_id NUMBER(3),
Address ADDRESS_TYPE,
Phone PHONENUMBER_TYPE);

CREATE TABLE employees OF employee_Type(
branch_ID PRIMARY KEY);

CREATE type employee_Type AS object(
branch ref branch_type,
emp_id NUMBER(8),
address ADDRESS_TYPE,
name name_type,
supervisor REF EMPLOYEE_TYPE,
position VARCHAR2(20),
salary   NUMBER(5),
ninum    VARCHAR2(8));

CREATE TABLE account OF account_type(
acc_num PRIMARY KEY);

CREATE type account_type AS object(
branch_id ref branch_Type,
acc_num       NUMBER(8),
acc_type      VARCHAR(20));

CREATE TABLE customer OF customer_Type
(cust_ID PRIMARY KEY);

CREATE type customer_Type AS object(
cust_ID NUMBER(8),
address address_type,
name name_type,
ninum VARCHAR2(8));

CREATE TABLE customer_account OF cust_acc_type;

CREATE type cust_acc_Type AS object(
acc_num ref account_Type,
cust_id ref customer_Type);

SELECT          e.name.firstname    as f_name,
                e.emp_id            as emp_id,
                m.name.firstname    as manager,
                e.SUPERVISOR.emp_id as s_id
FROM            EMPLOYEES   e
LEFT OUTER JOIN EMPLOYEES   m   ON  e.SUPERVISOR.emp_id = m.emp_id
WHERE           e.SUPERVISOR.emp_id IS NOT NULL;

| fname | emp_id | supervisor | s_id |
|-------|--------|------------|------|
| john  | 102    | alison     | 101  |
| chris | 106    | john       | 102  |
| ryan  | 108    | chris      | 106  |
| jack  | 804    | loraine    | 802  |

SELECT          e.name.firstname       as f_name,
                e.BRANCH_ID            as emp_id,
                m.name.firstname       as manager,
                e.SUPERVISOR.branch_id as s_id,
                c.cust_id.cust_id      as cust_id
FROM            EMPLOYEES           e,
                customer_account    c
LEFT OUTER JOIN EMPLOYEES           m   ON  e.SUPERVISOR.branch_id = m.BRANCH_ID
WHERE           e.SUPERVISOR.branch_id IS NOT NULL
AND             c.CUST_ID.ninum = e.ninum;