文件名称：orcal课程练习代码
文件大小：162KB
文件格式：DOC
更新时间：2014-06-25 16:31:21
orcal
SQL> SQL> --1 SQL> SQL> desc dept; Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) SQL> select dname from dept; DNAME -------------- ACCOUNTING RESEARCH SALES OPERATIONS SQL> SQL> SQL> --1.2 SQL> SQL> desc emp; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> select ename as "姓 名" ,sal+comm 年收入 from emp; select ename as "姓 名" ,sal+comm 年收入 from emp * ERROR at line 1: ORA-00911: invalid character SQL> select ename as "姓 名" ,sal+comm "年收入" from emp; ?? ?? ?????? ---------- ---------- SMITH ALLEN 1900 WARD 1750 JONES MARTIN 2650 BLAKE CLARK SCOTT KING TURNER 1500 ADAMS ?? ?? ?????? ---------- ---------- JAMES FORD MILLER 14 rows selected. SQL> select ename as "emp name" ,sal+comm income from emp; emp name INCOME ---------- ---------- SMITH ALLEN 1900 WARD 1750 JONES MARTIN 2650 BLAKE CLARK SCOTT KING TURNER 1500 ADAMS emp name INCOME ---------- ---------- JAMES FORD MILLER 14 rows selected. SQL> select ename ,sal,comm from emp; ENAME SAL COMM ---------- ---------- ---------- SMITH 800 ALLEN 1600 300 WARD 1250 500 JONES 2975 MARTIN 1250 1400 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 0 ADAMS 1100 ENAME SAL COMM ---------- ---------- ---------- JAMES 950 FORD 3000 MILLER 1300 14 rows selected. SQL> select ename as "emp name" ,sal+nvl(comm,0) income from emp;//nvl是oracle中的函数意思是如果comm为null，那么返回0，否则返回comm的值 emp name INCOME ---------- ---------- SMITH 800 ALLEN 1900 WARD 1750 JONES 2975 MARTIN 2650 BLAKE 2850 CLARK 2450 SCOTT 3000 KING 5000 TURNER 1500 ADAMS 1100 emp name INCOME ---------- ---------- JAMES 950 FORD 3000 MILLER 1300 14 rows selected. SQL> select ename as "emp name" ,12 *(sal+nvl(comm,0) ) income from emp; emp name INCOME ---------- ---------- SMITH 9600 ALLEN 22800 WARD 21000 JONES 35700 MARTIN 31800 BLAKE 34200 CLARK 29400 SCOTT 36000 KING 60000 TURNER 18000 ADAMS 13200 emp name INCOME ---------- ---------- JAMES 11400 FORD 36000 MILLER 15600 14 rows selected. SQL> SQL> select deptno from dept; DEPTNO ---------- 10 20 30 40 SQL> --1.3 SQL> select deptno from emp; DEPTNO ---------- 20 30 30 20 30 30 10 20 10 30 20 DEPTNO ---------- 30 20 10 14 rows selected. SQL> select distinct deptno from emp; DEPTNO ---------- 30 20 10 SQL> SQL> SQL> SQL> --2 SQL> SQL> --2.1 SQL> SQL> select ename,sal from emp where sal >2850; ENAME SAL ---------- ---------- JONES 2975 SCOTT 3000 KING 5000 FORD 3000 SQL> SQL> SQL> --2.2 SQL> SQL> SQL> select ename,sal from emp where sal<1500 or sal>2850; ENAME SAL ---------- ---------- SMITH 800 WARD 1250 JONES 2975 MARTIN 1250 SCOTT 3000 KING 5000 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 10 rows selected. SQL> SQL> SQL> select ename,sal from emp where not between 1500 and 2850; select ename,sal from emp where not between 1500 and 2850 * ERROR at line 1: ORA-00936: missing expression SQL> select ename,sal from emp where sal not between 1500 and 2850; ENAME SAL ---------- ---------- SMITH 800 WARD 1250 JONES 2975 MARTIN 1250 SCOTT 3000 KING 5000 ADAMS 1100 JAMES 950 FORD 3000 MILLER 1300 10 rows selected. SQL> select ename,sal from emp where sal between 1500 and 2850; ENAME SAL ---------- ---------- ALLEN 1600 BLAKE 2850 CLARK 2450 TURNER 1500 SQL> SQL> SQL> SQL> SQL> --2.3 SQL> SQL> select ename,deptno from emp where empno=7566; ENAME DEPTNO ---------- ---------- JONES 20 SQL> SQL> --2.4 SQL> SQL> select ename,sal from emp where sal>1500 and (deptno=10 or deptno=30); ENAME SAL ---------- ---------- ALLEN 1600 BLAKE 2850 CLARK 2450 KING 5000 SQL> SQL> select ename,sal from emp where sal>1500 and deptno in(10,30); ENAME SAL ---------- ---------- ALLEN 1600 BLAKE 2850 CLARK 2450 KING 5000 SQL> SQL> SQL> --2.5 SQL> SQL> desc emp; Name Null? Type ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SQL> select empno,ename,job,mgr from emp; EMPNO ENAME JOB MGR ---------- ---------- --------- ---------- 7369 SMITH CLERK 7902 7499 ALLEN SALESMAN 7698 7521 WARD SALESMAN 7698 7566 JONES MANAGER 7839 7654 MARTIN SALESMAN 7698 7698 BLAKE MANAGER 7839 7782 CLARK MANAGER 7839 7788 SCOTT ANALYST 7566 7839 KING PRESIDENT 7844 TURNER SALESMAN 7698 7876 ADAMS CLERK 7788 EMPNO ENAME JOB MGR ---------- ---------- --------- ---------- 7900 JAMES CLERK 7698 7902 FORD ANALYST 7566 7934 MILLER CLERK 7782 14 rows selected. SQL> SQL> SQL> select ename,job from emp where mgr=''; no rows selected SQL> select ename,job from emp where mgr is null; ENAME JOB ---------- --------- KING PRESIDENT SQL> select ename,job from emp where mgr is not null; ENAME JOB ---------- --------- SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER MARTIN SALESMAN BLAKE MANAGER CLARK MANAGER SCOTT ANALYST TURNER SALESMAN ADAMS CLERK JAMES CLERK ENAME JOB ---------- --------- FORD ANALYST MILLER CLERK 13 rows selected. SQL> spool off