oracle 练习题目

 

第1题：

从2个表中产生报表,假定有两个表:checking和savings

表结构

checking表

cus_id int

acct_no varchar2(8)

savings表

cus_id int

acct_no varchar2(8)

1号顾客有checking account 账号为10001,同时还有 savings account 账号为 50001

2号顾客有checking account 账号为10002,但是没有 savings account

3号顾客有 savings account  账号为50002,但是没有 checking account

写SQL语句,产生报表如下:

cus_id checking account savings account

1 10001 50001

2 10002

3 50002

答案：

select c.cus_id,c.acct_no,s.acct_no from checking c,savings s where c.cus_id=s.cus_id(+) union select s.cus_id,c.acct_no,s.acct_no from checking c,savings s where c.cus_id(+)=s.cus_id

 =================================================================================

第2题：

从一个表中产生报表

表accounts

cus_id acct_no acct_type

1 10001 checking

1 50001 savings

2 10002 checking

3 50002 savings

写SQL语句产生这样的报表

cus_id checking account savings account

1 10001 50001

2 10002

3 50002

答案：

select a1.cus_id,a1.acct_no,a2.acct_no from (select * from accounts c where acct_type='checking' )a1 left join (select * from accounts c where acct_type='savings')a2 on a1.cus_id=a2.cus_id
union select a2.cus_id,a1.acct_no,a2.acct_no from (select * from accounts c where acct_type='checking' )a1 right join (select * from accounts c where acct_type='savings')a2 on a1.cus_id=a2.cus_id;

 =================================================================================

第3题：

有两个表 account 和 payment

account表

acct_no varchar2(5)

date_created  date

amount int

account

acct_no date_created amount

1000 2010-1-14 900

1001 2010-4-15 500

1002 2010-6-20 2000

1003 2010-8-4 3000

1004 2010-10-10 4000

payment表

acct_no varchar2(5)

serial_no int

payment int

date_paid  date

payment

acct_no serial_no payment date_paid

1000 1 450 2010-1-30

1000 2 450 2010-2-22

1002 1 300 2010-7-20

1002 2 300 2010-8-20

1002 3 300 2010-9-30

1003 1 1000 2010-9-9

我们需要这样一份应收款分类报表

balance

acct_no last_act_date <=30days <30days

1001 2010-4-15 500

1002 2010-9-30 1100

1003 2010-9-9 2000

1004 2010-10-10 4400

last_act_date 最近的活动日期,即最近的支付日期,如无支付,就用账户创建日期

1000:余额为零,不显示在报表中

1001:无payment,它的账户创建日期就是最近的活动日期,已超过30天

1002:余额为1400.其最近的payment是2010-9-30,在30天内

1003:余额为2000,其最近的payment是2010-9-9,已超过30天.

1004:无payment,它的账户创建日期就是最近的活动日期,在30天内.

由于这样的SQL书写比较复杂,而且运行的时间也会比较长,有人建议在account中添加字段

pay_sum 每个账户的payment的总和

last_act_date 最近的活动日期,即最近的支付日期,如无支付.就用账户的创建日期

account_alter

acct_no date_created amount pay_sum last_act_date

1000 2010-1-14 900

1001 2010-4-15 500

1002 2010-6-20 2000

1003 2010-8-4 3000

1004 2010-10-10 4000

(1).数据同步 data sync

通过存储过程完成account_alter表与原有的account,payment表中的数据同步

create or replace procedure proc is
v_no account.acct_no%type;  --帐户号
v_date_created account.date_created%type;   --创建时间
v_amount account.amount%type;    --存款
v_no1 account.acct_no%type;    --payment的帐户号
v_sum payment.payment%type;     --账单总数
v_date account.date_created%type;   --最后消费时间

cursor cur_save is select a.acct_no,a.date_created,a.amount from account a order by a.acct_no;
cursor cur_save1 is select b.acct_no,sum(b.payment),max(b.date_paid) from payment b group by b.acct_no;
begin
open cur_save;
open cur_save1;
loop
   fetch cur_save into v_no,v_date_created,v_amount;
   exit when cur_save%notfound;
    insert into account_alter values(v_no,v_date_created,v_amount,null,null);
   end loop;
loop
  fetch cur_save1 into v_no1,v_sum,v_date ;
  exit when cur_save1%notfound;
  update account_alter a set a.pay_sum=v_sum,a.last_act_date=v_date where a.acct_no=v_no1;
  end loop;
   close cur_save;
   close cur_save1;
end;

 (2) 触发器 trigger 

 在payment表中添加触发器,完成当插入或更新payment表时,同时更新account_alter中pay_sum及last_act_date数据.

 (3)报表

给出balance报表SQL语句