Oracle SQL:经典查询练手第二篇

数据库 Oracle
本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询,欢迎大家补充,同时你认为有那些经典的也可分享出来。在本文中,对每一个问题,你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛,感谢!

本文与大家共同讨论与分享ORACLE SQL的一些常用经典查询,欢迎大家补充,同时你认为有那些经典的也可分享出来。在本文中,对每一个问题,你要是认为有什么更好的解决方法也欢迎你及时提出。交流与分享才能共同进步嘛,感谢!

接上一篇Oracle SQL:经典查询练手第一篇

本篇相对上篇来说比较简单,如果你对本篇的各测试做得不称心如意的话,我想你是时候给自己充下电了!

本文使用的实例表结构与表的数据如下:

scott.emp员工表结构如下:

  1. SQL> DESC SCOTT.EMP;  
  2. Name     Type         Nullable Default Comments   
  3. -------- ------------ -------- ------- --------   
  4. EMPNO    NUMBER(4)                     员工编号    
  5. ENAME    VARCHAR2(10) Y                员工姓名    
  6. JOB      VARCHAR2(9)  Y                职位       
  7. MGR      NUMBER(4)    Y                上级编号    
  8. HIREDATE DATE         Y                雇佣日期    
  9. SAL      NUMBER(7,2)  Y                薪金       
  10. COMM     NUMBER(7,2)  Y                佣金       
  11. DEPTNO   NUMBER(2)    Y                所在部门编号  
  12. --提示:工资 = 薪金 + 佣金 

scott.dept部门表

  1. SQL> DESC SCOTT.DEPT;  
  2. Name   Type         Nullable Default Comments   
  3. ------ ------------ -------- ------- --------   
  4. DEPTNO NUMBER(3)                     部门编号    
  5. DNAME  VARCHAR2(14) Y                部门名称    
  6. LOC    VARCHAR2(13) Y                地点  

scott.emp表的现有数据如下:

  1. SQL> SELECT * FROM SCOTT.EMP;  
  2.    
  3. EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO  
  4. ----- ---------- --------- ----- ----------- --------- --------- ------  
  5.  7369 SMITH      CLERK      7902 1980-12-17     800.00               20  
  6.  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30  
  7.  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30  
  8.  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20  
  9.  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30  
  10.  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30  
  11.  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10  
  12.  7788 SCOTT      ANALYST    7566 1987-4-19     4000.00               20  
  13.  7839 KING       PRESIDENT       1981-11-17    5000.00               10  
  14.  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30  
  15.  7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20  
  16.  7900 JAMES      CLERK      7698 1981-12-3      950.00               30  
  17.  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20  
  18.  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10  
  19.   102 EricHu     Developer  1455 2011-5-26 1   5500.00     14.00     10  
  20.   104 huyong     PM         1455 2011-5-26 1   5500.00     14.00     10  
  21.   105 WANGJING   Developer  1455 2011-5-26 1   5500.00     14.00     10  
  22.    
  23. 17 rows selected  

Scott.dept表的现有数据如下:

  1. SQL> SELECT * FROM SCOTT.DEPT;  
  2.    
  3. DEPTNO DNAME          LOC  
  4. ------ -------------- -------------  
  5.    110 信息科         海口  
  6.     10 ACCOUNTING     NEW YORK  
  7.     20 RESEARCH       DALLAS  
  8.     30 SALES          CHICAGO  
  9.     40 OPERATIONS     BOSTON  
  10.     50 50abc          50def  
  11.     60 Developer      HaiKou  
  12.    
  13. rows selected  

用SQL完成以下问题列表:

  1. 找出EMP表中的姓名(ENAME)第三个字母是A 的员工姓名。
  2. 找出EMP表员工名字中含有A 和N的员工姓名。
  3. 找出所有有佣金的员工,列出姓名、工资、佣金,显示结果按工资从小到大,佣金从大到小。
  4. 列出部门编号为20的所有职位。
  5. 列出不属于SALES 的部门。
  6. 显示工资不在1000 到1500 之间的员工信息:名字、工资,按工资从大到小排序。
  7. 显示职位为MANAGER 和SALESMAN,年薪在15000 和20000 之间的员工的信息:名字、职位、年薪。
  8. 说明以下两条SQL语句的输出结果:
    1. SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;  
    2. SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL
  9. 让SELECT 语句的输出结果为
    1. SELECT * FROM SALGRADE;  
    2. SELECT * FROM BONUS;  
    3. SELECT * FROM EMP;  
    4. SELECT * FROM DEPT;  
    5. ……

    列出当前用户有多少张数据表,结果集中存在多少条记录。 

  10. 判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错,为什么?

