oracle经典查询练手

SQL 教程                 SQL教材2

第一手

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

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

scott.emp员工表结构如下:

  1. Name     Type         Nullable Default Comments   
  2. -------- ------------ -------- ------- --------   
  3. EMPNO    NUMBER(4)                       员工号         
  4. ENAME    VARCHAR2(10) Y                  员工姓名         
  5. JOB      VARCHAR2(9)  Y                  工作         
  6. MGR      NUMBER(4)    Y                  上级编号         
  7. HIREDATE DATE         Y                  雇佣日期         
  8. SAL      NUMBER(7,2)  Y                  薪金         
  9. COMM     NUMBER(7,2)  Y                  佣金         
  10. DEPTNO   NUMBER(2)    Y                  部门编号 

scott.dept部门表:

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

提示:工资 = 薪金 + 佣金

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

  1. SQL> select * from 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 dept;  
  2.    
  3. DEPTNO DNAME          LOC  
  4. ------ -------------- -------------  
  5.     10 ACCOUNTING     NEW YORK  
  6.     20 RESEARCH       DALLAS  
  7.     30 SALES          CHICAGO  
  8.     40 OPERATIONS     BOSTON  
  9.     50 50abc          50def  
  10.     60 Developer      HaiKou  
  11.    
  12. rows selected 

用SQL完成以下问题列表:

  1. 列出至少有一个员工的所有部门。
  2. 列出薪金比“SMITH”多的所有员工。
  3. 列出所有员工的姓名及其直接上级的姓名。
  4. 列出受雇日期早于其直接上级的所有员工。
  5. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
  6. 列出所有“CLERK”(办事员)的姓名及其部门名称。
  7. 列出最低薪金大于1500的各种工作。
  8. 列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。
  9. 列出薪金高于公司平均薪金的所有员工。
  10. 列出与“SCOTT”从事相同工作的所有员工。
  11. 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
  12. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
  13. 列出在每个部门工作的员工数量、平均工资和平均服务期限。
  14. 列出所有员工的姓名、部门名称和工资。
  15. 列出所有部门的详细信息和部门人数。
  16. 列出各种工作的最低工资。
  17. 列出各个部门的MANAGER(经理)的最低薪金。
  18. 列出所有员工的年工资,按年薪从低到高排序。

各答案如下,欢迎大家给出不出的解答方式。

  1. --------1.列出至少有一个员工的所有部门。---------  
  2. SQL> select dname from dept where deptno in(select deptno from emp);   
  3. DNAME  
  4. --------------  
  5. RESEARCH  
  6. SALES  
  7. ACCOUNTING  
  8. --------或--------  
  9. SQL> select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1);   
  10. DNAME  
  11. --------------  
  12. ACCOUNTING  
  13. RESEARCH  
  14. SALES  
  1. --------2.列出薪金比“SMITH”多的所有员工。----------  
  2. SQL> select * from emp where sal > (select sal from emp where ename = 'SMITH');  
  3.    
  4. EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO  
  5. ----- ---------- --------- ----- ----------- --------- --------- ------  
  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.  16 rows selected 
  1. --------3.列出所有员工的姓名及其直接上级的姓名。----------  
  2. SQL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;   
  3. ENAME      BOSS_NAME  
  4. ---------- ----------  
  5. SMITH      FORD  
  6. ALLEN      BLAKE  
  7. WARD       BLAKE  
  8. JONES      KING  
  9. MARTIN     BLAKE  
  10. BLAKE      KING  
  11. CLARK      KING  
  12. SCOTT      JONES  
  13. KING         
  14. TURNER     BLAKE  
  15. ADAMS      SCOTT  
  16. JAMES      BLAKE  
  17. FORD       JONES  
  18. MILLER     CLARK  
  19. EricHu       
  20. huyong       
  21. WANGJING      
  22. 17 rows selected 
  1. --------4.列出受雇日期早于其直接上级的所有员工。----------  
  2. SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr);   
  3. ENAME  
  4. ----------  
  5. SMITH  
  6. ALLEN  
  7. WARD  
  8. JONES  
  9. BLAKE  
  10. CLARK   
  11. rows selected  
  12.  
  1. --------5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门----------  
  2. SQL> select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno  
  3.   2  from dept a left join emp b on a.deptno=b.deptno;  
  4.    
  5. DNAME          EMPNO ENAME      JOB         MGR HIREDATE          SAL DEPTNO  
  6. -------------- ----- ---------- --------- ----- ----------- --------- ------  
  7. RESEARCH        7369 SMITH      CLERK      7902 1980-12-17     800.00     20  
  8. SALES           7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00     30  
  9. SALES           7521 WARD       SALESMAN   7698 1981-2-22     1250.00     30  
  10. RESEARCH        7566 JONES      MANAGER    7839 1981-4-2      2975.00     20  
  11. SALES           7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00     30  
  12. SALES           7698 BLAKE      MANAGER    7839 1981-5-1      2850.00     30  
  13. ACCOUNTING      7782 CLARK      MANAGER    7839 1981-6-9      2450.00     10  
  14. RESEARCH        7788 SCOTT      ANALYST    7566 1987-4-19     4000.00     20  
  15. ACCOUNTING      7839 KING       PRESIDENT       1981-11-17    5000.00     10  
  16. SALES           7844 TURNER     SALESMAN   7698 1981-9-8      1500.00     30  
  17. RESEARCH        7876 ADAMS      CLERK      7788 1987-5-23     1100.00     20  
  18. SALES           7900 JAMES      CLERK      7698 1981-12-3      950.00     30  
  19. RESEARCH        7902 FORD       ANALYST    7566 1981-12-3     3000.00     20  
  20. ACCOUNTING      7934 MILLER     CLERK      7782 1982-1-23     1300.00     10  
  21. ACCOUNTING       102 EricHu     Developer  1455 2011-5-26 1   5500.00     10  
  22. ACCOUNTING       104 huyong     PM         1455 2011-5-26 1   5500.00     10  
  23. ACCOUNTING       105 WANGJING   Developer  1455 2011-5-26 1   5500.00     10  
  24. 50abc                                                                   
  25. OPERATIONS                                                              
  26. Developer                                                            
  27.    
  28. 20 rows selected  

  1. --------6.列出所有“CLERK”(办事员)的姓名及其部门名称。----------  
  2. SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';   
  3. ENAME      DNAME  
  4. ---------- --------------  
  5. SMITH      RESEARCH  
  6. ADAMS      RESEARCH  
  7. JAMES      SALES  
  8. MILLER     ACCOUNTING 
  1. --------7.列出最低薪金大于1500的各种工作。----------  
  2. SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500;   
  3. HIGHSALJOB  
  4. ----------  
  5. ANALYST  
  6. Developer  
  7. MANAGER  
  8. PM  
  9. PRESIDENT 
  1. --------8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。----------  
  2. SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES');   
  3. ENAME  
  4. ----------  
  5. ALLEN  
  6. WARD  
  7. MARTIN  
  8. BLAKE  
  9. TURNER  
  10. JAMES   
  11. rows selected 
  1. --------9.列出薪金高于公司平均薪金的所有员工。----------  
  2. SQL> select ename from emp where sal>(select avg(sal) from emp);   
  3. ENAME  
  4. ----------  
  5. JONES  
  6. BLAKE  
  7. SCOTT  
  8. KING  
  9. FORD  
  10. EricHu  
  11. huyong  
  12. WANGJING   
  13. rows selected 
  1. --------10.列出与“SCOTT”从事相同工作的所有员工。--------  
  2. SQL> select ename from emp where job=(select job from emp where ename='SCOTT');  
  3.  ENAME  
  4. ----------  
  5. SCOTT  
  6. FORD 
  1. --------11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。---------  
  2. SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal  
  3.   2  from emp b where b.deptno=30) and a.deptno<>30;   
  4. ENAME            SAL  
  5. ---------- --------- 
  1. --------12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。---------  
  2. SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);   
  3. ENAME            SAL  
  4. ---------- ---------  
  5. JONES        2975.00  
  6. SCOTT        4000.00  
  7. KING         5000.00  
  8. FORD         3000.00  
  9. EricHu       5500.00  
  10. huyong       5500.00  
  11. WANGJING     5500.00   
  12. rows selected 
  1. --------13.列出在每个部门工作的员工数量、平均工资和平均服务期限。---------  
  2. SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal  
  3.   2  from emp a group by deptno;   
  4. DEPTNAME        DEPTCOUNT DEPTAVGSAL  
  5. -------------- ---------- ----------  
  6. ACCOUNTING              6 4208.33333  
  7. RESEARCH                5       2375  
  8. SALES                   6 1566.66666 


 

  1. --------14.列出所有员工的姓名、部门名称和工资。---------  
  2. SQL> select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a;   
  3.    
  4. ENAME      DEPTNAME             SAL  
  5. ---------- -------------- ---------  
  6. SMITH      RESEARCH          800.00  
  7. ALLEN      SALES            1600.00  
  8. WARD       SALES            1250.00  
  9. JONES      RESEARCH         2975.00  
  10. MARTIN     SALES            1250.00  
  11. BLAKE      SALES            2850.00  
  12. CLARK      ACCOUNTING       2450.00  
  13. SCOTT      RESEARCH         4000.00  
  14. KING       ACCOUNTING       5000.00  
  15. TURNER     SALES            1500.00  
  16. ADAMS      RESEARCH         1100.00  
  17. JAMES      SALES             950.00  
  18. FORD       RESEARCH         3000.00  
  19. MILLER     ACCOUNTING       1300.00  
  20. EricHu     ACCOUNTING       5500.00  
  21. huyong     ACCOUNTING       5500.00  
  22. WANGJING   ACCOUNTING       5500.00  
  23.    
  24. 17 rows selected 
  1. --------15.列出所有部门的详细信息和部门人数。---------  
  2. SQL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a;   
  3. DEPTNO DNAME          LOC            DEPTCOUNT  
  4. ------ -------------- ------------- ----------  
  5.     10 ACCOUNTING     NEW YORK               6  
  6.     20 RESEARCH       DALLAS                 5  
  7.     30 SALES          CHICAGO                6  
  8.     40 OPERATIONS     BOSTON          
  9.     50 50abc          50def           
  10.     60 Developer      HaiKou       
  11.    
  12. rows selected 
  1. --------16.列出各种工作的最低工资。---------  
  2. SQL> select job,avg(sal) from emp group by job;  
  3.    
  4. JOB         AVG(SAL)  
  5. --------- ----------  
  6. ANALYST         3500  
  7. CLERK         1037.5  
  8. Developer       5500  
  9. MANAGER   2758.33333  
  10. PM              5500  
  11. PRESIDENT       5000  
  12. SALESMAN        1400  
  13.    
  14. rows selected 
  1. --------17.列出各个部门的MANAGER(经理)的最低薪金。--------  
  2. SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno;  
  3.    
  4. DEPTNO   MIN(SAL)  
  5. ------ ----------  
  6.     10       2450  
  7.     20       2975  
  8. 30       2850 
  1. --------18.列出所有员工的年工资,按年薪从低到高排序。---------  
  2. SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;  
  3.    
  4. ENAME       SALPERSAL  
  5. ---------- ----------  
  6. SMITH            9600  
  7. JAMES           11400  
  8. ADAMS           13200  
  9. MILLER          15600  
  10. TURNER          18000  
  11. WARD            21000  
  12. ALLEN           22800  
  13. CLARK           29400  
  14. MARTIN          31800  
  15. BLAKE           34200  
  16. JONES           35700  
  17. FORD            36000  
  18. SCOTT           48000  
  19. KING            60000  
  20. EricHu          66168  
  21. huyong          66168  
  22. WANGJING        66168  
  23.    
  24. 17 rows selected 

