/*--------5、列出同部门中工资高于1000 的员工数量超过2 人的部门，
显示部门名字、地区名称。---------*/
SQL> SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*)
2 FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L
3 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
4 D.LOCATION_ID = L.LOCATION_ID AND
5 E.SALARY > 1000
6 GROUP BY D.DEPARTMENT_NAME,L.CITY
7 HAVING COUNT(*) > 2;
DEPARTMENT_NAME CITY COUNT(*)
------------------------------ ------------------------------ ----------
IT Southlake 5
Sales Oxford 34
Finance Seattle 6
Shipping South San Francisco 45
Executive Seattle 3
Purchasing Seattle 6
6 rows selected
显示部门名字、地区名称。---------*/
SQL> SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*)
2 FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L
3 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
4 D.LOCATION_ID = L.LOCATION_ID AND
5 E.SALARY > 1000
6 GROUP BY D.DEPARTMENT_NAME,L.CITY
7 HAVING COUNT(*) > 2;
DEPARTMENT_NAME CITY COUNT(*)
------------------------------ ------------------------------ ----------
IT Southlake 5
Sales Oxford 34
Finance Seattle 6
Shipping South San Francisco 45
Executive Seattle 3
Purchasing Seattle 6
6 rows selected
/*--------6、哪些员工的工资，高于整个公司的平均工资，
列出员工的名字和工资（降序）。---------*/
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME,SALARY
2 FROM EMPLOYEES
3 WHERE SALARY > (
4 SELECT AVG(SALARY)
5 FROM EMPLOYEES
6 )
7 ORDER BY SALARY DESC;
FIRST_NAME||''||LAST_NAME SALARY
---------------------------------------------- ----------
Steven King 24000.00
Neena Kochhar 20000.00
Lex De Haan 20000.00
John Russell 14000.00
Karen Partners 13500.00
Michael Hartstein 13000.00
Nancy Greenberg 12000.00
Alberto Errazuriz 12000.00
Shelley Higgins 12000.00
Lisa Ozer 11500.00
Den Raphaely 11000.00
Gerald Cambrault 11000.00
Ellen Abel 11000.00
Eleni Zlotkey 10500.00
Clara Vishney 10500.00
Peter Tucker 10000.00
Janette King 10000.00
Harrison Bloom 10000.00
Hermann Baer 10000.00
Tayler Fox 9600.00
--共50条数据
/*--------7、哪些员工的工资，介于50号 和80号 部门平均工资之间。---------*/
SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY
2 FROM EMPLOYEES
3 WHERE SALARY
4 BETWEEN
5 (SELECT AVG(SALARY) FROM EMPLOYEES
6 WHERE DEPARTMENT_ID = 50)
7 AND (SELECT AVG(SALARY) FROM EMPLOYEES
8 WHERE DEPARTMENT_ID = 80);
NAME SALARY
---------------------------------------------- ----------
Bruce Ernst 6000.00
David Austin 4800.00
Valli Pataballa 4800.00
Diana Lorentz 4200.00
John Chen 8200.00
Ismael Sciarra 7700.00
Jose Manuel Urman 7800.00
Luis Popp 6900.00
Matthew Weiss 8000.00
Adam Fripp 8200.00
Payam Kaufling 7900.00
Shanta Vollman 6500.00
Kevin Mourgos 5800.00
Renske Ladwig 3600.00
Trenna Rajs 3500.00
Christopher Olsen 8000.00
Nanette Cambrault 7500.00
Oliver Tuvault 7000.00
Lindsey Smith 8000.00
Louise Doran 7500.00
Sarath Sewall 7000.00
Mattea Marvins 7200.00
David Lee 6800.00
Sundar Ande 6400.00
Amit Banda 6200.00
William Smith 7400.00
Elizabeth Bates 7300.00
Sundita Kumar 6100.00
Alyssa Hutton 8800.00
Jonathon Taylor 8600.00
Jack Livingston 8000.00
Kimberely Grant 7000.00
Charles Johnson 7211.00
Nandita Sarchand 4200.00
Alexis Bull 4100.00
Kelly Chung 3800.00
Jennifer Dilly 3600.00
Sarah Bell 4000.00
Britney Everett 3900.00
Jennifer Whalen 4400.00
Pat Fay 6000.00
Susan Mavris 6500.00
William Gietz 8300.00
43 rows selected
