Oracle SQL：经典查询练手第三篇(1)(4)
/* --------13、整个公司中，最高工资和最低工资相差多少。---------*/ SQL SELECT MAX (SALARY)- MIN (SALARY) FROM HR.EMPLOYEES; MAX (SALARY)- MIN (SALARY) ----------------------- 2190
：http://www.cnblogs.com/huyong/archive/2011/06/20/2085137.html
- /*--------13、整个公司中，最高工资和最低工资相差多少。---------*/
- SQL> SELECT MAX(SALARY) - MIN(SALARY) FROM HR.EMPLOYEES;
- MAX(SALARY)-MIN(SALARY)
- -----------------------
- 21900
- /*--------14、提成大于0 的人数。---------*/
- SQL> SELECT COUNT(*) AS 提成大小0的人数 FROM HR.EMPLOYEES
- 2 WHERE COMMISSION_PCT > 0;
- 提成大小0的人数
- ---------------
- 35
- --或者
- SQL> SELECT COUNT(COMMISSION_PCT) AS 提成大小0的人数
- 2 FROM HR.EMPLOYEES
- 3 WHERE COMMISSION_PCT > 0;
- 提成大小0的人数
- ---------------
- 35
- /*--------15、显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。---------*/
- SQL> SELECT MAX(NVL(SALARY,0)) AS 最高工资,
- 2 MIN(NVL(SALARY,0)) AS 最低工资,
- 3 SUM(NVL(SALARY,0)) AS 工资总和,
- 4 ROUND(AVG(NVL(SALARY,0))) AS 平均工资
- 5 FROM HR.EMPLOYEES;
- 最高工资 最低工资 工资总和 平均工资
- ---------- ---------- ---------- ---------
- 24000 2100 698011 6523
- /*--------16、整个公司有多少个领导。---------*/
- SQL> SELECT COUNT(DISTINCT(MANAGER_ID)) FROM HR.EMPLOYEES
- 2 WHERE MANAGER_ID IS NOT NULL;
- COUNT(DISTINCT(MANAGER_ID))
- ---------------------------
- 18
- /*--------17、列出在同一部门入职日期晚但工资高于其他同事的员工：
- 名字、工资、入职日期。---------*/
- SQL> SELECT DISTINCT E1.FIRST_NAME || ' ' || E1.LAST_NAME AS 姓名,
- 2 E1.SALARY AS 工资,E1.HIRE_DATE AS 入职日期
- 3 FROM HR.EMPLOYEES E1,HR.EMPLOYEES E2
- 4 WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
- 5 AND E1.HIRE_DATE > E2.HIRE_DATE
- 6 AND E1.SALARY > E2.SALARY
- 7 ORDER BY 工资 DESC;
- 姓名 工资 入职日期
- ---------------------------------------------- ---------- -----------
- John Russell 14000.00 1996-10-1
- Karen Partners 13500.00 1997-1-5
- Alberto Errazuriz 12000.00 1997-3-10
- Nancy Greenberg 12000.00 1994-8-17
- Lisa Ozer 11500.00 1997-3-11
- Ellen Abel 11000.00 1996-5-11
- Gerald Cambrault 11000.00 1999-10-15
- Clara Vishney 10500.00 1997-11-11
- Eleni Zlotkey 10500.00 2000-1-29
- Harrison Bloom 10000.00 1998-3-23
- Peter Tucker 10000.00 1997-1-30
- Tayler Fox 9600.00 1998-1-24
- Danielle Greene 9500.00 1999-3-19
- David Bernstein 9500.00 1997-3-24
- Peter Hall 9000.00 1997-8-20
- Alyssa Hutton 8800.00 1997-3-19
- Jonathon Taylor 8600.00 1998-3-24
- Adam Fripp 8200.00 1997-4-10
- Christopher Olsen 8000.00 1998-3-30
- Jack Livingston 8000.00 1998-4-23
- Matthew Weiss 8000.00 1996-7-18
- Jose Manuel Urman 7800.00 1998-3-7
- Nanette Cambrault 7500.00 1998-12-9
- William Smith 7400.00 1999-2-23
- Elizabeth Bates 7300.00 1999-3-24
- Charles Johnson 7211.00 2000-1-4
- Mattea Marvins 7200.00 2000-1-24
- Shanta Vollman 6500.00 1997-10-10
- Kevin Mourgos 5800.00 1999-11-16
- Nandita Sarchand 4200.00 1996-1-27
- Alexis Bull 4100.00 1997-2-20
- Sarah Bell 4000.00 1996-2-4
- Britney Everett 3900.00 1997-3-3
- Kelly Chung 3800.00 1997-6-14
- Jennifer Dilly 3600.00 1997-8-13
- Julia Dellinger 3400.00 1998-6-24
- Laura Bissot 3300.00 1997-8-20
- Julia Nayer 3200.00 1997-7-16
- Samuel McCain 3200.00 1998-7-1
- Stephen Stiles 3200.00 1997-10-26
- Winston Taylor 3200.00 1998-1-24
- Alana Walsh 3100.00 1998-4-24
- Jean Fleaur 3100.00 1998-2-23
- Anthony Cabrio 3000.00 1999-2-7
- Kevin Feeney 3000.00 1998-5-23
- Michael Rogers 2900.00 1998-8-26
- Shelli Baida 2900.00 1997-12-24
- Timothy Gates 2900.00 1998-7-11
- Girard Geoni 2800.00 2000-2-3
- Mozhe Atkinson 2800.00 1997-10-30
- Vance Jones 2800.00 1999-3-17
- Irene Mikkilineni 2700.00 1998-9-28
- John Seo 2700.00 1998-2-12
- Donald OConnell 2600.00 1999-6-21
- Douglas Grant 2600.00 2000-1-13
- Randall Matos 2600.00 1998-3-15
- Martha Sullivan 2500.00 1999-6-21
- Randall Perkins 2500.00 1999-12-19
- Ki Gee 2400.00 1999-12-12
- Hazel Philtanker 2200.00 2000-2-6
- Steven Markle 2200.00 2000-3-8
- 61 rows selected
精彩图集
精彩文章