1.select deptno,min(sal) from emp
where job_id='manager'
group by deptno;
2.select * from emp
where hiredate=last_day(hiredate);
3.select initcap(ename) from emp ;
4.select job from emp
having min(sal)>1500
group by job;
5.select d.deptno,d.dname ,e.* from dept d left join e on d.deptno=e.deptno;
6.
update emp set sal=sal*1.2
where job='SALESMAN';
7.select empno,ename from emp
having sal>(select avg(sal) from emp);
8.select round(sysdate-hiredate) worked_day
from emp;
9.select hiredate,sum(sal)+sum(nvl(comm,0)) from emp
where hiredate like '%__81' group by hiredate;
10.
update emp set
sal=sal*1.1
where (sysdate-hiredate)/365>25;
二.
1.declare
cursor emp_sal_cursor is select avg(sal),deptno
from emp
group by deptno;
begin
for c in emp_sal_cursor loop
dbms_output.put_line('demtno:'||c.deptno||'avg_sal:'||c.sal)
end loop;
end;
2.
create or replace procedure SWAP(
num1 in out number,num2 in out number)
is
swap number;
begin
swap:=num1;
num1:=num2;
num2:=swap;
end SWAP;
declare
num1 number;
num2 number;
begin
num1:=1;
num2:=2;
SWAP(num1,num2);
dbms_output.put_line(num1||' '||num2);
end;
3.
declare
v_sum number(10):=0;
begin
for c in 1..100 loop
if mod(c,2)=0 then v_sum:=v_sum+c;
end if;
end loop;
dbms_output.put_line(v_sum);
end;
4.
create or replace function shang(v_num1 number,v_num2 number)
return number
is
v_sum number(10);
v_min number(10);
v_s number(10);
begin
v_sum:=v_num1+v_num2;
v_min:=v_num1-v_num2;
v_s=v_sum/v_min;
return v_s;
end;
调用方式1：
select shang(3，1) from dual;
调用方式2：
declare
num number;
begin
num:=shang_two(3,1);
dbms_output.put_line(num);
end;
