软件测试网 软件测试培训 软件测试论坛 测试解决方案 文章资料精选 软件测试博客 软件测试招聘

Oracle SQL：经典查询练手第四篇

发表于：2011-6-28 10:15

字体：大 中 小 | 上一篇 | 下一篇 | 我要投稿

 作者：胡勇    来源：51Testing软件测试网采编

分享：
  1. /*--------7、哪些员工的工资，介于50号 和80号 部门平均工资之间。---------*/  
  2.  
  3. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY  
  4.   2  FROM EMPLOYEES  
  5.   3  WHERE SALARY  
  6.   4  BETWEEN 
  7.   5      (SELECT AVG(SALARY) FROM EMPLOYEES  
  8.   6       WHERE DEPARTMENT_ID = 50)  
  9.   7  AND (SELECT AVG(SALARY) FROM EMPLOYEES  
  10.   8       WHERE DEPARTMENT_ID = 80);  
  11.    
  12. NAME                                               SALARY  
  13. ---------------------------------------------- ----------  
  14. Bruce Ernst                                       6000.00  
  15. David Austin                                      4800.00  
  16. Valli Pataballa                                   4800.00  
  17. Diana Lorentz                                     4200.00  
  18. John Chen                                         8200.00  
  19. Ismael Sciarra                                    7700.00  
  20. Jose Manuel Urman                                 7800.00  
  21. Luis Popp                                         6900.00  
  22. Matthew Weiss                                     8000.00  
  23. Adam Fripp                                        8200.00  
  24. Payam Kaufling                                    7900.00  
  25. Shanta Vollman                                    6500.00  
  26. Kevin Mourgos                                     5800.00  
  27. Renske Ladwig                                     3600.00  
  28. Trenna Rajs                                       3500.00  
  29. Christopher Olsen                                 8000.00  
  30. Nanette Cambrault                                 7500.00  
  31. Oliver Tuvault                                    7000.00  
  32. Lindsey Smith                                     8000.00  
  33. Louise Doran                                      7500.00  
  34. Sarath Sewall                                     7000.00  
  35. Mattea Marvins                                    7200.00  
  36. David Lee                                         6800.00  
  37. Sundar Ande                                       6400.00  
  38. Amit Banda                                        6200.00  
  39. William Smith                                     7400.00  
  40. Elizabeth Bates                                   7300.00  
  41. Sundita Kumar                                     6100.00  
  42. Alyssa Hutton                                     8800.00  
  43. Jonathon Taylor                                   8600.00  
  44. Jack Livingston                                   8000.00  
  45. Kimberely Grant                                   7000.00  
  46. Charles Johnson                                   7211.00  
  47. Nandita Sarchand                                  4200.00  
  48. Alexis Bull                                       4100.00  
  49. Kelly Chung                                       3800.00  
  50. Jennifer Dilly                                    3600.00  
  51. Sarah Bell                                        4000.00  
  52. Britney Everett                                   3900.00  
  53. Jennifer Whalen                                   4400.00  
  54. Pat Fay                                           6000.00  
  55. Susan Mavris                                      6500.00  
  56. William Gietz                                     8300.00  
  57.    
  58. 43 rows selected

  1. /*--------8、所在部门平均工资高于5000 的员工名字。---------*/  
  2.  
  3. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS NAME,SALARY  
  4.    2  FROM EMPLOYEES  
  5.    3  WHERE DEPARTMENT_ID IN 
  6.    4        (SELECT DEPARTMENT_ID FROM EMPLOYEES  
  7.    5         GROUP BY DEPARTMENT_ID  
  8.    6         HAVING AVG(SALARY) > 5000);  
  9.    
  10. NAME                                               SALARY  
  11. ---------------------------------------------- ----------  
  12. Michael Hartstein                                13000.00  
  13. Pat Fay                                           6000.00  
  14. Susan Mavris                                      6500.00  
  15. Alexander Hunold                                  9000.00  
  16. Bruce Ernst                                       6000.00  
  17. David Austin                                      4800.00  
  18. Valli Pataballa                                   4800.00  
  19. Diana Lorentz                                     4200.00  
  20. Hermann Baer                                     10000.00  
  21. John Russell                                     14000.00  
  22. Karen Partners                                   13500.00  
  23. Alberto Errazuriz                                12000.00  
  24. Gerald Cambrault                                 11000.00  
  25. Eleni Zlotkey                                    10500.00  
  26. Peter Tucker                                     10000.00  
  27. David Bernstein                                   9500.00  
  28. Peter Hall                                        9000.00  
  29. Christopher Olsen                                 8000.00  
  30. Nanette Cambrault                                 7500.00  
  31. Oliver Tuvault                                    7000.00  
  32. --等54行数据…

65/6<123456>
价值398元的测试课程免费赠送，填问卷领取吧！

热门推荐

搜索风云榜

测试技术了解

玩转数据分析

挣点稿费

测试用例指南get

关注51Testing

热门标签

博文推荐

热点聚焦

最近讲堂

友情链接

51Testing软件测试网 蓝色理想 太平洋软件 天极软件 51CTO开发频道 中国站长学院 云计算 第三媒体数码 数据恢复 51Job职场资讯 新客网 Java开源大全 数据分析

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计

法律顾问：上海漕溪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2022
投诉及意见反馈：webmaster@51testing.com; 业务联系：service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号