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;
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;