oralce练习题

 找出用户表(ccare.inf_subscriber_all)中创建时间（create_date）相同的如下记录
--创建时间相同,用户ID（sub_id）最大和最小的两条记录
select create_date,max(sub_id),min(sub_id) from ccare.inf_subscriber_all group by create_date having count(2)>=2;
--创建时间相同且大于该时间用户ID（sub_id）平均数据的记录
select * from (select create_date,avg(sub_id) avg_id from ccare.inf_subscriber_all group by create_date having count(2)>=2) t,ccare.inf_subscriber_all s where s.create_date=t.create_date and s.sub_id>t.avg_id;

--实现 行转列 功能：

create table T_ROW_COL(

  ID NUMBER(38),

  CN CHAR(2),

  CV VARCHAR2(10)

);

insert into t_row_col (ID, CN, CV)

values (1, 'c1', 'v11');

insert into t_row_col (ID, CN, CV)

values (1, 'c2', 'v21');

insert into t_row_col (ID, CN, CV)

values (1, 'c3', 'v31');

insert into t_row_col (ID, CN, CV)

values (2, 'c1', 'v12');

insert into t_row_col (ID, CN, CV)

values (2, 'c2', 'v22');

insert into t_row_col (ID, CN, CV)

values (2, 'c3', '');

insert into t_row_col (ID, CN, CV)

values (3, 'c1', 'v13');

insert into t_row_col (ID, CN, CV)

values (3, 'c2', '');

insert into t_row_col (ID, CN, CV)

values (3, 'c3', 'v33');

insert into t_row_col (ID, CN, CV)

values (4, 'c1', '');

insert into t_row_col (ID, CN, CV)

values (4, 'c2', 'v24');

insert into t_row_col (ID, CN, CV)

values (4, 'c3', 'v34');

insert into t_row_col (ID, CN, CV)

values (5, 'c1', 'v15');

insert into t_row_col (ID, CN, CV)

values (5, 'c2', '');

insert into t_row_col (ID, CN, CV)

values (5, 'c3', '');

insert into t_row_col (ID, CN, CV)

values (6, 'c1', '');

insert into t_row_col (ID, CN, CV)

values (6, 'c2', '');

insert into t_row_col (ID, CN, CV)

values (6, 'c3', 'v35');

insert into t_row_col (ID, CN, CV)

values (7, 'c1', '');

insert into t_row_col (ID, CN, CV)

values (7, 'c2', '');

insert into t_row_col (ID, CN, CV)

values (7, 'c3', '');

 

select id,max(decode(cn, 'c1', cn, 'c1')) CN_1,max(decode(cn, 'c1', cv, '')) CV_1, max(decode(cn, 'c2', cn, 'c2')) CN_2,

       max(decode(cn, 'c2', cv, '')) CV_2, max(decode(cn, 'c3', cn, 'c3')) CN_3, max(decode(cn, 'c3', cv, '')) CV_3

  from t_row_col t group by id;

 

--统计

create table HUXUE_TEST
(
  ACCT_MONTH VARCHAR2(10),
  CALL_TYPE  VARCHAR2(10),
  TOT_NUM    NUMBER
)
;

insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201201', '0', 100);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201201', '1', 200);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201201', '1', 200);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201201', '0', 300);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201202', '0', 300);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201202', '0', 300);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201203', '1', 300);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201203', '0', 300);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201204', '1', 300);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201204', '0', 300);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201205', '1', 300);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201205', '0', 300);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201206', '1', 300);
insert into HUXUE_TEST (ACCT_MONTH, CALL_TYPE, TOT_NUM)
values ('201206', '0', 300);
commit;

select h.acct_month,
sum(decode(h.call_type,'0',h.tot_num,0)) call_type_0,
sum(decode(h.call_type,'1',h.tot_num,0)) call_type_1
from HUXUE_TEST h group by h.acct_month order by h.acct_month;

--日期统计

create table AA (
a_id number,              --交易操作员ID           
paymoney number,    --交易金额
a_time date               --交易时间
);
insert into AA values(1,11,sysdate);
insert into AA values(2,12,sysdate);
insert into AA values(3,13,sysdate);
insert into AA values(4,21,sysdate-1);
insert into AA values(2,22,sysdate-1);
insert into AA values(6,23,sysdate-1);
insert into AA values(7,31,sysdate-7);
insert into AA values(1,32,sysdate-7);
insert into AA values(1,33,sysdate-7);
insert into AA values(7,41,sysdate-2);
insert into AA values(3,52,sysdate-30);
insert into AA values(9,53,sysdate-30);
commit;
SELECT * FROM AA;

