oracle练习题_牛客博客


/*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;
/