oracle考试总结

--综合题（30分）


--第一题：简单的循环游标
--给你一个部门号，用游标打印出该部门的员工的姓名，薪水（定义两个变量）
declare
cursor emp_cursor(dept_number number:=20)--类型是不精确的，别忘了
is
select ename,sal from emp where deptno=dept_number;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open emp_cursor(&deptno);
loop
fetch emp_cursor into v_ename,v_sal;
if(v_sal<2000)
then update emp set sal=sal*1.6 where ename=v_ename;
end if;
exit when emp_cursor%notfound;
dbms_output.put_line('第'||emp_cursor%rowcount||','||v_ename||','||v_sal);
end loop;
close emp_cursor;
end;
--给你一个部门号，用游标打印出该部门的员工的姓名，薪水（记录类型的变量）
declare
cursor emp_cursor (deptnumber number:=20)
is
select ename,sal from emp where deptno=deptnumber;
one_emp emp_cursor%rowtype;
begin
open emp_cursor(&deptnumber);
loop
fetch emp_cursor into one_emp;
if(one_emp.sal<2000)
then update emp set sal=sal*1.8 where ename=one_emp.ename;
end if;
exit when emp_cursor%notfound;
dbms_output.put_line('第'||emp_cursor%rowcount||'行'||','||one_emp.ename||','||one_emp.sal);
end loop;
close emp_cursor;
end;
--第二题：参照游标


--定义变量：v_ename,v_sal
declare
type sp_emp_cursor is ref cursor;
--定义变量
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin


--用游标接受结果集
open test_cursor for select ename,sal from emp where deptno=&no;
loop
fetch test_cursor into v_ename,v_sal;
exit when test_cursor%notfound;
dbms_output.put_line('名字：'||v_ename||'工资: '||v_sal);
end loop;
close test_cursor;
end;


select * from emp
where deptno=20;
--定义行变量


declare
type emp_cursor is ref cursor;
test_cursor emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;




begin
    open test_cursor for select ename,sal from emp where deptno=&deptno;
loop
fetch test_cursor into v_ename,v_sal;

if(v_sal<2000)
then update emp set sal=sal*1.1 where ename=v_ename;
end if;
exit when test_cursor%notfound;

dbms_output.put_line('姓名：'||v_ename||'薪水:'||v_sal);

end loop;
close test_cursor;
end;




--第三题：返回结果集的存储过程实例及调用


--创建一个包，定义一个游标类型，为存储过程的输出参数使用(定义存储过程的输出参数)。


create or replace package sp_emp_pk
as
type sp_emp_cursor is ref cursor;--好像全局变量
end sp_emp_pk;
--创建返回结果集的存储过程


create or replace procedure sproc_cursor(deptnum in number,emp_cursor out sp_emp_pk.sp_emp_cursor)
begin
open emp_cursor for select ename,sal from emp where deptno=deptnum;
end sproc_cursor;
--返回结果集的存储过程的调用
declare 
type sp_emp_cursor is ref cursor;
emp_cursor sp_emp_cursor;
--v_empno emp.empno%type :=7839;
v_deptno emp.deptno%type :=10;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
sproc_cursor(v_deptno,emp_cursor);
loop
fetch emp_cursor into v_ename,v_sal;
exit when emp_cursor%notfound;
dbms_output.put_line('姓名：'||v_ename||'薪水：'||v_sal);
end loop;
close emp_cursor;
end;






--第四题：触发器和主键结合实现主键列的值
create table book
(
bid number(4) primary key,
bname varchar2(10)
);
create sequence book_seq


start with 1
increment by 1
;


create or replace trigger add_book_trigger
before insert
on book
for each row
begin


select book_seq.nextval into :new.bid from dual;
end;


insert into book(bname) values('数据库');
select * from book;


--简答题：（20分）


--1、数据库逻辑结构和物理结构


