/*--------8、所在部门平均工资高于5000 的员工名字。---------*/
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY
2 FROM EMPLOYEES
3 WHERE DEPARTMENT_ID IN
4 (SELECT DEPARTMENT_ID FROM EMPLOYEES
5 GROUP BY DEPARTMENT_ID
6 HAVING AVG(SALARY) > 5000);
NAME SALARY
---------------------------------------------- ----------
Michael Hartstein 13000.00
Pat Fay 6000.00
Susan Mavris 6500.00
Alexander Hunold 9000.00
Bruce Ernst 6000.00
David Austin 4800.00
Valli Pataballa 4800.00
Diana Lorentz 4200.00
Hermann Baer 10000.00
John Russell 14000.00
Karen Partners 13500.00
Alberto Errazuriz 12000.00
Gerald Cambrault 11000.00
Eleni Zlotkey 10500.00
Peter Tucker 10000.00
David Bernstein 9500.00
Peter Hall 9000.00
Christopher Olsen 8000.00
Nanette Cambrault 7500.00
Oliver Tuvault 7000.00
--等54行数据…
/*--------9、列出各个部门中工资最高的员工的信息：名字、部门号、工资。---------*/
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME
2 ,SALARY,DEPARTMENT_ID
3 FROM EMPLOYEES
4 WHERE (DEPARTMENT_ID,SALARY) IN
5 (SELECT DEPARTMENT_ID,MAX(SALARY)
6 FROM EMPLOYEES
7 GROUP BY DEPARTMENT_ID);
NAME SALARY DEPARTMENT_ID
---------------------------------------------- ---------- -------------
Jennifer Whalen 4400.00 10
Michael Hartstein 13000.00 20
Den Raphaely 11000.00 30
Susan Mavris 6500.00 40
Adam Fripp 8200.00 50
Alexander Hunold 9000.00 60
Hermann Baer 10000.00 70
John Russell 14000.00 80
Steven King 24000.00 90
Nancy Greenberg 12000.00 100
Shelley Higgins 12000.00 110
11 rows selected
/*--------10、最高的部门平均工资是多少。---------*/
SQL> SELECT MAX(AVGSALARY)
2 FROM(SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY
3 FROM EMPLOYEES
4 GROUP BY DEPARTMENT_ID);
MAX(AVGSALARY)
--------------
21333.33333333
