首页 > 试题广场 >

数据库 假如某系统包括客户信息、账户余额、资金明细表，

[问答题]
数据库
假如某系统包括客户信息、账户余额、资金明细表，其表结构如下：

客户信息表“clientinfo”（主键：custid)：

客户号

客户姓名

        性别

分行号

custid

custname

custsex  男/女

branchid

账户余额表“acctbalance”（主键：acctountno,ccycode)：

帐号

币种

      当前余额

客户号

存款利率

accountno

ccycode (‘156’人民币)

currentbalance

custid

interestrate

资金明细表“funddetail”（主键：transdate,serialno)：

交易日期

流水编号

      帐号

币种

借方金额

 贷方金额

transdate

serialno 

accountno

ccycode

creditamount

debitamount

请根据上述提示，完成如下题目：

1)   找出未持有账户的客户信息列表（输出姓名、性别以及所属分行）；

2)   找出在2013年有交易发生的账户列表（输出帐号、币种、客户姓名、性别）；

3)   计算分行‘100000’所有账户的存款金额(按币种输出)；

4)   找出2013年未发生交易且账户余额小于300的客户列表（输出帐号、币种、客户编号);

5)   将账户余额超过1000000且属于分行‘300000’的客户存款利率涨15%；


添加回答
牛客903744135号头像 牛客903744135号
1)   找出未持有账户的客户信息列表（输出姓名、性别以及所属分行）；
select custname，custsex，branchid from clientinfo a not exists （select custid from acctbalance b where a.custid = b.custid）或
select a.custname，a.custsex，a.branchid from clientinfo a letf join acctbalance b on a.custid ！= b.custid

2)   找出在2013年有交易发生的账户列表（输出帐号、币种、客户姓名、性别）；
select a.custname, a.custsex, b.accountno, b.ccycode from clientinfo a,acctbalance b where a.custid = b.custid and esists(select 1 from funddetail c where c.transdate between '20130101' and '20131231' and c.accountno = b.c.accountno and c.ccycode = b.ccycode)

3)   计算分行‘100000’所有账户的存款金额(按币种输出)；
select ccycode,sum(current_balance) from clientinfo a, acctbalance b where a.custid = b.custid and a.branchid = '100000' group by ccycode

4)   找出2013年未发生交易且账户余额小于300的客户列表（输出帐号、币种、客户编号);
select b.accountno, b.ccycode , b.custid from acctbalance b where b.currentbalance < 300 and not exists (select 1 from funddetail c where c.accountno = b.accountno and c.ccycode = b.ccycode and transdate between '20130101' and ‘20131231’)

5)   将账户余额超过1000000且属于分行‘300000’的客户存款利率涨15%；
update accountno b set interestrate = interestrate*1.15 where b.currentbalance > 1000000 and exists(select 1 from clientinfo a where a.custid = b.custid and a.branchid = '300000')
发表于 2022-06-24 18:57:47 回复(0)
讨厌动态规划头像 讨厌动态规划
答案确定列出了所有的查询方法吗，我的答案虽然跟答案不一样，可是我觉得自己是对的啊，但是又没有办法证明，无语。
发表于 2020-10-07 23:31:54 回复(0)
jaybo_头像 jaybo_

1、select * from clientinfo where custid not in(select cusitid from acctibalance)

2、select table1.account, table2.ccycode, table2.custname, table2.custsex from

(select *  where transdate between ‘2013-01-01 00:00:00’and ‘2013-12-31 00:00:00’) as table1 
join (select a.*, c.*  from acctibalance a left join clientinfo c on c.custid=a.custid) as table2 
where table1.accountno=table2.accountno

3、select ccycode, sum(currentbalance) from

(select * from acctbalance where custid in(select custid from clientinfo where branched=’100000)
) group by ccycode

4、select accoutno,ccycode,custid from acctbalance where currentbalance<300 and accountno not in

 (select accoutno from funddetail where trainsdate between‘2013-01-01 00:00:00’and ‘2013-12-31 00:00:00’)

