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'