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,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,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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!