ORACLE_SQL 练手 part4

更多参考和数据脚本下载：

1.万事俱备之ORACLE_SQL 练手 part1

--用到的表
select * from regions;
select * from countries;
select * from locations;
select * from departments;
select * from jobs;

update countries set country_name = 'Others' where country_id='ZZ';

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

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

--3. 各个部门平均工资和人数，按照部门名字升序排列。
select department_name,empp.department_id, round(avg(salary)) avgsal,count(*) nums
from employees empp left join departments d
on empp.department_id = d.department_id
group by empp.department_id,department_name
order by department_name;

---一种答案，用where连接
SELECT DPTNAME,AVG(SALARY),COUNT(*) FROM
           (SELECT
               (SELECT DEPT.DEPARTMENT_NAME FROM DEPARTMENTS DEPT
               WHERE DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME,
               EMP.SALARY
    FROM EMPLOYEES EMP)
    GROUP BY DPTNAME
    ORDER BY DPTNAME;

--4. 列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。
---where
select a.department_id, a.salary ,count(*) nums_same 
from employees a , employees b
where a.salary =b.salary
and a.employee_id!=b.employee_id
and a.department_id=b.department_id
group by a.department_id,a.salary;
---join（默认inner join） 因为表相同,所以取都有的那部分
select a.department_id, a.salary ,count(*) nums_same 
from employees a   join employees b
on a.salary =b.salary
and a.employee_id!=b.employee_id
and a.department_id=b.department_id
group by a.department_id,a.salary;



--5. 列出同部门中工资高于1000 的员工数量超过2 人的部门，显示部门名字、地区名称。
---两表显连接+子查询
select d.department_name,l.city
from departments d left join locations l
on d.location_id=l.location_id
where d.department_id in
(select department_id from  
employees 
where salary>1000
group by department_id
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  first_name||' '||last_name ename, salary  from employees
where salary>(select round(avg(salary)) from employees) 
order by salary desc;

--7. 哪些员工的工资，介于50 号和80 号部门平均工资之间。
select first_name||' '||last_name ename, salary  from employees
where salary between 
(select round(avg(salary)) from employees where department_id=50) 
and (select round(avg(salary)) from employees where department_id=80);

--8. 所在部门平均工资高于5000 的员工名字。
select first_name||' '||last_name ename,department_id，salary from employees
where department_id in
(select department_id from employees group by department_id having  avg(salary)>5000 );

--9. 列出各个部门中工资最高的员工的信息：名字、部门号、工资。
---报错~~ not properly ended
select employee_id from employees
group by department_id,employee_id
where salary =(select  max(salary) from employees);

---报错~~ not a group expression ended
select employee_id from employees
group by department_id,employee_id
having salary =(select  max(salary) from employees);

--- good! but not enough only employee_id
select DEPARTMENT_ID,employee_id,FIRST_NAME || ' ' || LAST_NAME ename from employees
group by department_id,employee_id,salary
having (salary,department_Id) in(select  max(salary),department_Id from employees group by department_id );

--答案 （X,Y） IN (select x,y from z...)
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. 最高的部门平均工资是多少。
---max((avg))
select max(avsal)maxsal from 
(select round(avg(salary)) avsal,department_id from employees 
group by department_id);

---想多拿个部门ID,搞不定-  - 窘 - -
select department_id,max(avsal) from
(select round(avg(salary)) avsal,department_id from employees 
group by department_id );

--多拿个部门ID (avg order) rownum
select  * from 
(select  round(avg(salary)) avsal,department_id  from employees 
group by department_id
order by avsal desc) 
where rownum =1;