Oracle的SQL练习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