SQL> --查询员工号为7018的部门信息
SQL> select empno from emp
2 ;
EMPNO
----------
7566
7654
7698
7777
7778
7782
7788
7839
7844
7876
7900
7902
7934
9527
9528
已选择15行。
SQL> desc dept;
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> ed
已写入 file afiedt.buf
1 select e.empno,e.ename,d.dname
2 from emp e,dept d
3* where e.detpno = d.detpno;
SQL> /
where e.detpno = d.detpno;
*
第 3 行出现错误:
ORA-00911: 无效字符
SQL> ed
已写入 file afiedt.buf
1 select e.empno,e.ename,d.dname
2 from emp e,dept d
3* where e.deptno = d.deptno
SQL> /
EMPNO ENAME DNAME
---------- ---------- --------------
9527 余里 OPERATIONS
9528 余里 4楼男厕所
7777 lisi ACCOUNTING
7778 wangwu SALES
7566 JONES RESEARCH
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7788 SCOTT RESEARCH
7839 KING ACCOUNTING
7844 TURNER SALES
7876 ADAMS RESEARCH
7900 JAMES SALES
7902 FORD RESEARCH
7934 MILLER ACCOUNTING
已选择15行。
SQL> desc salgrade;
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> --查询每个员工的销售级别
SQL> ed
已写入 file afiedt.buf
1 select e.empno,e.ename,s.grade
2 from emp e,salgrade s
3* where e.sal between s.losal and s.hisal
SQL> /
EMPNO ENAME GRADE
---------- ---------- ----------
7900 JAMES 1
7778 wangwu 1
7876 ADAMS 1
7654 MARTIN 2
7844 TURNER 3
7777 lisi 3
7782 CLARK 4
7698 BLAKE 4
7566 JONES 4
7788 SCOTT 4
7902 FORD 4
7839 KING 5
已选择12行。
SQL> spool off
SQL> select empno from emp
2 ;
EMPNO
----------
7566
7654
7698
7777
7778
7782
7788
7839
7844
7876
7900
7902
7934
9527
9528
已选择15行。
SQL> desc dept;
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> ed
已写入 file afiedt.buf
1 select e.empno,e.ename,d.dname
2 from emp e,dept d
3* where e.detpno = d.detpno;
SQL> /
where e.detpno = d.detpno;
*
第 3 行出现错误:
ORA-00911: 无效字符
SQL> ed
已写入 file afiedt.buf
1 select e.empno,e.ename,d.dname
2 from emp e,dept d
3* where e.deptno = d.deptno
SQL> /
EMPNO ENAME DNAME
---------- ---------- --------------
9527 余里 OPERATIONS
9528 余里 4楼男厕所
7777 lisi ACCOUNTING
7778 wangwu SALES
7566 JONES RESEARCH
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7788 SCOTT RESEARCH
7839 KING ACCOUNTING
7844 TURNER SALES
7876 ADAMS RESEARCH
7900 JAMES SALES
7902 FORD RESEARCH
7934 MILLER ACCOUNTING
已选择15行。
SQL> desc salgrade;
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> --查询每个员工的销售级别
SQL> ed
已写入 file afiedt.buf
1 select e.empno,e.ename,s.grade
2 from emp e,salgrade s
3* where e.sal between s.losal and s.hisal
SQL> /
EMPNO ENAME GRADE
---------- ---------- ----------
7900 JAMES 1
7778 wangwu 1
7876 ADAMS 1
7654 MARTIN 2
7844 TURNER 3
7777 lisi 3
7782 CLARK 4
7698 BLAKE 4
7566 JONES 4
7788 SCOTT 4
7902 FORD 4
7839 KING 5
已选择12行。
SQL> spool off