Design and Operations ( Employee- Model )

-- DB design
drop table emp_Salary;
drop table emp_Designation;
drop table emp;
drop table Designations;
drop table Departments;
drop table Salary_Bands;
drop table Addresses;

Create table emp(
        emp_id                  number,
        emp_Name                varchar2(20),
        emp_address_id          varchar2(10),
        emp_created_date        date,
        emp_created_by          varchar2(20),
        constraint e_pk primary key(emp_id)
);

Create table Departments(
        dept_id                 number,
        dept_name               Varchar2(20),
        constraints dept_pk primary key(dept_id)
);

Create table Designations(
        dept_id number references Departments(dept_id),
        desg_id number,
        desg_description varchar2(20),
        constraints desg_pk primary key(dept_id,desg_id)        
);

Create table emp_Designation(
        emp_id                  number references emp(emp_id),
        emp_des_dept_id         number references departments(dept_id),
        emp_desg_id             number
);

Create table Salary_Bands(
        band_id         number,
        band_amount     float(20),
        constraints sal_band_pk primary key(band_id)
);

Create table emp_Salary(
        emp_id                  number  references emp(emp_id),
        emp_sal_band_id         number  references Salary_Bands(band_id),
        emp_sal_percentage      number,
        constraints emp_sal_pk primary key(emp_id,emp_sal_band_id)
);

Create table Addresses(
        address_id number,
        address_zip varchar2(10),
        address_country varchar2(100),
        address_city varchar2(100),
        address_town varchar2(100),
        address_street varchar2(100),
        address_desc varchar2(100),
        constraint addr_pk primary key(address_id)                
);


--Populate master details that do not have dependency on EMP data 
--Addresses
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1001,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-749');
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1002,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-750');
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1003,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-751');
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1004,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-752');
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1005,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-753');
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1006,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-754');
insert into addresses(address_id,address_zip,address_country,address_city,address_town,address_street,address_desc)
values(1007,'1360072','Japan','Tokyo','Koto','Ojima','4-1-3-755');

--Departments
insert into departments(dept_id,dept_name) values(100,'IT Solutions');
insert into departments(dept_id,dept_name) values(200,'Marketing');
insert into departments(dept_id,dept_name) values(300,'Customer Desk');
insert into departments(dept_id,dept_name) values(400,'Reception');
insert into departments(dept_id,dept_name) values(500,'Business Department');
insert into departments(dept_id,dept_name) values(1,'Office of CEO');

-- Designations
insert into Designations(dept_id,desg_id,desg_description) values(100,1,'Developer');
insert into Designations(dept_id,desg_id,desg_description) values(100,2,'Tester');
insert into Designations(dept_id,desg_id,desg_description) values(100,3,'Senior Developer');
insert into Designations(dept_id,desg_id,desg_description) values(100,4,'Technical Lead');
insert into Designations(dept_id,desg_id,desg_description) values(100,5,'Sr.Staff');
insert into Designations(dept_id,desg_id,desg_description) values(200,1,'Executive');
insert into Designations(dept_id,desg_id,desg_description) values(200,2,'Junior Executive');
insert into Designations(dept_id,desg_id,desg_description) values(300,1,'Desktop Engineer');
insert into Designations(dept_id,desg_id,desg_description) values(300,2,'Customer Person');
insert into Designations(dept_id,desg_id,desg_description) values(400,1,'Receptionist');
insert into Designations(dept_id,desg_id,desg_description) values(500,1,'Biz User');
insert into Designations(dept_id,desg_id,desg_description) values(500,2,'BA Architect');
insert into Designations(dept_id,desg_id,desg_description) values(1,1,'CEO');