#p#

各试题解答如下(欢迎大家指出不同的方法或建议!):

  1. --------1. 找出EMP表中的姓名(ENAME)第三个字母是A 的员工姓名。---------  
  2. SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';  
  3.    
  4. ENAME  
  5. ----------  
  6. ADAMS  
  7. BLAKE  
  8. CLARK 
  1. -------2. 找出EMP表员工名字中含有A 和N的员工姓名。----------  
  2. SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%N%';  
  3.    
  4. ENAME  
  5. ----------  
  6. ALLEN  
  7. MARTIN  
  8. WANGJING  
  9. --------或--------  
  10. SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%N%';  
  11.    
  12. ENAME  
  13. ----------  
  14. ALLEN  
  15. MARTIN  
  16. WANGJING 
  1. /*--------3. 找出所有有佣金的员工,列出姓名、工资、佣金,显示结果按工资从小到大,  
  2. 佣金从大到小。----------*/  
  3. SQL> SELECT ENAME,SAL + COMM AS WAGE,COMM  
  4.   2  FROM SCOTT.EMP  
  5.   3  ORDER BY WAGE,COMM DESC;  
  6.    
  7. ENAME            WAGE      COMM  
  8. ---------- ---------- ---------  
  9. TURNER           1500      0.00  
  10. WARD             1750    500.00  
  11. ALLEN            1900    300.00  
  12. MARTIN           2650   1400.00  
  13. EricHu           5514     14.00  
  14. WANGJING         5514     14.00  
  15. huyong           5514     14.00  
  16. SMITH                   
  17. JONES                   
  18. JAMES                   
  19. MILLER                  
  20. FORD                    
  21. ADAMS                   
  22. BLAKE                   
  23. CLARK                   
  24. SCOTT                   
  25. KING                    
  26.    
  27. 17 rows selected 
  1. -------4. 列出部门编号为20的所有职位。----------  
  2. SQL> SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 20;  
  3.    
  4. JOB  
  5. ---------  
  6. ANALYST  
  7. CLERK  
  8. MANAGER 
  1. -------5. 列出不属于SALES 的部门。----------  
  2. SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME <> 'SALES';  
  3.    
  4. DEPTNO DNAME          LOC  
  5. ------ -------------- -------------  
  6.     10 ACCOUNTING     NEW YORK  
  7.     20 RESEARCH       DALLAS  
  8.     40 OPERATIONS     BOSTON  
  9.     50 50abc          50def  
  10.     60 Developer      HaiKou  
  11.    110 信息科         海口  
  12.    
  13. rows selected  
  14.  
  15. --或者:  
  16. SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME != 'SALES';  
  17. SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME NOT IN('SALES');  
  18. SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME NOT LIKE 'SALES'
  1. ---6. 显示工资不在1000 到1500 之间的员工信息:名字、工资,按工资从大到小排序。---------  
  2. SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP  
  3.   2  WHERE SAL + COMM NOT BETWEEN 1000 AND 1500  
  4.   3  ORDER BY WAGE DESC;  
  5.    
  6. ENAME            WAGE  
  7. ---------- ----------  
  8. EricHu           5514  
  9. huyong           5514  
  10. WANGJING         5514  
  11. MARTIN           2650  
  12. ALLEN            1900  
  13. WARD             1750  
  14.    
  15. rows selected  
  16. --或者  
  17. SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP  
  18.   2  WHERE SAL + COMM < 1000 OR SAL + COMM > 1500  
  19.   3  ORDER BY WAGE DESC;  
  20.    
  21. ENAME            WAGE  
  22. ---------- ----------  
  23. EricHu           5514  
  24. huyong           5514  
  25. WANGJING         5514  
  26. MARTIN           2650  
  27. ALLEN            1900  
  28. WARD             1750  
  29.    
  30. rows selected 

