1.
1、向emp表中插入一纪录,员工TOM,80年1月10日入职,薪金为3000,没有补贴(comm)insert into emp (empno,ename,hiredate,sal)
values(7783,'TOM',to_date('1980-1-10','yyyy-MM-DD'),3000)
2. 利用子查询建立表emps,与表emp的结构相同,但是只是需要存储10号部门和岗位为MANAGER的员工
create table emps as(
select empno,ename,job,mgr,hiredate,sal,comm,deptno
from emp where deptno=10and job='MANAGER')
3.将emps表中的与emp表中scott用户具有相同工作岗位的人的工资更改为原来的105% update emps set sal=sal*1.05
where job =(select job from emp where ename = 'SCOTT')
4、将emp表中的属于同一工资等级的且此级别人数最多的那些人的工资增加3%
update emp
set sal=(1+0.03)*sal
where empno in
(
select empno
from emp,salgrade s
where sal between s.losal and s.hisal and grade in(
select grade
from (
select grade,count(empno) num
from emp e,salgrade s
where sal between s.losal and s.hisal
group by grade
)
where num =(
select max(t.num)
from(
select count(empno) num
from emp e,salgrade s
where sal between s.losal and s.hisal
group by grade
) t
)
)
);
5、将emp表中的部门平均工资最低的部门的所有人按照工资等级分别增加1级5%,2级4%,3级3%,4级2%,5级1%。
create table temptb
as
select empno,sal,decode(grade,1,1.05,2,1.04,3,1.03,4,1.02,5,1.01) addg