Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott
SQL> select * from 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 sum(sal) sumsal, avg(sal) avgsal,count(*),count(sal) from emp;
SUMSAL AVGSAL COUNT(*) COUNT(SAL)
---------- ---------- ---------- ----------
29025 2073.21428 14 14
SQL> select sum(sal),avg(sal),min(sal),mix(sal),count(sal),count(*) from emp;
select sum(sal),avg(sal),min(sal),mix(sal),count(sal),count(*) from emp
ORA-00904: "MIX": 标识符无效
SQL> select sum(sal),avg(sal),min(sal),max(sal),count(sal),count(*)from emp;
SUM(SAL) AVG(SAL) MIN(SAL) MAX(SAL) COUNT(SAL) COUNT(*)
---------- ---------- ---------- ---------- ---------- ----------
29025 2073.21428 800 5000 14 14
SQL> select * frpm dept;
select * frpm dept
ORA-00923: 未找到要求的 FROM 关键字
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select deptno,sum(sal) from emp ;
select deptno,sum(sal) from emp
ORA-00937: 不是单组分组函数
SQL> select deptno ,sum(sal), from emp group by deptno;
select deptno ,sum(sal), from emp group by deptno
ORA-00936: 缺失表达式
SQL> select deptno,sum(dal) from emp group by deptno;
select deptno,sum(dal) from emp group by deptno
ORA-00904: "DAL": 标识符无效
SQL> select deptno,sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
------ ----------
30 9400
20 10875
10 8750
SQL> select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;
DEPTNO SUM(SAL)
------ ----------
30 9400
20 10875
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select deptno,sum(sal),loc,count(*) from emp,dept group by deptno ,loc having sum(sal)>9000;
select deptno,sum(sal),loc,count(*) from emp,dept group by deptno ,loc having sum(sal)>9000
ORA-00918: 未明确定义列
SQL> select e.deptno,sum(sal),loc,count(*) from emp e,dept d where e.deptno=d.deptno group by e.deptno,loc having sum(sal)>9000;
DEPTNO SUM(SAL) LOC COUNT(*)
------ ---------- ------------- ----------
20 10875 DALLAS 5
30 9400 CHICAGO 6
SQL> slect loc,e.deptno,sum(sal),count(loc),count(sal),count(*) from emp e,dept d where e.deptno=d.deptno group by loc,e.deptno having sum(sal)>1000;
slect loc,e.deptno,sum(sal),count(loc),count(sal),count(*) from emp e,dept d where e.deptno=d.deptno group by loc,e.deptno having sum(sal)>1000
ORA-00900: 无效 SQL 语句
SQL> select loc,e.deptno,sum(sal),count(loc),count(sal),count(*) from emp e,dept d where e.deptno=d.deptno group by loc,e.deptno having sum(sal)>1000;
LOC DEPTNO SUM(SAL) COUNT(LOC) COUNT(SAL) COUNT(*)
------------- ------ ---------- ---------- ---------- ----------
CHICAGO 30 9400 6 6 6
DALLAS 20 10875 5 5 5
NEW YORK 10 8750 3 3 3
SQL> select * from 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 deptno, ename,hiredate from emp where (deptno,hiredate) in (select (deptno ,max(hiredate) from emp group by demptno));
select deptno, ename,hiredate from emp where (deptno,hiredate) in (select (deptno ,max(hiredate) from emp group by demptno))
ORA-00920: 无效的关系运算符
SQL>
SQL> select sname,birthday,dno
2 from student
3 where (dno , birthday) in ( select dno ,max(birthday)
4 from student
5 group by dno ) ;
select sname,birthday,dno
from student
where (dno , birthday) in ( select dno ,max(birthday)
from student
group by dno )
ORA-00942: 表或视图不存在
SQL> select ename, deptno,hiredate from emp where (deptno,hiredate) in (select deptno,max(hiredate) from emp group by deptno);
ENAME DEPTNO HIREDATE
---------- ------ -----------
ADAMS 20 1987-5-23
JAMES 30 1981-12-3
MILLER 10 1982-1-23
SQL> round(68.247);
round(68.247)
ORA-00900: 无效 SQL 语句
SQL> select round(68.247) from dual;
ROUND(68.247)
-------------
68
SQL> select round(68.247,-1) from dual;
ROUND(68.247,-1)
----------------
70
SQL> select round(68.247,-2) from dual;
ROUND(68.247,-2)
----------------
100
SQL> select round(68.247,-3) from dual;
ROUND(68.247,-3)
----------------
0
SQL> select trunc(68.247,-1) from dual;
TRUNC(68.247,-1)
----------------
60
SQL> select trunc(68.247,-2) from dual;
TRUNC(68.247,-2)
----------------
0
SQL> select trunc(68.247,1) from dual;
TRUNC(68.247,1)
---------------
68.2
SQL> select trunc(68.247,2) from dual;
TRUNC(68.247,2)
---------------
68.24
SQL> select trunc(68.247,3) from dual;
TRUNC(68.247,3)
---------------
68.247
SQL> select floor(68.247) from dual;
FLOOR(68.247)
-------------
68
SQL> select * from emp where lower(ename)='smith';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
SQL> select * from emp where ename=upper('smith');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
SQL> select concat('long','xiang') from dual;
CONCAT('LONG','XIANG')
----------------------
longxiang
SQL> select substr('character',4,3) from dual;
SUBSTR('CHARACTER',4,3)
-----------------------
rac
SQL> select substr('characheter',0,5) from dual;
SUBSTR('CHARACHETER',0,5)
-------------------------
chara
SQL> select substr('charachter',1,5) from dual;
SUBSTR('CHARACHTER',1,5)
------------------------
chara
SQL> select substr('charachter',10,4) from dual;
SUBSTR('CHARACHTER',10,4)
-------------------------
r
SQL> select substr('charachter',11,3) from dual;
SUBSTR('CHARACHTER',11,3)
-------------------------
SQL> select length('character') from dual;
LENGTH('CHARACTER')
-------------------
9
SQL> select mod(234,100) from dual;
MOD(234,100)
------------
34
SQL> select sysdate from dual;
SYSDATE
-----------
2013-6-1 下午
SQL> select sysdate+30 from dual;
SYSDATE+30
-----------
2013-7-1 下午
SQL> select system+2 from dual;
select system+2 from dual
ORA-00904: "SYSTEM": 标识符无效
SQL> select sysdate+2 from dual;
SYSDATE+2
-----------
2013-6-3 下午
SQL> select add_months(sysdate,18) from dual;
ADD_MONTHS(SYSDATE,18)
----------------------
2014-12-1 下午 03:02:08
SQL> select months_between(sysdate,add_months(sysdate,18)) from dual;
MONTHS_BETWEEN(SYSDATE,ADD_MON
------------------------------
-18
SQL> select next_day(sysdate) from dual;
select next_day(sysdate) from dual
ORA-00909: 参数个数无效
SQL> select next_day(sysdate,'星期六') from dual;
NEXT_DAY(SYSDATE,'星期六')
--------------------------
2013-6-8 下午 03:06:09
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2013-6-30 下午 03:0
SQL> select round(system,'month') from dual;
select round(system,'month') from dual
ORA-00904: "SYSTEM": 标识符无效
SQL> select round(sysdate,'month')from dual;
ROUND(SYSDATE,'MONTH')
----------------------
2013-6-1
SQL> select round(sysdate,'year') from dual;
ROUND(SYSDATE,'YEAR')
---------------------
2013-1-1
SQL> select round(sysdate,'day') from dual;
ROUND(SYSDATE,'DAY')
--------------------
2013-6-2
SQL> select sysdate-trunc(sysdate) from dual;
SYSDATE-TRUNC(SYSDATE)
----------------------
0.632731481481481
SQL> select To_char(sysdate,'yyyy-mm-dd,day') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DD,DA
------------------------------
2013-06-01,星期六
SQL> select to_char(sysdate+49,'yyyy-mm-dd,day') from dual;
TO_CHAR(SYSDATE+49,'YYYY-MM-DD
------------------------------
2013-07-20,星期六
SQL> select to_number('5021314') from dual;
TO_NUMBER('5021314')
--------------------
5021314
SQL> select to_number('5201314lx') from dual;
select to_number('5201314lx') from dual
ORA-01722: 无效数字
SQL> select to_date('1990-01-1','yyyy-mm-dd') from dual;
TO_DATE('1990-01-1','YYYY-MM-D
------------------------------
1990-1-1
SQL> select * from emp where hiredate>to_date('1980-1-1','yyyy-mm-dd') from dual;
select * from emp where hiredate>to_date('1980-1-1','yyyy-mm-dd') from dual
ORA-00933: SQL 命令未正确结束
SQL> select * from emp where hiredate>to_date('1980-1-1','yyyy-mm-dd');
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 emp where hiredate>to_date('1985-1-1','yyyyy-mm-dd');
select * from emp where hiredate>to_date('1985-1-1','yyyyy-mm-dd')
ORA-01812: 只能指定一次年份
SQL> select * from emp where hiredate>to_date('1985-1-1','yyyy-mm-dd');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
SQL> select ename, sal,comm from emp;
ENAME SAL COMM
---------- --------- ---------
SMITH 800.00
ALLEN 1600.00 300.00
WARD 1250.00 500.00
JONES 2975.00
MARTIN 1250.00 1400.00
BLAKE 2850.00
CLARK 2450.00
SCOTT 3000.00
KING 5000.00
TURNER 1500.00 0.00
ADAMS 1100.00
JAMES 950.00
FORD 3000.00
MILLER 1300.00
14 rows selected
SQL> select ename sal+comm from emp;
select ename sal+comm from emp
ORA-00923: 未找到要求的 FROM 关键字
SQL> select ename.sal+comm from emp;
select ename.sal+comm from emp
ORA-00904: "ENAME"."SAL": 标识符无效
SQL> select ename ,sal+comm from emp;
ENAME SAL+COMM
---------- ----------
SMITH
ALLEN 1900
WARD 1750
JONES
MARTIN 2650
BLAKE
CLARK
SCOTT
KING
TURNER 1500
ADAMS
JAMES
FORD
MILLER
14 rows selected
SQL> select ename,nvl(sal+comm) from emp;
select ename,nvl(sal+comm) from emp
ORA-00909: 参数个数无效
SQL> select ename,nvl(sal)+nvl(comm) from emp;
select ename,nvl(sal)+nvl(comm) from emp
ORA-00909: 参数个数无效
SQL> select ename,nvl(sal+comm,0) from dual;
select ename,nvl(sal+comm,0) from dual
ORA-00904: "COMM": 标识符无效
SQL> select ename,nvl(sal,0)+nvl(comm,0) from dual;
select ename,nvl(sal,0)+nvl(comm,0) from dual
ORA-00904: "COMM": 标识符无效
SQL> select ename,nvl(sal,0)+nvl(comm,0)from emp;
ENAME NVL(SAL,0)+NVL(COMM,0)
---------- ----------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
MARTIN 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 rows selected
SQL> select ename,nvl(sal+comm,0) from emp;
ENAME NVL(SAL+COMM,0)
---------- ---------------
SMITH 0
ALLEN 1900
WARD 1750
JONES 0
MARTIN 2650
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 1500
ADAMS 0
JAMES 0
FORD 0
MILLER 0
14 rows selected
SQL> select ename,vnl2(sal,sal,0)+nvl2(comm,comm,0) from emp;
select ename,vnl2(sal,sal,0)+nvl2(comm,comm,0) from emp
ORA-00904: "VNL2": 标识符无效
SQL> select ename,nvl2(sal,sal,0)+nvl(comm,comm,0) from emp;
select ename,nvl2(sal,sal,0)+nvl(comm,comm,0) from emp
ORA-00909: 参数个数无效
SQL> select ename,nvl2(sal,sal,0)+nvl2(comm,comm,0) from emp;
ENAME NVL2(SAL,SAL,0)+NVL2(COMM,COMM
---------- ------------------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
MARTIN 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 rows selected
SQL> select * from emp where deptno=30;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-3 950.00 30
6 rows selected
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from bonus;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> select ename,deptno,job ,sal where lower(job)='clerk';
select ename,deptno,job ,sal where lower(job)='clerk'
ORA-00923: 未找到要求的 FROM 关键字
SQL> select ename ,deptno,job,sal from emp where lower(job)='clerk';
ENAME DEPTNO JOB SAL
---------- ------ --------- ---------
SMITH 20 CLERK 800.00
ADAMS 20 CLERK 1100.00
JAMES 30 CLERK 950.00
MILLER 10 CLERK 1300.00
SQL>
Connected as scott
SQL> select * from 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 sum(sal) sumsal, avg(sal) avgsal,count(*),count(sal) from emp;
SUMSAL AVGSAL COUNT(*) COUNT(SAL)
---------- ---------- ---------- ----------
29025 2073.21428 14 14
SQL> select sum(sal),avg(sal),min(sal),mix(sal),count(sal),count(*) from emp;
select sum(sal),avg(sal),min(sal),mix(sal),count(sal),count(*) from emp
ORA-00904: "MIX": 标识符无效
SQL> select sum(sal),avg(sal),min(sal),max(sal),count(sal),count(*)from emp;
SUM(SAL) AVG(SAL) MIN(SAL) MAX(SAL) COUNT(SAL) COUNT(*)
---------- ---------- ---------- ---------- ---------- ----------
29025 2073.21428 800 5000 14 14
SQL> select * frpm dept;
select * frpm dept
ORA-00923: 未找到要求的 FROM 关键字
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select deptno,sum(sal) from emp ;
select deptno,sum(sal) from emp
ORA-00937: 不是单组分组函数
SQL> select deptno ,sum(sal), from emp group by deptno;
select deptno ,sum(sal), from emp group by deptno
ORA-00936: 缺失表达式
SQL> select deptno,sum(dal) from emp group by deptno;
select deptno,sum(dal) from emp group by deptno
ORA-00904: "DAL": 标识符无效
SQL> select deptno,sum(sal) from emp group by deptno;
DEPTNO SUM(SAL)
------ ----------
30 9400
20 10875
10 8750
SQL> select deptno,sum(sal) from emp group by deptno having sum(sal)>9000;
DEPTNO SUM(SAL)
------ ----------
30 9400
20 10875
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select deptno,sum(sal),loc,count(*) from emp,dept group by deptno ,loc having sum(sal)>9000;
select deptno,sum(sal),loc,count(*) from emp,dept group by deptno ,loc having sum(sal)>9000
ORA-00918: 未明确定义列
SQL> select e.deptno,sum(sal),loc,count(*) from emp e,dept d where e.deptno=d.deptno group by e.deptno,loc having sum(sal)>9000;
DEPTNO SUM(SAL) LOC COUNT(*)
------ ---------- ------------- ----------
20 10875 DALLAS 5
30 9400 CHICAGO 6
SQL> slect loc,e.deptno,sum(sal),count(loc),count(sal),count(*) from emp e,dept d where e.deptno=d.deptno group by loc,e.deptno having sum(sal)>1000;
slect loc,e.deptno,sum(sal),count(loc),count(sal),count(*) from emp e,dept d where e.deptno=d.deptno group by loc,e.deptno having sum(sal)>1000
ORA-00900: 无效 SQL 语句
SQL> select loc,e.deptno,sum(sal),count(loc),count(sal),count(*) from emp e,dept d where e.deptno=d.deptno group by loc,e.deptno having sum(sal)>1000;
LOC DEPTNO SUM(SAL) COUNT(LOC) COUNT(SAL) COUNT(*)
------------- ------ ---------- ---------- ---------- ----------
CHICAGO 30 9400 6 6 6
DALLAS 20 10875 5 5 5
NEW YORK 10 8750 3 3 3
SQL> select * from 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 deptno, ename,hiredate from emp where (deptno,hiredate) in (select (deptno ,max(hiredate) from emp group by demptno));
select deptno, ename,hiredate from emp where (deptno,hiredate) in (select (deptno ,max(hiredate) from emp group by demptno))
ORA-00920: 无效的关系运算符
SQL>
SQL> select sname,birthday,dno
2 from student
3 where (dno , birthday) in ( select dno ,max(birthday)
4 from student
5 group by dno ) ;
select sname,birthday,dno
from student
where (dno , birthday) in ( select dno ,max(birthday)
from student
group by dno )
ORA-00942: 表或视图不存在
SQL> select ename, deptno,hiredate from emp where (deptno,hiredate) in (select deptno,max(hiredate) from emp group by deptno);
ENAME DEPTNO HIREDATE
---------- ------ -----------
ADAMS 20 1987-5-23
JAMES 30 1981-12-3
MILLER 10 1982-1-23
SQL> round(68.247);
round(68.247)
ORA-00900: 无效 SQL 语句
SQL> select round(68.247) from dual;
ROUND(68.247)
-------------
68
SQL> select round(68.247,-1) from dual;
ROUND(68.247,-1)
----------------
70
SQL> select round(68.247,-2) from dual;
ROUND(68.247,-2)
----------------
100
SQL> select round(68.247,-3) from dual;
ROUND(68.247,-3)
----------------
0
SQL> select trunc(68.247,-1) from dual;
TRUNC(68.247,-1)
----------------
60
SQL> select trunc(68.247,-2) from dual;
TRUNC(68.247,-2)
----------------
0
SQL> select trunc(68.247,1) from dual;
TRUNC(68.247,1)
---------------
68.2
SQL> select trunc(68.247,2) from dual;
TRUNC(68.247,2)
---------------
68.24
SQL> select trunc(68.247,3) from dual;
TRUNC(68.247,3)
---------------
68.247
SQL> select floor(68.247) from dual;
FLOOR(68.247)
-------------
68
SQL> select * from emp where lower(ename)='smith';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
SQL> select * from emp where ename=upper('smith');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
SQL> select concat('long','xiang') from dual;
CONCAT('LONG','XIANG')
----------------------
longxiang
SQL> select substr('character',4,3) from dual;
SUBSTR('CHARACTER',4,3)
-----------------------
rac
SQL> select substr('characheter',0,5) from dual;
SUBSTR('CHARACHETER',0,5)
-------------------------
chara
SQL> select substr('charachter',1,5) from dual;
SUBSTR('CHARACHTER',1,5)
------------------------
chara
SQL> select substr('charachter',10,4) from dual;
SUBSTR('CHARACHTER',10,4)
-------------------------
r
SQL> select substr('charachter',11,3) from dual;
SUBSTR('CHARACHTER',11,3)
-------------------------
SQL> select length('character') from dual;
LENGTH('CHARACTER')
-------------------
9
SQL> select mod(234,100) from dual;
MOD(234,100)
------------
34
SQL> select sysdate from dual;
SYSDATE
-----------
2013-6-1 下午
SQL> select sysdate+30 from dual;
SYSDATE+30
-----------
2013-7-1 下午
SQL> select system+2 from dual;
select system+2 from dual
ORA-00904: "SYSTEM": 标识符无效
SQL> select sysdate+2 from dual;
SYSDATE+2
-----------
2013-6-3 下午
SQL> select add_months(sysdate,18) from dual;
ADD_MONTHS(SYSDATE,18)
----------------------
2014-12-1 下午 03:02:08
SQL> select months_between(sysdate,add_months(sysdate,18)) from dual;
MONTHS_BETWEEN(SYSDATE,ADD_MON
------------------------------
-18
SQL> select next_day(sysdate) from dual;
select next_day(sysdate) from dual
ORA-00909: 参数个数无效
SQL> select next_day(sysdate,'星期六') from dual;
NEXT_DAY(SYSDATE,'星期六')
--------------------------
2013-6-8 下午 03:06:09
SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-----------------
2013-6-30 下午 03:0
SQL> select round(system,'month') from dual;
select round(system,'month') from dual
ORA-00904: "SYSTEM": 标识符无效
SQL> select round(sysdate,'month')from dual;
ROUND(SYSDATE,'MONTH')
----------------------
2013-6-1
SQL> select round(sysdate,'year') from dual;
ROUND(SYSDATE,'YEAR')
---------------------
2013-1-1
SQL> select round(sysdate,'day') from dual;
ROUND(SYSDATE,'DAY')
--------------------
2013-6-2
SQL> select sysdate-trunc(sysdate) from dual;
SYSDATE-TRUNC(SYSDATE)
----------------------
0.632731481481481
SQL> select To_char(sysdate,'yyyy-mm-dd,day') from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DD,DA
------------------------------
2013-06-01,星期六
SQL> select to_char(sysdate+49,'yyyy-mm-dd,day') from dual;
TO_CHAR(SYSDATE+49,'YYYY-MM-DD
------------------------------
2013-07-20,星期六
SQL> select to_number('5021314') from dual;
TO_NUMBER('5021314')
--------------------
5021314
SQL> select to_number('5201314lx') from dual;
select to_number('5201314lx') from dual
ORA-01722: 无效数字
SQL> select to_date('1990-01-1','yyyy-mm-dd') from dual;
TO_DATE('1990-01-1','YYYY-MM-D
------------------------------
1990-1-1
SQL> select * from emp where hiredate>to_date('1980-1-1','yyyy-mm-dd') from dual;
select * from emp where hiredate>to_date('1980-1-1','yyyy-mm-dd') from dual
ORA-00933: SQL 命令未正确结束
SQL> select * from emp where hiredate>to_date('1980-1-1','yyyy-mm-dd');
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 emp where hiredate>to_date('1985-1-1','yyyyy-mm-dd');
select * from emp where hiredate>to_date('1985-1-1','yyyyy-mm-dd')
ORA-01812: 只能指定一次年份
SQL> select * from emp where hiredate>to_date('1985-1-1','yyyy-mm-dd');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
SQL> select ename, sal,comm from emp;
ENAME SAL COMM
---------- --------- ---------
SMITH 800.00
ALLEN 1600.00 300.00
WARD 1250.00 500.00
JONES 2975.00
MARTIN 1250.00 1400.00
BLAKE 2850.00
CLARK 2450.00
SCOTT 3000.00
KING 5000.00
TURNER 1500.00 0.00
ADAMS 1100.00
JAMES 950.00
FORD 3000.00
MILLER 1300.00
14 rows selected
SQL> select ename sal+comm from emp;
select ename sal+comm from emp
ORA-00923: 未找到要求的 FROM 关键字
SQL> select ename.sal+comm from emp;
select ename.sal+comm from emp
ORA-00904: "ENAME"."SAL": 标识符无效
SQL> select ename ,sal+comm from emp;
ENAME SAL+COMM
---------- ----------
SMITH
ALLEN 1900
WARD 1750
JONES
MARTIN 2650
BLAKE
CLARK
SCOTT
KING
TURNER 1500
ADAMS
JAMES
FORD
MILLER
14 rows selected
SQL> select ename,nvl(sal+comm) from emp;
select ename,nvl(sal+comm) from emp
ORA-00909: 参数个数无效
SQL> select ename,nvl(sal)+nvl(comm) from emp;
select ename,nvl(sal)+nvl(comm) from emp
ORA-00909: 参数个数无效
SQL> select ename,nvl(sal+comm,0) from dual;
select ename,nvl(sal+comm,0) from dual
ORA-00904: "COMM": 标识符无效
SQL> select ename,nvl(sal,0)+nvl(comm,0) from dual;
select ename,nvl(sal,0)+nvl(comm,0) from dual
ORA-00904: "COMM": 标识符无效
SQL> select ename,nvl(sal,0)+nvl(comm,0)from emp;
ENAME NVL(SAL,0)+NVL(COMM,0)
---------- ----------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
MARTIN 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 rows selected
SQL> select ename,nvl(sal+comm,0) from emp;
ENAME NVL(SAL+COMM,0)
---------- ---------------
SMITH 0
ALLEN 1900
WARD 1750
JONES 0
MARTIN 2650
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 1500
ADAMS 0
JAMES 0
FORD 0
MILLER 0
14 rows selected
SQL> select ename,vnl2(sal,sal,0)+nvl2(comm,comm,0) from emp;
select ename,vnl2(sal,sal,0)+nvl2(comm,comm,0) from emp
ORA-00904: "VNL2": 标识符无效
SQL> select ename,nvl2(sal,sal,0)+nvl(comm,comm,0) from emp;
select ename,nvl2(sal,sal,0)+nvl(comm,comm,0) from emp
ORA-00909: 参数个数无效
SQL> select ename,nvl2(sal,sal,0)+nvl2(comm,comm,0) from emp;
ENAME NVL2(SAL,SAL,0)+NVL2(COMM,COMM
---------- ------------------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
MARTIN 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 rows selected
SQL> select * from emp where deptno=30;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7900 JAMES CLERK 7698 1981-12-3 950.00 30
6 rows selected
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from bonus;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
SQL> select ename,deptno,job ,sal where lower(job)='clerk';
select ename,deptno,job ,sal where lower(job)='clerk'
ORA-00923: 未找到要求的 FROM 关键字
SQL> select ename ,deptno,job,sal from emp where lower(job)='clerk';
ENAME DEPTNO JOB SAL
---------- ------ --------- ---------
SMITH 20 CLERK 800.00
ADAMS 20 CLERK 1100.00
JAMES 30 CLERK 950.00
MILLER 10 CLERK 1300.00
SQL>