备案控制台
探索云世界
开发者社区 数据库 文章 正文

《卸甲笔记》-限定查询与排序显示

2016-06-20 1429
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

1查看emp表中的数据量
Oracle

SQL> select count(*) from emp;

  COUNT(*)
----------
    14

PPAS

scott=# select count(*) from emp;
 count 
-------
    14
(1 row)

2 统计出基本工资高于1500元的全部雇员信息
Oracle

SQL> select * from emp where sal>1500;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30


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

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20

      7839 KING         PRESIDENT              17-NOV-81
      5000              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20


7 rows selected.

PPAS

scott=# select * from emp where sal>1500;
 empno | ename |    job    | mgr  |      hiredate      |   sal   |  comm  | deptno 
-------+-------+-----------+------+--------------------+---------+--------+--------
  7499 | ALLEN | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 |     30
  7566 | JONES | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |        |     20
  7698 | BLAKE | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |        |     30
  7782 | CLARK | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |        |     10
  7788 | SCOTT | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |        |     20
  7839 | KING  | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |        |     10
  7902 | FORD  | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |        |     20
(7 rows)

3查询出所有基本工资小于等于2000元的全部雇员信息
Oracle

SQL> select * from emp where sal<=2000;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30

      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7900 JAMES        CLERK             7698 03-DEC-81
       950              30

      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10


8 rows selected.

PPAS

scott=# select * from emp where sal<=2000;
 empno | ename  |   job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+----------+------+--------------------+---------+---------+--------
  7369 | SMITH  | CLERK    | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK    | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK    | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7934 | MILLER | CLERK    | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
(8 rows)

4根据之前的查询结果发现SMITH的工资 最低，所以现在希望可以取得SMITH的详细资料
Oracle

SQL> select * from emp where ename='SMITH';

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

PPAS

scott=# select * from emp where ename='SMITH';
 empno | ename |  job  | mgr  |      hiredate      |  sal   | comm | deptno 
-------+-------+-------+------+--------------------+--------+------+--------
  7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 |      |     20
(1 row)

5查询出所有业务员（CLERK）的雇员信息
Oracle

SQL>  select * from emp where job='CLERK';

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20

      7900 JAMES        CLERK             7698 03-DEC-81
       950              30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10

PPAS

scott=# select * from emp where job='CLERK';
 empno | ename  |  job  | mgr  |      hiredate      |   sal   | comm | deptno 
-------+--------+-------+------+--------------------+---------+------+--------
  7369 | SMITH  | CLERK | 7902 | 17-DEC-80 00:00:00 |  800.00 |      |     20
  7876 | ADAMS  | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 |      |     20
  7900 | JAMES  | CLERK | 7698 | 03-DEC-81 00:00:00 |  950.00 |      |     30
  7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 |      |     10
(4 rows)

6为了和其他职位的雇员对比，现在决定查询所有不是业务员的雇员信息
Oracle

SQL>  select * from emp where job<>'CLERK';

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10


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

      7839 KING         PRESIDENT              17-NOV-81
      5000              10

      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20


10 rows selected.
SQL> select * from emp where job!='CLERK';

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10


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

      7839 KING         PRESIDENT              17-NOV-81
      5000              10

      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20


10 rows selected.

PPAS

scott=# select * from emp where job<>'CLERK';
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+-----------+------+--------------------+---------+---------+--------
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
(10 rows)
scott=# select * from emp where job!='CLERK';
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+-----------+------+--------------------+---------+---------+--------
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
(10 rows)

7查询出工资范围在1500~3000（包含1500和3000）元的全部雇员信息
Oracle

SQL>  select * from emp where sal>=1500 and sal<=3000;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30


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

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20

      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20


7 rows selected.

PPAS

scott=# select * from emp where sal>=1500 and sal<=3000;
 empno | ename  |   job    | mgr  |      hiredate      |   sal   |  comm  | deptno 
