应用随机过程习题

1、使用使用scott/tiger用户下的用户下的emp表和表和dept表完成下列练习表完成下列练习1列出至少有一个员工的所有部门。2列出薪金比“SMITH”多的所有员工。3列出所有员工的姓名及其直接上级的姓名。1.select distinct dnamefrom dept where deptno in (select distinct deptno from emp);2.select ename,job,mgr,hiredate,salfrom empwhere sal (select sal from emp where ename=SMITH);3.select e.ename, e.

2、mgr , w.enamefrom emp e, emp wwhere e.mgr=w.empno;4列出受雇日期早于其直接上级的所有员工。5列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。6列出所有“CLERK”（办事员）的姓名及其部门名称。4.select e.ename, e.mgr , e.hiredate,w.ename,w.hiredatefrom emp e, emp wwhere e.mgr=w.empnoand e.hiredate1500;8.select e.ename,d.dname from emp e,dept dwhere e.deptno=d.de

3、ptno 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 empwhere sal (select avg(sal) from emp );10列出与“SCOTT”从事相同工作的所有员工。11列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。12列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 10.select ename, job f

4、rom empwhere job = (select job from emp where ename=upper(scott);11.select ename, sal,job from empwhere sal in (select sal from emp where deptno=30);12.select ename, sal,job from empwhere sal all (select sal from emp where deptno=30);或者select ename ,sal from emp where sal(select max(sal) from emp wh

5、ere 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 dwhere e.deptno(+)= d.deptnogroup by d.deptno,dname;或者下面的方法，这种方法更好理解，把需要汇总的生成一个视图e。select e.*,d.d

6、name from (select deptno, count(ename) ,avg(sal),avg(months_between(sysdate,hiredate)/12) from emp group by deptno) e, dept dwhere e.deptno =d.deptno;14.select d.dname, e.ename, sal+nvl(comm,0) from emp e, dept dwhere d.deptno=e.deptno;15. select e.ename,e.job, e.deptno ,d.job, d.deptno from emp e,

7、emp dwhere e.job=d.job and e.deptnod.deptno;16列出所有部门的详细信息和部门人数。17列出各种工作的最低工资。18列出各个部门的MANAGER（经理）的最低薪金。19列出所有员工的年工资,按年薪从低到高排序。 16.select count(e.ename),d.dnamefrom emp e, dept dwhere e.deptno(+)= d.deptnogroup by e.deptno,d.dname;或者写成select d.deptno,e.*,d.dname from (select deptno,count(ename) from

8、emp group by deptno) e,dept dwhere 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 yearsalfrom emporder by yearsal ;20.查找EMP表中前5条记录 21查找EMP表中10条以后的记录 22查找EMP

9、表中薪水第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;表的约束是复制不过来的，需要自己去添加表的约束是复制不过来的，需要自己去添加create table myd

10、ept1 as select * from dept where 1=2insert into mydept1 select * from dept where deptno20;或者create table mydept1 as select * from dept where deptno20;2.alter table myempadd constraint myemp_empno_pk primary key (empno);创建以下表teachercreate table teacher( teacherxh varchar2(10) primary key, teachername

11、 varchar2(20) ); 插入记录，要求:教师的编号的格式是TH00001，TH00002.建立序列 create sequence teacher_seq maxvalue 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.以交互的方式给部门表插入一条记录，如果出现主键冲突的异常，请显示“部门号已被占用”的字样declare CURSOR c_emp(p_empno emp.empno%TYPE) IS SELECT emp.job,emp.empno,dept.lo

13、c,emp.deptno FROM emp,dept WHERE emp.deptno=dept.deptno and emp.empno=p_empno; eno emp.empno%type;begineno:=&no; for v_emp in c_emp(eno) loop if v_emp.job=MANAGER and v_emp.loc=DALLAS then update emp set sal = sal *1.15 where empno=eno; end if; if v_emp.job=CLERK and v_emp.loc=NEW YORK then upda

14、te emp set sal = sal *0.95 where empno=eno; end if; end loop;end;3declare cursor mycur is select emp.empno,emp.job,dept.loc from emp ,dept where emp.deptno=dept.deptno;begin for v_mycur in mycur loop if v_mycur.job=MANAGER and v_mycur.loc=DALLAS then update emp set sal = sal *1.15 where empno=v_mycu

15、r.empno; end if; if v_mycur.job=CLERK and v_mycur.loc=NEW YORK then update emp set sal = sal *0.95 where empno=v_mycur.empno; end if; end loop;end;declare cursor mycur is select * from emp for update; dno1 dept.deptno%type; dno2 dept.deptno%type; begin select deptno into dno1 from dept where loc=DAL

16、LAS;select deptno into dno2 from dept where loc=NEW YORK;for v_mycur in mycur loop if v_mycur.deptno=dno1 then update emp set sal = sal *1.15 where current of mycur; end if; if v_mycur.deptno=dno2 then update emp set sal = sal *0.95 where current of mycur; end if; end loop; end;创建员工表create table emp

17、 (emp_id number(5), emp_name varchar2(20), emp_salary number(4),job varchar2(20), dept_id number(3);部门表create table dept (dept_id number(3), dept_name varchar2(20), loc varchar2(20);编写一个数据库触发器，当任何时候某个部门从dept中删除时，该触发器将从emp表中删除该部门的所有雇员。 create or replace trigger tr_update_dept after delete on dept for

18、 each row begin delete from emp where deptno=:old.deptno; end;a)建立一个新的用户mk 密码m123;b)登录到用户mk 建立一张表 test;c)希望在scott用户下能看到mk用户中test表的数据?创建一个函数，以部门号为参数，返回该部门的平均工资。FUNCTION return_avgsal(p_deptno emp.deptno%TYPE) RETURN emp.sal%TYPE AS v_sal emp.sal%TYPE; BEGIN SELECT avg(sal) INTO v_sal FROM emp WHERE d

19、eptno=p_deptno; RETURN v_sal; END return_avgsal;创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。CREATE OR REPLACE PROCEDURE show_emp(p_deptno emp.deptno%TYPE)AS v_sal emp.sal%TYPE;BEGINSELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno;DBMS_OUTPUT.PUT_LINE(p_deptno| |average salary is:|v_sa

20、l);FOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno AND salv_sal) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.ename);END LOOP;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The department doesnt exists!);END show_emp;有某个学生运动会比赛信息的数据库，保存了如下的表：运动员sporter（运动员编号sporterid，运动员姓名name，运动员性别se

