《数学实验maple习题1》由会员分享，可在线阅读，更多相关《数学实验maple习题1（33页珍藏版）》请在金锄头文库上搜索。
1、使用scott/tiger用户下的emp表和dept表完成下列练习,1列出至少有一个员工的所有部门。2列出薪金比“SMITH”多的所有员工。3列出所有员工的姓名及其直接上级的姓名。,1.select distinct dname from dept where deptno in (select distinct deptno from emp);2.select ename,job,mgr,hiredate,sal from emp where sal (select sal from emp where ename=SMITH); 3.select e.ename, e.mgr , w.e
2、name from emp e, emp w where e.mgr=w.empno;,4列出受雇日期早于其直接上级的所有员工。5列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。6列出所有“CLERK”（办事员）的姓名及其部门名称。,4.select e.ename, e.mgr , e.hiredate,w.ename,w.hiredate from emp e, emp w where e.mgr=w.empno and e.hiredate1500;8.select e.ename,d.dname from emp e,dept d where e.deptno=d.dep
3、tno and d.dname=upper(sales); 或者： select ename from emp where deptno = (select deptno from dept where dname=upper(SALES);9.select ename, job, sal from emp where sal (select avg(sal) from emp );,10列出与“SCOTT”从事相同工作的所有员工。11列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。12列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。,10.select ename, jo
4、b from emp where job = (select job from emp where ename=upper(scott); 11.select ename, sal,job from emp where sal in (select sal from emp where deptno=30); 12.select ename, sal,job from emp where sal all (select sal from emp where deptno=30); 或者 select ename ,sal from emp where sal(select max(sal) f
5、rom emp where deptno=30);,13列出在每个部门工作的员工数量、平均工资和平均服务期限。14列出所有员工的姓名、部门名称和工资。15列出从事同一种工作但属于不同部门的员工的一种组合。,13.select d.deptno,d.dname, count(e.ename), avg(e.sal), avg(months_between(sysdate,e.hiredate)from emp e, dept d where e.deptno(+)= d.deptno group by d.deptno,dname; 或者下面的方法，这种方法更好理解，把需要汇总的生成一个视图e。
6、 select e.*,d.dname from(select deptno, count(ename) ,avg(sal),avg(months_between(sysdate,hiredate)/12) from emp group by deptno) e, dept d where e.deptno =d.deptno;14.select d.dname, e.ename, sal+nvl(comm,0) from emp e, dept d where d.deptno=e.deptno;15. select e.ename,e.job, e.deptno ,d.job, d.dep
7、tno from emp e, emp d where e.job=d.job and e.deptnod.deptno;,16列出所有部门的详细信息和部门人数。17列出各种工作的最低工资。18列出各个部门的MANAGER（经理）的最低薪金。19列出所有员工的年工资,按年薪从低到高排序。,16.select count(e.ename),d.dname from emp e, dept d where e.deptno(+)= d.deptno group by e.deptno,d.dname; 或者写成 select d.deptno,e.*,d.dname from (select de
8、ptno,count(ename) from emp group by deptno) e,dept d where e.deptno(+)= d.deptno;17.select job,min(sal+nvl(comm,0)from emp group by job;18.select deptno,min(sal) from emp where job=upper(manager) group by deptno ;19.select ename , (sal+nvl(comm,0)*12 yearsal from emp order by yearsal ;,20.查找EMP表中前5条
9、记录 21查找EMP表中10条以后的记录 22查找EMP表中薪水第5高的员工,20 SELECT * FROM emp WHERE ROWNUM10; 22 SELECT * FROM (SELECT a.*,rownum AS rn FROM emp a ORDER BY sal DESC ) WHERE rn=5;,1、创建表myemp和emp表具有相同的结构和记录。（若只是结构而没有数据呢？只复制部分数据呢？）2、给myemp的empno列添加主建约束。,1.create table myemp as select * from emp; 表的约束是复制不过来的，需要自己去添加 crea
10、te table mydept1 as select * from dept where 1=2 insert into mydept1 select * from dept where deptno20; 或者create table mydept1 as select * from dept where deptno20; 2.alter table myemp add constraint myemp_empno_pk primary key (empno);,创建以下表teacher create table teacher(teacherxh varchar2(10) primary
11、 key, teachername varchar2(20);插入记录，要求:教师的编号的格式是TH00001，TH00002.,建立序列create sequence teacher_seqmaxvalue 99999;insert into teacher values (TH|ltrim(to_char(teacher_seq.nextval,00000),张三);insert into teacher values (TH|ltrim(to_char(teacher_seq.nextval,00000),李);,使用PL/SQL块编程实现，注意必要的异常处理 1.输入一个员工号，输出该
12、员工的姓名、薪金和大概的服务年限（按年月日显示） 2.接收一个员工号，输出该员工所在部门的名称 3.接收一个员工号，如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15； 如果该员工职位是CLERK，并且在NEW YORK工作那么就给他薪金扣除5;其他情况不作处理。 4.接收一个员工号，输出这个员工所在部门的平均工资 5.以交互的方式给部门表插入一条记录，如果出现主键冲突的异常，请显示“部门号已被占用”的字样,declareCURSOR c_emp(p_empno emp.empno%TYPE) IS SELECT emp.job,emp.empno,dept.loc,e
13、mp.deptno FROM emp,dept WHERE emp.deptno=dept.deptno and emp.empno=p_empno;eno emp.empno%type; begin eno:=,3 declarecursor mycur is select emp.empno,emp.job,dept.loc from emp ,dept where emp.deptno=dept.deptno; begin for v_mycur in mycur loopif v_mycur.job=MANAGER and v_mycur.loc=DALLAS then update
14、emp set sal = sal *1.15 where empno=v_mycur.empno;end if;if v_mycur.job=CLERK and v_mycur.loc=NEW YORK thenupdate emp set sal = sal *0.95 where empno=v_mycur.empno;end if;end loop; end;,declarecursor mycur is select * from emp for update;dno1 dept.deptno%type;dno2 dept.deptno%type;begin select deptn
15、o into dno1 from dept where loc=DALLAS; select deptno into dno2 from dept where loc=NEW YORK; for v_mycur in mycur loopif v_mycur.deptno=dno1 thenupdate emp set sal = sal *1.15 where current of mycur; end if;if v_mycur.deptno=dno2 thenupdate emp set sal = sal *0.95 where current of mycur; end if;end loop;end;,
