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>1000groupby department_name,city havingcount(*) >2;

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

            select  first_name ||‘‘|| last_name ,salary ,department_id
from employees 
where salary=(selectmax(salary) from employees);

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

            select  first_name ||‘‘|| last_name  name  ,salary ,department_id
 from  employees 
 where (salary ,department_id)
        in ( 
        selectmax(salary),department_id from  employees groupby department_id);

            select employee_id,first_name||‘‘||last_name name,salary,department_id, 
(selectavg(salary) from employees emp  where emp.department_id=e.department_id ) avgsal
from employees e
where salary>(selectavg(salary) from employees emp  where emp.department_id=e.department_id )
orderby department_id,employee_id;

            SELECT department_id , avgsal from 

      (SELECT ROWNUM no, department_id , avgsal 

         from ( select department_id ,avg(salary) avgsal from employees 
  groupby  department_id  orderbyavg(salary))

       WHERE ROWNUM <=2)where no=2;

            select employee_id,first_name||‘‘||last_name name,salary,department_id, 
(selectavg(salary) from employees emp  where emp.department_id=e.department_id ) avgsal
from employees e
where salary>(selectavg(salary) from employees emp  where emp.department_id=e.department_id )
orderby department_id,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   orderbyleveldesc;

            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 orderby salary desc) salrank from employees)
where salrank<=3orderby department_id desc,salrank;