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>
本站僅提供存儲服務，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權內(nèi)容，請
點擊舉報。
