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