-- 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;
Editor's column There were days when Rooms-sized processors used for processing data. IT boomed up at the start of 21st Century and so many innumerable inventions made Computer world and few among them are still up in the markets for thier better implemented programming Alogrithms which couples with computer hardware and software ( OS ). Textpads or notepads were the repository of data and programmers had to be Experts to do data manipulation. In other words, Programmers had to use thier own logic to do insert , delete, update in data source. For instance, Consider the following: file1.txt: 1 I am line number 1 2 I am line number 2 3 I am line number 3 4 I am line number 4 5 I am line number 5 6 I am line number 6 7 I am line number 7 8 I am line number 8 9 I am line number 9 10 I am line number 10 To programmatically delete 8 th record , developer had to use the following logic with OLD file handling methods in 3rd generation language. 1. Open file1.txt, an...
Comments