Oracle 创表和查询表

--创建表
create table emp6 as select empno,ename,job,mgr,sal,comm,deptno from scott.emp;
select * from emp6;
rollback
drop table emp6;
--插入数据
insert into emp6 values (7788,'SCOTT','ANALYST',7566,3000,NULL,20);
insert into emp6 values (7876,'ADAMS','CLERK',7788,null,null,20);
--查询出每个部门的总工资
select deptno, sum(sal) from emp6 group by deptno 
--查询出部门平均工资超标的部门
select * from (select deptno, avg(nvl(sal,1000))as asal from emp6  group by deptno) where asal>=2500;
--查询出各部门工资最高的员工
select deptno, ename, sal from emp6 where sal in(select max(sal) from emp6 group by deptno);
--查询出每个员工主管的姓名
select e.empno,e.ename,t.ename from emp6 e ,(select ename, empno from emp6 where empno in (select mgr from emp6)) t where t.empno(+)=e.mgr;