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 deptwhere emp.department_id = dept.department_idgroup by dept.DEPARTMENT_NAMEorder by dept.DEPARTMENT_NAME asc;--4. 列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。  select a.department_id,a.salary ,count(*)from employees a,employees bwhere a.department_id = b.department_id and a.salary = b.salary and a.employee_id <> b.employee_idgroup 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,salaryfrom employeeswhere (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;