-------+--------+----------+------+--------------------+---------+--------+--------
  7499 | ALLEN  | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 |     30
  7566 | JONES  | MANAGER  | 7839 | 02-APR-81 00:00:00 | 2975.00 |        |     20
  7698 | BLAKE  | MANAGER  | 7839 | 01-MAY-81 00:00:00 | 2850.00 |        |     30
  7782 | CLARK  | MANAGER  | 7839 | 09-JUN-81 00:00:00 | 2450.00 |        |     10
  7788 | SCOTT  | ANALYST  | 7566 | 19-APR-87 00:00:00 | 3000.00 |        |     20
  7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 |   0.00 |     30
  7902 | FORD   | ANALYST  | 7566 | 03-DEC-81 00:00:00 | 3000.00 |        |     20
(7 rows)

8查询职位是销售人员，并且基本工资高于1200元的所有雇员信息
Oracle

SQL> select * from emp where job='SALESMAN' and sal>1200;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

PPAS

scott=# select * from emp where job='SALESMAN' and sal>1200;
 empno | ename  |   job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+----------+------+--------------------+---------+---------+--------
  7499 | ALLEN  | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
(4 rows)

9要求查询出10部门中的经理或者20部门中的业务员的信息
Oralce

SQL> select * from emp
  2   where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20

PPAS

scott=# select * from emp
scott-# where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');
 empno | ename |   job   | mgr  |      hiredate      |   sal   | comm | deptno 
-------+-------+---------+------+--------------------+---------+------+--------
  7369 | SMITH | CLERK   | 7902 | 17-DEC-80 00:00:00 |  800.00 |      |     20
  7782 | CLARK | MANAGER | 7839 | 09-JUN-81 00:00:00 | 2450.00 |      |     10
  7876 | ADAMS | CLERK   | 7788 | 23-MAY-87 00:00:00 | 1100.00 |      |     20
(3 rows)

10查询不是业务员且基本工资大于2000元的全部雇员信息
Oracle

SQL> select * from emp
  2   where job !='CLERK' and sal>2000;

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

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10


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

      7839 KING         PRESIDENT              17-NOV-81
      5000              10

      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20


6 rows selected.

SQL> select * from emp
  2    where  job <>'CLERK' and  sal>2000;

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

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10


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

      7839 KING         PRESIDENT              17-NOV-81
      5000              10

      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20


6 rows selected.

SQL> select * from emp
  2   where not(job='CLERK' or sal<=2000);

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

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10


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

      7839 KING         PRESIDENT              17-NOV-81
      5000              10

      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20


6 rows selected.

PPAS

scott=# select * from emp
scott-# where job !='CLERK' and sal>2000;
 empno | ename |    job    | mgr  |      hiredate      |   sal   | comm | deptno 
-------+-------+-----------+------+--------------------+---------+------+--------
  7566 | JONES | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |      |     20
  7698 | BLAKE | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |      |     30
  7782 | CLARK | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |      |     10
  7788 | SCOTT | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |      |     20
  7839 | KING  | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |      |     10
  7902 | FORD  | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |      |     20
(6 rows)

scott=# select * from emp 
scott-# where  job <>'CLERK' and  sal>2000;
 empno | ename |    job    | mgr  |      hiredate      |   sal   | comm | deptno 
-------+-------+-----------+------+--------------------+---------+------+--------
  7566 | JONES | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |      |     20
  7698 | BLAKE | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |      |     30
  7782 | CLARK | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |      |     10
  7788 | SCOTT | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |      |     20
  7839 | KING  | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |      |     10
  7902 | FORD  | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |      |     20
(6 rows)

scott=# select * from emp
scott-# where not(job='CLERK' or sal<=2000);
 empno | ename |    job    | mgr  |      hiredate      |   sal   | comm | deptno 
-------+-------+-----------+------+--------------------+---------+------+--------
  7566 | JONES | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |      |     20
  7698 | BLAKE | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |      |     30
  7782 | CLARK | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |      |     10
  7788 | SCOTT | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |      |     20
  7839 | KING  | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |      |     10
  7902 | FORD  | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |      |     20
(6 rows)

范围查询：BETWEEN 最小值 AND 最大值

11使用between … and 操作符查询出工资范围在1500~3000（包括1500和3000元的全部雇员信息）
Oracle

SQL> select * from emp where sal between 1500 and 3000;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30


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

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20

      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20
7 rows selected.

PPAS

scott=# select * from emp where sal between 1500 and 3000;
 empno | ename  |   job    | mgr  |      hiredate      |   sal   |  comm  | deptno 