--- Salary_Bands
insert into salary_bands(band_id,band_amount) values(1,100000);
insert into salary_bands(band_id,band_amount) values(2,200000);
insert into salary_bands(band_id,band_amount) values(3,300000);
insert into salary_bands(band_id,band_amount) values(4,400000);
insert into salary_bands(band_id,band_amount) values(5,500000);
insert into salary_bands(band_id,band_amount) values(6,600000);
insert into salary_bands(band_id,band_amount) values(7,700000);
insert into salary_bands(band_id,band_amount) values(8,800000);
insert into salary_bands(band_id,band_amount) values(9,900000);
insert into salary_bands(band_id,band_amount) values(10,1000000);
insert into salary_bands(band_id,band_amount) values(11,1100000);
insert into salary_bands(band_id,band_amount) values(12,1200000);
insert into salary_bands(band_id,band_amount) values(13,1300000);
insert into salary_bands(band_id,band_amount) values(14,1400000);
insert into salary_bands(band_id,band_amount) values(15,1500000);
insert into salary_bands(band_id,band_amount) values(16,1600000);
insert into salary_bands(band_id,band_amount) values(17,1700000);
insert into salary_bands(band_id,band_amount) values(18,1800000);
insert into salary_bands(band_id,band_amount) values(19,1900000);
insert into salary_bands(band_id,band_amount) values(20,2000000);
insert into salary_bands(band_id,band_amount) values(21,2100000);
insert into salary_bands(band_id,band_amount) values(22,2200000);
insert into salary_bands(band_id,band_amount) values(23,2300000);
insert into salary_bands(band_id,band_amount) values(24,2400000);
insert into salary_bands(band_id,band_amount) values(25,2500000);
insert into salary_bands(band_id,band_amount) values(26,2600000);
insert into salary_bands(band_id,band_amount) values(27,2700000);
insert into salary_bands(band_id,band_amount) values(28,2800000);
insert into salary_bands(band_id,band_amount) values(29,2900000);
insert into salary_bands(band_id,band_amount) values(30,3000000);
insert into salary_bands(band_id,band_amount) values(35,3500000);
insert into salary_bands(band_id,band_amount) values(40,4000000);
insert into salary_bands(band_id,band_amount) values(45,4500000);
insert into salary_bands(band_id,band_amount) values(50,5000000);

--Employee data 
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10001,'Name 1',1003,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10002,'Name 2',1001,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10003,'Name 3',1002,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10004,'Name 4',1004,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10005,'Name 5',1005,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10006,'Name 6',1006,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10007,'Name 7',1003,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10008,'Name 8',1004,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10009,'Name 9',1007,sysdate,user);
insert into emp(emp_id,emp_name,emp_address_id,emp_created_date,emp_created_by) values(10010,'Name 10',1005,sysdate,user);

--Emp department and Designation
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10001,100,1);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10002,100,1);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10003,200,1);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10004,200,1);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10005,300,1);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10006,300,2);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10007,100,2);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10008,100,2);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10009,500,1);
insert into emp_designation(emp_id,emp_des_dept_id,emp_desg_id) values(10010,500,2);

--Emp Salary
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10001,1,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10002,2,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10003,3,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10004,4,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10005,1,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10006,2,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10007,4,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10008,5,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10009,2,100);
insert into emp_salary(emp_id,emp_sal_band_id,emp_sal_percentage) values(10010,6,100);

commit;


--Get All Employees Details ( Department, Salary , Designation , Address ) order by Department
SELECT e.emp_id, e.emp_name, d.desg_description, sb.band_amount,
          a.address_country
       || ' '
       || a.address_city
       || ' '
       || a.address_town
       || ' '
       || a.address_street
       || ' '
       || a.address_desc address
  FROM emp e,
       emp_designation ed,
       emp_salary es,
       addresses a,
       designations d,
       salary_bands sb
 WHERE 1 = 1
   AND es.emp_sal_band_id = sb.band_id
   AND ed.emp_desg_id = d.desg_id
   AND ed.emp_des_dept_id = d.dept_id
   AND e.emp_address_id = a.address_id
   AND e.emp_id = es.emp_id
   AND e.emp_id = ed.emp_id
Order by 3;


--Find out employees who share the same address
Select  e1.emp_id,
        e1.emp_name,
        a.* 
from    emp e1,
        emp e2,
        addresses a
Where 1=1
and e1.EMP_ADDRESS_ID = a.ADDRESS_ID
and e1.EMP_ID != e2.EMP_ID
and e1.EMP_ADDRESS_ID = e2.EMP_ADDRESS_ID
order by e1.emp_address_id,e1.emp_id;

--Find out employees who get max salary in the same department 
SELECT all_details.dept_name, all_details.emp_id, all_details.emp_name,
       all_details.band_amount amount
  FROM (SELECT   ed.emp_des_dept_id dept_id, MAX (sb.band_amount) max_salary
            FROM emp e, emp_designation ed, emp_salary es, salary_bands sb
           WHERE emp_sal_band_id = band_id
             AND e.emp_id = es.emp_id
             AND e.emp_id = ed.emp_id
        GROUP BY ed.emp_des_dept_id) maxsalary,
       (SELECT e.emp_id, e.emp_name, ed.emp_des_dept_id, d.dept_name,
               sb.band_amount
          FROM emp e,
               emp_designation ed,
               emp_salary es,
               salary_bands sb,
               departments d
         WHERE d.dept_id = ed.emp_des_dept_id
           AND emp_sal_band_id = band_id
           AND e.emp_id = es.emp_id
           AND e.emp_id = ed.emp_id) all_details
 WHERE all_details.band_amount = maxsalary.max_salary
   AND all_details.emp_des_dept_id = maxsalary.dept_id;