AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

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

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
宋跃杰
目录
相关文章
yma16
|
7月前
|
关系型数据库 MySQL
Mysql语句_查询数据百分比、人员年龄、数据排序、添加查询时的列属性、合并查询结果
Mysql语句_查询数据百分比、人员年龄、数据排序、添加查询时的列属性、合并查询结果
yma16
45 0
zhaokk
|
SQL 存储 运维
order by 字段到底要不要加索引?[大坑]
order by 字段到底要不要加索引?[大坑]
zhaokk
202 0
order by 字段到底要不要加索引?[大坑]
虫无涯
|
关系型数据库 MySQL 数据库
MySql基础-笔记6 -排序、分组、连接的使用、NULL值处理
MySql基础-笔记6 -排序、分组、连接的使用、NULL值处理
虫无涯
198 0
MySql基础-笔记6 -排序、分组、连接的使用、NULL值处理
带你读小助手
|
自然语言处理 搜索推荐 开发者
高级查询（数组查询和组合查询）| 学习笔记
快速学习高级查询（数组查询和组合查询）。
带你读小助手
161 0
高级查询（数组查询和组合查询）| 学习笔记
TIkitianya
|
存储
【每日一题Day90】LC1814统计一个数组中好对子的数目 | 哈希表
思路：如果两个数满足好对子，那么这两个数反转后的变化量相同。因此可以使用哈希表存放反转后的变化量及其次数count，该变化量存在的所有好对子个数为count∗(count−1)/2
TIkitianya
75 0
云课程笔记
|
自然语言处理 算法 关系型数据库
常用查询算法（顺序扫描法）|学习笔记
快速学习常用查询算法（顺序扫描法）
云课程笔记
300 0
后端从入门到精通
|
SQL 搜索推荐 关系型数据库
B+树索引使用(8)排序使用及其注意事项（二十)
B+树索引使用(8)排序使用及其注意事项（二十)
后端从入门到精通
139 0
小目标青年
【硬着头皮】 你在筛选List里面的数据么？
【硬着头皮】 你在筛选List里面的数据么？
小目标青年
102 0
【硬着头皮】 你在筛选List里面的数据么？
学堂小助手
|
分布式计算 Hadoop 开发者
GroupingComparator 分组（辅助排序）| 学习笔记
快速学习 GroupingComparator 分组（辅助排序）
学堂小助手
148 0
GroupingComparator 分组（辅助排序）| 学习笔记
Michael阿明
|
C++
LeetCode 2070. 每一个查询的最大美丽值（离线查询+排序+优先队列）
LeetCode 2070. 每一个查询的最大美丽值（离线查询+排序+优先队列）
Michael阿明
206 0

热门文章

最新文章

  • 1
    Hadoop迁移MaxCompute神器之DataX-On-Hadoop使用指南
  • 2
    Nginx的启动、停止与重启
  • 3
    Hive之数据倾斜的原因和解决方法
  • 4
    开发人员各级岗位胜任力模型
  • 5
    Node.js 中流操作实践
  • 6
    传奇龙版地图查看器
  • 7
    调整DC的网络对网络是否会有影响?
  • 8
    How to maintain Oracle10g Recyclebin?
  • 9
    你知道自己感染了恶意软件吗？
  • 10
    Groovy 快速入门
  • 1
    Python学习的自我理解和想法（9）
    90
  • 2
    Kimi 上线视觉思考模型，K1 系列强化学习模型正式开放，无需借助外部 OCR 处理图像与文本进行思考并回答
    103
  • 3
    CosyVoice 2.0：阿里开源升级版语音生成大模型，支持多语言和跨语言语音合成，提升发音和音色等的准确性
    84
  • 4
    Megrez-3B-Omni：无问芯穹开源最强端侧全模态模型，支持理解图像、音频和文本三种模态数据
    52
  • 5
    FreeScale：无需微调即可提升模型的图像生成能力，生成 8K 分辨率的高质量图像
    39
  • 6
    Freestyler：微软联合西工大和香港大学推出说唱音乐生成模型，支持控制生成的音色、风格和节奏等
    38
  • 7
    《C 语言与统计假设检验：洞察人工智能模型性能差异》
    83
  • 8
    《C 语言赋能：物联网环境下人工智能应用的能耗优化之道》
    24
  • 9
    《C 语言助力神经网络剪枝：优化模型的卓越之道》
    25
  • 10
    《C 语言复数运算：赋能人工智能信号处理新境界》
    20

    • 相关电子书

    更多
  • RowKey与索引设计：技巧与案例分析
  • 低代码开发师（初级）实战教程
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    fs.oss.accessKeyId和fs.oss.accessKeySecret。