[Go to site: main page, start]
Scribd
Upload
5 views2 pages

Employee Data Views and Salary Calculations

The document contains SQL commands for various operations on employee and department data, including creating views, inserting data, updating job titles, and generating salary summaries. It also includes queries to find employees earning above their department's average salary and to list departments without employees. Additionally, it demonstrates the use of performance bonuses and salary grades in employee data management.

Uploaded by

anujasbamdale1519
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
5 views2 pages

Employee Data Views and Salary Calculations

The document contains SQL commands for various operations on employee and department data, including creating views, inserting data, updating job titles, and generating salary summaries. It also includes queries to find employees earning above their department's average salary and to list departments without employees. Additionally, it demonstrates the use of performance bonuses and salary grades in employee data management.

Uploaded by

anujasbamdale1519
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

--16.

Create a view showing all the employee’s data with their department &
gradeaccording to salary

create or replace view emp_dept_grade


as
select ename,job,[Link],dname,loc,sal,grade
from emp e,dept d,salgrade
where
[Link]=[Link]
and
sal between losal and hisal;

--17. Add 7.5% of salary as performance bonus for each employee and display thenet
yearly salary of each employee. (Do not update the database).

SELECT first_name, salary, (salary * 12) + (salary * 0.075 * 12) AS


net_yearly_salary
FROM employees;

--18. Write Insert Statement to fill data only for Empno, Ename, Job, Hiredate,
Dno,Sal in Emp Table. EMPNO is generated from a sequence by name SQL_EID.
AllCharacter Data is in Upper Case & Hiredate is Current Date.

insert into emp (empno, ename, job, hiredate, dno, sal)


values ([Link], 'JOHN', 'CLERK', SYSDATE, 10, 3000);

--19. Update the Employee’s Designations with following criteria using DECODE
function. CLERK as Assistant,ANALYST as Technical,Salesman as Marketing,Manager as
Boss & Rest as Other

update emp
set job = decode(upper(job),
'clerk', 'assistant',
'Analyst', 'Technical',
'Salesman', 'Marketing',
'Manager', 'Boss',
'Other');

--20. Create an index on emp name & job on employee table.

create index idx_emp_name_job ON emp(ename, job);

--21. Create a view listing the department wise, job wise summaries of employees

create or replace view EMP_Dept_Job_TOTALS AS


select
deptno,
job,
sum(sal) Tot_Sal,
count(*) Tot_Emps,
round(avg(sal)) Average_Sal,
min(sal) Lowest_Sal,
max(sal) Highest_Sal
from emp
group by deptno, job;
--22. Create a view listing the Clerks who have salary higher than that of the
maximum salary of the Salesmen

create or replace view high_paid_clerks AS


select*
from emp
where job = 'CLERK'
and sal > (select max(sal) from emp where job = 'SALESMAN');

--23. List the Department Data of employees who earn more than their own
department’s average salary

select [Link], [Link], [Link], [Link], [Link]


from emp e
join dept d on [Link] = [Link]
where [Link] > (
select avg(sal)
from emp
where deptno = [Link]);

--24. List the department which does not have any employees.

select [Link], [Link], [Link]


from dept d
left join emp e on [Link] = [Link]
where [Link] is null;

--25. List the Employees with Salary Grades & Location from where they work.

select [Link], [Link], [Link], [Link], [Link]


from emp e
join salgrade s on [Link] between [Link] and [Link]
join dept d on [Link] = [Link];

--26. List the employee data along with the manager name who earn the minimum
salary of their respective department.

select e.first_name AS emp_name, [Link], m.first_name AS mgr_name


from employees e
join employees m on e.manager_id = m.employee_id
where salary = (
select min(salary)
from employees
where department_id = e.department_id);

You might also like