-------+--------+----------+------+--------------------+---------+--------+--------
  7499 | ALLEN  | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 |     30
  7566 | JONES  | MANAGER  | 7839 | 02-APR-81 00:00:00 | 2975.00 |        |     20
  7698 | BLAKE  | MANAGER  | 7839 | 01-MAY-81 00:00:00 | 2850.00 |        |     30
  7782 | CLARK  | MANAGER  | 7839 | 09-JUN-81 00:00:00 | 2450.00 |        |     10
  7788 | SCOTT  | ANALYST  | 7566 | 19-APR-87 00:00:00 | 3000.00 |        |     20
  7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 |   0.00 |     30
  7902 | FORD   | ANALYST  | 7566 | 03-DEC-81 00:00:00 | 3000.00 |        |     20
(7 rows)

12查询出在1981年雇佣的全部雇员信息
Oracle

SQL> select * from emp where hiredate between '01-Jan-1981' and '31-Dec-1981';

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7839 KING         PRESIDENT              17-NOV-81
      5000              10

      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

      7900 JAMES        CLERK             7698 03-DEC-81
       950              30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20


10 rows selected.

PPAS

scott=# select * from emp where hiredate between '01-1-1981' and '31-12-1981';
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+-----------+------+--------------------+---------+---------+--------
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
(10 rows)

判断内容是否为null:IS NULL 、IS NOT NULL
13 使用==进行NULL比较
Oracle

SQL> select * from emp where  comm=null and empno=7369;

no rows selected

PPAS

scott=# select * from emp where  comm=null and empno=7369;
 empno | ename | job | mgr | hiredate | sal | comm | deptno 
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

14查询出所有领取佣金的雇员的完整信息
Oracle

SQL>  select * from emp where comm is not null;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

SQL>  select * from emp where not comm is null;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

PPAS

scott=# select * from emp where comm is not null;
 empno | ename  |   job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+----------+------+--------------------+---------+---------+--------
  7499 | ALLEN  | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
(4 rows)

scott=# select * from emp where not comm is null;
 empno | ename  |   job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+----------+------+--------------------+---------+---------+--------
  7499 | ALLEN  | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
(4 rows)

15 查询所有不领取佣金的雇员的完整信息
Oracle

SQL> select * from emp where comm is null;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30


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

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20

      7839 KING         PRESIDENT              17-NOV-81
      5000              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20

      7900 JAMES        CLERK             7698 03-DEC-81
       950              30

      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10


10 rows selected.

PPAS

scott=# select * from emp where comm is null; 
 empno | ename  |    job    | mgr  |      hiredate      |   sal   | comm | deptno 
-------+--------+-----------+------+--------------------+---------+------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |      |     20
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |      |     20
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |      |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |      |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |      |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |      |     10
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |      |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |      |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |      |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |      |     10
(10 rows)

16列出所有不领取奖金，同时基本工资大于2000元的全部雇员信息
Oracle

SQL> select * from emp where comm is null and sal>2000;

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

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10


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

      7839 KING         PRESIDENT              17-NOV-81
      5000              10

      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20


6 rows selected.

PPAS

scott=# select * from emp where comm is null and sal>2000;
 empno | ename |    job    | mgr  |      hiredate      |   sal   | comm | deptno 
-------+-------+-----------+------+--------------------+---------+------+--------
  7566 | JONES | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |      |     20
  7698 | BLAKE | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |      |     30
  7782 | CLARK | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |      |     10
  7788 | SCOTT | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |      |     20
  7839 | KING  | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |      |     10
  7902 | FORD  | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |      |     20
(6 rows)

17 查找不收取佣金 或 收取的佣金低于100元的员工
Oracle

SQL>  select * from emp where comm is null or comm<100;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30


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

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20

      7839 KING         PRESIDENT              17-NOV-81
      5000              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20

      7900 JAMES        CLERK             7698 03-DEC-81
       950              30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20

      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10


11 rows selected.

PPAS

scott=# select * from emp where comm is null or comm<100;
 empno | ename  |    job    | mgr  |      hiredate      |   sal   | comm | deptno 
