关于SQL数据库问题

我的周末就耗在这道题上了。。

来来去去，然后中间又夹杂一些杂事，做到现在终于做完。。

1create 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));

2insert 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));

4update name测试数据

注意：修改姓，但是名里面的牛不要改。策略：把牛姓的名字替换为'刘'||第二个字符开始到结束的串。

create sequence seq1;

update bank set name = '牛XX牛刘'||seq1nextval;

update bank set name = '刘'||substr(name,2,length(name)-1) where name like '牛%';

5insert money表测试数据

insert into money values (1,11);

insert into money values (2,12);

insert into money values (3,09);

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,011);

6delete from money where score < 2;

7insert into money select cardid,2 from bank

where cardid not in (select cid from money);

8select name from bank,money

where cardid = cid and rownum < 4 order by moneyscore desc;

9select 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查找了下身份z的生成规则，同时研究了下生成算法。。

资料：>

Create Function Avg_grade(@cnum varchar(50))

returns float(3,1) as begin declare @result float(31) select @result=avg(grade) from SC where cnum=@cnumreturn @result end

以上就是关于关于SQL数据库问题全部的内容，包括:关于SQL数据库问题、数据库SQL语句、SQL SERVER数据库编程:创建并执行用户自定义函数Avg_grade,完成：计算全体学生某门功课的平均成绩等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！