d R-S
A a c B b b a f C c d S-R
D b E g F a
2．描述下面查询的结果，如果将UNION用EXCEPT替代，又会有什么样的查询结果？
( SELECT A FROM r, s WHERE r.a = s.d ) UNION ( SELECT A FROM r, s WHERE r.c = s.d );
UNION 这条语句是查询数据库中r表的a字段的值和s表中的d字段的值，r表中c字段的值和s表中d字段的值相等的部分，取出相等的部分后，相同的结果只保留一个。将union换位EXPECT后 得到的结果是只存在表a中和d相等的部分，并且去掉重复行。
5
2．SQL
请写出针对以下问题的SQL语句（每一问必需用一条SQL语句实现，但该SQL语句可以包含子查询）。
1.
查询姓张的所有客户信息
Select * from clinet where clientname like ?张%?; 2.
查询客户号1001的客户拥有的计费设备个数。
Select count(*) from device where clientno=1001; 3.
计算客户号1001在2016年1月产生的附加费用1和附加费用2； Select
addfee1,addfee2
from
monthlyneedpay
where
to_char(logdate,?yyyy?)=2016 and to_char(logdate,?mm?)=1; 4.
查询客户号1001在2016年的历史缴费记录；
Select * from paylog where clientno=1001 and to_char(payday,?yyyyy?)=2016; 5.
更新客户号1001在2016年1月份的收费标识为1；
Update monthlyneedpay set paystatus=?1? where to_char(payday,?yyyy?)=2016 and
to_char(payday,?mm?)=1; 6.
Select * from monthlyneedpay order by clientno asc,payday desc; 查询应收费用表，先按照客户号升序排序，再按照年份排序降序排序。
3．Advanced SQL
在该练习中，我们根据银行代收费系统的需求，完成以下高级SQL语句的编写：
1. 查询前一年所有客户的欠费记录，按照客户编号升序排列。
Select clientno,needpay,payday from monthlyneedpay where paystatus=0 and to_char(payday,?yyyy?)=2015 order by clientno asc; 2. 查询当前年份欠费记录超过5条以上的用户。
select * from (select sum(case PAYSTATUS when '0' then 1 else 0 end) as QUANTITY from monthlyneedpay where to_char(LOGDATE,'yyyy')='2016' group by CLIENTNO)where QUANTITY>=5;
3计算客户号1001的客户，其名下所有设备的应收基本费用之和，附加费用1之和，附加费用
2之和。
Select count(needpay),count(addfee1),count(addfee2) from monthlyneedpay
6
where clientno=1001;
4.计算客户号1001在2016年1月份，计费设备号100的应收违约金。
Select latefee from monthlyneedpay where clientno=1001 and deviceno=100 and to_char(payday,?yyyy?)=2016 and to_char(payday,?mm?)=1;
5.计算银行代号为19的银行在20160130产生的缴费总次数和总金额，冲账的记录不记录总次数和总金额中。
select
banktotalcount,banktotalmoney
from
checktotal
where
bank_id=19
and and
to_char(checkdate,,?yyyy?)=2016 to_char(checkdate,?dd?)=30;
and to_char(checkdate,?mm?)=1
4．Programming with Transactions and Procedure Process
编写存储过程完成以下问题(如果不熟悉存储过程的编写，也可以顺序执行多条SQL语句来实现功能需求)： 第一部分【查询】： 交易描述：
判断客户号是否存在，然后根据客户号取得客户姓名，地址，应收费用。
其中应收费用的计算：该用户下所有计费设备，计算所有计费设备未交费的所有月份应收费用的和。计费设备应收费用的计算：根据计费设备号取得基本费用，附加费用1，附加费用2，应收违约金,实收违约金 ,减免违约金后，应收费用=基本费用+附加费用1+附加费用2+应收违约金-减免违约金，依次获取该计费设备收费标志为0的所有月份应收费用的和。修改应收费用表中的应收违约金，附加费用1，附加费用2几个字段，避免执行缴费操作后的重复计算。获取的客户姓名、地址、应收费用等数据返回。 createorreplaceprocedure checkclient(client_no innumber,error outvarchar2, outclientnanme outvarchar2,outaddress outvarchar2,outneedpay outnumber)is
Tclientnanme varchar2(20); Taddress varchar2(20); Tneedpay number(7,2); clientnum number(4); Taddfee1 number(7,2); Taddfee2 number(7,2); Terror varchar2(20); Tlatefee number(7,2); begin
7
selectcount(*)into clientnum from client where clientno=client_no; if
clientnum=0then
Terror:='没有此客户'; error:=Terror; else
select clientname,address into Tclientnanme,Taddress from client where clientno=client_no; selectsum(needpay),sum(addfee1),sum(addfee2),sum(latefee)into
Tneedpay ,Taddfee1,Taddfee2,Tlatefee from monthlyneedpay where clientno=client_no and paystatus=0;
outclientnanme:=Tclientnanme; outaddress:=Taddress;
outneedpay:=Tneedpay+Taddfee1+Taddfee2+Tlatefee; endif;
end checkclient;
第二部分【缴费】 交易描述：
判断交费金额是否正确，交费金额应该为基本费用，附加费用1，附加费用2，应收违约金的和再减去减免违约金之后的值。如果交费金额不正确，返回交费金额错误的提示信息。缴费金额正确，在应收费用表表中修改收费标志，实收违约金，其中实收违约金应该为应收违约金减去减免违约金。同时用户交费表中增加一条记录，其中操作类型为“存款”。如果客户存在违约金，应计算违约金（这部分可以在其他模块完成的情况下做，具体计算方法见附录）
返回包括姓名，金额，地址，明细费用等详细信息的发票数据。
createorreplaceprocedure checkcharge(client_no innumber,LName outvarchar2, balance outnumber,address outvarchar2,error outvarchar2)is
TName varchar2(20); Taddress varchar2(20);
8
Tpay number(7,2); Tclientnum number(4); Terror varchar2(20); Tneedpay number(7,2); Taddfee1 number(7,2); Taddfee2 number(7,2); Tlatefee number(7,2); Tcharge number(7,2); len number; TID number(4);
Tbasicfee number(7,2);
Cursor myCur isselect*from monthlyneedpay where clientno=client_no;
Tend Date; begin
selectcount(clientno)into Tclientnum from client where clientno=client_no; if
Tclientnum=0then Terror:='不存在此用户'; error:=Terror; endif;
for cur_result in myCur loop begin
Tend:=cur_result.logdate; TID:=cur_result.id;
len:=trunc(sysdate)-trunc(Tend)-10;
update monthlyneedpay set latefee=len*0.001where id=TID and clientno=client_no; end; endloop;
selectsum(paymoney)into Tpay from paylog where clientno=client_no;
selectsum(needpay),sum(basicfee),sum(addfee1),sum(addfee2),sum(latefee)intoTneedpay,Tbasicfee,Taddfee1,Taddfee2,Tlatefee from monthlyneedpay where clientno=client_no;
9