1查询公司中工资最低的雇员的完整信息
Oracle
SQL> select *
2 from emp
3 where sal=(
4 select MIN(sal) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
PPAS
scott=# select *
scott-# from emp
scott-# where sal=(
scott(# select MIN(sal) from emp);
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-------+------+--------------------+--------+------+--------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20
(1 row)
2子查询返回的结果就当它是一个数字，即直接判断此数字
Oracle
SQL> select *
2 from emp
3 where sal=800;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
PPAS
scott=# select *
scott-# from emp
scott-# where sal=800;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-------+------+--------------------+--------+------+--------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20
(1 row)
在WHERE子句中使用子查询
子查询返回单行单列数据
3查询出基本工资比ALLEN低的全部雇员信息
Oracle
SQL> select *
2 from emp
3 where sal<(
4 select sal
5 from emp
6 where ename='ALLEN');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
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
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7 rows selected.
PPAS
scott=# select *
scott-# from emp
scott-# where sal<(
scott(# select sal
scott(# from emp
scott(# where ename='ALLEN');
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+----------+------+--------------------+---------+---------+--------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.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
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
(7 rows)
4查询基本工资高于公司平均薪金的全部雇员信息
Oracle
SQL> select *
2 from emp
3 where sal>(
4 select AVG(sal)
5 from emp);
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
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 *
scott-# from emp
scott-# where sal>(
scott(# select AVG(sal)
scott(# from emp);
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)
5查找出与ALLEN从事同一工作，并且基本工资傲宇雇员编号为7521的全部雇员信息
Oracle
SQL> select *
2 from emp
3 where job=(
4 select job
5 from emp
6 where ename='ALLEN')
7 AND
8 sal>(
9 select sal
10 from emp
11 where empno=7521);
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
PPAS
scott=# select *
scott-# from emp
scott-# where job=(
scott(# select job
scott(# from emp
scott(# where ename='ALLEN')
scott-# AND
scott-# sal>(
scott(# select sal
scott(# from emp
scott(# where empno=7521);
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+----------+------+--------------------+---------+--------+--------
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
(2 rows)
子查询返回单行多列数据
6查询与SCOTT从事同一工作且工资相同的雇员信息
Oracle
SQL> select *
2 from emp
3 where(job,sal)=(
4 select job,sal
5 from emp
6 where ename='SCOTT')AND ename <>'SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
PPAS
scott=# select *
scott-# from emp
scott-# where(job,sal)=(
scott(# select job,sal
scott(# from emp
scott(# where ename='SCOTT')AND ename <>'SCOTT';
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+---------+------+--------------------+---------+------+--------
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20
(1 row)
7查询与雇员7566从事同一工作且领导相同的全部雇员信息
Oracle
SQL> select * from emp
2 where(job,mgr)=(
3 select job,mgr
4 from emp
5 where empno=7566)
6 and empno<>7566;
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
PPAS
scott=# select * from emp
scott-# where(job,mgr)=(
scott(# select job,mgr
scott(# from emp
scott(# where empno=7566)
scott-# and empno<>7566;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+---------+------+--------------------+---------+------+--------
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
(2 rows)
8查询与ALLEN从事同一工作且在同一年雇佣的全部雇员信息（包括ALLEN）
Oracle
SQL> select *
2 from emp
3 where(job,TO_CHAR(hiredate,'yyyy'))=(
4 select job,TO_CHAR(hiredate,'yyyy')
5 from emp
6 where ename='ALLEN');
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
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
PPAS
scott=# select *
scott-# from emp
scott-# where(job,TO_CHAR(hiredate,'yyyy'))=(
scott(# select job,TO_CHAR(hiredate,'yyyy')
scott(# from emp
scott(# where ename='ALLEN');
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)
子查询返回多行单列数据
IN操作符
9查询出与每个部门中最低工资相同的全部雇员信息
Oracle
SQL> select *
2 from emp
3 where sal IN(
4 select MIN(sal)
5 from emp
6 GROUP BY deptno);
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
7934 MILLER CLERK 7782 23-JAN-82 1300 10
PPAS
scott=# select *
scott-# from emp
scott-# where sal IN(
scott(# select MIN(sal)
scott(# from emp
scott(# GROUP BY deptno);
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
7934 | MILLER | CLERK | 7782 | 23-JAN-82 00:00:00 | 1300.00 | | 10
(3 rows)
10查询出不与每个部门中最低工资相同的全部雇员信息
Oracle
SQL> select *
2 from emp
3 where sal NOT IN(
4 select MIN(sal)
5 from emp
6 GROUP BY deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
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
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
11 rows selected.
PPAS
scott=# select *
scott-# from emp
scott-# where sal NOT IN(
scott(# select MIN(sal)
scott(# from emp
scott(# GROUP BY deptno);
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
7876 | ADAMS | CLERK | 7788 | 23-MAY-87 00:00:00 | 1100.00 | | 20
7902 | FORD | ANALYST | 7566 | 03-DEC-81 00:00:00 | 3000.00 | | 20
(11 rows)
如果使用IN操作符在指定的查询范围中存在一个null,则将不会有任何的查询结果返回
11观察null对NOT NI操作的影响
Oracle
SQL> select e.ename
2 from emp e
3 where e.empno NOT IN(
4 select m.mgr
5 from emp m);
no rows selected
PPAS
scott=# select e.ename
scott-# from emp e
scott-# where e.empno NOT IN(
scott(# select m.mgr
scott(# from emp m);
ename
-------
(0 rows)
ANY操作符
12列出每个部门经理的工资
Oracle
SQL> select MIN(sal)
2 from emp
3 where job='MANAGER'
4 GROUP BY deptno;
MIN(SAL)
----------
2850
2975
2450
PPAS
scott=# select MIN(sal)
scott-# from emp
scott-# where job='MANAGER'
scott-# GROUP BY deptno;
min
---------
2975.00
2850.00
2450.00
(3 rows)
13使用=ANY操作符完成查询
Oracle
SQL> select * from emp
2 where sal=ANY(
3 select MIN(sal)
4 from emp
5 where job='MANAGER'
6 GROUP BY deptno);
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
PPAS
scott=# select * from emp
scott-# where sal=ANY(
scott(# select MIN(sal)
scott(# from emp
scott(# where job='MANAGER'
scott(# GROUP BY deptno);
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
(3 rows)
14使用>ANY操作符完成查询
Oracle
SQL> select * from emp
2 where sal>ANY(
3 select MIN(sal)
4 from emp
5 where job='MANAGER'
6 GROUP BY deptno);
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
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
PPAS
scott=# select * from emp
scott-# where sal >ANY（
scott-# ^C
scott=# select * from emp
scott-# where sal>ANY(
scott(# select MIN(sal)
scott(# from emp
scott(# where job='MANAGER'
scott(# GROUP BY deptno);
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
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
(5 rows)
15使用<ANY操作符完成查询
Oracle
SQL> select * from emp
2 where sal<ANY(
3 select MIN(sal)
4 from emp
5 where job='MANAGER'
6 GROUP BY deptno);
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
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
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
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
10 rows selected.
PPAS
scott=# select * from emp
scott-# where sal<ANY(
scott(# select MIN(sal)
scott(# from emp
scott(# where job='MANAGER'
scott(# GROUP BY deptno);
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
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
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
(10 rows)
ALL操作符
16使用<>ALL操作符完成查询
Oracle
SQL> select * from emp
2 where sal<>ALL(
3 select MIN(sal)
4 from emp
5 where job='MANAGER'
6 GROUP BY deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
11 rows selected.
PPAS
scott=# select * from emp
scott-# where sal<>ALL(
scott(# select MIN(sal)
scott(# from emp
scott(# where job='MANAGER'
scott(# GROUP BY deptno);
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
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)
17使用>ALL操作符完成查询
Oracle
SQL> select * from emp
2 where sal>ALL(
3 select MIN(sal)
4 from emp
5 where job='MANAGER'
6 GROUP BY deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- ------------ ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
PPAS
scott=# select * from emp
scott-# where sal>ALL(
scott(# select MIN(sal)
scott(# from emp
scott(# where job='MANAGER'
scott(# GROUP BY deptno);
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----------+------+--------------------+---------+------+--------
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
(3 rows)
18使用<ALL操作符完成查询
Oracle
SQL> select * from emp
2 where sal<ALL(
3 select MIN(sal)
4 from emp
5 where job='MANAGER'
6 GROUP BY deptno);
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
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
7900 JAMES CLERK 7698 03-DEC-81 950 30
7369 SMITH CLERK 7902 17-DEC-80 800 20
8 rows selected.
PPAS
scott=# select * from emp
scott-# where sal<ALL(
scott(# select MIN(sal)
scott(# from emp
scott(# where job='MANAGER'
scott(# GROUP BY deptno);
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)
空数据判断
19验证EXISTS结构
Oracle
SQL> select * from emp
2 where exists(
3 select * from emp where empno=9999);
no rows selected
PPAS
scott=# select * from emp
scott-# where exists(
scott(# select * from emp where empno=9999);
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)
20验证EXISTS结构
Oracle
SQL> select * from emp
2 where exists(
3 select * from emp);
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
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
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
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
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
scott-# where exists(
scott(# select * from emp);
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)
21使用NO EXISTS
Oracle
SQL> select * from emp
2 where NOT EXISTS(
3 (select * from emp where empno=9999));
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
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
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
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
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
scott-# where NOT EXISTS(
scott(# (select * from emp where empno=9999));
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)
在HAVING子句中使用子查询
22查询部门编号、雇员人数、平均工资，并且要求这些部门的平均工资高于公司平均薪金
Oracle
SQL> select deptno,COUNT(empno),AVG(sal)
2 from emp
3 GROUP BY deptno
4 HAVING AVG(sal)>(
5 select AVG(sal)
6 from emp);
DEPTNO COUNT(EMPNO) AVG(SAL)
---------- ------------ ----------
20 5 2175
10 3 2916.66667
PPAS
scott=# select deptno,COUNT(empno),AVG(sal)
scott-# from emp
scott-# GROUP BY deptno
scott-# HAVING AVG(sal)>(
scott(# select AVG(sal)
scott(# from emp);
deptno | count | avg
--------+-------+-----------------------
20 | 5 | 2175.0000000000000000
10 | 3 | 2916.6666666666666667
(2 rows)
23查询出每个部门平均工资最高的部门名称及平均工资
Oracle
SQL> select d.dname,ROUND(AVG(e.sal),2)
2 from emp e,dept d
3 where e.deptno=d.deptno
4 GROUP BY d.dname
5 HAVING AVG(sal)=(
6 select MAX(AVG(sal))
7 from emp
8 GROUP BY deptno);
DNAME ROUND(AVG(E.SAL),2)
--------------- -------------------
ACCOUNTING 2916.67
PPAS
scott=# select d.dname,ROUND(AVG(e.sal),2)
scott-# from emp e,dept d
scott-# where e.deptno=d.deptno
scott-# GROUP BY d.dname
scott-# HAVING AVG(sal)=(
scott(# select MAX(AVG(sal))
scott(# from emp
scott(# GROUP BY deptno);
ERROR: aggregate function calls cannot be nested
LINE 6: select MAX(AVG(sal))
在FROM子句中使用子查询
24查询出每个部门的编号、名称、位置、部门人数、平均工资
Oracle
SQL> select d.deptno,d.dname,d.loc,temp.count,temp.avg
2 from dept d,(select deptno dno,COUNT(empno) count,
3 ROUND(AVG(sal),2) avg
4 from emp
5 GROUP BY deptno) temp
6 where d.deptno=temp.dno(+);
DEPTNO DNAME LOC COUNT AVG
---------- --------------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 3 2916.67
20 RESEARCH DALLAS 5 2175
30 SALES CHICAGO 6 1566.67
40 OPERATIONS BOSTON
PPAS
scott=# select d.deptno,d.dname,d.loc,temp.count,temp.avg
scott-# from dept d,(select deptno dno,COUNT(empno) count,
scott(# ROUND(AVG(sal),2) avg
scott(# from emp
scott(# GROUP BY deptno) temp
scott-# where d.deptno=temp.dno(+);
deptno | dname | loc | count | avg
--------+------------+----------+-------+---------
20 | RESEARCH | DALLAS | 5 | 2175.00
30 | SALES | CHICAGO | 6 | 1566.67
10 | ACCOUNTING | NEW YORK | 3 | 2916.67
40 | OPERATIONS | BOSTON | |
(4 rows)
25利用多字段分组
Oracle
SQL> select d.deptno,d.dname,d.loc,COUNT(e.empno) count,
2 ROUND(AVG(sal),2) avg
3 from emp e,dept d
4 where e.deptno(+)=d.deptno
5 GROUP BY d.deptno,d.dname,d.loc;
DEPTNO DNAME LOC COUNT AVG
---------- --------------- ---------- ---------- ----------
20 RESEARCH DALLAS 5 2175
40 OPERATIONS BOSTON 0
10 ACCOUNTING NEW YORK 3 2916.67
30 SALES CHICAGO 6 1566.67
PPAS
scott=# select d.deptno,d.dname,d.loc,COUNT(e.empno) count,
scott-# ROUND(AVG(sal),2) avg
scott-# from emp e,dept d
scott-# where e.deptno(+)=d.deptno
scott-# GROUP BY d.deptno,d.dname,d.loc;
deptno | dname | loc | count | avg
--------+------------+----------+-------+---------
10 | ACCOUNTING | NEW YORK | 3 | 2916.67
30 | SALES | CHICAGO | 6 | 1566.67
40 | OPERATIONS | BOSTON | 0 |
20 | RESEARCH | DALLAS | 5 | 2175.00
(4 rows)