oracle 练习题

1、   （1）select dname from dept；   （2）select ename,sal+comm as "年收入" from emp;   （3）select deptno from emp,(select count(empno) as tmp from emp group by deptno) a where a.tmp>0 group by deptno;   2、    (1)select ename,sal from emp where sal>2850;(2)select ename,sal from emp where sal not        between 1500 and 2850    (3)select emp.ename,dept.dname from emp,dept  where emp.deptno=dept.deptno and emp.empno=7566;(4)select ename,sal from emp where deptno in (10,30);(5)select ename,job from emp where emp.mgr is null3、    (1)select ename,job,hiredate from emp where hiredatebetween to_date('1981-02-01','yyyy-mm-dd') andto_date('1981-05-01','yyyy-mm-dd') order by hiredate;(2)select ename,sal,comm from emp where comm>0;4、给部门10的每个雇员增加10%的工资：    create or replace procedure add_sal(v_in emp.deptno%type)is begin   update emp set sal=sal*1.1 where deptno=v_in;exception   when others then      DBMS_OUTPUT.PUT_LINE('涨工资失败');    end add_sal;调用语法：call add_sal(10) 或者 call add_sal(v_in => 10)5、   (1)select avg(sal),sum(sal),max(sal),min(sal) from emp;   (2)select count(empno),avg(sal) from emp group by job;   (3)with temp1 as           (select count(empno) as n1 from emp),   temp2 as   (select count(empno) as n2 from emp where comm>0)   select temp1.n1,temp2.n2 from temp1,temp2;   with as 结构作为临时表；(4)with temp1 as     (select distinct mgr as n1 from emp)      select count(temp1.n1) from temp1;(5)select max(sal)-min(sal) as "差额" from emp;11、   (1)with temp1 as     (select deptno  from emp where ename='BLAKE'),     temp2 as     (select * from emp)   select temp2.ename from temp2,temp1 where temp2.deptno = temp1.deptno and temp2.ename != 'BLAKE';   (2)with temp1 as        (select avg(sal) as n1 from emp),temp2 as        (select ename,sal,deptno from emp)    select temp2.* from temp2,temp1 where temp2.sal>temp1.n1;    (3)with temp1 as       (select deptno,avg(sal) as n1 from emp group by(deptno)),    temp2 as        (select ename,sal,deptno from emp)    select temp2.* from temp2,temp1 where temp2.deptno=temp1.deptno and temp2.sal>n1;    (4)       with temp1 as       (select max(sal) as n1 from emp where job='CLERK'),            temp2 as       (select ename,sal,job from emp)       select temp2.* from temp1,temp2 where temp2.sal>temp1.n11、select ename,sal,   case   when sal>5000 then 1   when sal>1500 then 2   else 3   end "等级"  --注意：case when其别名在end后面并无as   from emp;   2、--with as 写法     with temp1 as     (select avg(sal) as avg_sal1,deptno from emp  group by deptno),     temp2 as     (select sum(avg(sal))*1/3 as avg_sal2 from emp group by deptno)     select temp1.* from temp1,temp2 where temp1.avg_sal1>temp2.avg_sal2; 3、select ename,rank() over (order by sal desc)     as "排名" from emp where job='SALESMAN';4、--注意两种排名写法：   select rank() over(partition by deptno order   by sal desc) as "排名" from emp;--各部门内部的工资排名   select rank() over(order by sum(sal) desc) as "排名" from emp group by deptno; --部门工资的排名   5、--从结果集中间选择：注意分页查询写法select a.*,a.rn from (select emp.*,rownum as rn from emp) awhere a.rn>4 and a.rn<11--使用row_number()函数；两者都是从结果集中间选择；SELECT * FROM(SELECT emp.*,row_number()over(ORDER BY sal asc) rn FROM emp)WHERE rn BETWEEN 5 AND 10  6、--注意点：输入测试数据时，如：1、'MANAGER' 单引号不要掉；2、&job代表输入；3、elsif注意declare     v_check varchar2(255):=&job;    v_rank emp.job%type;begin    if v_check = 'MANAGER' or v_check = 'PRESIDENT' then        v_rank:='金领';    elsif v_check='ANALYST' then        v_rank:='白领';    else        v_rank:='蓝领';    end if;    DBMS_OUTPUT.PUT_LINE('job对应的级别为：'||v_rank);end;