我的周末就耗在这道题上了。。
来来去去,然后中间又夹杂一些杂事,做到现在终于做完。。
1.【create table语句】
create table bank(
cardid number primary key,
perid number unique,
name varchar2(20),
sex char(2),
brithday varchar2(40),
constraint sex check(sex in ('男','女')) ,
constraint perid check(length(perid) in (15,18))
);
create table money(
cid number,
score number,
foreign key(cid) references bank(cardid));
2.【insert bank表测试数据】
insert into bank (cardid,perid) values (1,430903198511302113);
insert into bank (cardid,perid) values (2,430903851130230);
insert into bank (cardid,perid) values (3,430903001130234);
insert into bank (cardid,perid) values (4,430903981120231);
insert into bank (cardid,perid) values (5,430903011111228);
insert into bank (cardid,perid) values (6,430903200112212103);
insert into bank (cardid,perid) values (7,430903311111221);
insert into bank (cardid,perid) values (8,430903198112212103);
insert into bank (cardid,perid) values (9,430913981120239);
insert into bank (cardid,perid) values (10,431903001130234);
insert into bank (cardid,perid) values (11,420903198112212133);
3.【更新性别,出生日期】
update bank set
sex = decode(length(perid),15,
decode(mod(substr(perid,14,1),2),0,'女',1,'男'),18,
decode(mod(substr(perid,17,1),2),0,'女',1,'男')),brithday =
decode(length(perid),15,19||substr(perid,7,6),18,substr(perid,7,8));
4.【update name测试数据】
注意:修改姓,但是名里面的牛不要改。策略:把牛姓的名字替换为'刘'||第二个字符开始到结束的串。
create sequence seq1;
update bank set name = '牛XX牛刘'||seq1.nextval;
update bank set name = '刘'||substr(name,2,length(name)-1) where name like '牛%';
5.【insert money表测试数据】
insert into money values (1,1.1);
insert into money values (2,12);
insert into money values (3,0.9);
insert into money values (4,122);
insert into money values (5,12);
insert into money values (6,12);
insert into money values (7,131);
insert into money values (8,12);
insert into money values (9,123);
insert into money values (10,12);
insert into money values (11,0.11);
6.delete from money where score < 2;
7.insert into money select cardid,2 from bank
where cardid not in (select cid from money);
8.select name from bank,money
where cardid = cid and rownum < 4 order by money.score desc;
9.select name from bank
where substr(brithday,5,2) = to_char(sysdate,'mm');
--本月生日的客户姓名
select name from bank where
to_char(to_date(brithday,'yyyymmdd'),'ww') = to_char(sysdate,'ww');
--本周生日的客户姓名
10.题意不清,个人理解为显示所有客户余额为平均余额的客户姓名,其中平均存款计算时不算一个最高和一个最低余额。
select name from bank where cardid in
(select cid from money where score in
(select (sum(score)-max(score)-min(score))/(count(score)-2)
from money));
花絮:
1.查找了下身份证的生成规则,同时研究了下生成算法。。
资料:http://www.xaccp.com/Article/200808/1016.shtml
2.在由身份证号码更新生日时,遇到一个问题,就算是上面的链接处引用的算法也无法解决,那就是15位身份证的日期段为00XXXX时,无法判断生日为1900XXXX或是2000XXXX。
2.1 在连接字符串时,因为想到用substr函数取perid字段得到的是字符类型,本来可以隐式转换为数字类型,但是强制转换为数字类型后,连接时会出问题,比如select 01||00 from dual;得到的是01而不是0100。所以当时在更新生日时用到一个很复杂的decode语句。。
update bank set
sex = decode(length(perid),15,decode(mod(substr(perid,14,1),2),0,'女',1,'男'),18,
decode(mod(substr(perid,17,1),2),0,'女',1,'男')),
brithday = decode(length(perid),15,
decode(length(19||to_number(substr(perid,7,6))),8,19||to_number(substr(perid,7,6)),7,190||to_number(substr(perid,7,6)),6,
1900||to_number(substr(perid,7,6))),18,substr(perid,7,8));
2.2在整个编译过程中由于文档和SQL环境的多次变换,导致符号半全角混杂,带来极大不便。。经常莫名其妙的错误。应对策略,在使用ORACLE的过程养成用E文编译,测试学习的习惯,ORACLE认证考试OCA,OCP也是全E文化的。
3.在第8问中,本来想到可以用以下语句解决的
【select name from bank where cardid in (select cid from money where rownum < 4 order by score desc)】
多次出错后,查笔记想起order by 子句不能用到嵌套语句中,只能放在最外层语句的最后,不知可对,各位看官提下意见。
4.关于第10题,如果去掉所有最高和最低余额,不是都只去掉一个计算平均余额时的查询:
select name from bank where cardid in
(select cid from money where score in
( select sum(score)/count(score) from money where score not in
((select max(score) from money),(select min(score) from money))));
大功告成!以上全是ORACLE支持的,MYSQL的话,需要更换MYSQL自适应的函数,还有其中的7问中的insert语句,insert into money select cardid,2 from bank where cardid not in (select cid from money);MYSQL不支持。
吃饭。。