第二手

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

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

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'是否抱错,为什么?

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

  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 


 

  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. --......等等,在此不列出。 

第三手

本篇相对上两篇来说难度有所增加,继续努力,通过我为大家设立的这个系列,只要你对每一篇,每一个试题都实践测试,认真练习。我相信你对常用、经典的SQL已能熟能生巧,信手拈来!

本文使用Oracle自带的人力资源(HR)实例数据,本文所用表结构如下:

HR.EMPLOYEES员工表结构如下:

 

HR.DEPARTMENTS表结构如下:

HR.REGIONS表结构如下:

  1. SQL> DESC HR.REGIONS;  
  2. Name        Type         Nullable Default Comments   
  3. ----------- ------------ -------- ------- --------   
  4. REGION_ID   NUMBER                                   
  5. REGION_NAME VARCHAR2(25) Y 

用SQL完成以下问题列表:

1. 让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE  ROWNUM < 5 输出结果的货币单位是¥和$。

2. 列出前五位每个员工的名字,工资、涨薪后的的工资(涨幅为8%),以“元”为单位进行四舍五入。

3. 找出谁是最高领导,将名字按大写形式显示。

4. 找出First_Name 为David,Last_Name为Austin 的直接领导名字。

5. First_Name 为Alexander,Last_Name为Hunold领导谁。(谁向David 报告)。

6. 哪些员工的工资高于他直接上司的工资,列出员工的名字和工资,上司的名字和工资。

7. 哪些员工和Chen(LAST_NAME)同部门。

8. 哪些员工跟De Haan(LAST_NAME)做一样职位。

9. 哪些员工跟Hall(LAST_NAME)不在同一个部门。

10. 哪些员工跟William(FIRST_NAME)、Smith(LAST_NAME)做不一样的职位。

11. 显示有提成的员工的信息:名字、提成、所在部门名称、所在地区的名称。

12. 显示Executive部门有哪些职位。

13. 整个公司中,最高工资和最低工资相差多少。

14. 提成大于0 的人数。

15. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。

16. 整个公司有多少个领导。

