oracle课后部分习题答案.pdf

第 4 章 SQL应 用 基 础 14.请 从 表 EM P中 查 找 工 资 低 于 2000的 雇 员 的 姓 名、工 作、工 _select ename,job,sal from emp where sal=2000;17.在 表 EM P中 查 询 所 有 工 资 高 于 JONES的 所 有 雇 员 姓 名、工 作 和 工 资。select ename,job,sal from emp where sal(select sal from emp whereename=JONES);18.列 出 没 有 对 应 部 门 表 信 息 的 所 有 雇 员 的 姓 名、工 作 以 及 部 门 号。select ename,job,deptno from emp where deptno not in(selectdeptno from dept);2 0.查 找 工 资 在 1000 3000之 间 的 雇 员 所 在 部 门 的 所 有 人 员 信 息 select*from emp where deptno in(select distinct deptno from empwhere sal between 1000 and 3000);21.查 询 列 出 来 公 司 就 职 时 间 超 过 2 4年 的 员 工 名 单 select ename from emp where hiredate=add_months(sysdate,-288);22.查 询 显 示 每 个 雇 员 加 入 公 司 的 准 确 时 间，按 xxxx年 xx月 xx日 时 分 秒 显 示。select ename,to_char(hiredate,yyyy-mm-dd hh24:mi:ss)from emp;23.查 询 所 有 8 1年 7 月 1 日 以 前 来 的 员 工 姓 名、工 资、所 属 部 门 的 名 字 select ename,sal,dname from emp,dept whereemp.deptno=dept.deptno andhiredate=to_dateC 1981-07-0 r,yyyy-mm-dd,);24.查 询 公 司 中 按 年 份 月 份 统 计 各 地 的 录 用 职 工 数 量 select to_char(hiredate,yyyy-mm),loc,count(*)from emp,deptwhere emp.deptno=dept.deptnogroup by to_char(hiredate,yyyy-mm),loc;25.查 询 部 门 平 均 工 资 最 高 的 部 门 名 称 和 最 低 的 部 门 名 称 select dname from dept where deptno=(select deptno from(selectdeptno from emp group by deptno order by avg(sal)whererownum=l)union all select dname from dept where deptno=(select deptno from(select deptno from emp group by deptno order by avg(sal)desc)where rownum=l);26.对 每 一 个 雇 员，显 示 employee_id、last_name、salary和 salary增 加 15%,并 且 表 示 成 整 数，列 标 签 显 示 为 New_SalarySELECT employee_id,last_name,salary,ROUND(salary*1.15,0)New SalaryFROM employees;27.写 一 个 查 询 用 首 字 母 大 写，其 它 字 母 小 写 显 示 雇 员 的 last_names,显 示 名 字 的 长 度，对 所 有 名 字 开 始 字 母 是 J、A 或 M的 雇 员，给 每 列 一 个 适 当 的 标 签。用 雇 员 的 last_names排 序 结 果。SELECT INITCAP(last_name)Name,LENGTH(last_name)LengthFROM employeesWHERE last_name LIKE J%OR last_name LIKE M%OR last_name LIKE A%ORDER BY last_name;28.对 每 一 个 雇 员，显 示 其 last_nam e,并 计 算 从 雇 员 受 雇 日 期 到 今 天 的 月 数，歹！J标 签 MONTHS_WORKED。按 受 雇 月 数 排 序 结 果，四 舍 五 入 月 数 到 最 靠 近 的 整 数 月。SELECT last_name,ROUND(MONTHS_BETWEEN(SYSDATE,hire_date)MONTHS.WORKEDFROM employeesORDER BY MONTHS_BETWEEN(SYSDATE,hire_date);.显 示 last_name、hire_date和 雇 员 开 始 工 作 的 周 日，列 标 签 为 DAY,以 星 期 一 作 为 周 的 起 始 日 排 序 结 果。SELECT last_name,hire_date,TO_CHAR(hire_date,DAY)DAYFROM employeesORDER BY TO_CHAR(hire_date-1,d);LASTNAME HIREDATE DAYGrant 24-MAY-99 MONDAYErnst 21-MAY-91 TUESDAYMourgos 16-NOV-99 TUESDAYTaylor 24-MAR-98 TUESDAYRajs 17-OCT-95 TUESDAYGietz 07-JUN-94 TUESDAYHiggins 07-JUN-94 TUESDAYKing 17-JUN-87 WEDNESDAYDe Haan 13-J AN-93 WEDNESDAYDavies 29-JAN-97 WEDNESDAYHunold 03-JAN-90 WEDNESDAYKochhar 21-SEP-89 THURSDAYWhalen 17-SEP-87 THURSDAYVargas 09-JUL-98 THURSDAYMatos 15-MAR-98 SUNDAY第 6 章 视 图 与 序 列 6.CREATE VIEW VW_DEPT_SUM(DEPT_NAME,EMP_COUNT,MINSAL,MAXSAL,AVGSAL)ASSELECT dname,count(empno),min(sal),max(sal),avg(sal)FROM dept d left join emp e on e.deptno=d.deptnoGROUP BY dname7.CREATE VIEW VW_EMP_AVGASSELECT e.ename,e.sal,d.dname,s.avgsalFROM emp e,(select deptno,AVG(sal)avgsal from emp GROUPBY deptno)s,dept dWHERE e.deptno=s.deptno AND s.deptno=d.deptno ANDe.sals.avgsal第 7 章 PL/SQL基 础 12、declaretype emp_table_type is table of emp%rowtype;emp_table emp_table_type;beginselect e.*bulk collect into emp_tablefrom emp e,dept dwhere e.deptno=d.deptno and d.dname=&dname;for i in l.emp_table.count loopdbms_output.put_line(emp_table(i).empnoH,|emp_table(i).ename|r,|emp_table(i).hiredate|,|emp_table(i).deptno);END LOOP;end;13、declarev_salary emp.sal%type;v_addsal number;beginselect sal into v_salary from empwhere empno=&empno;dbms_output.put ine（原 工 资：|v_salary）;if v_salary 10000 thenv_addsal:=0.1;elsif v_salary5000 thenv_addsal:=0.2;elsev_addsal-0.3;end if;update empset sal=sal*（1+v_addsal）where empno=&empno;select sal into v_salary from empwhere empno=&empno;dbms_output.put ine（增 长 后 工 资：|v_salary）;end;第 8 章 PL/SQL进 阶 22、编 写 一 个 数 据 包，它 有 两 个 函 数 和 两 个 过 程 以 操 作 emp”表。该 数 据 包 要 执 行 的 任 务 为：a）插 入 一 个 新 雇 员；删 除 一 个 现 有 雇 员；显 示 指 定 雇 员 的 整 体薪 水(薪 水+佣 金)；显 示 指 定 雇 员 所 在 部 门 名 称。create or replace package emppack asprocedure insrec(pempno emp.empno%type,penameemp.ename%type,pjob emp.job%type,pmgr emp.mgr%type,phiredate emp.hiredate%type,psal emp.sal%type,pcomm m%type,pdeptno emp.deptno%type);procedure delrec(pempno in number);function selsal(pempno number)return number;function seldname(pempno number)return varchar2;end;create or replace package body emppack asprocedure insrec(pempno emp.empno%type,penameemp.ename%type,pjob emp.job%type,pmgr emp.mgr%type,phiredate emp.hiredate强 type,psal emp.sal%type,pcomm m%type,pdeptno emp.deptno%type)isbeginin s e rt in to emp values(pempno,pename,pjob,pmgr,p h ired ate,p sal,pcomm,pdep tn o);dbms_output.put ine(T record is created.*);end in s r e c;procedure d elrec(pempno in number)i sbegind e le te from emp where empno=pempno;dbms_output.p u t_ lin e(1 record is d e le te d.);end d e lre c;fu n ctio n selsal(pem pno number)re tu rn numberi svtotalsal number;beginselect nvl(sal,0)+nvl(comm,0)into vtotalsalfrom empwhere empno=pempno;return vtotalsal;end selsal;function seldname(pempno number)return varchar2i svdname dept.dname%type;beginselect dname into vdnamefrom emp,deptwhere empno=pempno and emp.deptno=dept.deptno;return vdname;end seldname;end;-执 行 包 中 的 过 程 和 函 数 execemppack.insrec(1111/goldens,manager,7698,to_date(J 2003-01-18，,，yyyy-mm-dd),2000,400,30);exec emppack.delrec(1111);declaresalary number;beginsalary emppack.selsal(7369);dbms_output.put_line(4total salary is salary);end;/declaredepartment varchar2(30);begindepartmentemppack.seldname(7369);dbms_output.put_line(department name is department);end;b)编 写 一 个 函 数 以 检 查 所 指 定 雇 员 的 薪 水 是 否 有 效 范 围 内。不 同 职 位 的 薪 水 范 围 为：designation raiseclerk 1500-2500salesman 2501-3500analyst 3501-4500others 4501 and above.如 果 薪 水 在 此 范 围 内，则 显 示 消 息”salary is ok，否 则，更 新 薪 水 为 该 范 围 内 的 最 水 值。create or replace function sal_level(no emp.empno%type)returnvarchar2 asvjob emp.job%type;vsal emp.sal%type;vmesg varchar2(50);beginselect job,sal into vjob,vsal from emp where empno=no;if vjob=,clerk thenif vsal=1500 and vsal=2500 thenvmesg:=salary is ok.;elsevsal:=1500;vmesg:=have updated your salary to C|to_char(vsal);end if;elsif vjob=salesman thenif vsal=2501 and vsal=3501 and vsal=4501 thenvmesg:=salary is ok/;elsevsal:=4501;vmesg:=have updated your salary to5|to_char(vsal);end if;end if;update emp set sal=vsal where empno=no;return vmesg;end;/declarevmesg varchar2(50);vempno emp.empno%type;beginvempno&empno;vmesg:=sal_level(vempno);dbms_output.put_line(vmesg);end;/select empno,ename,sal,comm,hiredate from emp whereempno=:no;