oracle课后习题答案.doc

练习62.实训题(2)Create table exer_class(CNO number(2) primary key, CNAME varchar2(20), NUM number(3) );Create table exer_student(SNO number(4) primary key, SNAME varchar2(10) unique, SAGE number, SEX char(2), CNO number(2) );(3)Alter table exer_student add constraint ck_sage check(sage0 and sagecreate user bs identified by bs default tablespace users;SQLgrant resource,connect,create view to bs;然后再创建三个表：Create table book(no number(10) primary key,title varchar2(40) not null,author varchar2(16) not null,publish varchar2(20),pub_date date,price number(4)tablespace users;Create table reader(rno number(10) primary key,rname varchar2(10) not null)tablespace users;Create table borrow(no number(10) primary key,rno number(10) references reader(rno),borrow_date date)tablespace users;book表：Insert into book values (100001,Oracle 9i 数据库系统管理,李代平,冶金工业出版社,to_date(2003-01-01,yyyy-mm-dd),38);. .Insert into book values (100006,Oracle 8 实用教程,翁正科等,电子工业出版社,to_date(2003-07-08,yyyy-mm-dd),38);reader表：Insert into reader values (200001,张三);. .Insert into reader values (200005,刘英);borrow表：Insert into borrow values (100001,200001,to_date(2004-08-10.10:06:14,yyyy-mm-dd.hh24:mi:ss);. .Insert into borrow values (100005,200005,to_date(2004-08-10.10:06:58,yyyy-mm-dd.hh24:mi:ss);(3)Insert into book values(100007,Java网络编程,李程等,电子工业出版社,to_date(2000-08-01,yyyy-mm-dd),35)；(4)Update book set price=29 where no=100007;(5)Delete from book where no=100007;练习81) 查询100号部门的所有员工信息。 单表有条件查询select * from employees where department_id=100;2) 查询所有职位编号为“SA_MAN”的员工的员工号、员工名和部门名。 单表有条件查询select employee_id,first_name,department_id from employees where job_id like SA_MAN; 3) 查询每个员工的员工号、工资、奖金以及工资与奖金的和。 单表无条件带算术表达式查询select employee_id,salary,commission_pct*salary,salary*(1+commission_pct) from employees; (4) 查询40号部门中职位编号为“AD_ASST”和20号部门中职位编号为“SA_REP”的员工的信息。 单表有条件逻辑操作查询 select * from employees where (department_id=40 and job_id=AD_ASST) or (department_id=20 and job_id=SA_REP);5) 查询所有职位名称不是“Stock Manager”和“Purching Manager”，且工资大于或等于2000元的员工的详细信息。（多行单列子查询）select * from employees where salary=2000 and job_id in (select job_id from jobs where job_title!=Stock Manageror job_title!=Purchasing Manager); 6) 查询有奖金的员工的不同职位编号和名称。Select distinct job_id, job_title from jobs where job_id in (select job_id from employees where commission_pct is not null);(7) 查询没有奖金或奖金低于100元的员工信息。select * from employees where commission_pct is null or commission_pct*salary100 ;(8) 查询员工名(first_name)中不包含字母“S”员工。Select first_name from employees where first_name not like %S%;(9) 查询员工的姓名和入职日期，并按入职日期从先到后进行排序。Select first_name,last_name,hire_date from employees order by hire_date;10) 查询所有员工的姓名及其直接上级的姓名。 Select a.first_name,b.first_name from employees a join employees b on b.employee_id = a.manage_id; Select a.first_name,b.first_name from employees a join employees b on b.employee_id = a.manage_id; 11) 查询入职日期早于其直接上级领导的所有员工信息。 select * from employees a where hire_date(select hire_date from employees b b.employee_id=a.manager_id);select * from employees a where hire_date(select salary from employees where employee_id = 100);(18) 查询工资高于公司平均工资的所有员工信息。(反复查自己)Select * from employees where salary(select avg(salary) from employees);(19) 查询各个部门中不同职位的最高工资。 Select job_id,max(salary) from employees group by job_id;(20) 查询各个部门的人数及平均工资。 Select department_id,count(*),avg(salary ) from employees group by department_id; (21) 统计各个职位的员工人数与平均工资。 Select job_id ,count(employee_id),avg(salary) from employees group by job_id;(22) 统计每个部门中各个职位的人数与平均工资。Select department_id,job_id,count(*),avg(salary) from employees group by department_id,job_id;(23) 查询最低工资大于5000的各种工作。Select job_id,job_title from jobs where job_id in(Select job_id from employees group by job_id having min(salary)5000);(24) 查询平均工资低于6000的部门及其员工信息。select *from departments left outer join employees on departments.department_id=employees.department_idwhere departments.department_id in (select department_id from employeesgroup by department_id having avg(salary)(select max(salary) from employees where department_id=30);(28) 查询每个部门中的员工数量、平均工资和平均工作年限。 (经典的两个表的连接查询，用具体的名称替换一个表中的主键的id (解决很多人在实际运用中会遇到的不能绑定多列的问题)，也可用where来查询)Select count(*),avg(salary),avg(round(sysdate-hire_date)/365) from employees group by department_id;(29) 查询工资为某个部门平均工资的员工的信息。Select * from employees where salary in(select avg(Salary) from employees group by department_id);(30) 查询工资高于本部门平均工资的员工的信息。 Select * from employees e1 where salary(select avg(salary) from employees e2 where e2.department_id=e1.department_id )(31) 查询工资高于本部门平均工资的员工的信息及其部门的平均工资。 Select e.*,avgsalFrom employees e join (select department_id,avg(salary) avgsal from employees group by department_id) dOn e.department_id=d.department_id And e.salaryd.avgsal(32) 查询工资高于50号部门某个员工工资的员工的信息。 Select * from employees where salaryany(select salary from employees where department_id=50);(33) 查询工资、奖金与10 号部门某个员工工资、奖金都相同的员工的信息。 select employees.* from employees join (select salary,commission_pct from employees where department_id=10) t on employees.salary=t.salary and nvl(mission_pct,0)=nvl(mission_pct,0) and employees.department_id!=10;或者：Select * from employees where (salary,nvl(commission_pct) ) in (Select salary,nvl(commission_pct) from employees where department_id=10);(34) 查询部门人数大于10的部门的员工的信息。Select * from employees where department_id in(select department_id from employees group by department_id having count(*)10);(35) 查询所有员工工资都大于1000元的部门的信息。 Select * from department where department_id in (select distinct department_id from employees group by department_id having min(salary)10000);(36) 查询所有员工工资都大于5000的部门的信息及其员工信息。 select * from employees join departments on departments.department_id in (select distinct department_id from employees where department_id not in (select distinct department_id from employees where salary =4000 and max(salary)=8000);(38) 查询人数最多的部门信息。 select * from departments where department_id in ( select department_id from ( select count(*) count,department_id from employees group by department_id) where count in ( select max(count) from ( select count(*) count,department_id from employees group by department_id ) ) );(39) 查询30号部门中工资排序前3名的员工信息。 Select * from employees where department_id=30 and salary is not null and rownum=10;(44) 查询员工信息，要求以首字母大写的方式显示所有员工的姓(last_name)和员工名。select initcap(first_name),initcap(last_name) from employees;(45) 查询员工名(first_name)正好为6个字符的员工的信息。select * from employees where length(first_name)=6;(46) 查询员工名(first_name)的第2字母为“M”的员工信息。select first_name from employees where first_name like _M%;(47) 查询所有员工名(first_name)，如果包含字母“s”，则用“S”替换。-返回被替换了指定子串的字符串。-REPLACE (，) -用string_expression3 替换在string_expression1 中的子串string_expression2。select replace(first_name,S,s) from employees;(48) 查询在2月份入职的所有员工信息。 select * from employees where to_char(hire_date,mm)=2;练习9P157 实训题(1)参考P148利用简单循环检索游标declare cursor c_emp is select * from employees;begin for v_emp in c_emp loop dbms_output.put_line(v_emp.first_name| |v_emp.last_name| | v_emp.employee_id| |v_emp.salary| |v_emp.department_id); end loop;end;(2)declare v_avgsal employees.salary%type;begin for v_emp in (select * from employees) loop select avg(salary) into v_avgsal from employees where department_id=v_emp.department_id; if v_emp.salaryv_avgsal then dbms_output.put_line(v_emp.first_name| |v_emp.last_name| | v_emp.employee_id| |v_emp.salary| |v_emp.department_id); end if; end loop;end(3)declare cursor c_emp is select e.employee_id eid,e.last_name ename, e.department_id edid,m.employee_id mid,m.last_name mname from employees e join employees m on e.manager_id=m.employee_id; v_emp c_emp%rowtype;begin open c_emp; loop fetch c_emp into v_emp; exit when c_emp%notfound; dbms_output.put_line(v_emp.eid| |v_emp.ename| | v_emp.edid| |v_emp.mid| |v_emp.mname); end loop; close c_emp;end;(4)declare v_emp employees%rowtype;begin select * into v_emp from employees where last_name=Smith; dbms_output.put_line(v_emp.employee_id| | v_emp.first_name| |v_emp.last_name| | v_emp.salary| |v_emp.department_id);exception when no_data_found then insert into employees(employee_id,last_name,salary,email,hire_date, job_id,department_id) values(2010,Smith,7500,, to_date(2000-10-5,yyyy-mm-dd),AD_VP,50); when too_many_rows then for v_emp in(select * from employees where last_name=Smith)loop dbms_output.put_line(v_emp.employee_id| | v_emp.first_name| |v_emp.last_name| | v_emp.salary| |v_emp.department_id); end loop;end;(5)declare cursor c_emp is select * from employees; v_increment employees.salary%type;begin for v_emp in c_emp loop case when v_emp.job_id=AD_PRESOR v_emp.job_id=AD_VP OR v_emp.job_id=AD_ASST THEN v_increment:=1000; when v_emp.job_id=FI_MGR OR v_emp.job_id=FI_ACCOUNTTHEN v_increment:=800; WHEN v_emp.job_id=AC_MGR OR v_emp.job_id=AC_ACCOUNT THEN v_increment:=700; WHEN v_emp.job_id=SA_MAN OR v_emp.job_id=SA_REP THEN v_increment:=600; WHEN v_emp.job_id=PU_MAN OR v_emp.job_id=PU_CLERK THEN v_increment:=500; WHEN v_emp.job_id=ST_MAN OR v_emp.job_id=ST_CLERK OR v_emp.job_id=SH_CLERK THEN v_increment:=400; WHEN v_emp.job_id=IT_PROG OR v_emp.job_id=MK_MAN OR v_emp.job_id=MK_REP THEN v_increment:=300; ELSE v_increment:=200; end case; update employees set salary=salary+v_increment where employee_id=v_emp.employee_id; end loop;end;(6)declare v_lowsal jobs.min_salary%type; v_highsal jobs.max_salary%type; e exception;begin update employees set salary=8000 where employee_id=201; select min_salary,max_salary into v_lowsal,v_highsal from jobs where job_id=(select job_id from employees where employee_id=201); if 8000 not between v_lowsal and v_highsal then raise e; end if;exception when e then raise_application_error(-20001,beyond limit); rollback;end;练习101.实训题 说明数据库以教材提供的人力资源系统为例。(2)创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，则工资增加140元；若属于20号部门，则工资增加200元；若属于30号部门，则工资增加250元；若属于其他部门，则工资增长300元。CREATE OR REPLACE PROCEDURE pro_updatesal( p_empno employees.employee_id%type)AS v_deptno employees.department_id%type; v_inc number;BEGIN SELECT department_id INTO v_deptno FROM employees WHERE employee_id=p_empno; CASE v_deptno WHEN 10 then v_inc:=140; WHEN 20 then v_inc:=200; WHEN 30 then v_inc:=250; ELSE v_inc:=300; END CASE; UPDATE employees SET salary=salary+v_inc WHERE employee_id=p_empno;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(There is not such an employees.);END;(3) 创建一个函数，以员工号为参数，返回该员工的工资。CREATE OR REPLACE FUNCTION func_retsal( p_empno employees.employee_id%type) return employees.salary%typeAS v_sal employees.salary%type;BEGIN SELECT salary INTO v_sal FROM employees WHERE employee_id=p_empno; RETURN v_sal;EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(there is not such an employees);END; begin dbms_output.put_line(func_retsal(100);end; (5)创建一个包，包中包含一个函数和一个过程。函数以部门号为参数，返回该部门员工的最高工资；过程以部门号为参数，输出该部门中工资最高的员工名、员工号。CREATE OR REPLACE PACKAGE pkg_empAS FUNCTION func_ret_maxsal(p_deptno number) RETURN NUMBER; PROCEDURE pro_showemp(p_deptno NUMBER);END pkg_emp; CREATE OR REPLACE PACKAGE BODY pkg_empas FUNCTION func_ret_maxsal(p_deptno NUMBER) RETURN