17. 列出在同一部门入职日期晚但工资高于其他同事的员工:名字、工资、入职日期。

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

  1. /*--------1、改变NLS_LANG 的值,让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是¥和$。---------*/  
  2. -----在没有设置NLS_LANG的情况下:  
  3.  
  4. SQL> SELECT TO_CHAR(SALARY,'L99,999.99')  
  5.    2  FROM HR.EMPLOYEES  
  6.    3  WHERE ROWNUM < 5;  
  7.  
  8. TO_CHAR(SALARY,'L99,999.99')  
  9. ----------------------------  
  10.          ¥24,000.00  
  11.          ¥20,000.00  
  12.          ¥20,000.00  
  13.           ¥9,000.00  
  14.  
  15. SQL> SELECT TO_CHAR(SALARY,'$99,999.99')  
  16.    2  FROM HR.EMPLOYEES  
  17.    3  WHERE ROWNUM < 5;  
  18.    
  19. TO_CHAR(SALARY,'$99,999.99')  
  20. ----------------------------  
  21.  $24,000.00  
  22.  $20,000.00  
  23.  $20,000.00  
  24.   $9,000.00  
  25.  
  26. /*--说明:对于'$99,999.99'格式符:  
  27. L:表示强制显示当地货币符号  
  28. $: 表示显示美元符号  
  29. 9: 表示一个数字  
  30. 0: 表示强制0显示  
  31. .: 表示一个小数点  
  32. ,: 表示一个千位分隔符  
  33. --------------*/  
  34.  
  35. /*--------2、列出前五位每个员工的名字,工资、涨薪后的的工资(涨幅为8%),以“元”为单位进行四舍五入。---------*/  
  36.  
  37. SQL> SELECT FIRST_NAME,SALARY,ROUND(SALARY * 1.08) FROM HR.EMPLOYEES  
  38.    2  WHERE ROWNUM <=5;  
  39.    
  40. FIRST_NAME               SALARY ROUND(SALARY*1.08)  
  41. -------------------- ---------- ------------------  
  42. Steven                 24000.00              25920  
  43. Neena                  20000.00              21600  
  44. Lex                    20000.00              21600  
  45. Alexander               9000.00               9720  
  46. Bruce                   6000.00               6480  
  47.  
  48. /*--------3、找出谁是最高领导,将名字按大写形式显示。---------*/  
  49. SQL> SELECT UPPER(FIRST_NAME || ' ' || LAST_NAME) AS NAME 
  50.    2  FROM HR.EMPLOYEES  
  51.    3  WHERE MANAGER_ID IS NULL;  
  52.    
  53. NAME 
  54. ----------------------------------------------  
  55. STEVEN KING  
  56.  
  57. /*--------4、找出David 的直接领导的名字。---------*/  
  58. SQL> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME 
  59.    2  FROM HR.EMPLOYEES  
  60.    3  WHERE EMPLOYEE_ID IN(  
  61.    4  SELECT MANAGER_ID FROM HR.EMPLOYEES  
  62.    5  WHERE FIRST_NAME = 'David' AND LAST_NAME = 'Austin');  
  63.    
  64. NAME 
  65. ----------------------------------------------  
  66. ALEXANDER HUNOLD  
  67.  
  68. --或采用以下方法  
  69.  
  70. SQL> SELECT UPPER( EMP1.FIRST_NAME ||' ' ||  EMP1.LAST_NAME) AS NAME 
  71.    2  FROM HR.EMPLOYEES EMP1,HR.EMPLOYEES EMP2  
  72.    3  WHERE EMP1.EMPLOYEE_ID = EMP2.MANAGER_ID  
  73.    4  AND EMP2.FIRST_NAME = 'David' AND EMP2.LAST_NAME =  'Austin';   
  74.  
  75. NAME 
  76. ----------------------------------------------  
  77. ALEXANDER HUNOLD  
  78.    
  79. /*--------5、First_Name 为Alexander,LAST_NAME为Hunold领导谁。(谁向David 报告)。---------*/  
  80. SQL> SELECT UPPER(FIRST_NAME ||' ' || LAST_NAME) AS NAME 
  81.    2  FROM HR.EMPLOYEES  
  82.    3  WHERE MANAGER_ID IN(  
  83.    4  SELECT EMPLOYEE_ID FROM HR.EMPLOYEES  
  84.    5  WHERE FIRST_NAME = 'Alexander' AND LAST_NAME = 'Hunold');  
  85.    
  86. NAME 
  87. ----------------------------------------------  
  88. BRUCE ERNST  
  89. DAVID AUSTIN  
  90. VALLI PATABALLA  
  91. DIANA LORENTZ  
  92.  
  93. --或采用以下方法  
  94.  
  95. SQL> SELECT UPPER( EMP1.FIRST_NAME || ' ' ||  EMP1.LAST_NAME) AS NAME 
  96.   2  FROM HR.EMPLOYEES EMP1,HR.EMPLOYEES EMP2  
  97.   3  WHERE EMP1.MANAGER_ID = EMP2.EMPLOYEE_ID  
  98.   4  AND EMP2.FIRST_NAME = 'Alexander' AND EMP2.LAST_NAME =  'Hunold';  
  99.    
  100. NAME 
  101. ----------------------------------------------  
  102. BRUCE ERNST  
  103. DAVID AUSTIN  
  104. VALLI PATABALLA  
  105. DIANA LORENTZ  
  106.  
  107. /*--------6、哪些员工的工资高于他直接上司的工资,列出员工的名字和工资,上司的名字和工资。---------*/  
  108.  
  109. SQL> SELECT E.FIRST_NAME,E.SALARY,M.FIRST_NAME,M.SALARY  
  110.   2  FROM EMPLOYEES E,EMPLOYEES M  
  111.   3  WHERE E.MANAGER_ID = M.EMPLOYEE_ID AND E.SALARY > M.SALARY;  
  112.    
  113. FIRST_NAME               SALARY FIRST_NAME               SALARY  
  114. -------------------- ---------- -------------------- ----------  
  115. Lisa                   11500.00 Gerald                 11000.00  
  116. Ellen                  11000.00 Eleni                  10500.00  
  117.  
  118. --要是只列出员工的名字与工资的话,还可以这样:  
  119.  
  120. SQL> SELECT E.FIRST_NAME,E.SALARY  
  121.   2  FROM EMPLOYEES E WHERE E.SALARY >  
  122.   3 (SELECT M.SALARY FROM EMPLOYEES M   
  123.   4  WHERE E.MANAGER_ID = M.EMPLOYEE_ID);  
  124.    
  125. FIRST_NAME               SALARY  
  126. -------------------- ----------  
  127. Lisa                   11500.00  
  128. Ellen                  11000.00 

  1. /*--------7、哪些员工和Chen(LAST_NAME)同部门。---------*/  
  2.  
  3. SQL> SELECT FIRST_NAME FROM EMPLOYEES  
  4.   2  WHERE DEPARTMENT_ID IN 
  5.   3  (SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'Chen')  
  6.   4  AND LAST_NAME <> 'Chen';  
  7.    
  8. FIRST_NAME  
  9. --------------------  
  10. Nancy  
  11. Daniel  
  12. Ismael  
  13. Jose Manuel  
  14. Luis  
  15. --或者--  
  16.  
  17. SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2  
  18.   2  WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID  
  19.   3  AND E2.LAST_NAME = 'Chen' AND E1.LAST_NAME <> 'Chen';  
  20.    
  21. FIRST_NAME  
  22. --------------------  
  23. Nancy  
  24. Daniel  
  25. Ismael  
  26. Jose Manuel  
  27. Luis  
  28.  
  29. /*--------8、哪些员工跟De Haan(LAST_NAME)做一样职位。---------*/  
  30. SQL> SELECT FIRST_NAME FROM EMPLOYEES  
  31.   2  WHERE JOB_ID IN 
  32.   3  (SELECT JOB_ID FROM EMPLOYEES  
  33.   4  WHERE LAST_NAME = 'De Haan')  
  34.   5  AND LAST_NAME <> 'De Haan';  
  35.    
  36. FIRST_NAME  
  37. --------------------  
  38. Neena  
  39.  
  40. --或者--  
  41.  
  42. SQL> SELECT E1.FIRST_NAME FROM EMPLOYEES E1,EMPLOYEES E2  
  43.   2  WHERE E1.JOB_ID = E2.JOB_ID   
  44.   3  AND E2.LAST_NAME = 'De Haan' AND E1.LAST_NAME <> 'De Haan';  
  45.    
  46. FIRST_NAME  
  47. --------------------  
  48. Neena  
  49.  
  50. /*--------9、哪些员工跟Hall(LAST_NAME)不在同一个部门。---------*/  
  51.  
  52. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES  
  53.   2  WHERE DEPARTMENT_ID NOT IN(  
  54.   3  SELECT DEPARTMENT_ID FROM HR.EMPLOYEES  
  55.   4  WHERE LAST_NAME = 'Hall');  
  56.    
  57. FIRST_NAME||''||LAST_NAME  
  58. ----------------------------------------------  
  59. Steven King  
  60. Neena Kochhar  
  61. Lex De Haan  
  62. Alexander Hunold  
  63. Bruce Ernst  
  64. David Austin  
  65. Valli Pataballa  
  66. Diana Lorentz  
  67. Nancy Greenberg  
  68. --...初始有72条数据  
  69.  
  70. --或者:  
  71.  
  72. SQL> SELECT e1.FIRST_NAME FROM EMPLOYEES e1,EMPLOYEES e2  
  73.   2  WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID(+)  
  74.   3  and e2.LAST_NAME(+) = 'Hall' 
  75.   4  and e2.LAST_NAME IS NULL;  
  76.  
  77. /*-------10、哪些员工跟William(FIRST_NAME)、Smith(LAST_NAME)做不一样的职位。--------*/  
  78.  
  79. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME FROM HR.EMPLOYEES  
  80.   2  WHERE JOB_ID <> (SELECT DISTINCT JOB_ID FROM EMPLOYEES  
  81.   3  WHERE FIRST_NAME = 'William' AND LAST_NAME = 'Smith');   
  82.  
  83. FIRST_NAME||''||LAST_NAME  
  84. ----------------------------------------------  
  85. Steven King  
  86. Neena Kochhar  
  87. Lex De Haan  
  88. Alexander Hunold  
  89. ----...初始有77条数据  
  90.  
  91. /*--------11、显示有提成的员工的信息:名字、提成、所在部门名称、所在地区的名称。---------*/  
  92.  
  93. SQL> SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME,  
  94.   2  E.COMMISSION_PCT,D.DEPARTMENT_NAME,L.CITY  
  95.   3  FROM HR.EMPLOYEES E,HR.DEPARTMENTS D,HR.LOCATIONS L  
  96.   4  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID  
  97.   5  AND D.LOCATION_ID = L.LOCATION_ID  
  98.   6  AND E.COMMISSION_PCT IS NOT NULL;  
  99.  
  100. /*--------12、显示Executive部门有哪些职位。---------*/  
  101. SQL> SELECT DISTINCT E.JOB_ID FROM HR.EMPLOYEES E,HR.DEPARTMENTS D  
  102.   2  WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID  
  103.   3  AND D.DEPARTMENT_NAME = 'Executive';  
  104.    
  105. JOB_ID  
  106. ----------  
  107. AD_PRES  
  108. AD_VP  

  1. /*--------13、整个公司中,最高工资和最低工资相差多少。---------*/  
  2. SQL> SELECT MAX(SALARY) - MIN(SALARY) FROM HR.EMPLOYEES;  
  3.    
  4. MAX(SALARY)-MIN(SALARY)  
  5. -----------------------  
  6.                   21900  
  7.  
  8. /*--------14、提成大于0 的人数。---------*/  
  9. SQL> SELECT COUNT(*) AS 提成大小0的人数 FROM HR.EMPLOYEES  
  10.   2  WHERE COMMISSION_PCT > 0;  
  11.    
  12.        提成大小0的人数  
  13. ---------------  
  14.              35  
  15. --或者  
  16.  
  17. SQL> SELECT COUNT(COMMISSION_PCT) AS 提成大小0的人数    
  18.   2   FROM HR.EMPLOYEES  
  19.   3   WHERE COMMISSION_PCT > 0;  
  20.        提成大小0的人数  
  21. ---------------  
  22.              35  
  23.  
  24. /*--------15、显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。---------*/  
  25.  
  26. SQL> SELECT MAX(NVL(SALARY,0)) AS 最高工资,  
  27.   2           MIN(NVL(SALARY,0)) AS 最低工资,  
  28.   3           SUM(NVL(SALARY,0)) AS 工资总和,  
  29.   4           ROUND(AVG(NVL(SALARY,0))) AS 平均工资  
  30.   5  FROM HR.EMPLOYEES;  
  31.  
  32.       最高工资       最低工资       工资总和       平均工资  
  33.       ----------    ----------      ----------     ---------  
  34.       24000          2100           698011         6523  
  35.  
  36. /*--------16、整个公司有多少个领导。---------*/  
  37. SQL> SELECT COUNT(DISTINCT(MANAGER_ID))  FROM HR.EMPLOYEES  
  38.    2  WHERE MANAGER_ID IS NOT NULL;  
  39.    
  40. COUNT(DISTINCT(MANAGER_ID))  
  41. ---------------------------  
  42.                          18  
  43.  
  44. /*--------17、列出在同一部门入职日期晚但工资高于其他同事的员工:  
  45. 名字、工资、入职日期。---------*/  
  46.  
  47. SQL> SELECT DISTINCT E1.FIRST_NAME || ' ' || E1.LAST_NAME AS 姓名,  
  48.   2         E1.SALARY AS 工资,E1.HIRE_DATE AS 入职日期  
  49.   3  FROM HR.EMPLOYEES E1,HR.EMPLOYEES E2  
  50.   4  WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID  
  51.   5  AND E1.HIRE_DATE > E2.HIRE_DATE  
  52.   6  AND E1.SALARY > E2.SALARY  
  53.   7  ORDER BY 工资 DESC;  
  54.  
  55. 姓名                                                   工资 入职日期  
  56. ---------------------------------------------- ---------- -----------  
  57. John Russell                                     14000.00 1996-10-1  
  58. Karen Partners                                   13500.00 1997-1-5  
  59. Alberto Errazuriz                                12000.00 1997-3-10  
  60. Nancy Greenberg                                  12000.00 1994-8-17  
  61. Lisa Ozer                                        11500.00 1997-3-11  
  62. Ellen Abel                                       11000.00 1996-5-11  
  63. Gerald Cambrault                                 11000.00 1999-10-15  
  64. Clara Vishney                                    10500.00 1997-11-11  
  65. Eleni Zlotkey                                    10500.00 2000-1-29  
  66. Harrison Bloom                                   10000.00 1998-3-23  
  67. Peter Tucker                                     10000.00 1997-1-30  
  68. Tayler Fox                                        9600.00 1998-1-24  
  69. Danielle Greene                                   9500.00 1999-3-19  
  70. David Bernstein                                   9500.00 1997-3-24  
  71. Peter Hall                                        9000.00 1997-8-20  
  72. Alyssa Hutton                                     8800.00 1997-3-19  
  73. Jonathon Taylor                                   8600.00 1998-3-24  
  74. Adam Fripp                                        8200.00 1997-4-10  
  75. Christopher Olsen                                 8000.00 1998-3-30  
  76. Jack Livingston                                   8000.00 1998-4-23   
  77. Matthew Weiss                                     8000.00 1996-7-18  
  78. Jose Manuel Urman                                 7800.00 1998-3-7  
  79. Nanette Cambrault                                 7500.00 1998-12-9  
  80. William Smith                                     7400.00 1999-2-23  
  81. Elizabeth Bates                                   7300.00 1999-3-24  
  82. Charles Johnson                                   7211.00 2000-1-4  
  83. Mattea Marvins                                    7200.00 2000-1-24  
  84. Shanta Vollman                                    6500.00 1997-10-10  
  85. Kevin Mourgos                                     5800.00 1999-11-16  
  86. Nandita Sarchand                                  4200.00 1996-1-27  
  87. Alexis Bull                                       4100.00 1997-2-20  
  88. Sarah Bell                                        4000.00 1996-2-4  
  89. Britney Everett                                   3900.00 1997-3-3  
  90. Kelly Chung                                       3800.00 1997-6-14  
  91. Jennifer Dilly                                    3600.00 1997-8-13  
  92. Julia Dellinger                                   3400.00 1998-6-24  
  93. Laura Bissot                                      3300.00 1997-8-20  
  94. Julia Nayer                                       3200.00 1997-7-16  
  95. Samuel McCain                                     3200.00 1998-7-1  
  96. Stephen Stiles                                    3200.00 1997-10-26  
  97. Winston Taylor                                    3200.00 1998-1-24   
  98. Alana Walsh                                       3100.00 1998-4-24  
  99. Jean Fleaur                                       3100.00 1998-2-23  
  100. Anthony Cabrio                                    3000.00 1999-2-7  
  101. Kevin Feeney                                      3000.00 1998-5-23  
  102. Michael Rogers                                    2900.00 1998-8-26  
  103. Shelli Baida                                      2900.00 1997-12-24  
  104. Timothy Gates                                     2900.00 1998-7-11  
  105. Girard Geoni                                      2800.00 2000-2-3  
  106. Mozhe Atkinson                                    2800.00 1997-10-30  
  107. Vance Jones                                       2800.00 1999-3-17  
  108. Irene Mikkilineni                                 2700.00 1998-9-28  
  109. John Seo                                          2700.00 1998-2-12  
  110. Donald OConnell                                   2600.00 1999-6-21  
  111. Douglas Grant                                     2600.00 2000-1-13  
  112. Randall Matos                                     2600.00 1998-3-15  
  113. Martha Sullivan                                   2500.00 1999-6-21  
  114. Randall Perkins                                   2500.00 1999-12-19  
  115. Ki Gee                                            2400.00 1999-12-12  
  116. Hazel Philtanker                                  2200.00 2000-2-6  
  117. Steven Markle                                     2200.00 2000-3-8  
  118.    
  119. 61 rows selected 