1、用一个sql实现：昨日交易总金额、上周交易总金额、上月交易总金额、本年交易总金额。
   昨日交易总金额：
select sum(a.paymoney)
  from (select paymoney, to_char(a_time, 'yyyymmdd') as atime from AA) a
 where a.atime = (select to_char(sysdate - 1, 'yyyymmdd') from dual);
   上周交易总金额：
select sum(a.paymoney)
  from (select paymoney, to_char(a_time, 'yyyymmdd') as atime from AA) a
 where a.atime = (select to_char(sysdate - 7, 'yyyymmdd') from dual)
   上月交易总金额：
select sum(a.paymoney)
  from (select paymoney, to_char(a_time, 'yyyymmdd') as atime from AA) a
 where a.atime = (select to_char(sysdate - 30, 'yyyymmdd') from dual)
   本年交易总金额：
select sum(a.paymoney)
  from (select paymoney, to_char(a_time, 'yyyy') as atime from AA) a
 where a.atime = (select to_char(sysdate, 'yyyy') from dual)

整合一条：select sum(decode(a.atime,(select to_char(sysdate,'yyyy') from dual),a.paymoney)) 本年,
sum(decode(a.btime,(select to_char(sysdate-30,'yyyymmdd') from dual),a.paymoney)) 上月,
sum(decode(a.btime,(select to_char(sysdate-7,'yyyymmdd') from dual),a.paymoney)) 上周,
sum(decode(a.btime,(select to_char(sysdate-1,'yyyymmdd') from dual),a.paymoney)) 昨天
  from (select paymoney, to_char(a_time, 'yyyy') atime,to_char(a_time, 'yyyymmdd') btime  from AA) a

2、求出单笔交易额最高的前三位操作员id及金额。

select rownum,a_id,paymoney from (select a_id,paymoney from aa order by paymoney desc)
where rownum<=3

-------------------------------------------------------------------------------------------------------------------------------------------

--create user t_scott,passwd t_scott should use user 'sys'
--grant resource,connect,create session to t_scott
--grant all on scott.emp to t_scott
--grant all on scott.dept to t_scott
--grant all on scott.salgrade to t_scott
--grant all on scott.bonus to _t_scott
-- select any table
-- login as user t_scott
--1. create table t_emp using sql as 'create table t_emp as select * from scott.emp where 1 = 1'
--2.create table t_dept as above
--3.create table t_salgrade as above
--4.create table t_bonus as above
--5.create index idx_empno on t_scott.t_emp.empno
-- create index  idx_empno on t_emp(empno);
--6.insert date into t_emp,t_dept,t_salgrade,t_bonus from scott.emp,scott.dept,scott.salgrade,scott.bonus using
--'insert into table select * from ohters table'

--=====================================follows are all in the user t_scott===============
--=====================================sql practice====================

    --1、列出至少有一个雇员的所有部门
 
　       select t.deptno from t_emp t group by t.deptno having count(1)>=1;

　　--2、列出薪金比"SMITH"多的所有雇员
 
         select empno,ename from t_emp where sal>(select sal from t_emp where ename='SMITH');

　　--3、列出所有雇员的姓名及其直接上级的姓名

         select a.ename as name,b.ename as mgr_name from t_emp a,t_emp b where a.mgr=b.empno;　　

　　--4、列出入职日期早于其直接上级的所有雇员

         select a.empno,a.ename from t_emp a where  a.hiredate<(select b.hiredate from t_emp b where a.mgr=b.empno);
 
　　--5、列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门

　       select d.dname,t.ename from t_emp t,t_dept d where t.deptno(+) = d.deptno;

　　--6、列出所有“CLERK”（办事员）的姓名及其部门名称
 
　       select t.ename,d.dname from t_emp t,t_dept d where t.deptno = d.deptno and t.job = 'CLERK';　

　　--7、列出各种工作类别的最低薪金，显示最低薪金大于1500的记录

         select min(t.sal) as sal from t_emp t group by t.job having min(t.sal)>1500;

　　--8、列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号

         select t.ename from t_emp t,t_dept d where t.deptno = d.deptno and d.dname like '%SALES%';  　

　　--9、列出薪金高于公司平均水平的所有雇员

　       select t.empno,t.ename from t_emp t where t.sal > (select avg(sal) from t_emp);

　　--10、列出与“SCOTT”从事相同工作的所有雇员