-------+--------+-----------+------+--------------------+---------+------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |      |     20
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |      |     20
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |      |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |      |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |      |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |      |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 | 0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |      |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |      |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |      |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |      |     10
(11 rows)

18查找收取佣金的员工的不同工作
Oracle

SQL>  select distinct job from emp where comm is not null;

JOB
---------
SALESMAN

PPAS

scott=# select distinct job from emp where comm is not null;
   job    
----------
 SALESMAN
(1 row)

列表范围查找：IN 、NOT IN
19查询出雇员编号是7369、7788/7566的雇员信息
Oracle

SQL>  select * from emp where empno=7369 or empno=7788 or empno=7566;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20

PPAS

scott=# select * from emp where empno=7369 or empno=7788 or empno=7566;
 empno | ename |   job   | mgr  |      hiredate      |   sal   | comm | deptno 
-------+-------+---------+------+--------------------+---------+------+--------
  7369 | SMITH | CLERK   | 7902 | 17-DEC-80 00:00:00 |  800.00 |      |     20
  7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 |      |     20
  7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 |      |     20
(3 rows)

20 通过IN操作符指定查询范围
Oracle

SQL>  select * from emp where empno IN(7369,7788,7566);

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20

PPAS

scott=# select * from emp where empno IN(7369,7788,7566);
 empno | ename |   job   | mgr  |      hiredate      |   sal   | comm | deptno 
-------+-------+---------+------+--------------------+---------+------+--------
  7369 | SMITH | CLERK   | 7902 | 17-DEC-80 00:00:00 |  800.00 |      |     20
  7566 | JONES | MANAGER | 7839 | 02-APR-81 00:00:00 | 2975.00 |      |     20
  7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 |      |     20
(3 rows)

21查询除了7369、7788、7566之外的雇员信息
Oracle

SQL> select * from emp where empno NOT IN(7369,7788,7566);

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30


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

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10

      7839 KING         PRESIDENT              17-NOV-81
      5000              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20

      7900 JAMES        CLERK             7698 03-DEC-81
       950              30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20

      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10


11 rows selected.

PPAS

scott=# select * from emp where empno NOT IN(7369,7788,7566);
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+-----------+------+--------------------+---------+---------+--------
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
(11 rows)

注意：在使用NOT IN操作符时列表不能有NULL
22在使用NOT IN操作符中 设置 null
Oracle

SQL> select * from emp where empno NOT IN(7369,7788,null);

no rows selected

PPAS

scott=# select * from emp where empno NOT IN(7369,7788,null);
 empno | ename | job | mgr | hiredate | sal | comm | deptno 
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

模糊查询：LIKE、NOT LIKE

23查询出雇员姓名是以S开头的全部雇员信息
Oracle

SQL> select * from emp where ename like 'S%';

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20

PPAS

scott=# select * from emp where ename like 'S%';
 empno | ename |   job   | mgr  |      hiredate      |   sal   | comm | deptno 
-------+-------+---------+------+--------------------+---------+------+--------
  7369 | SMITH | CLERK   | 7902 | 17-DEC-80 00:00:00 |  800.00 |      |     20
  7788 | SCOTT | ANALYST | 7566 | 19-APR-87 00:00:00 | 3000.00 |      |     20
(2 rows)

24查询姓名的第2个字母是M的全部雇员信息
Oracle

SQL> select * from emp where ename like '_M%';

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

PPAS

scott=# select * from emp where ename like '_M%';
 empno | ename |  job  | mgr  |      hiredate      |  sal   | comm | deptno 
-------+-------+-------+------+--------------------+--------+------+--------
  7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 |      |     20
(1 row)

25查询姓名中任意位置包含字母F的雇员信息
Oracle

SQL> select * from emp where ename like '%F%';

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20

PPAS

scott=# select * from emp where ename like '%F%';
 empno | ename |   job   | mgr  |      hiredate      |   sal   | comm | deptno 
-------+-------+---------+------+--------------------+---------+------+--------
  7902 | FORD  | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 |      |     20
(1 row)

26查询姓名长度为6或者超过6的雇员信息
Oracle

SQL> select * from emp where ename like '______%';

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30

      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10

PPAS

