Oracle数据库的上机作业

select  department_name,city,count(*)
from departments d, employees e, locations l
where e.department_id=d.department_id 
and l.location_id=d.location_id 
and e.salary>1000
group by department_name,city having count(*) > 2;

 select first_name||' '||last_name name,salary
from employees
where salary between(select avg(salary)
from employees  
where department_id=50)and(select avg(salary) from employees where department_id=80);

select  first_name ||''|| last_name ,salary ,department_id
from employees 
where salary in (select max(salary) from employees);
/*或者salary>=all(select salary from employees)

selectemployee_id,first_name,last_name,salary,commission_pct,salary*(1+NVL(commission_pct,0)) totalincome
from employees 
order by totalincome;

select  first_name ||''|| last_name  name  ,salary ,department_id
 from  employees 
 where (salary ,department_id)
        in ( 
        select max(salary),department_id from  employees group by department_id);

本题只需要对部门号进行分组，找出每个部门的平均工资，但在select语句中要同时输出人的名字，所以要在where 条件中进行分组查找。

select employee_id,first_name||' '||last_name name,salary,department_id, 
(select avg(salary) from employees emp  where emp.department_id=e.department_id ) avgsal
from employees e
where salary>(select avg(salary) from employees emp  where emp.department_id=e.department_id )
order by department_id,employee_id;

SELECT department_id , avgsal from 

      (SELECT ROWNUM no, department_id , avgsal 

         from ( select department_id ,avg(salary) avgsal from employees 
  group by  department_id  order by avg(salary))

       WHERE ROWNUM <= 2)where no=2;

select level position,employee_id,first_name,last_name,manager_id
from employees
where level!=1
start with employee_id=101
connect by prior employee_id=manager_id
order by level,employee_id;

select level ,employee_id ,first_name, last_name ,manager_id
from employees 
start with first_name='David' and last_name='Austin'
connect by prior manager_id=employee_id   order  by level desc;

select department_id,employee_id,first_name||' '||last_name,salary,salrank
from (select department_id,employee_id,first_name,last_name,salary,
dense_rank() over (partition by department_id order by salary desc) salrank from employees)
where salrank<=3
order by department_id desc,salrank;