Oracle11g学习-sql/函数练习

Oracle

--1、查询工作地点在纽约或波士顿的员工信息  地点在dept
select * from emp e where e.deptno in(select deptno from dept where loc='NEW YORK' or loc='BOSTON');
--3、查询工资比scott用户高的用户
select * from emp e where e.sal>(select e.sal from emp e where e.ename='SCOTT');
--4、查询部门名称为SALES或ACCOUNTING的员工信息
select * from emp e where e.deptno in(select d.deptno from dept d where d.dname='SALES' or d.dname='ACCOUNTING');
--5、查询每一个部门中工资最低的员工信息
select * from emp e,(select deptno ,min(sal) minSal from emp group by deptno) s where e.deptno=s.deptno and e.sal=s.minSal;
--6、查询员工中工资前三的员工信息（利用rownum）
select * from (select * from emp order by sal desc) s where rownum<=3
--7、查询工资大于本岗位平均工资的员工信息
select * from emp e ,(select job,avg(sal) avgSal from emp group by job) s where e.job=s.job and e.sal>s.avgSal;
--8、查询工资大于最小工资且小于最大工资的员工信息
select * from emp e,(select min(e.sal) minSal,max(e.sal) maxSal from emp e) s where e.sal>s.minSal and e.sal<s.maxSal;

--1、利用pl/sql 显示9*9乘法表
declare
begin
  for i in 1..9
  loop 
    for j in i..9
    loop
      dbms_output.put(i||'*'||j||' ');
    end loop;
    dbms_output.put_line('');
  end loop;
end;
--2、使用存储过程给scott所在的部门的员工全体涨300工资
create or replace procedure pro_scottDept_addSal
as
p_scottDeptno emp.deptno%type;
begin
  select deptno into p_scottDeptno from emp where ename='SCOTT';
  update (select sal,empno from emp e where e.deptno= p_scottDeptno) s set s.sal=s.sal+300;
end;
exec pro_scottDept_addSal();
select * from emp where deptno=20;
select * from dept;
--4、使用存储函数，在给定员工编号的情况下，返回该员工的工作地点
create or replace function fun_getAddress_byEmpno(
  p_empno in emp.empno%type
)
return varchar2
as
p_deptno dept.deptno%type;
p_empAddress dept.loc%type;
begin
  select deptno into p_deptno from emp where empno=p_empno;
  select loc into p_empAddress from dept where deptno=p_deptno;
  return p_empAddress;
end;
select fun_getAddress_byEmpno(7499) from dual;