|
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 Connected as SYS SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7369 SMITH CLERK 7902 1980-12-17 800.00 20 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 14 rows selected SQL> select * from scott.dept 2 SQL> / DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> desc scott.dept Name Type Nullable Default Comments ------ ------------ -------- ------- -------- DEPTNO NUMBER(2) DNAME VARCHAR2(14) Y LOC VARCHAR2(13) Y SQL> desc dept Object dept does not exist. SQL> set timing on SQL> select count(*)from scott.dept; COUNT(*) ---------- 4 Executed in 0.031 seconds SQL> select depno,job from emp; select depno,job from emp ORA-00942: table or view does not exist SQL> select depno,job from scott.emp; select depno,job from scott.emp ORA-00904: "DEPNO": invalid identifier SQL> select deptno,job from scott.emp; DEPTNO JOB ------ --------- 20 CLERK 30 SALESMAN 30 SALESMAN 20 MANAGER 30 SALESMAN 30 MANAGER 10 MANAGER 20 ANALYST 10 PRESIDENT 30 SALESMAN 20 CLERK 30 CLERK 20 ANALYST 10 CLERK 14 rows selected Executed in 0.094 seconds SQL> select distinct deptno,job from scott.emp 2 SQL> / DEPTNO JOB ------ --------- 10 CLERK 10 MANAGER 10 PRESIDENT 20 ANALYST 20 CLERK 20 MANAGER 30 CLERK 30 MANAGER 30 SALESMAN 9 rows selected Executed in 0.047 seconds SQL> select deptno,job,sal from scott.emp where ename='SMITH'; DEPTNO JOB SAL ------ --------- --------- 20 CLERK 800.00 Executed in 0.016 seconds SQL> select sal*12 from emp; select sal*12 from emp ORA-00942: table or view does not exist SQL> select sal*12 from scott.emp; SAL*12 ---------- 9600 19200 15000 35700 15000 34200 29400 36000 60000 18000 13200 11400 36000 15600 14 rows selected Executed in 0.078 seconds SQL> select sal*12,ename from scott.emp; SAL*12 ENAME ---------- ---------- 9600 SMITH 19200 ALLEN 15000 WARD 35700 JONES 15000 MARTIN 34200 BLAKE 29400 CLARK 36000 SCOTT 60000 KING 18000 TURNER 13200 ADAMS 11400 JAMES 36000 FORD 15600 MILLER 14 rows selected Executed in 0.078 seconds SQL> select sal*13"年工資" from scott.emp 2 SQL> / 年工資 ---------- 10400 20800 16250 38675 16250 37050 31850 39000 65000 19500 14300 12350 39000 16900 14 rows selected Executed in 0.078 seconds SQL> select sal*12+comm*12 "niangongzi" ,ename from scott.emp; niangongzi ENAME ---------- ---------- SMITH 22800 ALLEN 21000 WARD JONES 31800 MARTIN BLAKE CLARK SCOTT KING 18000 TURNER ADAMS JAMES FORD MILLER 14 rows selected Executed in 0.078 seconds SQL> set timing off SQL> select sal*12+comm*12 "niangongzi" ,ename,comm from scott.emp; niangongzi ENAME COMM ---------- ---------- --------- SMITH 22800 ALLEN 300.00 21000 WARD 500.00 JONES 31800 MARTIN 1400.00 BLAKE CLARK SCOTT KING 18000 TURNER 0.00 ADAMS JAMES FORD MILLER 14 rows selected SQL> select sal*12+nvl(comm,0)*12 "niangongzi" ,ename,comm from scott.emp; niangongzi ENAME COMM ---------- ---------- --------- 9600 SMITH 22800 ALLEN 300.00 21000 WARD 500.00 35700 JONES 31800 MARTIN 1400.00 34200 BLAKE 29400 CLARK 36000 SCOTT 60000 KING 18000 TURNER 0.00 13200 ADAMS 11400 JAMES 36000 FORD 15600 MILLER 14 rows selected SQL> SQL> select ename,sal from scott.emp where sal>=2000 and sal<=2500; ENAME SAL ---------- --------- CLARK 2450.00 SQL> select ename,sal from scott.emp where ename like 'S%'; ENAME SAL ---------- --------- SMITH 800.00 SCOTT 3000.00 SQL> select ename,sal from scott.emp where ename like '_O%'; ENAME SAL ---------- --------- JONES 2975.00 FORD 3000.00 SQL> select ename,sal from scott.emp where ename like '__O%'; ENAME SAL ---------- --------- SCOTT 3000.00 SQL> select * from emp where empno in(7698); select * from emp where empno in(7698) ORA-00942: table or view does not exist SQL> select * from scott.emp where empno in(7698); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 SQL> select * from scott.emp where 2 select * from scott.emp where 3 SQL> select * from scott.emp where mgr='null'; select * from scott.emp where mgr='null' ORA-01722: invalid number SQL> select * from scott.emp where mgr='null'; select * from scott.emp where mgr='null' ORA-01722: invalid number SQL> select * from scott.emp where mgr is null; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7839 KING PRESIDENT 1981-11-17 5000.00 10 SQL> select * from scott.emp where (sal>500 or job='MANAGER')and ename like "J%"; select * from scott.emp where (sal>500 or job='MANAGER')and ename like "J%" ORA-00904: "J%": invalid identifier SQL> select * from scott.emp where (sal>500 or job='MANAGER')and ename like 'J%'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 SQL> select * from scott.emp sal order by sal dec; select * from scott.emp sal order by sal dec ORA-00933: SQL command not properly ended SQL> select * from scott.emp sal order by sal dsc; select * from scott.emp sal order by sal dsc ORA-00933: SQL command not properly ended SQL> select * from scott.emp sal order by sal desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7839 KING PRESIDENT 1981-11-17 5000.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7369 SMITH CLERK 7902 1980-12-17 800.00 20 14 rows selected SQL> select * from scott.emp (sal order by sal desc)and (empno order by sal desc); select * from scott.emp (sal order by sal desc)and (empno order by sal desc) ORA-00933: SQL command not properly ended SQL> select * from scott.emp order by sal desc,order by deptno asc); select * from scott.emp order by sal desc,order by deptno asc) ORA-00936: missing expression SQL> select * from scott.emp order by sal desc,deptno asc); select * from scott.emp order by sal desc,deptno asc) ORA-00933: SQL command not properly ended SQL> select * from scott.emp order by sal desc,deptno asc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7839 KING PRESIDENT 1981-11-17 5000.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7369 SMITH CLERK 7902 1980-12-17 800.00 20 14 rows selected SQL> select * from scott.emp order by hiredate desc,deptno asc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7876 ADAMS CLERK 7788 1987-5-23 1100.00 20 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7934 MILLER CLERK 7782 1982-1-23 1300.00 10 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7900 JAMES CLERK 7698 1981-12-3 950.00 30 7839 KING PRESIDENT 1981-11-17 5000.00 10 7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 7369 SMITH CLERK 7902 1980-12-17 800.00 20 14 rows selected SQL> select ename ,(sal+nvl(comm,0))*12 "年薪" from scott.emp order by "年薪",； 2 SQL> ,、 2 SQL> select ename ,(sal+nvl(comm,0))*12 "年薪" from scott.emp order by "年薪"； 2 SQL> select ename ,(sal+nvl(comm,0))*12 "年薪" from scott.emp order by "年薪"; ENAME 年薪 ---------- ---------- SMITH 9600 JAMES 11400 ADAMS 13200 MILLER 15600 TURNER 18000 WARD 21000 ALLEN 22800 CLARK 29400 MARTIN 31800 BLAKE 34200 JONES 35700 SCOTT 36000 FORD 36000 KING 60000 14 rows selected SQL> select ename,max(sal) from scott.emp; select ename,max(sal) from scott.emp ORA-00937: not a single-group group function SQL> select max(sal) from scott.emp; MAX(SAL) ---------- 5000 SQL> select ename,sal from scott.emp where sal=(select max(sal) from scott.emp); ENAME SAL ---------- --------- KING 5000.00 SQL> SQL> select * from scott.emp where sal>(select avg(sal) from scott.emp); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 6 rows selected SQL> select avg(sal) from scott.emp; AVG(SAL) ---------- 2073.21428 SQL> select * from scott.emp where sal>(select avg(sal) from scott.emp) order by hiredate desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20 7902 FORD ANALYST 7566 1981-12-3 3000.00 20 7839 KING PRESIDENT 1981-11-17 5000.00 10 7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 7566 JONES MANAGER 7839 1981-4-2 2975.00 20 6 rows selected SQL> select avg(sal),max(sal),deptno from scott.emp group by deptno; AVG(SAL) MAX(SAL) DEPTNO ---------- ---------- ------ 2916.66666 5000 10 2175 3000 20 1566.66666 2850 30 SQL> select avg(sal),max(sal),deptno,job from scott.emp group by deptno,job; AVG(SAL) MAX(SAL) DEPTNO JOB ---------- ---------- ------ --------- 1300 1300 10 CLERK 2450 2450 10 MANAGER 5000 5000 10 PRESIDENT 950 1100 20 CLERK 3000 3000 20 ANALYST 2975 2975 20 MANAGER 950 950 30 CLERK 2850 2850 30 MANAGER 1400 1600 30 SALESMAN 9 rows selected SQL> SQL> select avg(sal),max(sal),min(sal)deptno,job from scott.emp group by deptno,job; AVG(SAL) MAX(SAL) DEPTNO JOB ---------- ---------- ---------- --------- 1300 1300 1300 CLERK 2450 2450 2450 MANAGER 5000 5000 5000 PRESIDENT 950 1100 800 CLERK 3000 3000 3000 ANALYST 2975 2975 2975 MANAGER 950 950 950 CLERK 2850 2850 2850 MANAGER 1400 1600 1250 SALESMAN 9 rows selected SQL> select avg(sal),max(sal),min(sal),deptno,job from scott.emp group by deptno,job; AVG(SAL) MAX(SAL) MIN(SAL) DEPTNO JOB ---------- ---------- ---------- ------ --------- 1300 1300 1300 10 CLERK 2450 2450 2450 10 MANAGER 5000 5000 5000 10 PRESIDENT 950 1100 800 20 CLERK 3000 3000 3000 20 ANALYST 2975 2975 2975 20 MANAGER 950 950 950 30 CLERK 2850 2850 2850 30 MANAGER 1400 1600 1250 30 SALESMAN 9 rows selected SQL> select avg(sal),max(sal),min(sal),deptno,job from scott.emp group by deptno,job; AVG(SAL) MAX(SAL) MIN(SAL) DEPTNO JOB ---------- ---------- ---------- ------ --------- 1300 1300 1300 10 CLERK 2450 2450 2450 10 MANAGER 5000 5000 5000 10 PRESIDENT 950 1100 800 20 CLERK 3000 3000 3000 20 ANALYST 2975 2975 2975 20 MANAGER 950 950 950 30 CLERK 2850 2850 2850 30 MANAGER 1400 1600 1250 30 SALESMAN 9 rows selected SQL> select avg(sal),max(sal),deptno from scott.emp group by deptno having avg(sal)>2000; AVG(SAL) MAX(SAL) DEPTNO ---------- ---------- ------ 2916.66666 5000 10 2175 3000 20 SQL> SQL> select avg(sal),max(sal),deptno from scott.emp group by deptno having avg(sal)>2000 order by avg(sal); AVG(SAL) MAX(SAL) DEPTNO ---------- ---------- ------ 2175 3000 20 2916.66666 5000 10 SQL> select avg(sal),max(sal),deptno from scott.emp group by deptno having avg(sal)>2000 order by avg(sal) desc; AVG(SAL) MAX(SAL) DEPTNO ---------- ---------- ------ 2916.66666 5000 10 2175 3000 20 SQL> select ename,sal, from scott.emp where dep 2 SQL> select a1.ename,a1.sal,a2.dname from scott.emp a1,scott.dept where a1.deptno=a2.deptno; select a1.ename,a1.sal,a2.dname from scott.emp a1,scott.dept where a1.deptno=a2.deptno ORA-00904: "A2"."DEPTNO": invalid identifier SQL> select a1.ename,a1.sal,a2.dname from scott.emp a1,scott.dept a2 where a1.deptno=a2.deptno; ENAME SAL DNAME ---------- --------- -------------- CLARK 2450.00 ACCOUNTING KING 5000.00 ACCOUNTING MILLER 1300.00 ACCOUNTING SMITH 800.00 RESEARCH ADAMS 1100.00 RESEARCH FORD 3000.00 RESEARCH SCOTT 3000.00 RESEARCH JONES 2975.00 RESEARCH ALLEN 1600.00 SALES BLAKE 2850.00 SALES MARTIN 1250.00 SALES JAMES 950.00 SALES TURNER 1500.00 SALES WARD 1250.00 SALES 14 rows selected SQL> |
|
|
來自： 何必清楚 > 《數(shù)據(jù)庫》