scott=# select * from emp where ename like '______%';
 empno | ename  |   job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+----------+------+--------------------+---------+---------+--------
  7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7934 | MILLER | CLERK    | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
(3 rows)

27查询出基本工资中包含 1或者在81年雇佣的全部雇员信息
Oracle

SQL> select * from emp where sal like '%1%' or hiredate like '%81%';

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7839 KING         PRESIDENT              17-NOV-81
      5000              10

      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7900 JAMES        CLERK             7698 03-DEC-81
       950              30

      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20

      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10


12 rows selected.

PPAS

scott=# select * from emp where sal like '%1%' or hiredate like '%81%';
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+-----------+------+--------------------+---------+---------+--------
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
(12 rows)

28不设置查询关键字标识查询全部
Oracle

SQL> select * from emp where empno like '%%' or ename like '%%' or job like '%%'    
  2   or hiredate like '%%' or sal like '%%' or comm like '%%';

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30


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

      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30


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

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20

      7839 KING         PRESIDENT              17-NOV-81
      5000              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20

      7900 JAMES        CLERK             7698 03-DEC-81
       950              30


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20

      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10
14 rows selected.

PPAS

scott=# select * from emp where empno like '%%' or ename like '%%' or job like '%%'                                                  
scott-# or hiredate like '%%' or sal like '%%' or comm like '%%';
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
(14 rows)

29找出部门10中所有经理（MANAGER）,部门20中所有业务员（CLERK），既不是经理又不是业务员但薪金大于2000元的所有员工的详细资料，并且要求 这些雇员的姓名中包含字母S或字母K
Oracle

