数据库简单练习

现有表如下：
员工表：(employee)
职位表：(post)
（1）在数据库中建立以上几个表，并建立关系。

（2）输入部门的名称查找本部门的员工数量。

（使用存储过程）
create or replace procedure pai(
p_department_name varchar2,
p_count_e out number
) is
begin
select count(e.EMP_ID) into p_count_e from department d,employee e,post p where e.POST_ID=p.POST_ID and d.DEPARTMENT_ID=
p.DEPARTMENT_ID and e.EMP_NAME=p_department_name;
end pai;
（3）要求输入部门的名称查找到本部门工资最高的员工信息。

（使用存储过程）
如：输入：软件部；
显示
p_department_name varchar2,
p_max_e out testpackage.test_cursor
) is
maxs number;
p_sql varchar2(1000);
begin
p_sql:='select max(salary) from department d,employee e,post p where e.POST_ID=p.POST_ID and d.DEPARTMENT_ID=
p.DEPARTMENT_ID and d.department_name='||p_department_name;
execute immediate p_sql into maxs;
open p_max_e for
select* from department d,employee e,post p where e.POST_ID=p.POST_ID and d.DEPARTMENT_ID= p.DEPARTMENT_ID and e.salary=maxs;
end two;
（4）查找所有比自己本部门平均工资高的员工信息。

（使用视图）
create or replace view three
as
select avg(salary) sal,d.DEPARTMENT_NAME men from department d,employee e,post p where e.POST_ID=p.POST_ID and d.DEPARTMENT_ID=
p.DEPARTMENT_ID group by d.DEPARTMENT_NAME;
select * from department d,employee e,post p,three t where e.POST_ID=p.POST_ID and d.DEPARTMENT_ID= p.DEPARTMENT_ID and d.department_name=t.men and e.salary>t.sal;。