Oracle SQL：经典查询练手第四篇
复杂业务 有点难度 目标熟能生巧 信手拈来
使用Oracle自带的hr用户
表结构:
- COUNTRIES
- DEPARTMENTS
- EMPLOYEES
- JOB_HISTORY
- JOBS
- LOCATIONS
- REGION
表间关系
- EMPLOYEES的DEPARTMENT_ID和DEPARTMENTS的DEPARTMENT_ID
- DEPARTMENTS的LOCATION_ID和LOCATIONS的LOCATION_ID
问题:
- 1. 各个部门平均、最大、最小工资、人数，按照部门号升序排列。
- 2. 各个部门中工资大于5000的员工人数。
- 3. 各个部门平均工资和人数，按照部门名字升序排列。
- 4. 列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。
- 5. 列出同部门中工资高于1000 的员工数量超过2 人的部门，显示部门名字、地区名称。
- 6. 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）。
- 7. 哪些员工的工资，介于50号 和80号部门平均工资之间。
- 8. 所在部门平均工资高于5000 的员工名字。
- 9. 列出各个部门中工资最高的员工的信息：名字、部门号、工资。
- 10. 最高的部门平均工资是多少。
我的解答:
1 --1. 各个部门平均、最大、最小工资、人数，按照部门号升序排列。
2 -- 既然是各个部门 分组即可 分组后使用聚合函数
3 SELECT ROUND(AVG(e.SALARY)) 平均工资,MAX(e.SALARY) 最大工资,MIN(e.SALARY) 最小工资,count(1) 人数 FROM EMPLOYEES e GROUP BY e.DEPARTMENT_ID ORDER BY e.DEPARTMENT_ID ASC;
--2. 各个部门中工资大于5000的员工人数。
-- 先过滤掉工资大于5000的
-- 再分组
SELECT e.DEPARTMENT_ID 部门编号,count(1) 人数 FROM EMPLOYEES e WHERE e.SALARY>5000 GROUP BY e.DEPARTMENT_ID;
1 --3. 各个部门平均工资和人数，按照部门名字升序排列。
2 -- 要显示部门名称 需要employees和departments做关联
3 -- 我根据部门编号分组 在查询列中做了一个子查询
4 --(SELECT d.DEPARTMENT_NAME FROM DEPARTMENTS d WHERE d.DEPARTMENT_ID=e.DEPARTMENT_ID)
5
6 SELECT count(1) cnt,AVG(e.SALARY) agv,(SELECT d.DEPARTMENT_NAME FROM DEPARTMENTS d WHERE d.DEPARTMENT_ID=e.DEPARTMENT_ID) department_name FROM EMPLOYEES e GROUP BY e.DEPARTMENT_ID;
7
8 -- 结果还要按照部门名字升序排列 所有把上面的查询作为查询源表 继续操作
9 SELECT s.cnt,s.agv,s.department_name FROM(SELECT count(1) cnt,AVG(e.SALARY) agv,(SELECT d.DEPARTMENT_NAME FROM DEPARTMENTS d WHERE d.DEPARTMENT_ID=e.DEPARTMENT_ID) department_name FROM EMPLOYEES e GROUP BY e.DEPARTMENT_ID) s
10 ORDER BY s.department_name;
1 --4. 列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数。 2 -- 没啥思路 既然是每个部门 按照department_id分组 过滤条件工资相同 部门相同 外加雇员编号不能一样 这个千万不能忘记 不然就不是同部门 同工资的员工 没有把自己给过滤掉 3 -- 两表自关联 4 SELECT e.DEPARTMENT_ID,COUNT(1),e.SALARY FROM EMPLOYEES e,EMPLOYEES ee WHERE e.SALARY=ee.SALARY AND e.DEPARTMENT_ID=ee.DEPARTMENT_ID AND e.EMPLOYEE_ID<>ee.EMPLOYEE_ID GROUP BY e.DEPARTMENT_ID, e.SALARY ORDER BY e.DEPARTMENT_ID;
DEPARTMENT_ID COUNT(1) SALARY
------------- ---------- ----------
50 2 2200
50 2 2400
50 20 2500
50 6 2600
50 2 2700
50 6 2800
50 2 2900
50 2 3000
50 6 3100
50 12 3200
50 2 3300
50 2 3600
60 2 4800
80 2 6200
80 2 7000
80 2 7500
80 2 8000
80 2 9000
80 6 9500
80 6 10000
80 2 10500
80 2 11000
90 2 17000
选定了 23 行
1 --5. 列出同部门中工资高于1000 的员工数量超过2 人的部门，显示部门名字、地区名称。 2 -- 工资大于1000 count(1)>2 3 -- 部门名称 地区 需要 departments和locations表关联 4 --先把employees departments locations三表关联起来 5 -- 分组使用department_id和city 6 -- having 过滤分组后的结果 7 SELECT d.DEPARTMENT_NAME,l.CITY,count(1) 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(1)>2;
DEPARTMENT_NAME CITY COUNT(1)
------------------------------ ------------------------------ ----------
IT Southlake 5
Sales Oxford 34
Shipping South San Francisco 45
Purchasing Seattle 6
Executive Seattle 3
Finance Seattle 6
选定了 6 行
1 --6. 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序） 2 -- 算出平均工资 3 -- 公司平均工资 4 SELECT AVG(e.SALARY) FROM EMPLOYEES e; 5 -- 高于 6 SELECT ee.FIRST_NAME||' '||ee.LAST_NAME,ee.SALARY FROM EMPLOYEES ee WHERE ee.SALARY >(SELECT AVG(e.SALARY) FROM EMPLOYEES e) ORDER BY ee.SALARY desc;
EE.FIRST_NAME||''||EE.LAST_NAME SALARY
---------------------------------------------- ----------
Steven King 24000
Neena Kochhar 17000
Lex De Haan 17000
John Russell 14000
Karen Partners 13500
Michael Hartstein 13000
Shelley Higgins 12008
Nancy Greenberg 12008
Alberto Errazuriz 12000
Lisa Ozer 11500
Gerald Cambrault 11000
Ellen Abel 11000
Den Raphaely 11000
Clara Vishney 10500
Eleni Zlotkey 10500
Peter Tucker 10000
Janette King 10000
Hermann Baer 10000
Harrison Bloom 10000
Tayler Fox 9600
Danielle Greene 9500
Patrick Sully 9500
David Bernstein 9500
Allan McEwen 9000
Daniel Faviet 9000
Alexander Hunold 9000
Peter Hall 9000
Alyssa Hutton 8800
Jonathon Taylor 8600
Jack Livingston 8400
William Gietz 8300
John Chen 8200
Adam Fripp 8200
Christopher Olsen 8000
Matthew Weiss 8000
Lindsey Smith 8000
Payam Kaufling 7900
Jose Manuel Urman 7800
Ismael Sciarra 7700
Nanette Cambrault 7500
Louise Doran 7500
William Smith 7400
Elizabeth Bates 7300
Mattea Marvins 7200
Sarath Sewall 7000
Oliver Tuvault 7000
Kimberely Grant 7000
Luis Popp 6900
David Lee 6800
Susan Mavris 6500
Shanta Vollman 6500
选定了 51 行
1 --7. 哪些员工的工资，介于50号 和80号部门平均工资之间 2 -- 50部门平均工资 3 SELECT AVG(e.SALARY) min FROM EMPLOYEES e WHERE e.DEPARTMENT_ID=50; 4 --80部门平均工资 5 SELECT AVG(e.SALARY) max FROM EMPLOYEES e WHERE e.DEPARTMENT_ID=80; 6 7 -- 使用between and 8 SELECT ee.FIRST_NAME||' '||ee.LAST_NAME,ee.SALARY FROM EMPLOYEES ee WHERE ee.SALARY BETWEEN (SELECT AVG(e.SALARY) min FROM EMPLOYEES e WHERE e.DEPARTMENT_ID=50) AND (SELECT AVG(e.SALARY) max FROM EMPLOYEES e WHERE e.DEPARTMENT_ID=80);
EE.FIRST_NAME||''||EE.LAST_NAME SALARY
---------------------------------------------- ----------
Jennifer Whalen 4400
Pat Fay 6000
Susan Mavris 6500
William Gietz 8300
Bruce Ernst 6000
David Austin 4800
Valli Pataballa 4800
Diana Lorentz 4200
John Chen 8200
Ismael Sciarra 7700
Jose Manuel Urman 7800
Luis Popp 6900
Matthew Weiss 8000
Adam Fripp 8200
Payam Kaufling 7900
Shanta Vollman 6500
Kevin Mourgos 5800
Renske Ladwig 3600
Trenna Rajs 3500
Christopher Olsen 8000
Nanette Cambrault 7500
Oliver Tuvault 7000
Lindsey Smith 8000
Louise Doran 7500
Sarath Sewall 7000
Mattea Marvins 7200
David Lee 6800
Sundar Ande 6400
Amit Banda 6200
William Smith 7400
Elizabeth Bates 7300
Sundita Kumar 6100
Alyssa Hutton 8800
Jonathon Taylor 8600
Jack Livingston 8400
Kimberely Grant 7000
Charles Johnson 6200
Nandita Sarchand 4200
Alexis Bull 4100
Kelly Chung 3800
Jennifer Dilly 3600
Sarah Bell 4000
Britney Everett 3900
选定了 43 行
1 --8. 所在部门平均工资高于5000 的员工名字 2 -- 先算出平均工资高于5000的部门 3 SELECT AVG(e.SALARY),e.DEPARTMENT_ID FROM EMPLOYEES e GROUP BY e.DEPARTMENT_ID HAVING AVG(e.SALARY)>5000; 4 -- 再跟employees做关联 算出对应部门的员工 5 -- 注意 不要再过滤工资大于5000 因为部门平均工资大于5000 不表示员工的工资大于5000 也可以小于5000 6 SELECT ee.FIRST_NAME||' '||ee.LAST_NAME,ee.SALARY FROM EMPLOYEES ee,(SELECT AVG(e.SALARY) agv,e.DEPARTMENT_ID FROM EMPLOYEES e GROUP BY e.DEPARTMENT_ID HAVING AVG(e.SALARY)>5000) v 7 WHERE ee.DEPARTMENT_ID=v.DEPARTMENT_ID;
EE.FIRST_NAME||''||EE.LAST_NAME SALARY
---------------------------------------------- ----------
Nancy Greenberg 12008
Daniel Faviet 9000
John Chen 8200
Ismael Sciarra 7700
Jose Manuel Urman 7800
Luis Popp 6900
Michael Hartstein 13000
Pat Fay 6000
Hermann Baer 10000
Steven King 24000
Neena Kochhar 17000
Lex De Haan 17000
Shelley Higgins 12008
William Gietz 8300
Susan Mavris 6500
John Russell 14000
Karen Partners 13500
Alberto Errazuriz 12000
Gerald Cambrault 11000
Eleni Zlotkey 10500
Peter Tucker 10000
David Bernstein 9500
Peter Hall 9000
Christopher Olsen 8000
Nanette Cambrault 7500
Oliver Tuvault 7000
Janette King 10000
Patrick Sully 9500
Allan McEwen 9000
Lindsey Smith 8000
Louise Doran 7500
Sarath Sewall 7000
Clara Vishney 10500
Danielle Greene 9500
Mattea Marvins 7200
David Lee 6800
Sundar Ande 6400
Amit Banda 6200
Lisa Ozer 11500
Harrison Bloom 10000
Tayler Fox 9600
William Smith 7400
Elizabeth Bates 7300
Sundita Kumar 6100
Ellen Abel 11000
Alyssa Hutton 8800
Jonathon Taylor 8600
Jack Livingston 8400
Charles Johnson 6200
Alexander Hunold 9000
Bruce Ernst 6000
David Austin 4800
Valli Pataballa 4800
Diana Lorentz 4200
选定了 54 行
1 --9. 列出各个部门中工资最高的员工的信息：名字、部门号、工资 2 -- 先找出部门最高工资 部门号 3 SELECT MAX(e.SALARY) max_money,e.DEPARTMENT_ID FROM EMPLOYEES e GROUP BY e.DEPARTMENT_ID; 4 -- 作为子表和员工表做关联 5 SELECT ee.FIRST_NAME||' '||ee.LAST_NAME,ee.DEPARTMENT_ID,ee.SALARY FROM EMPLOYEES ee,(SELECT MAX(e.SALARY) max_money,e.DEPARTMENT_ID FROM EMPLOYEES e GROUP BY e.DEPARTMENT_ID) e WHERE ee.SALARY=e.max_money AND ee.DEPARTMENT_ID=e.DEPARTMENT_ID; 6 -- 或者使用in 工资和部门在... 这种思路是我还不知道的 7 SELECT ee.FIRST_NAME||' '||ee.LAST_NAME,ee.DEPARTMENT_ID,ee.SALARY FROM EMPLOYEES ee WHERE (ee.SALARY,ee.DEPARTMENT_ID) IN (SELECT MAX(e.SALARY) max_money,e.DEPARTMENT_ID FROM EMPLOYEES e GROUP BY e.DEPARTMENT_ID);
EE.FIRST_NAME||''||EE.LAST_NAME DEPARTMENT_ID SALARY
---------------------------------------------- ------------- ----------
Nancy Greenberg 100 12008
Den Raphaely 30 11000
Michael Hartstein 20 13000
Hermann Baer 70 10000
Steven King 90 24000
Shelley Higgins 110 12008
Adam Fripp 50 8200
Susan Mavris 40 6500
John Russell 80 14000
Jennifer Whalen 10 4400
Alexander Hunold 60 9000
选定了 11 行
1 --10. 最高的部门平均工资是多少。
2 -- 按部门分组 就平均工资
3 -- 求完了 直接使用max
4 SELECT max(AVG(e.SALARY)) FROM EMPLOYEES e GROUP BY e.DEPARTMENT_ID;
5 --或者做一个子查询
6 SELECT MAX(s.avgsalary) FROM ( SELECT AVG(e.SALARY) avgsalary FROM EMPLOYEES e GROUP BY e.DEPARTMENT_ID) s
总结:没有复杂的查询 也没有独特的技巧 需要理解一些概念 比如4 同样工资的员工 要过滤掉相同employee_id