SQL> select * from emp
  2  where ((deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK')
  3  or (job not in ('MANAGER','CLERK') and sal > 2000))
  4  and (ename like '%S%' or ename like '%K%');

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7839 KING         PRESIDENT              17-NOV-81
      5000              10

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20

PPAS

scott=# select * from emp
scott-# where ((deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK')
scott(# or (job not in ('MANAGER','CLERK') and sal > 2000))
scott-# and (ename like '%S%' or ename like '%K%');
 empno | ename |    job    | mgr  |      hiredate      |   sal   | comm | deptno 
-------+-------+-----------+------+--------------------+---------+------+--------
  7369 | SMITH | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |      |     20
  7782 | CLARK | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |      |     10
  7788 | SCOTT | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |      |     20
  7839 | KING  | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |      |     10
  7876 | ADAMS | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |      |     20
(5 rows)

对结果排序
30 查询员工的完整信息，并且按照级别工资由高到低进行排序
Oracle

SQL> select * from emp order by sal desc;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7839 KING         PRESIDENT              17-NOV-81
      5000              10

      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20


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

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7900 JAMES        CLERK             7698 03-DEC-81
       950              30

      7369 SMITH        CLERK             7902 17-DEC-80
       800              20


14 rows selected.

PPAS

scott=# select * from emp order by sal desc;
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+-----------+------+--------------------+---------+---------+--------
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
(14 rows)

31利用序号设置排序列
Oracle

SQL>  select empno,ename,sal,job from emp order by 3 desc;

     EMPNO ENAME               SAL JOB
---------- -------------------- ---------- ------------------
      7839 KING               5000 PRESIDENT
      7902 FORD               3000 ANALYST
      7788 SCOTT              3000 ANALYST
      7566 JONES              2975 MANAGER
      7698 BLAKE              2850 MANAGER
      7782 CLARK              2450 MANAGER
      7499 ALLEN              1600 SALESMAN
      7844 TURNER              1500 SALESMAN
      7934 MILLER              1300 CLERK
      7521 WARD               1250 SALESMAN
      7654 MARTIN              1250 SALESMAN

     EMPNO ENAME               SAL JOB
---------- -------------------- ---------- ------------------
      7876 ADAMS              1100 CLERK
      7900 JAMES               950 CLERK
      7369 SMITH               800 CLERK

14 rows selected.

PPAS

scott=# select empno,ename,sal,job from emp order by 3 desc;
 empno | ename  |   sal   |    job    
-------+--------+---------+-----------
  7839 | KING   | 5000.00 | PRESIDENT
  7902 | FORD   | 3000.00 | ANALYST
  7788 | SCOTT  | 3000.00 | ANALYST
  7566 | JONES  | 2975.00 | MANAGER
  7698 | BLAKE  | 2850.00 | MANAGER
  7782 | CLARK  | 2450.00 | MANAGER
  7499 | ALLEN  | 1600.00 | SALESMAN
  7844 | TURNER | 1500.00 | SALESMAN
  7934 | MILLER | 1300.00 | CLERK
  7654 | MARTIN | 1250.00 | SALESMAN
  7521 | WARD   | 1250.00 | SALESMAN
  7876 | ADAMS  | 1100.00 | CLERK
  7900 | JAMES  |  950.00 | CLERK
  7369 | SMITH  |  800.00 | CLERK
(14 rows)

32按照级别工资由低到高排序
Oracle

SQL>  select * from emp order by sal;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7900 JAMES        CLERK             7698 03-DEC-81
       950              30

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30

      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10


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

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20

      7839 KING         PRESIDENT              17-NOV-81
      5000              10
14 rows selected.
SQL> select * from emp order by sal asc;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7900 JAMES        CLERK             7698 03-DEC-81
       950              30

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30

      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10


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

      7566 JONES        MANAGER          7839 02-APR-81
      2975              20

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20

      7839 KING         PRESIDENT              17-NOV-81
      5000              10
14 rows selected.

PPAS

scott=# select * from emp order by sal;
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
(14 rows)
scott=# select * from emp order by sal asc;
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
(14 rows)

33查询出所有业务员（CLERK）的详细资料，并且按照基本工资由低到高排序
Oracle

SQL>  select * from emp where job='CLERK' order by sal;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7369 SMITH        CLERK             7902 17-DEC-80
       800              20

      7900 JAMES        CLERK             7698 03-DEC-81
       950              30

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10

PPAS

scott=# select * from emp where job='CLERK' order by sal;
 empno | ename  |  job  | mgr  |      hiredate      |   sal   | comm | deptno 
-------+--------+-------+------+--------------------+---------+------+--------
  7369 | SMITH  | CLERK | 7902 | 17-DEC-80 00:00:00 |  800.00 |      |     20
  7900 | JAMES  | CLERK | 7698 | 03-DEC-81 00:00:00 |  950.00 |      |     30
  7876 | ADAMS  | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 |      |     20
  7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 |      |     10
(4 rows)

34查询出所有雇员信息，要求按照基本工资由高到低排序，如果工资相等则按照雇佣日期由早到晚进行排序
Oracle

SQL> select * from emp order by sal desc,hiredate  asc;

     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7839 KING         PRESIDENT              17-NOV-81
      5000              10

      7902 FORD         ANALYST          7566 03-DEC-81
      3000              20

      7788 SCOTT        ANALYST          7566 19-APR-87
      3000              20


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

      7698 BLAKE        MANAGER          7839 01-MAY-81
      2850              30

      7782 CLARK        MANAGER          7839 09-JUN-81
      2450              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7499 ALLEN        SALESMAN         7698 20-FEB-81
      1600      300          30

      7844 TURNER        SALESMAN         7698 08-SEP-81
      1500        0          30

      7934 MILLER        CLERK             7782 23-JAN-82
      1300              10


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7521 WARD         SALESMAN         7698 22-FEB-81
      1250      500          30

      7654 MARTIN        SALESMAN         7698 28-SEP-81
      1250     1400          30

      7876 ADAMS        CLERK             7788 23-MAY-87
      1100              20


     EMPNO ENAME        JOB              MGR HIREDATE
---------- -------------------- ------------------ ---------- ------------
       SAL     COMM      DEPTNO
---------- ---------- ----------
      7900 JAMES        CLERK             7698 03-DEC-81
       950              30

      7369 SMITH        CLERK             7902 17-DEC-80
       800              20
14 rows selected.

PPAS

scott=# select * from emp order by sal desc,hiredate  asc;
 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno 
-------+--------+-----------+------+--------------------+---------+---------+--------
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
(14 rows)
文章标签：
SQL
关系型数据库
Oracle
宋跃杰
目录
相关文章
bbpp
|
14天前
|
存储 关系型数据库 索引
10. 在一个非主键字段上创建了索引, 想要根据该字段查询到数据, 需要查询几次 ?
在非主键字段上创建索引，查询数据通常需两次。对于MyISAM，先通过索引找到数据行指针，再获取数据；而InnoDB则先找主键ID，再从主键索引中查找数据。
bbpp
11 0
HannYang
|
11月前
|
图计算 C++
C/C++每日一练(20230518) 表列序号、移除元素、接雨水
C/C++每日一练(20230518) 表列序号、移除元素、接雨水
HannYang
54 0
fenggbinn
|
前端开发 数据库
FineReport中使用一个搜索框查询数据库中多列值返回一列值：使用union函数
前端使用一个查询框（搜索框）查询数据库中多列值，这里使用数据库的union函数进行实现
fenggbinn
173 0
虫无涯
|
关系型数据库 MySQL 数据库
MySql基础-笔记6 -排序、分组、连接的使用、NULL值处理
MySql基础-笔记6 -排序、分组、连接的使用、NULL值处理
虫无涯
166 0
MySql基础-笔记6 -排序、分组、连接的使用、NULL值处理
一身仙气
|
SQL 关系型数据库 MySQL
select _ from t_student where class between 200 and 300需要执行几次索引树的搜索操作，会扫描多少行
select _ from t_student where class between 200 and 300需要执行几次索引树的搜索操作，会扫描多少行
一身仙气
117 1
小博测试成长之路
|
Python
测试编程笔试题No.1-输入表格列的英文名称，返回其对应的数字序号
同一个字母，在不同的位置，对应的值不一样，字母A在个位对应的是1，在十位对应的是26 ，因此，联想到了进制的移位运算和进制之间的转换。
小博测试成长之路
129 0
测试编程笔试题No.1-输入表格列的英文名称，返回其对应的数字序号
Kam
|
Java
枚举去除大量if-else if -else优化记录
java中去除大量if-else
Kam
318 1
一个处女座的程序猿
|
Python
Python编程语言学习：判断两个列表是否对应完全相等(巧解输出是一摸一样的列表数据，但就是不相等)
Python编程语言学习：判断两个列表是否对应完全相等(巧解输出是一摸一样的列表数据，但就是不相等)
一个处女座的程序猿
290 0
小周sir
|
小程序 JavaScript API
小程序实现索引列表排序
小程序实现索引列表排序
小周sir
354 0
小周sir
|
存储 小程序
小程序实现搜索历史记录，去重搜索字段以及限制展示字段数量
小程序实现搜索历史记录，去重搜索字段以及限制展示字段数量
小周sir
1347 0

热门文章

最新文章

  • 1
    疑犯追踪第一季/全集Person Of Interest迅雷下载
  • 2
    【直播系列之一】1篇文章看懂峰值带宽、流量、转码、连麦、截图五大直播计费方式
  • 3
    理解事务的4种隔离级别
  • 4
    阿里云网站域名备案流程全过程讲解(图文)
  • 5
    在服务器的raid1中安装windows server系统（踩坑记录）
  • 6
    如何用Vue实现简易的富文本编辑器，并支持Markdown语法
  • 7
    怎么才能快速提高小程序留存率！
  • 8
    程序员请放下浮躁的心
  • 9
    如何解决域中普通用户只限于10台客户机加入域的数量问题
  • 10
    博客园模板 样式优化
  • 1
    未来技术纵横谈：区块链、物联网与虚拟现实的融合与创新
    37
  • 2
    软件体系结构 - 缓存技术（9）缓存穿透
    32
  • 3
    提升Android应用性能的实用技巧
    35
  • 4
    工具变量法(两阶段最小二乘法2SLS)线性模型分析人均食品消费时间序列数据和回归诊断（下）
    39
  • 5
    软件体系结构 - 缓存技术（8）缓存雪崩
    39
  • 6
    软件体系结构 - 缓存技术（7）Redis持久化方法
    39
  • 7
    Python用KShape对时间序列进行聚类和肘方法确定最优聚类数k可视化
    30
  • 8
    软件体系结构 - 缓存技术（6）淘汰策略
    35
  • 9
    基于R语言股票市场收益的统计可视化分析
    38
  • 10
    软件体系结构 - 数据分片（2）一致性哈希分片
    36

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）