605.txt

关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品

ORA-00904: "DEPT": 标识符无效

SQL> select * from emp where sal > (select max(sal) from emp group by dept having deptno = 20)
  2  ;

select * from emp where sal > (select max(sal) from emp group by dept having deptno = 20)

ORA-00904: "DEPT": 标识符无效

SQL> select * from emp where sal > (select max(sal) from emp group by deptno having deptno = 20)
  2  ;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT       1981-11-17    5000.00               10

SQL> select * from emp where sal > (select sal from emp where deptno = 20);

select * from emp where sal > (select sal from emp where deptno = 20)

ORA-01427: 单行子查询返回多个行

SQL> select * from emp where sal > all(select sal from emp where deptno = 20);

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7839 KING       PRESIDENT       1981-11-17    5000.00               10

SQL> select deptno,(select avg(sal) from emp group by deptno) from emp;

select deptno,(select avg(sal) from emp group by deptno) from emp

ORA-01427: 单行子查询返回多个行

SQL> select deptno,(select avg(sal) from emp e2 group by deptno where e2.deptno = e1.deptno) from emp e1;

select deptno,(select avg(sal) from emp e2 group by deptno where e2.deptno = e1.deptno) from emp e1

ORA-00907: 缺失右括号

SQL> select deptno,(select avg(sal) from emp e2 group by deptno having e2.deptno = e1.deptno) from emp e1;

DEPTNO (SELECTAVG(SAL)FROMEMPE2GROUPB
------ ------------------------------
    20                           2175
    30               1566.66666666667
    30               1566.66666666667
    20                           2175
    30               1566.66666666667
    30               1566.66666666667
    10               2916.66666666667
    20                           2175
    10               2916.66666666667
    30               1566.66666666667
    20                           2175
    30               1566.66666666667
    20                           2175
    10               2916.66666666667

14 rows selected

SQL> select distinct deptno,(select avg(sal) from emp e2 group by deptno having e2.deptno = e1.deptno) from emp e1;

DEPTNO (SELECTAVG(SAL)FROMEMPE2GROUPB
------ ------------------------------
    10               2916.66666666667
    20                           2175
    30               1566.66666666667

SQL> select deptno as 部门编号,(select dname,loc from dept   d where d.deptno = emp.deptno ) 部门名字, avg(sal) 平均工资 from emp group by deptno;

select deptno as 部门编号,(select dname,loc from dept   d where d.deptno = emp.deptno ) 部门名字, avg(sal) 平均工资 from emp group by deptno

ORA-00913: 值过多

SQL> select deptno as 部门编号,(select dname,loc from dept   d where d.deptno = emp.deptno ), avg(sal) 平均工资 from emp group by deptno;

select deptno as 部门编号,(select dname,loc from dept   d where d.deptno = emp.deptno ), avg(sal) 平均工资 from emp group by deptno

ORA-00913: 值过多

SQL> select deptno as 部门编号,(select loc from dept   d where d.deptno = emp.deptno ), avg(sal) 平均工资 from emp group by deptno;

部门编号 (SELECTLOCFROMDEPTDWHERED.DEPT   平均工资
-------- ------------------------------ ----------
      10 NEW YORK                       2916.66666
      20 DALLAS                               2175
      30 CHICAGO                        1566.66666

SQL> select * from myemp;

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 name from emp e1 ;

select name from emp e1

ORA-00904: "NAME": 标识符无效

SQL> select ename from emp e1 ;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected

SQL> select ename,(select job,sal from myemp where myemp.empno = e1.empno) from emp e1 ;

select ename,(select job,sal from myemp where myemp.empno = e1.empno) from emp e1

ORA-00913: 值过多

SQL> select ename,(select job from myemp where myemp.empno = e1.empno) from emp e1 ;

ENAME      (SELECTJOBFROMMYEMPWHEREMYEMP.
---------- ------------------------------
SMITH      CLERK