第四手

本篇数据查询属于复杂业务,难度比较高,请继续努力,通过我为大家设立的这个系列,循序渐进,只要你对每一篇,每一个试题都实践测试,认真练习。我相信你对常用、经典的、复杂的SQL已能熟能生巧,信手拈来!

本文使用ORACLE自带的人力资源(HR)实例数据,本文所用表结构如下:

表名:REGIONS

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
REGION_ID
NUMBER
 
 
 
 
 
2
REGION_NAME
VARCHAR2
25
 
 
 
 
 

表名:COUNTRIES

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
COUNTRY_ID
CHAR
2
 
 
 
 
2
COUNTRY_NAME
VARCHAR2
40
 
 
 
 
 
3
REGION_ID
NUMBER
 
 
 
 
 
 

表名:LOCATIONS

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
LOCATION_ID
NUMBER
4
0
 
 
 
2
STREET_ADDRESS
VARCHAR2
40
 
 
 
 
 
3
POSTAL_CODE
VARCHAR2
12
 
 
 
 
 
4
CITY
VARCHAR2
30
 
 
 
 
 
5
STATE_PROVINCE
VARCHAR2
25
 
 
 
 
 
6
COUNTRY_ID
CHAR
2
 
 
 
 
 

表名:DEPARTMENTS

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
DEPARTMENT_ID
NUMBER
4
0
 
 
 
