oracle数据库sql语句05

conn system/12345678
alter user scott account unlock;
alter user scott identified by 123456;
GRANT CREATE VIEW TO SCOTT;
conn scott/123456


create table emp1 as select *  from emp;


指定标签名 插入数据
insert into emp2(empno,ename,sal) values('1111', 'smith', 5200);


insert into dept values('1111', 'smith', 5200);


创建最高工资，最低工资表
create table dept_sal(depno number(2), maxsal number(7,2), minsal number(7,2));


查询部门最大工资：
select max(sal),min(sal) from emp where deptno = 120;


分组查询最高工资和最低工资：
select deptno,max(sal),min(sal) from emp group by deptno;


插入查询的最高工资和最低工资：
insert into dept_sal select deptno,max(sal),min(sal) from emp group by deptno;


插入员工编号为1234 姓名为 liuwei 其他与员工号为150信息相同：
insert into emp select 1234, 'liuwei', job, hiredate, mgr, sal, comm, deptno from emp where empno='7902';




多表插入：
create table emp3 as select empno, ename,job from emp where 1=2;#创建emp3
create table emp4 as select empno, ename,job from emp where 1=2;#创建emp4




insert all 
into emp3 values(empno,ename,job) 
into emp4 values(empno,ename,job) 
select * from emp where sal > 1000;#多表插入


#多条件多表插入：
create table emp10 as select empno, ename,job,deptno from emp where 1=2;
create table emp20 as select empno, ename,job,deptno from emp where 1=2;
create table emp30 as select empno, ename,job,deptno from emp where 1=2;
#把部门放到指定表
insert all
when deptno = 10 then into emp10
when deptno = 20 then into emp20
when deptno = 30 then into emp30
select empno, ename,job,deptno from emp;










create table emp10 as select empno, ename,job,deptno from emp where 1=2;










insert all 
into emp3 values(empno,ename,job) 
into emp4 values(empno,ename,job) 
select * from emp3;




--创建emp1,无数据;
create table emp1 as select * from emp where 1=2;


--在emp1表中插入部门编号为10的员工信息
insert into emp1 select * from emp where deptno=10;




--将emp1表中所以工资设置为5000
update emp1 set sal=5000;


--将编号7839设置为部门平均工资
update emp1 set sal=(select avg(sal) from emp1) where empno = 7839;










#事务处理：


--提交
commit;


--回滚
rollback;