- 博客(0)
- 资源 (1)
空空如也
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
2011-06-05
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人