Skip to main content

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;

Comments

Post a Comment

Popular posts from this blog

SQL to return records in m,n sequence

I want my SQL to return only records from 101 to 200. See below options which would do the required.. Select * from (Select rownum rownumber,employee.* from employee) Where rownumber between 101 and 200; -- Using rownum label in oracle Select * from (Select employee.*, row_number() over (order by emp_id desc) Rownumber from employee) where Rownumber between 101 and 200; --Using the analytical function row_number Select * from (Select employee.*, row_number() over (order by emp_id desc) Rownumber from employee) where Rownumber between :Fromnumber and :tonumber; -- lets user decide the recordset chunk... Hope this is helpful!!!!!!
Post a Comment
Read more

PLSQL programming - Part 1

Pre-requisite : Basic SQL Knowledge in Oracle and programming knowledge in any language Browse my blog to read the overview of PLSQL if you need it. Anonymous block ............. ........... This is where you declare Variables that you are going to use it in PLSQL block ............ Data types are chosen based on requirement. For example, if you want to process strings you go for data types of characters and for numeric operations Int, float , double or number. Begins ..... ......... ......... .......... This is where you implement programming logic and get the desired outcome. .......... .......... Ends..... We call it anonymous block because we don't name it or reference it. (Unlike Package, Stored procedure,& Other objects) Learn from examples Example 1: declare var1_num number; --Declaring number type variable var2_str varchar2; begin var1_num := 10; --Assigning 'Number' Value to the variable var2_str := 'I am a string value...
1 comment
Read more

What happens when you create tables with the existing SYNONYM names

See below snippets: Select * from dba_synonyms where synonym_name='DUAL'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK PUBLIC DUAL SYS DUAL (NULL) We know that DUAL table is accessible by users. Lets create a table named DUAL under our schema (USER). CREATE TABLE dual AS SELECT * FROM dual; Select * from dual; insert into dual values('Y'); commit; Select * from dual; DUMMY ---------- X Y The Synonym is not referred unless the Synonym name is not used by any object under the USER/SCHEMA... So , When creating tables in your schema, take a look at SYNONYMs declared as PUBLIC and try not to use them.
1 comment
Read more