oracle

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;