1.希望Scott用户的岗位，工资，补助和Smith一样
update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT'
2.希望一次向某一个表中插入大量数据
insert into student (id,ename) select (empno,ename) from emp where empno=10;
3设置只读事务
set transaction read only(只用于sys管理其他用户)
4只想读取表的结构
create table student2 as select * from student where 1=2;
5向表中插入多行数据
insert into student
select '1','zhangyaoming','22' from dual
union
select '2','xiaozhang','22' from dual
union
select '3','huihui','22' from dual;
6>求部门中哪些人的薪水最高
select ename,sal,deptno from emp
join(select max(sal)max_sal,deptno from emp group by deptno)e
on emp.sal=e.max_sal and emp.deptno=e.deptno;
7>球部门平均薪水的等级
select e.avg_sal,e.deptno,grade from
(select avg(sal) avg_sal,deptno from emp group by deptno)e
join salgrade on(e.avg_sal between losal and hisal );
8>雇员中哪些人是经理人
select ename from emp where empno in
(select distinct mgr from emp)
9>不准用组函数求薪水最高值
select ename from emp where sal not in
(select e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal))
10>不准用组函数求薪水最小值
select ename from emp where sal not in
(select e1.sal from emp e1 join emp e2 on(e1.sal > e2.sal))
11>求比普通员工的最高薪水还要高的经理人的名称
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)
)
12>求薪水最高的前五名雇员
select e.*,rownum rn from
(select ename from emp order by sal desc)e where rownum<=5;
13>求薪水最高的雇员（第六到第十）
select * from(
select e.*,rownum rn from
(select ename from emp order by sal desc)e where rownum<=10
) where rn>6;