2
DEPARTMENT_NAME
VARCHAR2
30
 
 
 
 
 
3
MANAGER_ID
NUMBER
6
0
 
 
 
 
4
LOCATION_ID
NUMBER
4
0
 
 
 
 

表名:JOBS

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
JOB_ID
VARCHAR2
10
 
 
 
 
2
JOB_TITLE
VARCHAR2
35
 
 
 
 
 
3
MIN_SALARY
NUMBER
6
0
 
 
 
 
4
MAX_SALARY
NUMBER
6
0
 
 
 
 

表名:EMPLOYEES

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
EMPLOYEE_ID
NUMBER
6
0
 
 
 
2
FIRST_NAME
VARCHAR2
20
 
 
 
 
 
3
LAST_NAME
VARCHAR2
25
 
 
 
 
 
4
EMAIL
VARCHAR2
25
 
 
 
 
 
5
PHONE_NUMBER
VARCHAR2
20
 
 
 
 
 
6
HIRE_DATE
DATE
7
 
 
 
 
 
7
JOB_ID
VARCHAR2
10
 
 
 
 
 
8
SALARY
NUMBER
8
2
 
 
 
 
9
COMMISSION_PCT
NUMBER
2
2
 
 
 
 
10
MANAGER_ID
NUMBER
6
0
 
 
 
 
11
DEPARTMENT_ID
NUMBER
4
0
 
 
 
 

ER图:

 