--1.1、物理结构：由构成数据库的操作系统文件组成，它是从操作系统的角度来分析数据库的组成，在操作系统中可以看得到的文件，也就是说它是数据库在操作系统中的存储位置。常见的物理结构包括：控制文件、数据文件、重作日志文件、归档日志文件、初始化参数文件、还有其它文件（密码文件、报警日志文件和后台及用户跟踪文件）。 
--1.2、逻辑结构：描述数据库从逻辑上如何存储数据库中的数据。它是从数据库的角度来分析数据的逻辑存储。常见的逻辑结构包括：表空间、数据段、扩展区间、块构成。




--2、数据库和实例的区分
--这个问题如果回答得形象点就是： 
--一个数据库可以包含一个或者多个实例 
--实例的定义：由操作系统后台进程和分配的内存区域构成。说 
--得简单点就是oracle在nomount状态就分配了内存区域，一个实 
--例也就差不多快形成了。




-- 数据库（database）：物理操作系统文件或磁盘（ disk）的集合。
-- 实例（instance）：一组Oracle 后台进程/线程以及一个共享内存区，这些内存由同一个计算机上运行的线程/进程所共享。


--查询题：（20分）




--第一题：列出至少有一个员工的所有部门。列出这个部门的部门编号、名称、并统计出这些部门的平均工资、最低工资、最高工资
select d.deptno,d.dname,count(empno),avg(sal),min(sal),max(sal)
from emp e,dept d
where e.deptno=d.deptno
having count(empno)>1
group by d.deptno,d.dname
--第二题：列出薪金比'SMITH'或'ALLEN'多的所用员工的编号、姓名、部门编号、其领导姓名


select e.empno,e.ename,e.deptno,m.empno,m.ename
from emp e,emp m
where e.sal> any (select sal from emp where ename='SMITH' or ename='ALLEN')and e.mgr=m.empno(+);
--第三题：列出所用员工的编号,姓名及其直接上级的编号,名字,显示的结果按领导工资的降序排列
select e.empno,e.ename,m.empno,m.ename,m.sal,12*(m.sal+nvl(m.comm,0)) boss_sal
from emp e,emp m
where e.mgr=m.empno(+)
order by boss_sal desc




--第四题：列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称,部门位置,部门人数。


select e.empno,e.ename,e.hiredate,d.dname,d.loc,m.empno,m.ename,m.hiredate,d.deptno,temp.count
from emp e,emp m,dept d,(select deptno dno,count(empno) count from emp group by deptno ) temp
where e.mgr=m.empno(+) and e.hiredate<m.hiredate and e.deptno=d.deptno and temp.dno=d.deptno;


--第六题：列出所有'CLERK'(办事员)的姓名及其部门名称，部门的人数，工资等级
select e.ename,d.deptno,d.dname,temp.count,s.grade
from emp e,dept d,(select deptno dno,count(empno) count from emp group by deptno) temp,salgrade s
where job='CLERK' and e.deptno=d.deptno and temp.dno=d.deptno and e.sal>s.losal and e.sal<s.hisal;


--第七题：列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数及所在部门名称、位置、平均工资(相同的部门job可能不同)
select temp.job,temp.count,d.dname,e.ename,res.avg
from dept d,
(select e.job job ,count(e.empno) count from emp e group by e.job having min(e.sal)>1500) temp,
emp e,
(select deptno dno,avg(sal) avg from emp group by deptno) res
where e.deptno=d.deptno and temp.job=e.job and res.dno=d.deptno
--第八题：列出在部门'SALES'(销售部)工作的员工姓名、基本工资、雇佣日期、部门名称、假定不知道销售部的部门编号
select e.empno,e.ename,e.sal,e.hiredate,d.dname
from emp e,dept d
where e.deptno=d.deptno and d.dname='SALES';
--第九题：列出薪金高于公司平均薪金的所用员工，所在部门，上级领导，公司的工资等级
select e.empno,e.ename,d.deptno,d.dname,m.deptno,m.ename,s.grade
from emp e,dept d,salgrade s,emp m
where e.mgr=m.empno(+) and e.deptno=d.deptno and e.sal between s.losal and s.hisal and e.sal>(select avg(sal) from emp)


--第十题：列出与'SCOTT'从事相同工作的所有员工及部门名称，部门人数


