oracle练习题

布满荆棘的人生 2024-02-18 22:24 19阅读 0赞

/*1）使用decode( )函数实现对职务的替换显示，例如，职务是‘CLERK’，
    则显示时显示为‘general work’。
    替换列表如下，对emp表进行查询。
    ‘CLERK’? ‘general work’
    ‘MANAGER’? ‘administrative work’
    ‘ANALYST’ ? ‘technician work’
    ‘SALESMAN’? ‘sale work’
    NULL? ‘not known’*/
    
    SELECT ename,sal,
      decode(job, 'CLERK', 'general work', 
                  'MANAGER', 'administrative work', 
                  'ANALYST', 'technician work',
                  'SALESMAN','sale work',
                  'null','not known')
      FROM emp;
    
    ----------------------------------------------------------------------
    /*2）使用case函数显示员工的工资等级，对emp表查询。对应关系如下：
    小于1000 ? ‘lower’
    1001~2000 ? ‘modest’
    2001~4000 ? ‘high’
    大于4000 ? ‘too high’*/
    
    
    
    select (case
             when sal<1000 then 'lower'
             when sal>1001 and sal<2000 then 'modest'  
             when sal>2001 and sal<4000 then 'high'
             when sal>4000 then 'too high'  
            end) 
            from emp;
    
    
    
    
    
    ----------------------------------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------
    /*3）使用translate函数对字符串进行替换，
    要求能将字符串‘I am god’替换为 ‘U ‘a bed’。*/
    
    
    
    SELECT TRANSLATE('I am god', 'I am god', 'U a  bed') "Translate example" FROM DUAL;
    
    
    
    
    
    
    
    
    
    
    
    
    ------------------------------------------------------
    /*4）在不知道emp表结构的情况下建立3个和emp表结构完全相同但不包含任何数据的表，
    表名分别是emp_low, emp_med, emp_high，写出建表语句。
    然后，用一条语句将emp表中数据按照sal取值的大小分别放入上述3个表中。分割标准如下：
    sal<1000 ? emp_low
    1001-sal-2000 ?emp_med
    2000<sal ?emp_high*/
    
    
    ---先根据emp复制3张表，并清空表数据
    
    create table emp_low as (select * from emp);
    truncate table emp_low;  
      
    create table emp_med as (select * from emp);
    truncate table emp_med;   
      
    create table emp_high as (select * from emp);
    truncate table emp_high;   
    ---法2
    create table emp_low     as (select * from emp where 1=0);---只复制表结构
    create table emp_med   as (select * from emp where 1=0);
    create table emp_high   as (select * from emp where 1=0);
    ---然后，用一条语句将emp表中数据按照sal取值的大小分别放入上述3个表中
    
    insert into emp_low (select * from emp where sal<1000);
    insert into emp_med (select * from emp where sal>1001 and sal<2000);
    insert into emp_high (select * from emp where sal>2000);
    commit;
    
    
    
    
    ----------------------------------------------------------------
    ----5）使用PL/SQL程序输出1-13中所有不是3的倍数的整数数值。*/
    declare
      b number;
      x number;
    begin
      for x in 1 .. 13 
        loop
           b := x / 3;
        if instr(b,'.') > 0 then --判断b是否是整数。若不是整数，则说明x不能被3整除。
           dbms_output.put_line(x);
        end if;
        end loop;
    end;
    
    ---法2
    begin
      FOR x IN 1 .. 13 LOOP
      
        if mod(x, 3) != 0 then
          ---mod(x, 3)取余数,相当于x%3
          dbms_output.put_line('不是3的倍数的整数:' || +x);
        
        end if;
      
      end loop;
    end;
    /