21、x，所属系号department）项目item（项目编号itemid，项目名称itemname，项目比赛地点location）成绩grade（运动员编号id，项目编号itemid，积分mark）请用SQL语句完成如下功能：1. 建表，注意满足如下要求：a) 定义各个表的主键外键约束b) 运动员的姓名和所属系别不能为空值。c) 积分要么为空值，要么为6,4,2,0, 分别代表第一，二，三名和其他名次的积分，注意名次可以有并列名次，后面的排名不往前提升，例如，如果有两个并列第一，则没有第二名。2. 往表中插入数据：运动员（ 1001, 李明，男，计算机系 1002，张三，男，数学系 1003, 李

22、四，男，计算机系 1004, 王二，男，物理系 1005, 李娜，女，心理系 1006, 孙丽，女，数学系）项目（ x001, 男子五千米，一操场 x002，男子标枪，一操场 x003, 男子跳远，二操场 x004, 女子跳高，二操场 x005, 女子三千米，三操场）积分（ 1001, x001, 6 1002, x001, 4 1003, x001, 2 1004, x001, 0 1001, x003, 4 1002, x003, 6 1004, x003, 2 1005, x004, 6 1006, x004, 4)建立sporter 表create table sporter(spor

23、terid number(4) primary key,name varchar2(20) not null,sex char(2),department varchar2(20) not null);建立item 表create table item(itemid varchar2(6) primary key,itemname varchar2(20), location varchar2(20);建立grade表( 记住id 不是主键，否则违反唯一性了）create table grade1(itemid varchar2(6),mark number(2),constraint fk_

24、itemidforeign key( itemid) references item( itemid),constraint mark_ck check ( mark in( 6,4,2,0);insert into sporter( sporterid, name,sex,department)values ( 1001, 李明, 男,计算机);insert into sporter( sporterid, name,sex,department)values ( 1002, 张三, 男,数学系);insert into sporter( sporterid, name,sex,department)values ( 1003, 李四, 男,计算机);insert into sporter( sporterid, name,sex,department)values ( 1004, 王二, 男,物理系);insert into sporter( sporterid, name,sex,department)values ( 1005, 李娜, 女,心理系);insert into sporter( sporterid, name,sex,department)values ( 1006, 孙丽, 女,数学系);insert into