用SQL完成以下问题列表:

  1. /*---------------------------------------------  
  2. 1. 各个部门平均、最大、最小工资、人数,按照部门号升序排列。  
  3. 2. 各个部门中工资大于5000的员工人数。  
  4. 3. 各个部门平均工资和人数,按照部门名字升序排列。  
  5. 4. 列出每个部门中有同样工资的员工的统计信息,列出他们的部门号,工资,人数。  
  6. 5. 列出同部门中工资高于1000 的员工数量超过2 人的部门,显示部门名字、地区名称。  
  7. 6. 哪些员工的工资,高于整个公司的平均工资,列出员工的名字和工资(降序)。  
  8. 7. 哪些员工的工资,介于50号 和80号部门平均工资之间。  
  9. 8. 所在部门平均工资高于5000 的员工名字。  
  10. 9. 列出各个部门中工资最高的员工的信息:名字、部门号、工资。  
  11. 10. 最高的部门平均工资是多少。  
  12. ---------------------------------------------*/ 

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

  1. /*--------1、各个部门平均、最大、最小工资、人数,按照部门号升序排列。---------*/  
  2. SQL> SELECT DEPARTMENT_ID AS 部门号,AVG(SALARY) AS 平均工资  
  3.   2         ,MAX(SALARY) AS 最高工资,MIN(SALARY)  AS 最低工资  
  4.   3         ,COUNT(*) AS 人数  
  5.   4  FROM EMPLOYEES  
  6.   5  GROUP BY DEPARTMENT_ID  
  7.   6  ORDER BY DEPARTMENT_ID ASC;  
  8.    
  9.    部门号       平均工资       最高工资       最低工资         人数  
  10. ------         ----------         ----------         ----------         ----------  
  11.        10           4400               4400               4400                 1  
  12.     20          9500              13000           6000                  2  
  13.     30           4150              11000           2500                  6  
  14.     40           6500               6500               6500                  1  
  15.     50             3475.55555     8200               2100                 45  
  16.     60           5760               9000               4200                  5  
  17.     70          10000              10000              10000                  1  
  18.     80             8973.85294     14000           6100                 34  
  19.     90             21333.3333     24000              20000                  3  
  20.    100           8600              12000           6900                  6  
  21.    110          10150              12000           8300                  2  
  22.                 7000               7000               7000                  1  
  23.    
  24. 12 rows selected  
  25.  
  26. /*--------2、各个部门中工资大于5000的员工人数。---------*/  
  27. SQL> SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES  
  28.    2  WHERE SALARY > 5000  
  29.    3  GROUP BY DEPARTMENT_ID;  
  30.    
  31. DEPARTMENT_ID   COUNT(*)  
  32. ------------- ----------  
  33.            20          2  
  34.            30          1  
  35.            40          1  
  36.            50          5  
  37.            60          2  
  38.            70          1  
  39.            80         34  
  40.            90          3  
  41.           100          6  
  42.           110          2  
  43.                        1  
  44.    
  45. 11 rows selected  
  46.  
  47. /*--------3、各个部门平均工资和人数,按照部门名字升序排列。---------*/  
  48.  
  49. SQL> SELECT DPTNAME,AVG(SALARY),COUNT(*) FROM 
  50.   2         (SELECT 
  51.   3             (SELECT DEPT.DEPARTMENT_NAME FROM DEPARTMENTS DEPT  
  52.   4             WHERE DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID) DPTNAME,  
  53.   5             EMP.SALARY  
  54.   6  FROM EMPLOYEES EMP)  
  55.   7  GROUP BY DPTNAME  
  56.   8  ORDER BY DPTNAME;  
  57.    
  58. DPTNAME                        AVG(SALARY)   COUNT(*)  
  59. ------------------------------ ----------- ----------  
  60. Accounting                           10150          2  
  61. Administration                        4400          1  
  62. Executive                      21333.33333          3  
  63. Finance                               8600          6  
  64. Human Resources                       6500          1  
  65. IT                                    5760          5  
  66. Marketing                             9500          2  
  67. Public Relations                     10000          1  
  68. Purchasing                            4150          6  
  69. Sales                          8973.852941         34  
  70. Shipping                       3475.555555         45  
  71.                                       7000          1   
  72. 12 rows selected  
  73.  
  74. --或者--  
  75.  
  76. SQL> SELECT DEPT.DEPARTMENT_NAME,AVG(EMP.SALARY),COUNT(*)  
  77.    2  FROM EMPLOYEES EMP,DEPARTMENTS DEPT  
  78.    3  WHERE EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID  
  79.    4  GROUP BY DEPT.DEPARTMENT_NAME  
  80.    5  ORDER BY DEPT.DEPARTMENT_NAME;  
  81.    
  82. DEPARTMENT_NAME                AVG(EMP.SALARY)   COUNT(*)  
  83. ------------------------------ --------------- ----------  
  84. Accounting                               10150          2  
  85. Administration                            4400          1  
  86. Executive                      21333.333333333          3  
  87. Finance                                   8600          6  
  88. Human Resources                           6500          1  
  89. IT                                        5760          5  
  90. Marketing                                 9500          2  
  91. Public Relations                         10000          1  
  92. Purchasing                                4150          6  
  93. Sales                          8973.8529411764         34  
  94. Shipping                       3475.5555555555         45  
  95.    
  96. 11 rows selected  
  97. --可以看到,这种方式,对于部门号为空的没有统计出来  
  98.  
  99. /*--------4、列出每个部门中有同样工资的员工的统计信息,  
  100.   列出他们的部门号,工资,人数。---------*/  
  101.  
  102. SQL> SELECT EMP1.DEPARTMENT_ID,EMP1.SALARY,COUNT(*) CNT  
  103.    2  FROM   EMPLOYEES EMP1,EMPLOYEES EMP2  
  104.    3  WHERE  EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID AND 
  105.    4          EMP1.SALARY = EMP2.SALARY  
  106.    5          AND EMP1.EMPLOYEE_ID <> EMP2.EMPLOYEE_ID  
  107.    6  GROUP BY EMP1.DEPARTMENT_ID,EMP1.SALARY;  
  108.    
  109. DEPARTMENT_ID     SALARY        CNT  
  110. ------------- ---------- ----------  
  111.            50    2200.00          2  
  112.            50    2400.00          2  
  113.            50    2500.00         20  
  114.            50    2600.00          6  
  115.            50    2700.00          2  
  116.            50    2800.00          6  
  117.            50    2900.00          2  
  118.            50    3000.00          2  
  119.            50    3100.00          6  
  120.            50    3200.00         12  
  121.            50    3300.00          2  
  122.            50    3600.00          2  
  123.            60    4800.00          2  
  124.            80    7000.00          2  
  125.            80    7500.00          2  
  126.            80    8000.00          6  
  127.            80    9000.00          2  
  128.            80    9500.00          6  
  129.            80   10000.00          6  
  130.            80   10500.00          2   
  131.            80   11000.00          2  
  132.            90   20000.00          2  
  133.    
  134. 22 rows selected  
  135.  
  136. /*--------5、列出同部门中工资高于1000 的员工数量超过2 人的部门,  
  137. 显示部门名字、地区名称。---------*/  
  138.  
  139. SQL> SELECT D.DEPARTMENT_NAME,L.CITY,COUNT(*)  
  140.    2  FROM EMPLOYEES E,DEPARTMENTS D,LOCATIONS L  
  141.    3  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND 
  142.    4         D.LOCATION_ID   = L.LOCATION_ID    AND 
  143.    5         E.SALARY > 1000  
  144.    6  GROUP BY D.DEPARTMENT_NAME,L.CITY  
  145.    7  HAVING COUNT(*) > 2;  
  146.    
  147. DEPARTMENT_NAME                CITY                             COUNT(*)  
  148. ------------------------------ ------------------------------ ----------  
  149. IT                             Southlake                               5  
  150. Sales                          Oxford                                 34  
  151. Finance                        Seattle                                 6  
  152. Shipping                       South San Francisco                    45  
  153. Executive                      Seattle                                 3  
  154. Purchasing                     Seattle                                 6  
  155.    
  156. rows selected 

  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 

第五手

本篇数据查询属于复杂业务,难度比较高,请继续努力,通过我为大家设立的这个系列,循序渐进,只要你对每一篇,每一个试题都实践测试,认真练习。我相信你对常用、经典的、复杂的SQL已能熟能生巧,信手拈来!

本文使用ORACLE自带的人力资源(HR)实例数据,本文所用表结构如下:

表名:REGIONS

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
REGION_ID
NUMBER
 
 
 
 
 
2
REGION_NAME
VARCHAR2
25
 
 
 
 
 

 
表名:COUNTRIES

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
COUNTRY_ID
CHAR
2
 
 
 
 
2
COUNTRY_NAME
VARCHAR2
40
 
 
 
 
 
3
REGION_ID
NUMBER
 
 
 
 
 
 

表名:LOCATIONS

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
LOCATION_ID
NUMBER
4
0
 
 
 
2
STREET_ADDRESS
VARCHAR2
40
 
 
 
 
 
3
POSTAL_CODE
VARCHAR2
12
 
 
 
 
 
4
CITY
VARCHAR2
30
 
 
 
 
 
5
STATE_PROVINCE
VARCHAR2
25
 
 
 
 
 
6
COUNTRY_ID
CHAR
2
 
 
 
 
 

表名:DEPARTMENTS

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
DEPARTMENT_ID
NUMBER
4
0
 
 
 
2
DEPARTMENT_NAME
VARCHAR2
30
 
 
 
 
 
3
MANAGER_ID
NUMBER
6
0
 
 
 
 
4
LOCATION_ID
NUMBER
4
0
 
 
 
 

表名:JOBS

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
JOB_ID
VARCHAR2
10
 
 
 
 
2
JOB_TITLE
VARCHAR2
35
 
 
 
 
 
3
MIN_SALARY
NUMBER
6
0
 
 
 
 
4
MAX_SALARY
NUMBER
6
0
 
 
 
 

