select deptno,avg(grade) avg_grade (
select ename,deptno,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal) ) group by deptno ) group by deptno 正确写法:
select dname,t1.deptno,grade,avg_sal from (
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal) ) t1 join dept on (t1.deptno = dept.deptno) where t1.grade = (
select min(grade) from (
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal) ) )
8.视图:view就是一张虚表,一个子查询 conn sys/change_on_install as sysdba; grant create table,create view to scott; conn scott/tiger; 创建视图
create view v$_dept_avg_sal_info as select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal) 使用视图后可以简写
select dname,t1.deptno,grade,avg_sal_info from
v$_dept_avg_sal t1 join dept on (t1.deptno = dept.deptno) where t1.grade = (
select min(grade) from v$_dept_avg_sal_info )
9.求比普通员工最高薪水还要高的经理人的名称 先求普通员工的最高薪水
select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null);
select ename from emp where empno in
(select distinct mgr from emp where mgr is not null) and sal > (
select max(sal) from emp where empno not in
(select distinct mgr from emp where mgr is not null); )
第二十四课
备份与恢复 --backup scott;exp;import the date; DOS环境下:exp导出,imp导入
5
尚学堂马士兵Oracle学习笔记_IT/计算机_专业资料。自己整理的--尚学堂马士兵Oracle学习笔记尚学堂马士兵 Oracle 学习笔记--1 学习笔记---1 01_sqlplus_...
尚学堂马士兵oracle笔记53节完整版_计算机软件及应用_IT/计算机_专业资料。尚学堂马士兵oracle笔记53节完整版尚学堂马士兵老师 oracle 笔记 (2008-10-30 10:17:...
下载-尚学堂马士兵oracle笔记53节完整版_文学_高等教育_教育专区。51CTO下载-尚学堂马士兵oracle笔记53节完整版尚学堂马士兵老师 oracle 笔记 (2008-10-30 10:...
尚学堂马士兵Oracle教程笔记(2011-03-28 09:04:04)转载标签: oracle笔记 分类: Oracle Java代码检查Oracle安装 首先,以超级管理员的身份登录oracle sqlplus sys/...
马士兵oracle课上笔记马士兵oracle课上笔记隐藏>> 尚学堂马士兵老师 oracle 笔记 (2008-10-30 10:17:39) 第一课:客户端 1. Sql Plus(客户端) ,命令行...
_info t1 ) 版本二: 版本二: 笔记(一 尚学堂 Oracle 笔记 一)2009-01-18 13:401、http://127.0.0.1:5560/isqlplus/ 访问自己的或者别 人的 oracle...
尚学堂 马士兵 oracle笔记_IT/计算机_专业资料。尚学堂 马士兵 oracle笔记第一课:客户端 1. Sql Plus(客户端),命令行直接输入:sqlplus,然后按提示输入用户名,...
尚学堂马士兵老师oracle,plsql笔记_英语学习_外语学习_教育专区。尚学堂马士兵老师oracle笔记PL/SQL declare v_name varchar2(20); begin v_name := 'myname...
尚学堂 Oracle 课堂笔记(收藏) 2009-04-13 15:39 1.desc(描述) emp 描述 emp 这张表 2.desc dept 部门表 3.desc salgrade 薪水等级 4.select *from ...
v$_dept_avg-sal_info t1 ) 版本二: 尚学堂 Oracle 笔记(一)2009-01-18 13:401、http://127.0.0.1:5560/isqlplus/ 访问自 己的或者别人的 oracle。...
热门文档