5、update actable set interstrate=insterstrate*1.15 where currentbalance>100000 and custid =( select custid from clientinfo where branched=’300000’)



编辑于 2020-03-19 01:28:27 回复(0)
小余同学要努力头像 小余同学要努力
1)select * from clientinfo where custid not in (select custid from acctbalance)
2)select * from funddetail where transdate >=to_date('2019-2-1 00:00:00','yyyy-MM-dd hh24:mi:ss') and transdate <=to_date('2019-3-1 00:00:00','yyyy-MM-dd hh24:mi:ss');
3)select ccycode,sum(currentbalance) from
(
select * from acctbalance where custid in (
   select custid from clientinfo where branchid=100000
)
)
group by ccycode;
4)select accountno,ccycode,custid from acctbalance where currentbalance <2000 and accountno not in
(
select accountno from funddetail
where transdate >=to_date('2019-2-1 00:00:00','yyyy-MM-dd hh24:mi:ss') and transdate <=to_date('2019-3-1 00:00:00','yyyy-MM-dd hh24:mi:ss')

)
5)
发表于 2019-09-08 13:58:42 回复(0)
Rachel_Wang521头像 Rachel_Wang521
<p>w</p>
发表于 2020-10-09 16:56:45 回复(0)
miaomaio12138头像 miaomaio12138
1.select custname, custsex,branchid from clientinfo where custid not exists in(select custid from acctbalance);
2.select accountno,ccycode,custname,custsex from clientinfo c, acctbalance a where accountno not exists in(select accountno from funddtail where translate between 20130101 and 20131231) group by accountno;
3.select ccycode,sum(currentbalance) from (（select c.branchid,a.ccycode.a.currentbalance from clientinfo c left join acctbalance a ） as sheet where sheet.branchid='100000');
4.select accountno, ccycode, custid  from acctbalance where accountno not exists in (select accountno from funddetail where transdate between 20130101 and 20131231 );
5.update table acctbalance set insterestrate=insterestrate*1.15 where currentbalance >100000 and custid exists in (select custid from clientinfo where branchid='300000');
发表于 2020-10-06 12:30:46 回复(0)
v无风头像 v无风
1.
    select custname,custsex,branchid
    from clientinfo
    where custid not in(
        select crustid from acctbalance
    )
    
2.
    select accountno,ccycode,custname,custsex
    from acctbalance ac,clientinfo cl
    where ac.custid=cl.custid and accountno in(
        select accountno from funddetail
        where trandsdate between 
        '2013-01-01 00:00:00' and '2013-12-31 23:59:59'
    )
3.
    select ccycode,sum(currentbalance) from acctbalance
    where custid in(
        select custid from clientinfo
        where branchid='100000'
    )
    group by ccycode
4.
    select accountno,ccycode,crustid
    from acctbalance
    where currentbalance<300 and accountno not in(
        select accountno from funddetail
        where trandsdate between 
        '2013-01-01 00:00:00' and '2013-12-31 23:59:59'
    )
发表于 2020-08-22 15:14:11 回复(0)
hnit2020头像 hnit2020
  1. update acctbalance a set interestrate = interestrate * 1.15 where currentbalance > 1000000 and custid in (select custid from clientinfo where branchid = ‘300000’;
发表于 2020-03-16 00:58:02 回复(0)
ILLMX头像 ILLMX
1)select c.custname,c.custsex,c.branchid from clientinfo c where c.custid not in(select c.custid from clientinfo c,acctbalance a where c.custid=a.custid);
2)
发表于 2019-09-21 14:20:09 回复(0)
提交观点

问题信息

C++工程师 golang工程师 iOS工程师 安卓工程师 运维工程师 前端工程师 恒生电子 2015 c#工程师 Java工程师
来自：恒生公司2015秋招开...
上传者：小小
难度：
9条回答 6695浏览

热门推荐

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号