select e.empno,e.ename,d.dname,e.job,temp.count
from emp e,dept d,(select deptno dno,count(empno) count from emp group by deptno) temp
where e.deptno=d.deptno and e.job=(select job from emp where ename='SCOTT') and temp.dno=d.deptno and e.ename<>'SCOTT';


--第十一题：列出公司各个工资等级雇员的数量、平均工资
select count(e.empno),avg(e.sal),s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal
group by s.grade
 
--第十二题：列出薪金高于在部门30工作的所有员工的新进的员工姓名和薪金，部门名称
select e.ename,e.empno,d.deptno,d.dname
from emp e,dept d
where e.deptno=d.deptno and e.sal>all (select sal from emp where deptno=30)
--第十三题：列出在每个部门工作的员工数量、平均工资和平均服务期限
select count(e.empno),avg(sal),avg(months_between(sysdate,e.hiredate)/12) year
from emp e
group by deptno
--第十五题：列出所有部门的详尽信息和部门的人数
select e.ename,e.empno,d.dname,d.deptno,count(e.empno)
from emp e,dept d
where e.deptno(+)=d.deptno--没人的部门也出来了
group by e.ename,e.empno,d.dname,d.deptno
--第十六题：列出各种工作的最低工资及从事此工作的雇员姓名


select e.ename,e.job,e.sal
from emp e,(select job ,min(sal) min from emp group by job) temp
where e.job=temp.job and e.sal=temp.min


--第十八题：列出所有员工的年工资，所在部门名称，按年薪从低到高排序
select (e.sal+nvl(e.comm,0))*12 year_sal,d.dname
from emp e,dept d
where e.deptno=d.deptno
order by year_sal desc


--第十九题：查出某个员工的上级主管及所在部门名称，并要求这些主管中的薪金超过3000元
select e.empno,e.ename,m.empno,m.ename,d.dname
from emp e,dept d,emp m
where e.deptno=d.deptno and e.mgr=m.empno(+) and m.sal>3000;


--第二十题：求出部门名称中，带'S'字符的部门员工的工资合计、部门人数
select d.dname,sum(e.sal),count(e.empno) 
from emp e,dept d
where d.deptno=e.deptno and d.dname LIKE '%S%'
group by d.dname


--第二十一题：给任职超过30年或者在87年雇佣的雇员加薪，加薪原则：10部门增长10%100,20部门增长20%，30部门增长30%，一次类推
update emp set sal=(1+deptno/100)*sal
where months_between(sysdate,hiredate)/12>30
or to_char(hiredate,'yyyy')='1987'




select * from emp;


--2、查询与SMITH的部门和岗位完全相同的所用雇员
select e.*
from emp e
where (e.deptno,e.job)=(select deptno,job from emp where ename='SMITH') AND e.ename <>'SMITH'




--3、显示高于自己部门平均工资的员工的信息


--解法一：
select e.*
from emp e
where e.sal>(select avg(sal) from emp where deptno=e.deptno)
--解法二：
select e.*,temp.myavg
from emp e,(select avg(sal) myavg ,deptno from emp group by deptno) temp
where e.deptno=temp.deptno and e.sal>temp.myavg
--4、修改员工scott的岗位、工资 让其与smith员工一样。
update emp set (job,sal)=(select job,sal from emp where ename='SMITH')
WHERE ENAME='SCOTT'


--检验
select * from emp where ename='SCOTT' OR ENAME='SMITH'
--5、显示满10年服务年限的员工的姓名和受雇日期
select ename,hiredate
from emp
where months_between(sysdate,hiredate)/12>10
--6、显示各月倒数第3天受雇的所有员工
select ename,hiredate
from emp
where hiredate=last_day(hiredate)-2


--7、显示所有12月份入职的员工
select hiredate
from emp
where to_char(hiredate,'mm')='12'
--8、显示每个部门工资最高的人的详细资料
--解法一
select e.*
from emp e,(select deptno dno,max(sal) mymax from emp group by deptno)  temp
where e.deptno=temp.dno and e.sal=temp.mymax
--解法二
select e.*
from emp e
where e.sal=(select max(sal) from emp where deptno=e.deptno)