Oracle6

446. 【上机】完成下面的 SQL 语句，并截图表示出结果。

i. 求 10 这个部门的所有员工
ii. 求名字是 KING 的这个人的信息
iii. 求薪水大于 2000 的员工信息
iv. 求部门不是 10 的员工信息
v. 求薪水在 800 和 1500 之间的员工信息(包含 800 和 1500)
vi. 列出 deptno 为 10 或者 30，并且工资>2000 的所有人
vii. 利用 in 操作符，列出部门 10 和 20 的人员
viii. 利用 like 操作符，查处名字中含有”H”的人员
ix. 分别利用 like 操作符和正则表达式，查处名字中含有”S”或者”M”的人员

01 select * from emp where deptno=10

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 09-6月-81       2450           
        10

      7839 KING       PRESIDENT            17-11月-8       5000           
                                           1                              
        10

      7934 MILLER     CLERK           7782 23-1月-82       1300           
        10


3 rows selected.

02 select * from emp where ename=’KING’;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7839 KING       PRESIDENT            17-11月-8       5000           
                                           1                              
        10

1 row selected.

03 select * from emp where sal>2000;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 02-4月-81       2975           
        20

      7698 BLAKE      MANAGER         7839 01-5月-81       2850           
        30

      7782 CLARK      MANAGER         7839 09-6月-81       2450           
        10

      7788 SCOTT      ANALYST         7566 19-4月-87       3000           
        20

      7839 KING       PRESIDENT            17-11月-8       5000           
                                           1                              
        10

      7902 FORD       ANALYST         7566 03-12月-8       3000           
                                           1                              
        20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------


6 rows selected.

04 select * from emp where deptno<>10

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-8        800           
                                           0                              
        20

      7499 ALLEN      SALESMAN        7698 20-2月-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-2月-81       1250        500
        30

      7566 JONES      MANAGER         7839 02-4月-81       2975           
        20

      7654 MARTIN     SALESMAN        7698 28-9月-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-5月-81       2850           
        30


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7788 SCOTT      ANALYST         7566 19-4月-87       3000           
        20

      7844 TURNER     SALESMAN        7698 08-9月-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-5月-87       1100           
        20

      7900 JAMES      CLERK           7698 03-12月-8        950           
                                           1                              
        30

      7902 FORD       ANALYST         7566 03-12月-8       3000           
                                           1                              
        20


11 rows selected.

05 select * from emp where sal between 800 and 1500;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-8        800           
                                           0                              
        20

      7521 WARD       SALESMAN        7698 22-2月-81       1250        500
        30

      7654 MARTIN     SALESMAN        7698 28-9月-81       1250       1400
        30

      7844 TURNER     SALESMAN        7698 08-9月-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-5月-87       1100           
        20

      7900 JAMES      CLERK           7698 03-12月-8        950           
                                           1                              
        30

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------

      7934 MILLER     CLERK           7782 23-1月-82       1300           
        10


7 rows selected.

06 select * from emp where (deptno=10 or deptno=30)and sal>2000;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7698 BLAKE      MANAGER         7839 01-5月-81       2850           
        30

      7782 CLARK      MANAGER         7839 09-6月-81       2450           
        10

      7839 KING       PRESIDENT            17-11月-8       5000           
                                           1                              
        10


3 rows selected.

07 select * from emp where deptno in (10 , 20);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-8        800           
                                           0                              
        20

      7566 JONES      MANAGER         7839 02-4月-81       2975           
        20

      7782 CLARK      MANAGER         7839 09-6月-81       2450           
        10

      7788 SCOTT      ANALYST         7566 19-4月-87       3000           
        20

      7839 KING       PRESIDENT            17-11月-8       5000           
                                           1                              
        10

      7876 ADAMS      CLERK           7788 23-5月-87       1100           
        20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------

      7902 FORD       ANALYST         7566 03-12月-8       3000           
                                           1                              
        20

      7934 MILLER     CLERK           7782 23-1月-82       1300           
        10


8 rows selected.

08 select * from emp where ename like ‘%H%’;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-8        800           
                                           0                              
        20

1 row selected.

09 select * from emp where ename like ‘%S%’ union select * from emp where ename like ‘%M%’

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-12月-8        800           
                                           0                              
        20

      7566 JONES      MANAGER         7839 02-4月-81       2975           
        20

      7654 MARTIN     SALESMAN        7698 28-9月-81       1250       1400
        30

      7788 SCOTT      ANALYST         7566 19-4月-87       3000           
        20

      7876 ADAMS      CLERK           7788 23-5月-87       1100           
        20

      7900 JAMES      CLERK           7698 03-12月-8        950           
                                           1                              
        30

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------

      7934 MILLER     CLERK           7782 23-1月-82       1300           
        10


7 rows selected.