Oracle第三次作业.doc

1.1、 向emp表中插入一纪录，员工TOM，80年1月10日入职，薪金为3000，没有补贴（comm）insertintoemp(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=10 and job=MANAGER)3. 将emps表中的与emp表中scott用户具有相同工作岗位的人的工资更改为原来的105% update emps set sal=sal*1.05where job =(select job from emp where ename = SCOTT)4、 将emp表中的属于同一工资等级的且此级别人数最多的那些人的工资增加3%update empset sal=(1+0.03)*salwhere empno in(select empnofrom emp,salgrade swhere sal between s.losal and s.hisal and grade in(select gradefrom (select grade,count(empno) numfrom emp e,salgrade swhere sal between s.losal and s.hisalgroup by grade) where num =(select max(t.num)from(select count(empno) numfrom emp e,salgrade swhere sal between s.losal and s.hisalgroup by grade) t );5、 将emp表中的部门平均工资最低的部门的所有人按照工资等级分别增加1级5%，2级4%，3级3%，4级2%，5级1%。create table temptbasselect empno,sal,decode(grade,1,1.05,2,1.04,3,1.03,4,1.02,5,1.01) addgfrom emp e,salgrade swhere e.sal between s.losal and s.hisal and deptno in(select deptno from(select deptno,avg(sal) savgfrom empgroup by deptno)where savg=(select min(t.savg)from(select avg(sal) savgfrom empgroup by deptno) t);update temptb set sal=sal*addg;6、 将emp表中岗位平均工资最高的岗位的所有人插入到新表hi_job_emp.create table hi_job_empasselect empno,ename,job,hiredate,sal,comm,deptnofrom empwhere job =(select jobfrom (select job,avg(sal) as avgsalfrom emp group by job) twhere t.avgsal=(select max(avgsal)from(select job,avg(sal) as avgsalfrom emp group by job);2、 7. 创建my_employee表，并向表中添加数据，数据参考如下：IDLast_name First_nameUserIDSalary 1PatelRalph rpatel795 2DancsBettybdancs860 3 BiriBenbbiri110 4 NewmanChardcnewman750 5 RopeburnAudryapopebur1550 1、编写如下的脚本: set echo off set feedback off prompt Creating The My_employee table. Please wait. create table my_employee ( id number(4) constraint my_employee_id_nn not null, last_name varchar2(25), first_name varchar2(25), userid varchar(28), salary number(9,2) ); (1).执行该脚本 (2).显示my_employee表的结构 (3).向my_employee表中添加首条纪录，要求不在insert语句中使用字段列表 (4).向my_employee表中添加第二条纪录，要求在insert语句中使用字段列表 (5).验证数据是否添加成功 (6).创建脚本文件loademp.sql，以交互方式向向my_employee表添加纪录。提示用户输入雇员的id, first_name, last_name, salary, userid(由first_name的第一个字母及last_name的前7个字母组成) (7).运行脚本，插入下两条纪录。 (8).验证表中的纪录 (9).使数据的添加成为永久性的。 2、将3号员工的Last_name修改为Drexler 3、将所有工资小于900的员工的工资修改为1000，并验证数据修改 4、将Betty Dancs从my_employee表中删除，验证删除后的结果，并使数据修改变为永久的。 5、运行脚本文件loademp.sql添加最后一条纪录，并验证数据的添加。 6、将当前数据状态保存为一个事务存储点，删除表中的所有数据，并确认表是否为空。 7、放弃所有的数据删除，并将数据恢复到删除前的状态，并验证数据是否已经恢复。 8、把数据的变化变为永久的。（3）insert into my_employee values(1,Patel,Ralph,rpatel,795)（4）insert into my_employee(id,last_name,first_name,userid,salary) values(2,Dancs,Betty,bdancs,860)（6）2. update my_employee set last_name=Drexlerwhere id=33. update my_employee set salary=1000where salary9004. delete my_employeewhere first_name=Betty第五部分综合练习1、 创建一个查询显示如下信息：EMP_INFThe Job Title for SCOTT is sales .The Job Title for MILLER is clerk .已选择14行。 select The job Title |ename| is |jobfrom empdeclarecursor c_emp is select ename,job from emp;v_ename emp.ename%type;v_job emp.job%type;v_count binary_integer;begin select count(rowid) into v_count from emp; open c_emp; for i in 1.v_count loop fetch c_emp into v_ename,v_job; dbms_output.put_line(The Job Title for|v_ename|is|v_job ); end loop;end2、 2. 检索姓名最后一个字符为N的雇员的姓名（姓名首字母大写），还显示姓名的长度，以及姓名中的字母A的位置。select ename,initcap(ename),length(ename),instr(ename,A,1,1) from empwhere ename like %N3. 计算出雇员进入公司的星期数select ename,(sysdate-hiredate)/7 星期数 from emp4. 显示受雇时间不满250个月的雇员的编号、受雇日期、受雇的月数、满六个月的复审日期受雇后的第一个星期五以及受雇当月的最后一天 select empno,hiredate,months_between(sysdate,hiredate) 月数,add_months(hiredate,6) 复审日期,next_day(hiredate,星期五) 星期五,last_day(hiredate) 本月最后一天from empwhere months_between(sysdate,hiredate)2505 以$99，999的形式显示雇员工资及雇员的姓名、部门名称和工资等级select to_char(sal,$99,999) salary,ename,dname,gradefrom emp,dept,salgradewhere emp.deptno=dept.deptno and sal between salgrade.losal and salgrade.hisal6显示没有上级管理者的公司首脑的姓名，并在MGR列上显示No Manager。select ename,decode(mgr,null,No Manager) from emp7显示雇员雇佣期满6个月后下一个星期五的日期，显示格式为yyyy年mm月dd日，并且按照雇佣日期排序。select ename,hiredate,to_char(next_day(add_months(hiredate,6),星期五),yyyy年MM月DD日) from emporder by hiredate8显示雇员姓名、受雇日期及工资复审日期（复审日期为受雇后6个月后的第一个星期一），复审日期以列标题review显示，并且显示的日期形式为“星期一,12月的第23天，1998年”。select ename,hiredate,to_char(next_day(add_months(hiredate,6),星期一),DY MM月的第DD天，yyyy年) reviewfrom emp9显示每个雇员的姓名并计算出从受雇日期起到目前一共工作了多少个月，以列标题month_worked显示select ename,months_between(sysdate,hiredate) months_worked from emp10编写一个查询显示名字以J、A、M开头的雇员的姓名及姓名所占的字符数，姓名的显示格式为第一个字母大写其他字母小写，为每个列设置合适的列标题select initcap(ename) 姓名,length(ename) 字符数 from empwhere ename like J% or ename like A% or ename likeM%11。显示雇员的姓名、受雇日期及受雇当天是星期几（列标题为DAY），并以DAY升序排列。select ename,hiredate,to_char(hiredate,DY) DAYfrom emporder by DAY12创建一个查询显示雇员姓名及其奖金