查询所有在上海市总行办理了产品编号为“CS”和“RS”的个人客户（individual表）的身份证号（ID_NUMBER）和姓名全名。（输出：ID_NUMBER，全名）
提示：输出客户全名，需要用到concat函数
题目本意是想考察“包含关系”查询，但考虑到中文理解的二义性，以下两种查询的含义都算对。
1. 选择“CS”或者“RS”二者之一
SELECT ID_NUMBER,CONCAT(LAST_NAME,FIRST_NAME) as fullname from individual WHERE CUST_ID in
(SELECT CUST_ID from account where (PRODUCT_CD='CS' or PRODUCT_CD='RS') and OPEN_BRANCH_ID=1);
2. 选择至少包含“CS”和“RS”
思路：可以用集合的交，也可以用包含关系查询
#包含关系查询（需要构建常量关系）
SELECT ID_NUMBER,CONCAT(LAST_NAME,FIRST_NAME) as fullname from individual WHERE not exists
(select PRODUCT_CD from (select 'RS' as PRODUCT_CD UNION select 'CS') as temp where PRODUCT_CD not in
(select PRODUCT_CD from account where individual.CUST_ID = account.CUST_ID and OPEN_BRANCH_ID=1));
#集合交
SELECT ID_NUMBER,CONCAT(LAST_NAME,FIRST_NAME) as fullname from individual
WHERE CUST_ID in (select cust_ID from account where PRODUCT_CD='CS' and OPEN_BRANCH_ID=1 and cust_id in
(select cust_ID from account where PRODUCT_CD='RS' and OPEN_BRANCH_ID=1));
select distinct id_number, concat(last_name, first_name) full_name
from individual i , account a, branch b
where i.cust_id = a.cust_id
and (product_cd = 'CD' or product_cd = 'RS')
and a.open_branch_id = b.branch_id
and b.name = '上海市总行';
查询个人客户（individual）中“尤青”所有办理的业务信息。（输出：身份证号、姓名全名、客户编号、账户编号、可用余额、产品名称、产品类型名称）
with tmp as (
SELECT i.ID_NUMBER,
CONCAT(i.LAST_NAME,i.FIRST_NAME)as fullname,c.CUST_ID , a.ACCOUNT_ID,a.AVAIL_BALANCE,
p.name as product_name, pt.name as product_type_name
from individual i
LEFT JOIN customer c on c.CUST_ID = i.CUST_ID
LEFT JOIN account a on a.CUST_ID = c.CUST_ID
LEFT JOIN product p on p.PRODUCT_CD = a.PRODUCT_CD
LEFT JOIN product_type pt on pt.PRODUCT_TYPE_CD = p.PRODUCT_TYPE_CD
)
SELECT * from tmp WHERE fullname='尤青';
select id_number, concat(last_name, first_name) full_name,
i.cust_id, account_id, avail_balance, product_cd, PRODUCT_TYPE_CD
from individual i natural join account a natural join product p
where #i.cust_id = a.cust_id
concat(last_name, first_name) = '尤青';
查询客户（customer）表，列出所有客户的姓名全名、及其在单位的职位名称（title）(提示：对私客户职位为NULL)。(输出：全名，职位名称）
SELECT
(
case when t.CUST_TYPE_CD = 'B'
then (SELECT CONCAT(o.LAST_NAME,o.FIRST_NAME) from officer o WHERE o.CUST_ID = t.CUST_ID)
else (SELECT CONCAT(o.LAST_NAME,o.FIRST_NAME) from individual o WHERE o.CUST_ID = t.CUST_ID)
end
) as fullname,
(case when t.CUST_TYPE_CD = 'B'
then (SELECT i.TITLE FROM officer i WHERE i.CUST_ID = t.CUST_ID)
else NULL end) as title
from customer t;
select concat(c.last_name, c.first_name) full_name from
customer c left join
(
(select cust_id, concat(last_name, first_name) full_name, title
from officer)
union
select cust_id, concat(last_name, first_name) full_name, null
from customer natural join individual) e
on c.cust_id = e.cust_id;
;
select
(
case when c.cust_type_cd = 'B'
then
(
select concat(o.last_name, o.first_name) full_name
from officer o
where o.cust_id = c.cust_id
)
else
(
select concat(o.last_name, o.first_name) full_name
from individual o
where o.cust_id = c.cust_id
)
end
) as full_name,
(
case when c.cust_type_cd = 'B'
then
(
select o.title
from officer o
where o.cust_id = c.cust_id
)
else null
end
) as title
from customer c
;
查询所有在2015年有交易记录的客户（customer）的cust_id号、姓名全名、账户余额，并对余额降序排序。（输出：CUST_ID，全名，账户余额）
WITH tmp as (SELECT ACCOUNT_ID from acc_transaction WHERE YEAR(TXN_DATE)=2015 )
SELECT a.cust_id,
(case when a.cust_id in (SELECT i.cust_id from individual i)
then (SELECT CONCAT(o.LAST_NAME,o.FIRST_NAME) from individual o WHERE o.cust_id = a.cust_id)
else (SELECT CONCAT(o.LAST_NAME,o.FIRST_NAME) from officer o WHERE o.cust_id = a.cust_id)
end
)as fullname,
a.avail_balance
from tmp t JOIN account a on t.account_ID = a.account_id
ORDER BY a.avail_balance DESC;
(
select cust_id, concat(last_name, first_name) full_name, sum(avail_balance) sum_deposit
from customer c natural join account a natural join acc_transaction ac natural join individual
where account_id in
(
select account_id
from acc_transaction
where TXN_DATE between '2014-12-31' and '2016-01-01'
)
group by c.cust_id
)
union
(
select cust_id, concat(last_name, first_name) full_name, sum(avail_balance) sum_deposit
from customer c natural join account a natural join acc_transaction ac natural join officer
where account_id in
(
select account_id
from acc_transaction
where TXN_DATE between '2014-12-31' and '2016-01-01'
)
group by cust_id
)
order by sum_deposit desc;
查询所有不在2015年（例如在2011、2019年等）有交易记录的账户编号（ACCOUNT_ID）。（输出：账户编号）
要求：需要用外连接来写该查询语句；
注意：某些账户可能在2011、2015年都有交易记录，但只要在2015年有一条交易记录的账户都会被排除在结果集之外。
select account.ACCOUNT_ID from account left join
(select * from acc_transaction where year(TXN_DATE) = 2015) as t
on account.ACCOUNT_ID = t.ACCOUNT_ID where t.ACCOUNT_ID is NULL;
select distinct a.account_id
from account a left outer join acc_Transaction ac
on a.account_id = ac.account_id
where ac.account_id not in
(
select account_id
from acc_transaction
where txn_date between '2014-12-31' and '2016-01-01'
)
order by a.account_id
查询各个银行中余额最多的客户信息。（输出：银行名称、姓名全名、账户余额）
在account表中插入一条测试元组：
insert into account values (null, 2000000, null, null, ‘2022-10-12’, ‘正常’, 5, 2, 15, ‘CS’);
提示：需要处理银行中余额最多不止一个客户的情况。
with temp as (select open_branch_id, max(AVAIL_BALANCE) as MAX_BALANCE
from account group by open_branch_id)
select
(
case when cust_id in (select cust_id from individual)
then (select CONCAT(individual.LAST_NAME,individual.FIRST_NAME) from individual WHERE individual.cust_id = account.cust_id)
else (select CONCAT(officer.LAST_NAME, officer.FIRST_NAME) from officer WHERE officer.cust_id = account.cust_id)
end
), branch.NAME, account.AVAIL_BALANCE
from (account join temp
on account.OPEN_BRANCH_ID = temp.OPEN_BRANCH_ID
and account.AVAIL_BALANCE = temp.MAX_BALANCE)
join branch on account.OPEN_BRANCH_ID = branch.BRANCH_ID;