EMPLOYEES(employee_id NUMBER, first_name VARCHAR2(25), last_name VARCHAR2(25), salary number(8,2), hired_date DATE, department_id number(2))
Departments(Departmentid number(2),DepartmentName VARCHAR2(25))
(1）基于上述EMPLOYEES表写出查询：写出雇用日期在今年的，或者工资在[1000,2000]之间的，或者员工姓名（last_name）以’Obama’打头的所有员工，列出这些员工的全部个人信息。
答案一、
select * from employees
where
Year(hired_date) = Year(date())
or (salary between 1000 and 200)
or left(last_name,5)=' Obama’';
答案二、
select * from employees
where
(
hired_date >= to_date(Year(date()) || ‘-01-01’, ‘yyyy-mm-dd’
and hired_date < to_date((Year(date())+1) || ‘-01-01’, ‘yyyy-mm-dd’
)
or (salary between 1000 and 200)
or last_name like ‘ Obama%’
(2) 查出部门平均工资大于1800元的部门的所有员工，列出这些员工的全部个人信息。
答案一、
select * from employee em
where
em.department_id in
(
select department_id
from employee e
group by department_id
having avg(salary) > 1800
)
答案二、
select * from employee em
where
(
select avg(e.salary) from employee e where e.department_id = em. department_id
) > 1800
(3)查出个人工资高于其所在部门平均工资的员工，列出这些员工的全部个人信息及该员工工资高出部门平均工资百分比。
答案一、
select e.*, ((e.salary - as.avg_salary) / as.avg_salary) as salary_percent
from employee e
left join
(
select em.department_id, avg(em.salary) as avg_salary
from employee em
group by em.department_id
) as on e.department_id = as.department_id
where
e.salary > as.avg_salary
答案二、
select employee e.*,(e.salary-t.avg_salary) / as.avg_salary
from employee e,
(
select t.department_id,avg(salary) avg_salary
from employee em
group by t.department_id
) as t
where
e. department_id = t. department_id and e.salary>t.avg_salary