　       select t.empno,t.ename from t_emp t where t.job = (select job from t_emp where ename = 'SCOTT');

　　--11、列出某些雇员的姓名和薪金，条件是他们的薪金等于部门30中任何一个雇员的薪金

　　     select t.ename,t.sal from t_emp t where t.sal in (select sal from t_emp where deptno='30');

　　--12、列出某些雇员的姓名和薪金，条件是他们的薪金高于部门30中所有雇员的薪金

　       select t.ename,t.sal from t_emp t where t.sal > all(select sal from t_emp where deptno='30');

　　--13、列出每个部门的信息以及该部门中雇员的数量

　       select d.deptno,d.dname,d.loc,count(empno) as count from t_emp t,t_dept d where t.deptno(+) = d.deptno group by d.deptno,d.dname,d.loc;

　　--14、列出所有雇员的雇员名称、部门名称和薪金

　　     select t.ename,d.dname,t.sal from t_emp t,t_dept d where t.deptno = d.deptno;

　　--15、列出从事同一种工作但属于不同部门的雇员的不同组合

         select distinct t.job,t.deptno,min(t.empno)over(partition by t.job,t.deptno) from t_emp t order by t.job;

　　--16、列出分配有雇员数量的所有部门的详细信息，即使是分配有0个雇员

         select d.deptno,d.dname,d.loc,count(empno) as count from t_dept d left join t_emp t on d.deptno = t.deptno group by d.deptno,d.dname,d.loc;        

　　--17、列出各种类别工作的最低工资
     
          select t.job,min(sal) as sal from t_emp t group by t.job;

　　--18、列出各个部门的MANAGER（经理）的最低薪金

　       select t.deptno,d.dname,min(t.sal) as sal from t_emp t,t_dept d where t.deptno = d.deptno and t.job = 'MANAGER' group by t.deptno,d.dname;

　　--19、列出按年薪排序的所有雇员的年薪

         select t.empno,t.ename,t.sal*12 as sal from t_emp t order by t.sal;  　

　　--20、列出薪金水平处于第四位的雇员

          select empno,ename,sal from (select t.*,rownum r from (select empno,ename,sal from t_emp  order by sal desc) t where rownum <5) where r=4;

--======================================function=============================
-- 1.新建函数f_getenamebyempno,参数传入empno，返回对应ename，函数尽量考虑异常等

CREATE OR REPLACE FUNCTION f_getenamebyempno(emp_no in number) RETURN VARCHAR2
IS
  v_ename t_emp.ename%type;
BEGIN
 
 
 FOR EMPINFO IN (SELECT ENAME FROM T_EMP WHERE EMPNO = EMP_NO) LOOP

 v_ename := EMPINFO.ENAME;
END LOOP;

  select t.ename into v_ename from t_emp t where t.empno = emp_no and rownum = 1;
  RETURN(v_ename);
 
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE||':'||SQLERRM); 
END f_getenamebyempno;

 

 

 

--2.新建函数f_gettaxbyempno 一个计算员工应缴所得税的函数,
--传入员工的编号，返回该员工应缴纳的个人所得税。
--个人所得税计算方式如下：
--工资 所得税
--1600以下 0
--1600-3000 工资的5%-75
--3000-5000 工资的7.5%-125
--5000以上 工资的10%-165

CREATE OR REPLACE FUNCTION f_gettaxbyempno(emp_no IN NUMBER) RETURN NUMBER
AS
  v_money   T_EMP.SAL%TYPE:=0;
 
BEGIN
  SELECT CASE
          WHEN T.SAL <= 1600 THEN 0
          WHEN T.SAL <= 3000 THEN (CASE WHEN T.SAL*0.05 <75 THEN T.SAL*0.05 ELSE 75 END)
          WHEN T.SAL <= 5000 THEN (CASE WHEN T.SAL*0.075 <125 THEN T.SAL*0.075 ELSE 125 END)
          WHEN T.SAL > 5000 THEN (CASE WHEN T.SAL*0.1 <165 THEN T.SAL*0.1 ELSE 165 END)
          ELSE 0
         END
    INTO v_money
    FROM T_EMP T
   WHERE T.EMPNO = emp_no
     AND ROWNUM = 1;

  RETURN(round(v_money));
 
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE||':'||SQLERRM);
END f_gettaxbyempno;

 

