ORACLE DAY4

/*
1. 显示工资涨幅，员工编号，原来的工资和增加的工资： 部门号10、50、110的有5%的涨幅，部门号为60的涨10%的工资部门号为20和80涨幅为15%，部门为90的不涨工资
*/
select round(aa*100,2)||’%raise’ as raise,employee_id, salary, aa * salary as new_salary
from (select employee_id as em_id,
(case
when department_id in (10,50,110) then 0.05
when department_id = 60 then 0.10
when department_id in (20,80) then 0.15
else 0
end
) aa
from employees),
employees
where employees.employee_id = em_id and aa <> 0;

/*
2. 找出哪个工作的最大工资大于整个公司内最大的工资的一半，使用with子句，显示出工作名，最大工
*/
WITH max_job as
(select job_title, max_salary as job_total from jobs),
max_all as
(select max(salary) as maxx from employees)
select * from max_job where job_total > (select maxx from max_all) / 2;
/*
3. 使用EXISTS/NOT EXISTS 找出部门员工数不大于3的部门信息，显示部门编号，部门名称
*/
select department_id, department_name
from departments
where NOT EXISTS (select department_id
from employees
where employees.department_id = departments.department_id
group by department_id
having count(*) > 3);

select department_id, department_name
from departments
where EXISTS ((select count(*)
from employees
where employees.department_id = departments.department_id
having count(*) <= 3));

/*
4. 显示员工编号、上司、等级、姓，要求姓前面加上下划线，等级越低横线越长
*/
select * from employees;
select employee_id,manager_id,last_name,lpad(level,2*level-1,’_’) as le
from employees
connect by prior employee_id=manager_id
start with manager_id is null

/*
5. 将employees表复制到EMP_CPY,在EMP_CPY上添加SEX列，根据实际情况，将sex列内容补充完整;复习group rollup函数
*/

create table EMP_CPY as select * from employees;
alter table emp_cpy add (sex varchar2(20) constraint ck_emp_cpy_sex check (sex in (‘M’,’F’)));

update emp_cpy set sex=case
when employee_id < 150
then ‘M’
else ‘F’
end