Oracle经典查询练手第三篇

--1. 各个部门平均、最大、最小工资、人数，按照部门号升序排列。 
select avg(salary),max(salary),min(salary),count(*) from employees group by department_id order by department_id;

--2. 各个部门中工资大于5000的员工人数。  
select count(*) from employees where salary >5000 group by department_id ;

--3. 各个部门平均工资和人数，按照部门名字升序排列。  
select dept.DEPARTMENT_NAME,avg(emp.salary),count(*)
from EMPLOYEES  emp,DEPARTMENTS dept
where emp.department_id = dept.department_id
group by dept.DEPARTMENT_NAME
order by dept.DEPARTMENT_NAME asc;

--4. 列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。  
select a.department_id,a.salary ,count(*)
from employees a,employees b
where a.department_id = b.department_id and a.salary = b.salary and a.employee_id <> b.employee_id
group by a.department_id,a.salary;    

--5. 列出同部门中工资高于1000 的员工数量超过2 人的部门，显示部门名字、地区名称。  
SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*)  
FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L  
WHERE  E.DEPARTMENT_ID = D.DEPARTMENT_ID AND 
       D.LOCATION_ID   = L.LOCATION_ID    AND 
       E.SALARY > 1000  
GROUP BY D.DEPARTMENT_NAME,L.CITY  
HAVING COUNT(*) > 2;  

--6. 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）。 
SELECT FIRST_NAME || ' ' || LAST_NAME,SALARY  
FROM EMPLOYEES  
WHERE SALARY > (  
        SELECT AVG(SALARY)  
        FROM EMPLOYEES  
        )  
ORDER BY SALARY DESC; 

--7. 哪些员工的工资，介于50号 和80号部门平均工资之间。 
SELECT FIRST_NAME || ' ' || LAST_NAME AS 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); 
      
--8. 所在部门平均工资高于5000 的员工名字。  
SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY  
FROM EMPLOYEES  
WHERE DEPARTMENT_ID IN 
        (SELECT DEPARTMENT_ID FROM EMPLOYEES  
         GROUP BY DEPARTMENT_ID  
         HAVING AVG(SALARY) > 5000);  

--9. 列出各个部门中工资最高的员工的信息：名字、部门号、工资。  
select FIRST_NAME || ' ' || LAST_NAME，department_id,salary
from employees
where (department_id,salary )in
 (
  select department_id ,max(salary) from employees
  group by department_id
 );

--10. 最高的部门平均工资是多少。 
select max(avg(salary)) from employees group by department_id;