oracle数据库：基本查询、层次查询和level伪列查询

1、查询每个部门、职位的总工资，以及每个部门的总工资，整个公司的总工资。

select department_id,job_id,sum(salary)from employeesgroup by rollup(department_id,job_id);

2、查询每个部门、职位的总工资，以及每个部门的总工资，整个公司的总工资。

select department_id,job_id,sum(salary)from employeesgroup by cube(department_id,job_id);

3、使用grouping函数，在合适的地方写上部门小计，职位小计和总计。

selectcase grouping(department_id)when 1 then \'all department\'else to_char(department_id)end as department,case grouping(job_id)when 1 then \'all jobs\'else job_idend as job,sum(salary)from employeesgroup by rollup(department_id,job_id)order by department_id,job_id;

4、使用层次查询和level伪列查询公司管理层次图。

select level,employee_id,manager_id,last_name,job_id from employeesstart with job_id = (select job_id from employees where employee_id=108)connect by prior manager_id=employee_id;

5、使用层次查询和level伪列查询某个领导的直接和间接员工。

select level,employee_id,manager_id,last_name,job_id from employeesstart with job_id=(select job_id from employees where employee_id=108)connect by prior employee_id=manager_id;