龙盟编程博客 | 无障碍搜索 | 云盘搜索神器
快速搜索
主页 > 数据库类 > Oracle 技术 >

Oracle SQL：经典查询练手第四篇(1)(3)

时间:2011-04-12 23:18来源:未知 作者:admin 点击: 次
分享到：
/* --------6、哪些员工的工资，高于整个公司的平均工资， 列出员工的名字和工资（降序）。 ---------*/ SQL SELECT FIRST_NAME|| '' ||LAST_NAME,SALARY 2 FROM EMPLOYEES 3 W

  1. /*--------6、哪些员工的工资，高于整个公司的平均工资，  
  2.   列出员工的名字和工资（降序）。---------*/  
  3.  
  4. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME,SALARY  
  5.    2  FROM EMPLOYEES  
  6.    3  WHERE SALARY > (  
  7.    4        SELECT AVG(SALARY)  
  8.    5        FROM EMPLOYEES  
  9.    6        )  
  10.    7  ORDER BY SALARY DESC;   
  11. FIRST_NAME||''||LAST_NAME                          SALARY  
  12. ---------------------------------------------- ----------  
  13. Steven King                                      24000.00  
  14. Neena Kochhar                                    20000.00  
  15. Lex De Haan                                      20000.00  
  16. John Russell                                     14000.00  
  17. Karen Partners                                   13500.00  
  18. Michael Hartstein                                13000.00  
  19. Nancy Greenberg                                  12000.00  
  20. Alberto Errazuriz                                12000.00  
  21. Shelley Higgins                                  12000.00  
  22. Lisa Ozer                                        11500.00  
  23. Den Raphaely                                     11000.00  
  24. Gerald Cambrault                                 11000.00  
  25. Ellen Abel                                       11000.00  
  26. Eleni Zlotkey                                    10500.00  
  27. Clara Vishney                                    10500.00  
  28. Peter Tucker                                     10000.00  
  29. Janette King                                     10000.00  
  30. Harrison Bloom                                   10000.00  
  31. Hermann Baer                                     10000.00  
  32. Tayler Fox                                        9600.00  
  33. --共50条数据  
  34.  
  35. /*--------7、哪些员工的工资，介于50号 和80号 部门平均工资之间。---------*/  
  36.  
  37. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY  
  38.   2  FROM EMPLOYEES  
  39.   3  WHERE SALARY  
  40.   4  BETWEEN 
  41.   5      (SELECT AVG(SALARY) FROM EMPLOYEES  
  42.   6       WHERE DEPARTMENT_ID = 50)  
  43.   7  AND (SELECT AVG(SALARY) FROM EMPLOYEES  
  44.   8       WHERE DEPARTMENT_ID = 80);  
  45.    
  46. NAME                                               SALARY  
  47. ---------------------------------------------- ----------  
  48. Bruce Ernst                                       6000.00  
  49. David Austin                                      4800.00  
  50. Valli Pataballa                                   4800.00  
  51. Diana Lorentz                                     4200.00  
  52. John Chen                                         8200.00  
  53. Ismael Sciarra                                    7700.00  
  54. Jose Manuel Urman                                 7800.00  
  55. Luis Popp                                         6900.00  
  56. Matthew Weiss                                     8000.00  
  57. Adam Fripp                                        8200.00  
  58. Payam Kaufling                                    7900.00  
  59. Shanta Vollman                                    6500.00  
  60. Kevin Mourgos                                     5800.00  
  61. Renske Ladwig                                     3600.00  
  62. Trenna Rajs                                       3500.00  
  63. Christopher Olsen                                 8000.00  
  64. Nanette Cambrault                                 7500.00  
  65. Oliver Tuvault                                    7000.00  
  66. Lindsey Smith                                     8000.00  
  67. Louise Doran                                      7500.00  
  68. Sarath Sewall                                     7000.00  
  69. Mattea Marvins                                    7200.00  
  70. David Lee                                         6800.00  
  71. Sundar Ande                                       6400.00  
  72. Amit Banda                                        6200.00  
  73. William Smith                                     7400.00  
  74. Elizabeth Bates                                   7300.00  
  75. Sundita Kumar                                     6100.00  
  76. Alyssa Hutton                                     8800.00  
  77. Jonathon Taylor                                   8600.00  
  78. Jack Livingston                                   8000.00  
  79. Kimberely Grant                                   7000.00  
  80. Charles Johnson                                   7211.00  
  81. Nandita Sarchand                                  4200.00  
  82. Alexis Bull                                       4100.00  
  83. Kelly Chung                                       3800.00  
  84. Jennifer Dilly                                    3600.00  
  85. Sarah Bell                                        4000.00  
  86. Britney Everett                                   3900.00  
  87. Jennifer Whalen                                   4400.00  
  88. Pat Fay                                           6000.00  
  89. Susan Mavris                                      6500.00  
  90. William Gietz                                     8300.00  
  91.    
  92. 43 rows selected  
  93.  
  94. /*--------8、所在部门平均工资高于5000 的员工名字。---------*/  
  95.  
  96. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY  
  97.    2  FROM EMPLOYEES  
  98.    3  WHERE DEPARTMENT_ID IN 
  99.    4        (SELECT DEPARTMENT_ID FROM EMPLOYEES  
  100.    5         GROUP BY DEPARTMENT_ID  
  101.    6         HAVING AVG(SALARY) > 5000);  
  102.    
  103. NAME                                               SALARY  
  104. ---------------------------------------------- ----------  
  105. Michael Hartstein                                13000.00  
  106. Pat Fay                                           6000.00  
  107. Susan Mavris                                      6500.00  
  108. Alexander Hunold                                  9000.00  
  109. Bruce Ernst                                       6000.00  
  110. David Austin                                      4800.00  
  111. Valli Pataballa                                   4800.00  
  112. Diana Lorentz                                     4200.00  
  113. Hermann Baer                                     10000.00  
  114. John Russell                                     14000.00  
  115. Karen Partners                                   13500.00  
  116. Alberto Errazuriz                                12000.00  
  117. Gerald Cambrault                                 11000.00  
  118. Eleni Zlotkey                                    10500.00  
  119. Peter Tucker                                     10000.00  
  120. David Bernstein                                   9500.00  
  121. Peter Hall                                        9000.00  
  122. Christopher Olsen                                 8000.00  
  123. Nanette Cambrault                                 7500.00  
  124. Oliver Tuvault                                    7000.00  
  125. --等54行数据…  
  126.  
  127. /*--------9、列出各个部门中工资最高的员工的信息：名字、部门号、工资。---------*/  
  128.  
  129. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME 
  130.    2         ,SALARY,DEPARTMENT_ID  
  131.    3  FROM EMPLOYEES  
  132.    4  WHERE (DEPARTMENT_ID,SALARY) IN 
  133.    5        (SELECT DEPARTMENT_ID,MAX(SALARY)  
  134.    6         FROM EMPLOYEES  
  135.    7         GROUP BY DEPARTMENT_ID);   
  136.  
  137. NAME                                               SALARY DEPARTMENT_ID  
  138. ---------------------------------------------- ---------- -------------  
  139. Jennifer Whalen                                   4400.00            10  
  140. Michael Hartstein                                13000.00            20  
  141. Den Raphaely                                     11000.00            30  
  142. Susan Mavris                                      6500.00            40  
  143. Adam Fripp                                        8200.00            50  
  144. Alexander Hunold                                  9000.00            60  
  145. Hermann Baer                                     10000.00            70  
  146. John Russell                                     14000.00            80  
  147. Steven King                                      24000.00            90  
  148. Nancy Greenberg                                  12000.00           100  
  149. Shelley Higgins                                  12000.00           110  
  150.    
  151. 11 rows selected  
  152.  
  153. /*--------10、最高的部门平均工资是多少。---------*/  
  154. SQL> SELECT MAX(AVGSALARY)  
  155.   2  FROM(SELECT DEPARTMENT_ID,AVG(SALARY) AVGSALARY  
  156.   3    FROM EMPLOYEES  
  157.   4    GROUP BY DEPARTMENT_ID);  
  158.    
  159. MAX(AVGSALARY)  
  160. --------------  
  161. 21333.33333333 
：http://www.cnblogs.com/huyong/archive/2011/06/22/2086772.html

精彩图集
oracle中截断表的使用介绍
oracle中截断
oracle常用sql查询语句部分集合(图文)
oracle常用s

热门标签

订火车票 扩展插件 页里面 一条sql语句 datatables jdom解析xml Tornado 复选框的值 system函数 定时提醒 拷贝构造函数 鼠标位置 ZIP chk web环境 escape函数 AndroidManif if语句 w 十个 php php在服务 移除tr 多张 authorizatio Count 选择符 整型数字 菜单类 多进程编程 弹出 弹出居 sys.argv 大端法 java读取csv文件 FileTable 横排 Administrato 表字段 SQLServer200 excel表格导出 父页面 js判断字符串 格式化 php运行环境 小计 php分页类 传引用 水仙花数 StringBuffer 链表操作 几个 DomXML 对象初始化 二叉树遍历 python日期 对外访问 图像 打印 插入记录 pdb调试

赞助商链接