Oracle查询部门平均工资等资讯的练习讲解

显示部门编号、部门名字、该部门的员工数、每个部门的平均工资，部门负责人信息，包括姓名、薪水、职业;平均工资保留2位小数，千分位分隔符显示;结果按部门升序

select d.department_id, d.department_name,

count(e1.employee_id) employees,

nvl(to_char(avg(e1.salary), '99,999,999.99'),

'no average' ) avg_sal,

e2.last_name, e2.salary, e2.job_id

from departments d, employees e1, employees e2

where d.department_id = e1.department_id(+)

and d.department_id = e2.department_id(+)

group by d.department_id, d.department_name,

e2.last_name, e2.salary, e2.job_id

order by d.department_id, employees

显示员工数最多的部门信息，显示部门id、名称、部门员工数，部门的主管经理姓名

select d.department_id,d.department_name,count(*),m.first_name||m.last_name manager_name

from departments d,employees e,employees m

where d.department_id = e.department_id(+)

and d.manager_id = m.manager_id(+)

group by d.department_id, d.department_name,m.first_name||m.last_name

having count(*) = (select max(count(*))

from employees

group by department_id)

显示工号、姓名、薪水、部门编号、薪资，薪资与部门平均工资的差异情况;按照部门id排序

select e.employee_id, e.last_name,

e.department_id,e.salary, (e.salary-avg(s.salary)) salary_avg

from employees e, employees s

where e.department_id = s.department_id

group by e.employee_id, e.last_name, e.department_id,e.salary

order by department_id

周几录取的人数最少，显示人名和日期

select employee_id,first_name,last_name, to_char(hire_date, 'day') day

from employees

where to_char(hire_date, 'day') =

(select to_char(hire_date, 'day')

from employees

group by to_char(hire_date, 'day')

having count(*) = (select min(count(*))

from employees

group by to_char(hire_date, 'day')))

自己做练习，验证 between .. and 的外链接 select job_id

from employees

where to_char(hire_date,'yyyy-mm-dd')

between '1990-01-01' and '1990-01-31'

intersect

select job_id

from employees

where to_char(hire_date,'yyyy-mm-dd')

between '1991-01-01'and '1991-01-31' 验证rollback ;并提供例子

create table testtab4

(pk1 number, field1 varchar2(200));

insert into testtab4 values(1,'aaa');

commit;

select * from testtab4;

delete from testtab4;

查询所有hr用户下的索引 select index_name from all_indexes where owner = 'hr'