表名:EMPLOYEES

序号
列名
数据类型
长度
小数位
标识
主键
允许空
默认值
说明
1
EMPLOYEE_ID
NUMBER
6
0
 
 
 
2
FIRST_NAME
VARCHAR2
20
 
 
 
 
 
3
LAST_NAME
VARCHAR2
25
 
 
 
 
 
4
EMAIL
VARCHAR2
25
 
 
 
 
 
5
PHONE_NUMBER
VARCHAR2
20
 
 
 
 
 
6
HIRE_DATE
DATE
7
 
 
 
 
 
7
JOB_ID
VARCHAR2
10
 
 
 
 
 
8
SALARY
NUMBER
8
2
 
 
 
 
9
COMMISSION_PCT
NUMBER
2
2
 
 
 
 
10
MANAGER_ID
NUMBER
6
0
 
 
 
 
11
DEPARTMENT_ID
NUMBER
4
0
 
 
 
 

ER图:


用SQL完成以下问题列表:

  1. 哪些部门的人数比90 号部门的人数多。  
  2. Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁(非关联子查询)。  
  3. Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(非关联子查询)。  
  4. Den(FIRST_NAME)、Raphaely(LAST_NAME) 的领导是谁(关联子查询)。  
  5. Den(FIRST_NAME)、Raphaely(LAST_NAME) 领导谁(关联子查询)。  
  6. 列出在同一部门共事,入职日期晚但工资高于其他同事的员工:名字、工资、入职日期(关联子查询)。  
  7. 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(非关联子查询)。  
  8. 哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)不在同一个部门(关联子查询)。  
  9. Finance部门有哪些职位(非关联子查询)。  
  10. Finance部门有哪些职位(关联子查询)。  


 

  1. /*--------1、哪些部门的人数比90号部门的人数多。---------*/  
  2.  
  3. SQL> SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES  
  4.    2  GROUP BY DEPARTMENT_ID  
  5.    3  HAVING COUNT(*) >  
  6.    4         (SELECT COUNT(*) FROM EMPLOYEES  
  7.    5          WHERE DEPARTMENT_ID = 90  
  8.    6         );  
  9.    
  10. DEPARTMENT_ID   COUNT(*)  
  11. ------------- ----------  
  12.            30          6  
  13.            50         45  
  14.            60          5  
  15.            80         34  
  16.           100          6  
  17.  
  18. /*-------2、Den(FIRST_NAME)、Raphaely(LAST_NAME)的  
  19. 领导是谁(非关联子查询)。---------*/  
  20.  
  21. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  22.    2  FROM EMPLOYEES  
  23.    3  WHERE EMPLOYEE_ID =   
  24.    4        (SELECT MANAGER_ID FROM EMPLOYEES  
  25.    5         WHERE FIRST_NAME = 'Den' 
  26.    6         AND   LAST_NAME  = 'Raphaely' 
  27.    7        );  
  28.    
  29. FIRST_NAME||''||LAST_NAME  
  30. ----------------------------------------------  
  31. Steven King  
  32.  
  33. /*-------3、Den(FIRST_NAME)、Raphaely(LAST_NAME)领导谁(非关联子查询)。---------*/  
  34.  
  35. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  36.    2  FROM EMPLOYEES  
  37.    3  WHERE MANAGER_ID IN 
  38.    4        (SELECT EMPLOYEE_ID FROM EMPLOYEES  
  39.    5         WHERE FIRST_NAME = 'Den' 
  40.    6         AND   LAST_NAME  = 'Raphaely' 
  41.    7        );  
  42.    
  43. FIRST_NAME||''||LAST_NAME  
  44. ----------------------------------------------  
  45. Alexander Khoo  
  46. Shelli Baida  
  47. Sigal Tobias  
  48. Guy Himuro  
  49. Karen Colmenares  
  50.  
  51. --或者  
  52.  
  53. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  54.    2  FROM EMPLOYEES  
  55.    3  WHERE MANAGER_ID =  
  56.    4        (SELECT EMPLOYEE_ID FROM EMPLOYEES  
  57.    5         WHERE FIRST_NAME = 'Den' 
  58.    6         AND   LAST_NAME  = 'Raphaely' 
  59.    7        );  
  60.    
  61. FIRST_NAME||''||LAST_NAME  
  62. ----------------------------------------------  
  63. Alexander Khoo  
  64. Shelli Baida  
  65. Sigal Tobias  
  66. Guy Himuro  
  67. Karen Colmenares  
  68.  
  69. /*-------4、Den(FIRST_NAME)、Raphaely(LAST_NAME)的领导是谁(关联子查询)。---------*/  
  70.  
  71. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  72.    2  FROM EMPLOYEES EMP1  
  73.    3  WHERE EXISTS (  
  74.    4        SELECT 1 FROM EMPLOYEES EMP2  
  75.    5        WHERE FIRST_NAME = 'Den' 
  76.    6        AND LAST_NAME  = 'Raphaely' 
  77.    7        AND EMP2.MANAGER_ID = EMP1.EMPLOYEE_ID);  
  78.    
  79. FIRST_NAME||''||LAST_NAME  
  80. ----------------------------------------------  
  81. Steven King  
  82.  
  83. /*-------5、Den(FIRST_NAME)、Raphaely(LAST_NAME)领导谁(关联子查询)。---------*/  
  84.  
  85. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  86.    2  FROM EMPLOYEES EMP1  
  87.    3  WHERE EXISTS (  
  88.    4        SELECT 1 FROM EMPLOYEES EMP2  
  89.    5        WHERE FIRST_NAME = 'Den' 
  90.    6        AND LAST_NAME  = 'Raphaely' 
  91.    7        AND EMP2.EMPLOYEE_ID = EMP1.MANAGER_ID);   
  92.  
  93. FIRST_NAME||''||LAST_NAME  
  94. ----------------------------------------------  
  95. Alexander Khoo  
  96. Shelli Baida  
  97. Sigal Tobias  
  98. Guy Himuro  
  99. Karen Colmenares 


 

  1. /*-------6、列出在同一部门共事,入职日期晚但工资高于其他同事的员工:  
  2. 名字、工资、入职日期(关联子查询)。---------*/  
  3.  
  4. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME AS 姓名,  
  5.    2          SALARY AS 工资,HIRE_DATE AS 入职日期  
  6.    3  FROM EMPLOYEES EMP1  
  7.    4  WHERE EXISTS (  
  8.    5        SELECT 1 FROM EMPLOYEES EMP2  
  9.    6        WHERE EMP2.DEPARTMENT_ID = EMP1.DEPARTMENT_ID  
  10.   7        AND   EMP1.HIRE_DATE > EMP2.HIRE_DATE  
  11.    8        AND   EMP1.SALARY    > EMP2.SALARY  
  12.   9        );  
  13.  
  14. 姓名                                                   工资 入职日期  
  15. ---------------------------------------------- ---------- -----------  
  16. Nancy Greenberg                                  12000.00 1994-8-17  
  17. Jose Manuel Urman                                 7800.00 1998-3-7  
  18. Shelli Baida                                      2900.00 1997-12-24  
  19. Adam Fripp                                        8200.00 1997-4-10  
  20. Matthew Weiss                                     8000.00 1996-7-18  
  21. Jennifer Dilly                                    3600.00 1997-8-13  
  22. Julia Dellinger                                   3400.00 1998-6-24  
  23. Laura Bissot                                      3300.00 1997-8-20  
  24. Kevin Mourgos                                     5800.00 1999-11-16  
  25. Shanta Vollman                                    6500.00 1997-10-10  
  26. Vance Jones                                       2800.00 1999-3-17  
  27. Anthony Cabrio                                    3000.00 1999-2-7  
  28. Girard Geoni                                      2800.00 2000-2-3  
  29. Douglas Grant                                     2600.00 2000-1-13  
  30. Donald OConnell                                   2600.00 1999-6-21  
  31. Randall Perkins                                   2500.00 1999-12-19  
  32. Martha Sullivan                                   2500.00 1999-6-21  
  33. Kevin Feeney                                      3000.00 1998-5-23  
  34. Alana Walsh                                       3100.00 1998-4-24  
  35. Samuel McCain                                     3200.00 1998-7-1  
  36. Timothy Gates                                     2900.00 1998-7-11  
  37. Jean Fleaur                                       3100.00 1998-2-23  
  38. Winston Taylor                                    3200.00 1998-1-24  
  39. Michael Rogers                                    2900.00 1998-8-26  
  40. Britney Everett                                   3900.00 1997-3-3  
  41. Kelly Chung                                       3800.00 1997-6-14  
  42. Alexis Bull                                       4100.00 1997-2-20  
  43. Randall Matos                                     2600.00 1998-3-15  
  44. John Seo                                          2700.00 1998-2-12  
  45. Stephen Stiles                                    3200.00 1997-10-26  
  46. Mozhe Atkinson                                    2800.00 1997-10-30  
  47. Irene Mikkilineni                                 2700.00 1998-9-28  
  48. Julia Nayer                                       3200.00 1997-7-16  
  49. Hazel Philtanker                                  2200.00 2000-2-6  
  50. Ki Gee                                            2400.00 1999-12-12  
  51. Steven Markle                                     2200.00 2000-3-8  
  52. Sarah Bell                                        4000.00 1996-2-4  
  53. Nandita Sarchand                                  4200.00 1996-1-27  
  54. Lisa Ozer                                        11500.00 1997-3-11  
  55. Clara Vishney                                    10500.00 1997-11-11  
  56. Eleni Zlotkey                                    10500.00 2000-1-29   
  57. Gerald Cambrault                                 11000.00 1999-10-15  
  58. Alberto Errazuriz                                12000.00 1997-3-10  
  59. Tayler Fox                                        9600.00 1998-1-24  
  60. Harrison Bloom                                   10000.00 1998-3-23  
  61. Danielle Greene                                   9500.00 1999-3-19  
  62. Charles Johnson                                   7211.00 2000-1-4  
  63. Mattea Marvins                                    7200.00 2000-1-24  
  64. Ellen Abel                                       11000.00 1996-5-11  
  65. Karen Partners                                   13500.00 1997-1-5  
  66. John Russell                                     14000.00 1996-10-1  
  67. Peter Tucker                                     10000.00 1997-1-30  
  68. David Bernstein                                   9500.00 1997-3-24  
  69. Jonathon Taylor                                   8600.00 1998-3-24  
  70. Alyssa Hutton                                     8800.00 1997-3-19  
  71. Peter Hall                                        9000.00 1997-8-20  
  72. Jack Livingston                                   8000.00 1998-4-23  
  73. Christopher Olsen                                 8000.00 1998-3-30  
  74. Elizabeth Bates                                   7300.00 1999-3-24  
  75. William Smith                                     7400.00 1999-2-23  
  76. Nanette Cambrault                                 7500.00 1998-12-9  
  77.    
  78. 61 rows selected  
  79.  
  80. /*-------7、哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)  
  81. 不在同一个部门(非关联子查询)。---------*/  
  82.  
  83. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  84.   2  FROM EMPLOYEES  
  85.    3  WHERE DEPARTMENT_ID <>  
  86.   4        (SELECT DEPARTMENT_ID FROM EMPLOYEES  
  87.    5         WHERE FIRST_NAME = 'Den' 
  88.   6         AND LAST_NAME  = 'Raphaely' 
  89.   7        );  
  90.    
  91. FIRST_NAME||''||LAST_NAME  
  92. ----------------------------------------------  
  93. Steven King  
  94. Neena Kochhar  
  95. Lex De Haan  
  96. Alexander Hunold  
  97. Bruce Ernst  
  98. David Austin  
  99. Valli Pataballa  
  100. --等等  
  101.  
  102. --或者  
  103.  
  104. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  105.    2  FROM EMPLOYEES  
  106.    3  WHERE DEPARTMENT_ID NOT IN 
  107.    4        (SELECT DEPARTMENT_ID FROM EMPLOYEES  
  108.    5         WHERE FIRST_NAME = 'Den' 
  109.    6         AND LAST_NAME  = 'Raphaely' 
  110.    7        );  
  111.  
  112. /*-------8、哪些员工跟Den(FIRST_NAME)、Raphaely(LAST_NAME)  
  113. 不在同一个部门(关联子查询)。---------*/  
  114.  
  115. SQL> SELECT FIRST_NAME || ' ' || LAST_NAME  
  116.    2  FROM EMPLOYEES EMP1  
  117.    3  WHERE NOT EXISTS (  
  118.    4        SELECT 1 FROM EMPLOYEES EMP2  
  119.    5        WHERE EMP1.DEPARTMENT_ID = EMP2.DEPARTMENT_ID  
  120.    6        AND EMP2.FIRST_NAME =  'Den' 
  121.    7        AND EMP2.LAST_NAME  =  'Raphaely');  
  122.    
  123. FIRST_NAME||''||LAST_NAME  
  124. ----------------------------------------------  
  125. Kimberely Grant 
  126. Lex De Haan  
  127. Neena Kochhar  
  128. Steven King  
  129. Pat Fay  
  130. Michael Hartstein  
  131. Diana Lorentz  
  132. Valli Pataballa  
  133. --等等  
  134.  
  135. /*-------9、Finance部门有哪些职位(非关联子查询)。---------*/  
  136.  
  137. SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES  
  138.    2  WHERE DEPARTMENT_ID = (  
  139.    3        SELECT DEPARTMENT_ID FROM DEPARTMENTS  
  140.    4        WHERE DEPARTMENT_NAME = 'Finance');  
  141.    
  142. JOB_ID  
  143. ----------  
  144. FI_ACCOUNT  
  145. FI_MGR  
  146.  
  147. --或者  
  148.  
  149. SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES  
  150.    2  WHERE DEPARTMENT_ID IN (  
  151.    3        SELECT DEPARTMENT_ID FROM DEPARTMENTS  
  152.    4        WHERE DEPARTMENT_NAME = 'Finance');  
  153.    
  154. JOB_ID  
  155. ----------  
  156. FI_ACCOUNT  
  157. FI_MGR  
  158.  
  159.  
  160. /*-------10、Finance部门有哪些职位(关联子查询)。---------*/  
  161. SQL> SELECT DISTINCT JOB_ID FROM EMPLOYEES  
  162.    2  WHERE EXISTS(  
  163.    3        SELECT 1 FROM DEPARTMENTS  
  164.    4        WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID  
  165.    5        AND DEPARTMENTS.DEPARTMENT_NAME = 'Finance');  
  166.    
  167. JOB_ID  
  168. ----------  
  169. FI_ACCOUNT  
  170. FI_MGR 

原文链接:http://www.cnblogs.com/


阅读更多
个人分类: Other
上一篇Oracle官方文档结构说明(教你如何快速从官方文档中获取需要的知识)
下一篇Google搜索技巧
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