oracle实训题目

一、sql查询题（日期：12月13日）
1.显示姓名字段的任何位置包含’A’的所有员工的姓名
2.显示所有员工姓名的前三个字符
3.显示员工‘FORD’的上级
4.找出在任意年份2月受聘的所有员工
5.列出员工表中每个部门的员工数（员工数必须大于 3），和部门名称
6.以职位分组，找出平均工资最高的两种职位
7.查找出不在部门 20，且比部门 20 中任何一个人工资都高的员工姓名、部门名称
8.得到平均工资大于 2000 的工作职种
9.查找出职位和’MARTIN’ 或者’SMITH’一样的员工的平均工资
10.查询出 king 所在部门的部门号\部门名称\部门人数
二、PLSQL编程（日期：12月18日）
1.输入一个雇员编号，根据它所在的部门涨工资，规则：
• 10 部门上涨 10%
• 20 部门上涨 20%
• 30 部门上涨 30%
所有部门的上涨工资，最高不能超过 5000，如果超过 5000，则工资就为 5000。
2. 用 loop 循环结构，为 dept 表增加 50-90 这些部门
3. 接收一个雇员名，显示该雇员的所有内容， （用%rowtype 实现），当没有这个雇员时（no_data_found），用异常来显示错误提示
三、游标、函数和存储过程（12月25日）
4. 使用游标 和 loop 循环来显示所有部门的名称
5. 写一个函数 输入一个员工名字，判断该名字在员工表中是否存在。存在返回 1，不存在返回 0
6. 编写一个过程，要求，可以传入部门的编号，部门的名称，之后调用此过程就可以完成部门的增加操作。

--1.显示姓名字段的任何位置包含’A’的所有员工的姓名
select ename from emp where ename like '%A%';
--2.显示所有员工姓名的前三个字符
select substr(ename,1,3) from emp;
--3.显示员工‘FORD’的上级
select ename from emp where empno=(select mgr from emp where ename='FORD'); 
--4.找出在任意年份2月受聘的所有员工
select * from emp where to_char(hiredate,'mm')=2;
--5.列出员工表中每个部门的员工数（员工数必须大于 3），和部门名称
select deptno,count(*) from emp group by deptno having count(*)>3;
--6.以职位分组，找出平均工资最高的两种职位
select * from (select job,avg(sal) from emp group by job order by avg(sal) desc) where rownum<=2;
--7.查找出不在部门 20，且比部门 20 中任何一个人工资都高的员工姓名、部门名称
select ename,deptno from emp where sal>=all(select sal from emp) and deptno<>20;
--8.得到平均工资大于 2000 的工作职种
select job from emp group by job having avg(sal)>2000; 
--9.查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资
select avg(sal) from emp group by job having job in (select job from emp where ename in('MARTIN','SMITH'));
--10.查询出 king 所在部门的部门号\部门名称\部门人数
select emp.deptno 部门名称,dname 部门名,count(1) 人数 from emp,dept where emp.deptno=dept.deptno and emp.deptno=(select deptno from emp where ename='KING') group by emp.deptno,dname;

select * from emp;
--1.输入一个雇员编号，根据它所在的部门涨工资，规则：10 部门上涨 10%, 20 部门上涨 20%, 30 部门上涨 30%所有部门的上涨工资，最高不能超过 5000，如果超过 5000，则工资就为 5000。
declare
  v_empno emp.empno%type:=&aa;
  v_sal emp.sal%type;
  v_dn emp.deptno%type;
begin
  SELECT deptno,sal into v_dn,v_sal from  emp where empno=v_empno;
  if v_dn=10 then v_sal:=v_sal*1.1;
  elsif v_dn=20 then v_sal:=v_sal*1.2;
  elsif v_dn=30 then v_sal:=v_sal*1.3;
  end if;
  if v_sal>5000 then v_sal:=5000;
  end if;
  update emp set sal=v_sal where empno=v_empno;
end;
--2. 用 loop 循环结构，为 dept 表增加 50-90 这些部门
declare
  v_dn number:=50;
begin
  loop
    insert into dept(deptno) values (v_dn);
    v_dn:=v_dn+10;
    exit when v_dn>90;
  end loop;
end;
select * from emp;
--3. 接收一个雇员名，显示该雇员的所有内容， （用%rowtype 实现），当没有这个雇员时（no_data_found），用异常来显示错误提示
declare
  v_ename emp.ename%type:='&aa';
  e emp%rowtype;
begin
  select * into e from emp where ename=v_ename;
    dbms_output.put_line(e.ename||' '||e.empno||' '||e.sal);
  exception
    when no_data_found then
      dbms_output.put_line('无该员工');
end;

declare
  cursor myc is select deptno from dept;
  v_dname dept.dname%type;
begin
  open myc;
  loop
      fetch myc into v_dname;
    exit when myc %notfound;
      dbms_output.put_line(v_dname);
  end loop;
  close myc;
end;

create or replace function pd return number
is
  v_ename emp.ename%type:='&aa';
  n number;
begin
  select count(*) into n from emp where ename=v_ename;
  if n=0 then return 0;
  else return 1;
  end if;
end;

create or replace procedure pdu(dna dept.deptno%type,den dept.dname%type)
is
begin
  insert into dept(deptno,dname) values(dna,den);
end;