#p#
 

  1. /*----- 7. 显示职位为MANAGER 和SALESMAN,年薪在15000 和20000 之间的员工的信息:名字、职位、年薪。----------*/  
  2. SQL> SELECT ENAME 姓名,JOB 职位,(SAL + COMM) * 12 AS 年薪  
  3.   2  FROM SCOTT.EMP  
  4.   3  WHERE (SAL + COMM) * 12 BETWEEN 15000 AND 20000  
  5.   4  AND JOB IN('MANAGER','SALESMAN');  
  6.    
  7. 姓名       职位              年薪  
  8. ---------- --------- ----------  
  9. TURNER     SALESMAN       18000 
  1. /*----- 8. 说明以下两条SQL语句的输出结果:  
  2.     SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;  
  3.     SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;  
  4. ----------*/  
  5. SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;  
  6.    
  7. EMPNO      COMM  
  8. ----- ---------  
  9.  7369   
  10.  7566   
  11.  7698   
  12.  7782   
  13.  7788   
  14.  7839   
  15.  7876   
  16.  7900   
  17.  7902   
  18.  7934   
  19.    
  20. 10 rows selected  
  21.  
  22. ---------------------------------------------------------------  
  23. SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;  
  24.    
  25. EMPNO      COMM  
  26. ----- ---------  
  27.  
  28. --说明:IS NULL是判断某个字段是否为空,为空并不等价于为空字符串或为数字0;  
  29. --而 =NULL 是判断某个值是否等于 NULL,NULL = NULL和NULL <> NULL都为 FALSE。 

 

  1. ---10. 语句SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错?---------  
  2. SQL> SELECT ENAME,SAL FROM EMP WHERE SAL > '1500';  
  3.    
  4. ENAME            SAL  
  5. ---------- ---------  
  6. ALLEN        1600.00  
  7. JONES        2975.00  
  8. BLAKE        2850.00  
  9. CLARK        2450.00  
  10. SCOTT        4000.00  
  11. KING         5000.00  
  12. FORD         3000.00  
  13. EricHu       5500.00  
  14. huyong       5500.00  
  15. WANGJING     5500.00  
  16.    
  17. 10 rows selected  
  18.    
  19. SQL> SELECT ENAME,SAL FROM EMP WHERE SAL > 1500;  
  20.    
  21. ENAME            SAL  
  22. ---------- ---------  
  23. ALLEN        1600.00  
  24. JONES        2975.00  
  25. BLAKE        2850.00  
  26. CLARK        2450.00  
  27. SCOTT        4000.00  
  28. KING         5000.00  
  29. FORD         3000.00  
  30. EricHu       5500.00  
  31. huyong       5500.00  
  32. WANGJING     5500.00  
  33.    
  34. 10 rows selected  
  35. --说明不会抱错,这儿存在隐式数据类型的。  
  1. /*-----9. 让SELECT 语句的输出结果为  
  2.     SELECT * FROM SALGRADE;  
  3. SELECT * FROM BONUS;  
  4. SELECT * FROM EMP;  
  5. SELECT * FROM DEPT;  
  6. ……  
  7. 列出当前用户有多少张数据表,结果集中存在多少条记录。  
  8. ----------*/  
  9.  
  10. SQL> SELECT 'SELECT * FROM '||TABLE_NAME||';' FROM USER_TABLES;  
  11.    
  12. 'SELECT*FROM'||TABLE_NAME||';' 
  13. ---------------------------------------------  
  14. SELECT * FROM BONUS;  
  15. SELECT * FROM EMP;  
  16. SELECT * FROM DEPT;  
  17. --......等等,在此不列出。 

 

 

原文链接:http://www.cnblogs.com/huyong/archive/2011/06/16/2082581.html

【编辑推荐】

  1. PL/SQL开发中动态SQL的使用与过程分页
  2. 详解Oracle的几种分页查询语句
  3. Oracle分页小谈
  4. 简单说说Oracle分区
  5. Oracle数据库日常维护知识总结

 

责任编辑:艾婧 来源: EricHu's Tech Space
相关推荐

2011-06-22 13:26:37

Oracle

2011-06-23 10:25:38

Oracle

2011-06-27 14:00:52

Oracle

2011-06-21 09:14:01

Oracle查询

2014-03-28 13:30:36

2021-07-21 15:33:03

鸿蒙HarmonyOS应用

2017-04-10 14:46:29

AndroidGradleBuild.gradl

2011-03-14 16:05:17

2022-08-02 10:26:09

网络层网络网络协议

2021-07-10 07:39:38

Node.js C++V8

2019-11-25 09:45:27

IBMIBM Cloud P多云

2019-11-26 16:31:22

IBM

2012-11-27 13:05:32

2019-07-31 12:34:23

数字化转型云计算架构

2019-11-06 11:25:48

IBMIBM Cloud P神州数码

2010-10-27 13:25:33

Oracle查询

2010-09-26 17:09:05

SQL语句

2021-01-26 05:33:07

排序算法快速

2011-07-11 16:10:38

SQL移植Oracle数据库

2017-02-24 12:29:20

Android Thi开发板硬件
点赞
收藏

51CTO技术栈公众号