Oracle DAY2

/*
1. 显示部门编号、部门名字、该部门的员工数、每个部门的平均工资，部门负责人信息，包括姓名、薪
水、职业；平均工资保留 2 位小数，千分位分隔符显示；结果按部门升序
*/
select employee.department_id,
employee.employees,
to_char(employee.avg_sal,’99,999.00’) as avg_sal,
department_name,
manager.job_id,
manager.last_name,
manager.salary
from (select department_id,count(*) as employees,avg(salary) as avg_sal,manager_id as manager
from employees group by department_id,manager_id) employee,
employees manager, departments
where employee.manager = manager.employee_id
and employee.department_id = departments.department_id;

/*
2. 显示员工数最多的部门信息，显示部门 ID、名称、部门员工数，部门的主管经理姓名
*/
select departments.department_id,
department_name,
a.counts,
employees.first_name||’.’||employees.last_name as manager_name
from (select department_id, count(*) as counts
from employees
group by department_id
having count() = (select max(count())
from employees
group by department_id)) a,
departments,
employees
where departments.department_id = a.department_id
and departments.manager_id = employees.employee_id

/2.2/
select a.department_id,
department_name,
a.counts,
first_name || ‘.’ || last_name as manager_name
from (select *
from (select department_id, count(*) as counts
from employees
group by department_id
order by count(*) desc)
where rownum = 1) a,
employees,
departments
where a.department_id = departments.department_id
and departments.manager_id = employees.employee_id

/*
3. 显示工号、姓名、薪水、部门编号、薪资，薪资与部门平均工资的差异情况；按照部门 ID 排序
*/

select employee_id,
last_name,
employees.department_id,
salary,
salary - a.avg_salary as salary_gap
from (select avg(salary) as avg_salary, department_id
from employees
group by department_id) a,
employees
where employees.department_id = a.department_id
order by employees.department_id

/*
4. 周几录取的人数最少，显示人名和日期
*/
select employee_id, first_name, last_name, dd
from employees,
(select *
from (select to_char(hire_date, ‘Day’) as dd, count(*) as counts
from employees
group by to_char(hire_date, ‘Day’)
order by counts)
where rownum = 1) a
where to_char(hire_date, ‘Day’) = a.dd

/*
5.自己做练习，验证 between .. and 的外链接。
*/
select department_name, departments.department_id, first_name
from departments, employees
where departments.manager_id = employees.manager_id(+)
and departments.department_id between 10 and 100;

/*
7. 查询所有 hr 用户下的索引
*/
SELECT index_name ,index_type ,owner FROM dba_indexes WHERE owner=’HR’;