SQL> --01查询员工所有数据，并说明使用*的缺点
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-4月 -81 2975
20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
7839 KING PRESIDENT 17-11月-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7876 ADAMS CLERK 7788 23-5月 -87 1100
20
7900 JAMES CLERK 7698 03-12月-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20
7934 MILLER CLERK 7782 23-1月 -82 1300
10
已选择14行。
SQL> --02查询职位（JOB）为TRESIDENT的员工工资
SQL> select sal from emp where job = 'PRESIDENT';
SAL
----------
5000
SQL> --查询佣金（COMM）为0或为NULL的员工的信息
SQL> select * from emp where nvl(comm,0)=0;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7566 JONES MANAGER 7839 02-4月 -81 2975
20
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
7839 KING PRESIDENT 17-11月-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7876 ADAMS CLERK 7788 23-5月 -87 1100
20
7900 JAMES CLERK 7698 03-12月-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20
7934 MILLER CLERK 7782 23-1月 -82 1300
10
已选择11行。
SQL> --04查询入职日期在1981-5-1到1981-12-31之间的所有员工信息
SQL> select * from emp where hiredate between to_date('1981-5-1','YYYY-MM-DD')and to_date('1981-12-31','YYY-MM-DD');
select * from emp where hiredate between to_date('1981-5-1','YYYY-MM-DD')and to_date('1981-12-31','YYY-MM-DD')
*
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配
SQL> select * from emp where hiredate between to_date('1981-5-1','YYYY-MM-DD')and to_date('1981-12-31','YYYY-MM-DD');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-11月-81 5000
10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7900 JAMES CLERK 7698 03-12月-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20
已选择7行。
SQL> --05查询所有名字长度为4的员工的员工编号，姓名
SQL> select empno,ename from emp where length(ename)=4;
EMPNO ENAME
---------- ----------
7521 WARD
7839 KING
7902 FORD
SQL> --06显示10号部门的所有经理（'MANAGER'）和20号部门的所有职员('CLERK')的详细信息
SQL> select* from emp where (job='MANAGER' and deptno=10)or(deptno=20 and job='CKERK');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
SQL> --07显示姓名中没有‘L’字的员工信息的详细信息或含有‘SM’字的员工信息
SQL> select * from emp where ename not like '%L%' or ename like '%SM%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500
30
7566 JONES MANAGER 7839 02-4月 -81 2975
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
7839 KING PRESIDENT 17-11月-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7876 ADAMS CLERK 7788 23-5月 -87 1100
20
7900 JAMES CLERK 7698 03-12月-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20
已选择10行。
SQL> --08显示各部门经理（‘MANAGER’）的工资
SQL> select sal from emp where job='MANAGER';
SAL
----------
2975
2850
2450
SQL> --09显示佣金（COMM）收入比工资（SAL）高的员工的详细信息
SQL> select * from emp wherre nvl(comm,0)>nvl(sal,0);
select * from emp wherre nvl(comm,0)>nvl(sal,0)
*
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束
SQL> select * from emp where nvl(comm,0)>nvl(sal,0);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
SQL> --10.把hiredate列看作是员工的生日，求本月过生日的员工（单行函数）
SQL> select * from emp where to_char(hiredae,'mm')=to_char(sysdate,'mm');
select * from emp where to_char(hiredae,'mm')=to_char(sysdate,'mm')
*
第 1 行出现错误:
ORA-00904: "HIREDAE": 标识符无效
SQL> select * from emp where to_char(hiredate,'mm')=to_char(sysdate,'mm');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-4月 -81 2975
20
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
SQL> spool off;
SQL> --11. 把hiredate列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数)
SQL> select * from emp where to_char( hiredate,'mm')=to_char( add_months(sysdate,1),'mm');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
SQL> --12. 求1982年入职的员工(考察知识点:单行函数)
SQL> select * from emp where to_char( hiredate,'YYYY')=1982;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
SQL> --13. 求1981年下半年入职的员工(考察知识点:单行函数)
SQL> select * from emp where to_char( hiredate,'YYYY-MM-DD') between '1981-07-01' and '1981-12-31' ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
SQL> --14. 求1981年各个月入职的的员工个数(考察知识点:组函数)
SQL> select count(*),trunc(hiredate,'month') from emp where to_char( hiredate,'YYYY')='1981' group by trunc(hiredate,'month') ;
COUNT(*) TRUNC(HIREDATE,'MONTH')
---------- -----------------------
2 1981/9/1
1 1981/5/1
2 1981/2/1
1 1981/6/1
1 1981/4/1
1 1981/11/1
2 1981/12/1
7 rows selected
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300
30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-4月 -81 2975
20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
7839 KING PRESIDENT 17-11月-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7876 ADAMS CLERK 7788 23-5月 -87 1100
20
7900 JAMES CLERK 7698 03-12月-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20
7934 MILLER CLERK 7782 23-1月 -82 1300
10
已选择14行。
SQL> --02查询职位（JOB）为TRESIDENT的员工工资
SQL> select sal from emp where job = 'PRESIDENT';
SAL
----------
5000
SQL> --查询佣金（COMM）为0或为NULL的员工的信息
SQL> select * from emp where nvl(comm,0)=0;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7566 JONES MANAGER 7839 02-4月 -81 2975
20
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
7839 KING PRESIDENT 17-11月-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7876 ADAMS CLERK 7788 23-5月 -87 1100
20
7900 JAMES CLERK 7698 03-12月-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20
7934 MILLER CLERK 7782 23-1月 -82 1300
10
已选择11行。
SQL> --04查询入职日期在1981-5-1到1981-12-31之间的所有员工信息
SQL> select * from emp where hiredate between to_date('1981-5-1','YYYY-MM-DD')and to_date('1981-12-31','YYY-MM-DD');
select * from emp where hiredate between to_date('1981-5-1','YYYY-MM-DD')and to_date('1981-12-31','YYY-MM-DD')
*
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配
SQL> select * from emp where hiredate between to_date('1981-5-1','YYYY-MM-DD')and to_date('1981-12-31','YYYY-MM-DD');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
7698 BLAKE MANAGER 7839 01-5月 -81 2850
30
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-11月-81 5000
10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7900 JAMES CLERK 7698 03-12月-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20
已选择7行。
SQL> --05查询所有名字长度为4的员工的员工编号，姓名
SQL> select empno,ename from emp where length(ename)=4;
EMPNO ENAME
---------- ----------
7521 WARD
7839 KING
7902 FORD
SQL> --06显示10号部门的所有经理（'MANAGER'）和20号部门的所有职员('CLERK')的详细信息
SQL> select* from emp where (job='MANAGER' and deptno=10)or(deptno=20 and job='CKERK');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-6月 -81 2450
10
SQL> --07显示姓名中没有‘L’字的员工信息的详细信息或含有‘SM’字的员工信息
SQL> select * from emp where ename not like '%L%' or ename like '%SM%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-12月-80 800
20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500
30
7566 JONES MANAGER 7839 02-4月 -81 2975
20
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
7839 KING PRESIDENT 17-11月-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7876 ADAMS CLERK 7788 23-5月 -87 1100
20
7900 JAMES CLERK 7698 03-12月-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20
已选择10行。
SQL> --08显示各部门经理（‘MANAGER’）的工资
SQL> select sal from emp where job='MANAGER';
SAL
----------
2975
2850
2450
SQL> --09显示佣金（COMM）收入比工资（SAL）高的员工的详细信息
SQL> select * from emp wherre nvl(comm,0)>nvl(sal,0);
select * from emp wherre nvl(comm,0)>nvl(sal,0)
*
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束
SQL> select * from emp where nvl(comm,0)>nvl(sal,0);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400
30
SQL> --10.把hiredate列看作是员工的生日，求本月过生日的员工（单行函数）
SQL> select * from emp where to_char(hiredae,'mm')=to_char(sysdate,'mm');
select * from emp where to_char(hiredae,'mm')=to_char(sysdate,'mm')
*
第 1 行出现错误:
ORA-00904: "HIREDAE": 标识符无效
SQL> select * from emp where to_char(hiredate,'mm')=to_char(sysdate,'mm');
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-4月 -81 2975
20
7788 SCOTT ANALYST 7566 19-4月 -87 3000
20
SQL> spool off;
SQL> --11. 把hiredate列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数)
SQL> select * from emp where to_char( hiredate,'mm')=to_char( add_months(sysdate,1),'mm');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
SQL> --12. 求1982年入职的员工(考察知识点:单行函数)
SQL> select * from emp where to_char( hiredate,'YYYY')=1982;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
SQL> --13. 求1981年下半年入职的员工(考察知识点:单行函数)
SQL> select * from emp where to_char( hiredate,'YYYY-MM-DD') between '1981-07-01' and '1981-12-31' ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
SQL> --14. 求1981年各个月入职的的员工个数(考察知识点:组函数)
SQL> select count(*),trunc(hiredate,'month') from emp where to_char( hiredate,'YYYY')='1981' group by trunc(hiredate,'month') ;
COUNT(*) TRUNC(HIREDATE,'MONTH')
---------- -----------------------
2 1981/9/1
1 1981/5/1
2 1981/2/1
1 1981/6/1
1 1981/4/1
1 1981/11/1
2 1981/12/1
7 rows selected
