Oracle第三次作业.pdf

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=10 and 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 from emp e,salgrade s where e.sal between s.losal and s.hisal and deptno in(select deptno from(select deptno,avg(sal)savg from emp group by deptno)where savg=(select min(t.savg)from(select avg(sal)savg from emp group by deptno)t);update temptb set sal=sal*addg;6、将 emp 表中岗位平均工资最高的岗位的所有人插入到新表 hi_job_emp.create table hi_job_emp as select empno,ename,job,hiredate,sal,comm,deptno from emp where job=(select job from(select job,avg(sal)as avgsal from emp group by job)t where t.avgsal=(select max(avgsal)from(select job,avg(sal)as avgsal from emp group by job);2、7.创建 my_employee 表，并向表中添加数据，数据参考如下：ID Last_name First_name UserID Salary 1 Patel Ralph rpatel 795 2 Dancs Betty bdancs 860 3 Biri Ben bbiri 110 4 Newman Chard cnewman 750 5 Ropeburn Audry apopebur 1550 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=Drexler where id=3 3.update my_employee set salary=1000 where salary900 4.delete my_employee where first_name=Betty 第五部分综合练习 1、创建一个查询显示如下信息：EMP_INF The Job Title for SCOTT is sales.The Job Title for MILLER is clerk.已选择 14 行。select The job Title|ename|is|job from emp declare cursor 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;end 2、2.检索姓名最后一个字符为N的雇员的姓名（姓名首字母大写），还显示姓名的长度，以及姓名中的字母A的位置。select ename,initcap(ename),length(ename),instr(ename,A,1,1)from emp where ename like%N 3.计算出雇员进入公司的星期数 select ename,(sysdate-hiredate)/7 星期数 from emp 4.显示受雇时间不满250个月的雇员的编号、受雇日期、受雇的月数、满六个月的复审日期受雇后的第一个星期五以及受雇当月的最后一天 select empno,hiredate,months_between(sysdate,hiredate)月数,add_months(hiredate,6)复审日期,next_day(hiredate,星期五)星期五,last_day(hiredate)本月最后一天 from emp where months_between(sysdate,hiredate)250 5 以$99，999的形式显示雇员工资及雇员的姓名、部门名称和工资等级 select to_char(sal,$99,999)salary,ename,dname,grade from emp,dept,salgrade where emp.deptno=dept.deptno and sal between salgrade.losal and salgrade.hisal 6显示没有上级管理者的公司首脑的姓名，并在 MGR 列上显示No Manager。select ename,decode(mgr,null,No Manager)from emp 7显示雇员雇佣期满 6 个月后下一个星期五的日期，显示格式为yyyy 年 mm 月 dd 日，并且按照雇佣日期排序。select ename,hiredate,to_char(next_day(add_months(hiredate,6),星期五),yyyy年MM月DD日)from emp order by hiredate 8显示雇员姓名、受雇日期及工资复审日期（复审日期为受雇后 6 个月后的第一个星期一），复审日期以列标题 review 显示，并且显示的日期形式为“星期一,12 月的第 23 天，1998 年”。select ename,hiredate,to_char(next_day(add_months(hiredate,6),星期一),DY MM月的第DD天，yyyy年)review from emp 9显示每个雇员的姓名并计算出从受雇日期起到目前一共工作了多少个月，以列标题month_worked显示 select ename,months_between(sysdate,hiredate)months_worked from emp 10编写一个查询显示名字以 J、A、M 开头的雇员的姓名及姓名所占的字符数，姓名的显示格式为第一个字母大写其他字母小写，为每个列设置合适的列标题 select initcap(ename)姓名,length(ename)字符数 from emp where ename like J%or ename like A%or ename likeM%11。显示雇员的姓名、受雇日期及受雇当天是星期几（列标题为 DAY），并以 DAY 升序排列。select ename,hiredate,to_char(hiredate,DY)DAY from emp order by DAY 12创建一个查询显示雇员姓名及其奖金（列标题为 comm），如果该雇员不能获得奖金则以No Commission显示。select ename,comm|decode(comm,null,No Commission)from emp;13创建一个查询显示员工名，工资，部门名称和工资等级。select ename,sal,dname,grade from emp,dept,salgrade where emp.deptno=dept.deptno and sal between salgrade.losal and salgrade.hisal