--=======================================procedure============================
-- 1.新建过程p_outputempinfo ,无参数，循环打印所有t_emp 中的信息
--输出采用dbms_output.put_line();
--如果在命令行中显示结果，需开启输出--set serveroutput on
--要求 1.用游标形式定义 ,赋值是采用三种形式,分别打印输出
---1.1 单个变量赋值，如v_ename varchar2(30)
---1.2  type ,如 v_ename t_scott.t_emp.ename%type;
---1.3 rowtype v_emp t_scott.t_emp%rowtype;
-- 游标采用如下形式
--type cur_sor is ref cursor
-- v_empinfo cur_sor
--open v_empinfo for select * from emp;
--loop
--fetch
--end loop
--2.用for loop形式输出,不做其他要求

CREATE OR REPLACE PROCEDURE p_outputempinfo
AS
  type cur_sor is ref cursor;
  v_empinfo cur_sor;
  v_empno    t_scott.t_emp.empno%type;
  v_ename    t_scott.t_emp.ename%type;
  v_job      t_scott.t_emp.job%type;
  v_mgr      t_scott.t_emp.mgr%type;
  v_hiredate t_scott.t_emp.hiredate%type;
  v_sal      t_scott.t_emp.sal%type;
  v_comm     t_scott.t_emp.comm%type;
  v_deptno   number(2);
 
BEGIN
  open v_empinfo for select * from t_emp;
   loop
     fetch v_empinfo into v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno;
     exit when v_empinfo%notfound;
     dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr||' '||v_hiredate||' '||v_sal||' '||v_comm||' '||v_deptno);
   end loop;
 CLOSE v_empinfo ;

  open v_empinfo for select * from t_emp;
   loop
     fetch v_empinfo into rowtype ;
     exit when v_empinfo%notfound;
     dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr||' '||v_hiredate||' '||v_sal||' '||v_comm||' '||v_deptno);
   end loop;
 CLOSE v_empinfo ;

 
  
exception
  when others then
    dbms_output.put_line(sqlcode||':'||sqlerrm);
END p_outputempinfo;

 

 

--2.新建过程p_outputempinfobyempno ,输入empno,打印出行信息

CREATE OR REPLACE PROCEDURE p_outputempinfobyempno(emp_no in number)
AS
  type cur_sor is ref cursor;
  v_empinfo cur_sor;
  v_empno    t_scott.t_emp.empno%type;
  v_ename    t_scott.t_emp.ename%type;
  v_job      t_scott.t_emp.job%type;
  v_mgr      t_scott.t_emp.mgr%type;
  v_hiredate t_scott.t_emp.hiredate%type;
  v_sal      t_scott.t_emp.sal%type;
  v_comm     t_scott.t_emp.comm%type;
  v_deptno   number(2);
 
BEGIN
  open v_empinfo for select * from t_emp where empno = emp_no;
   loop
     fetch v_empinfo into v_empno,v_ename,v_job,v_mgr,v_hiredate,v_sal,v_comm,v_deptno;
     exit when v_empinfo%notfound;
     dbms_output.put_line(v_empno||' '||v_ename||' '||v_job||' '||v_mgr||' '||v_hiredate||' '||v_sal||' '||v_comm||' '||v_deptno);
   end loop;
CLOSE cur_sor ;
  
exception
  when others then
    dbms_output.put_line(sqlcode||':'||sqlerrm);
END p_outputempinfobyempno;

 

 

 

--3.新建过程p_updatesal,传入empno，sal，
--要求
--3.1 自定义异常，如果没有这个empno，抛出自定义异常 ex_noempno，并rollback
--3.2 如果update后的工资超过10000，抛出自定义异常，ex_toomuchmoney，并rollback
--3.3 如果修改成功，打印一条信息'Success'，

CREATE OR REPLACE PROCEDURE p_updatesal(emp_no IN NUMBER,sal1 IN NUMBER)
AS
 ex_noempno      EXCEPTION;
 ex_toomuchmoney EXCEPTION;
 info            VARCHAR2(10):='Success';
 va              number(1);
BEGIN
  select 1 into va from t_emp where empno = emp_no;

  if(0 = nvl(va,0)) then
    raise ex_noempno;
  end if;

  if sal1 > 10000 then
    raise ex_toomuchmoney;
  end if;

  update t_emp
     set sal = sal1
   where empno = emp_no;
  
  commit; 
  dbms_output.put_line(info);
 
exception
  when ex_noempno then
    rollback;
  when ex_toomuchmoney then
    rollback;
  when others then
    dbms_output.put_line(SQLCODE||':'||SQLERRM);
END p_updatesal;