SQL练习(四)

--1. 各个部门平均、最大、最小工资、人数，按照部门号升序排列。 
select department_id,round(avg(salary),2),max(salary),min(salary),count(employee_id) from employees group by department_id order by department_id asc; 
--2. 各个部门中工资大于5000的员工人数。  
select department_id,count(employee_id) from employees where salary > 5000 group by department_id;
--3. 各个部门平均工资和人数，按照部门名字升序排列。  
select e.department_id,round(avg(e.salary)),count(e.employee_id) from employees e group by e.department_id order by (select d1.department_name from departments d1 where d1.department_id = e.department_id) asc;
--4. 列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。  
select e.department_id,e.salary,count(e.employee_id) from employees e,employees e1 where e.salary=e1.salary and e.department_id = e1.department_id and e.employee_id<>e1.employee_id group by e.department_id,e.salary;
SELECT EMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT(*) CNT FROM   EMPLOYEES EMP1,EMPLOYEES EMP2 
WHERE  EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND EMP1.SALARY = EMP2.SALARY AND EMP1.EMPLOYEE_ID <> EMP2.EMPLOYEE_ID  
GROUP BY EMP1.DEPARTMENT_ID,EMP1.SALARY;
--5. 列出同部门中工资高于1000 的员工数量超过2 人的部门，显示部门名字、地区名称。
select * from employees;
select * from regions;  
select * from departments;
select d.department_name,l.city 
from departments d ,employees e,locations l 
where d.department_id = e.department_id and d.location_id = l.location_id and e.salary > 1000 
group by d.department_name,l.city having count(*)>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 e.first_name,e.salary from employees e where e.salary >(select avg(salary) from employees) order by e.salary desc;
--7. 哪些员工的工资，介于50号 和80号部门平均工资之间。  
select * from employees e where e.salary between (select avg(salary) from employees where department_id=50) and (select avg(salary) from employees);
--8. 所在部门平均工资高于5000 的员工名字。 
select e.first_name from employees e group by e.department_id,e.first_name having avg(e.salary)> 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 e.first_name,e.department_id,e.salary from employees e where e.salary in (select max(salary) from employees group by department_id) group by e.department_id,e.first_name,e.salary;
SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY,DEPARTMENT_ID FROM EMPLOYEES  
WHERE (DEPARTMENT_ID,SALARY) IN (SELECT DEPARTMENT_ID,MAX(SALARY)  FROM EMPLOYEES GROUP BY DEPARTMENT_ID);  
--10. 最高的部门平均工资是多少
SELECT ROUND(MAX(AVGSALARY),2) FROM (SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY  FROM EMPLOYEES GROUP BY DEPARTMENT_ID);