欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-08 22:49:58
显示部门编号、部门名字、该部门的员工数、每个部门的平均工资,部门负责人信息,包括姓名、薪水、职业;平均工资保留2位小数,千分位分隔符显示;结果按部门升序 select d.department_id...

显示部门编号、部门名字、该部门的员工数、每个部门的平均工资,部门负责人信息,包括姓名、薪水、职业;平均工资保留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'