Oracle练习题(六)
(1) 通过create table emp_bak as select * from emp 语句创建表 emp_bak
create table emp_bak as select * from emp;
(2) 在emp_bak 表中插入一条记录
insert into emp_bak values(1234,‘zhangsan’,‘clerk’,‘7902’,sysdate,2500,100,30);
(3) 删除emp_bak表中工资低于本部门平均工资的员工信息
delete from emp_bak where empno in (select empno from emp e1,(
select avg(sal) avg_sal,deptno from emp group by deptno ) e2 where e1.deptno = e2 .deptno and e1.sal<avg_sal);
(4) 将20号部门中从事SALES工作的员工的工资增加100元
update emp_bak set sal = sal+100 where deptno = 20 and job = ‘SALES’;
(5) 将30号部门员工的工资修改为10号部门的平均工资
update emp_bak set sal = (select avg(sal) from emp where deptno = 10) where deptno = 30;
(6) 删除职业和SMITH一样,但部门和SMITH不一样的员工信息
delete from emp where empno in(select empno from emp where job = (select job from emp where ename =‘SMITH’) and deptno != (select deptno from emp where ename =‘SMITH’));