数据库题目5

题目描述：
假如某系统包括客户信息、账户余额、资金明细表，其表结构如下：客户信息表“clientinfo”（主键：custid)：

问题：
1) 找出未持有账户的客户信息列表（输出姓名、性别以及所属分行）；
SELECT custname, custsex, branchid
FROM clientinfo
WHERE custid
NOT IN (SELECT cusid
FROM acctbalance);

2) 找出在2013年有交易发生的账户列表（输出帐号、币种、客户姓名、性别）；
SELECT accountno, ccycode, custname, custsex
FROM clientinfo, acctbalance
WHERE accountno
IN (SELECT accountno
FROM funddetail
WHERE transdate
BETWEEN 20130101
AND 20131231);

3) 计算分行‘100000’所有账户的存款金额(按币种输出)；
SELECT SUM(currentbalance),ccycode
FROM ((SELECT c.branchid, a.currentbalance, a.ccycode
FROM clientinfo c
LEFT JOIN acctbalance a
ON c.custid = a.custid)
AS sheet)
WHERE sheet.branchid = 100000
GROUP BY ccycode;

4) 找出2013年未发生交易且账户余额小于300的客户列表（输出帐号、币种、客户编号);
SELECT accountno, ccycode, custid
FROM acctbalance
WHERE currentbalance < 300
AND accountno
NOT IN (SELECT accountno
FROM funddetail
WHERE transdate
BETWEEN 20130101
AND 2013123);

5) 将账户余额超过1000000且属于分行‘300000’的客户存款利率涨15%；
UPDATE acctbalance
SET interetrate = interestrate * 1.15
WHERE currentbalance > 100000
AND custid
IN (SELECT custid
FROM clientinfo
WHERE branchid = ‘300000’);