MySQL下数据库习题篇：银行数据库习题（第五版）

---

一、查询数据习题(只使用相关关系代数指令)

1.Find all loans of over $1200

${\sigma }_{amount>1200}(loan)$

选取贷款额超过1200的行，用$\sigma$

2.Find the loan number for each loan of an amount greater than $1200

${\Pi }_{loan\_number}({\sigma }_{amount>1200}(loan))$

选取the loan number列名，用$\Pi$。

3.Find the names of all customers who have a loan, an account, or both, from the bank

${\Pi }_{customer\_name}(borrower)\cup {\Pi }_{customer\_name}(depositor)$

选择 the names of all customers列名,${\Pi }_{customer\_name}$。

have a loan, an account, or both表示都有贷款或存款，按理来说要选loan贷款表和account存款表啊，但注意这两个表都没有表示名字的属性，所以要选则borrower贷款人表和depositor存款人表。

4.Find the names of all customers who have a loan and an account at bank.

${\Pi }_{customer\_name}(borrower)\cap {\Pi }_{customer\_name}(depositor)$

同上

5.Find the names of all customers who have a loan at the Perryridge branch.

Query1

${\Pi }_{customer\_name}({\sigma }_{branch\_name=“Perryridge”}({\sigma }_{borrower.loan\_number=loan.loan\_number}(borrower\times loan)))$

最外层：
选择 the names of all customers列名，用${\Pi }_{customer\_name}$。

内层：
列名要求一个是cutomer_name，另一个是branch_name，前者在borrower表中，后者在loan表中，所以我们才用两个表的笛卡尔积：$borrower\times loan$

两个表中贷款人的loan_number应该一致，所以选取一致的行${\sigma }_{borrower.loan\_number=loan.loan\_number}$

选择银行为the Perryridge branch，即${\sigma }_{branch\_name=“Perryridge”}$

Query2

${\Pi }_{customer\_name}({\sigma }_{loan.loan\_number=borrower.loan\_number}(({\sigma }_{branch\_name=“Perryridge”}(loan))\times borrower))$

同上的区别是先筛选出属于the Perryridge branch，然后再进行匹配loan_number.

Query3

${\Pi }_{custome{r}_{n}ame}({\sigma }_{branch\_name=“Perryridge”}(loan\bowtie borrower))$

loan表和borrower表只有loan_number列名是相同的，那么这个操作完全可以用连接natural join操作代替。

6.Find the names of all customers who have a loan at the Perryridge branch but do not have an account at any branch of the bank.

${\Pi }_{customer\_name}({\sigma }_{branch\_name=“Perryridge”}({\sigma }_{borrower.loan\_number=loan.loan\_number}(borrower\times loan)))–{\Pi }_{customer\_name}(depositor)$

最外层：
选择 the names of all customers列名，用${\Pi }_{customer\_name}$。

前半部分同5，后半部分but do not have an account at any branch of the bank表示在任何银行中都没有存款的。

7.Find the largest account balance

Query1

${\Pi }_{balance}(account)-{\Pi }_{A1.balance}({\sigma }_{A1.balance<A2.balance}({\rho }_{A1}(account)\times {\rho }_{A2}(account)))$

前半部分表示所有账户的存款余额集，后半部分表示选出存款余额小于别人的账户余额，即不是最大的存款余额。

题意是找出最大的存款余额，那么就用所有账户的存款余额集和不是最大的存款余额集做差集运算。

(SELECT DISTINCT balance
FROM  account)
EXCEPT
(SELECT  A1.balance
FROM  account AS A1, account AS A2
WHERE A1.balance < A2.balance);

Query2

Q2 (Aggregate Functions)

SELECT DISTINCT balance
FROM  account
WHERE balance = (SELECT max(balance) FROM account);

Query3

Q3 (Set Comparison)

SELECT DISTINCT balance
FROM  account
WHERE balance >= ALL (SELECT balance FROM account );

Query4

Q4 (EXISTS predicate)

SELECT DISTINCT A1.balance
FROM  account A1
WHERE  NOT EXISTS (
SELECT  * 
FROM  account A2
WHERE  A1.balance < A2.balance);

8.Find all customers who have an account from at least the “Downtown and the Uptown” branches.

Query1

${\Pi }_{custome{r}_{n}ame}({\sigma }_{branch\_name=“Downtown”}(depositor\bowtie account))\cap {\Pi }_{customer\_name}({\sigma }_{branch\_name=“Uptown”}(depositor\bowtie account))$

同上，branch_name和customer_name需要两个表的连接，然后做交集。

Query2

Π c u s t o m e r _ n a m e , b r a n c h _ n a m e ( d e p o s i t o r ⋈ a c c o u n t ) ÷ ρ t e m p ( b r a n c h _ n a m e ) ( { ( “ D o w n t o w n ” ) , ( “ U p t o w n ” ) } ) \Pi_{customer\_name, branch\_name} (depositor \bowtie account) \div \rho _{temp}(branch\_name) (\{ (“Downtown” ), (“Uptown” )\}) Πcustomer_name,branch_name​(depositor⋈account)÷ρtemp​(branch_name)({(“Downtown”),(“Uptown”)})

Note that Query 2 uses a constant relation.
???不了解

9.Find all customers who have an account at all branches located in Brooklyn city.

${\Pi }_{customer\_name,branch\_name}(depositor\times account)÷{\Pi }_{branch\_name}({\sigma }_{branch\_city=“Brooklyn”}(branch))$

有关customer_name和branch_city，那么就得得到depositor和account的笛卡尔积。
再做division除法运算，得到只在Brooklyn城市的银行。

select distinct S.customer_name
from depositor as S
where not exists (
(select branch_name from branch where branch_city = 'Brooklyn')
except
(select R.branch_name 
from depositor as T, account as R 
where T.account_number = R.account_number and S.customer_name = T.customer_name)
);

10.Find all customers who have both an account and a loan at the Perryridge branch

Query1

select distinct customer_name
from borrower, loan
where borrower.loan_number = loan.loan_number 
and branch_name = 'Perryridge'
and  (branch_name, customer_name ) in
(select branch_name, customer_name
from depositor, account
where depositor.account_number = account.account_number );

Query2

SELECT DISTINCT depositor.customer_name
FROM  depositor , account, borrower, loan
WHERE depositor.account_number = account.account_number
AND borrower.loan_number = loan.loan_number
AND depositor.customer_name = borrower.customer_name 
AND account.branch_name = 'Perryridge'
AND loan.branch_name = 'Perryridge' ;

11.Find all branches that have greater assets than some branch located in Brooklyn.

Query1

select distinct  T.branch_name
from branch as T, branch as S
where  T.assets > S.assets and S.branch_city = 'Brooklyn';

Query2

select branch_name
from branch
where assets > some
(select assets from branch where branch_city = 'Brooklyn');

12.Find the names of all branches that have greater assets than all branches located in Brooklyn.

Query

select branch_name
from branch
where assets > all
(select assets from branch where branch_city = 'Brooklyn');

13.Find the branch that has the highest average balance.

Query1

SELECT branch_name
FROM  account
GROUP BY branch_name
HAVING avg(balance)>=ALL
(SELECT avg(balance) FROM account GROUP BY branch_name);

14.Find the customers name who have at least one deposit of a balance greater than $700.

SELECT DISTINCT customer_name
FROM  depositor
WHERE EXISTS (
	SELECT  *
	FROM  account
	WHERE depositor.account_number = account.account_number AND balance > 700 );

15.Find all customers who have at most one account at the Perryridge branch.

Q1 (using UNIQUE)

select T.customer_name
from depositor as T
where unique (
select R.customer_name
from account, depositor as R
where T.customer_name = R.customer_name 
and R.account_number = account.account_number
and	account.branch_name = 'Perryridge' );

16.Find all customers who have at least two accounts at the Perryridge branch.

Query1

Q1 (using UNIQUE)

SELECT DISTINCT D1.customer_name
FROM depositor AS D1
WHERE NOT UNIQUE (
SELECT D2.customer_name
FROM account AS A, depositor AS D2
WHERE A.account_number = D2.account_number
AND D1.customer_name = D2.customer_name
AND  A.branch_name = 'Perryridge') ;

Query2

Q2 (using GROUP BY）

SELECT customer_name
FROM  depositor AS D, account AS A
WHERE D.account_number = A.account_number 
GROUP BY customer_name
HAVING  count(*) > 2 ;

16.Find all accounts with the maximum balance

(using With）

with max_balance(value) as
	(select max(balance)
	from account)
select account_number
from account, max_balance
where account.balance = max_balance.value

17.Find all branches where the total account deposit is greater than the average of the total account deposits at all branches.

(using With）

with
branch_total (branch_name, value) as
	(select branch_name, sum(balance)
	from account
	group by branch_name),
branch_total_avg (value) as
	(select avg(value)
	from branch_total)
select branch_name
from branch_total, branch_total_avg
where branch_total.value >= branch_total_avg.value

二、数据库操作的习题（增、删、改）

1.增insertion

（1）Insert information in the database specifying that Smith has $1200 in account A-973 at the Perryridge branch.

a c c o u n t ← a c c o u n t { ( “ P e r r y r i d g e ” , A − 973 , 1200 ) } account ← account \{(“Perryridge”, A-973, 1200)\} account←account{(“Perryridge”,A−973,1200)}
d e p o s i t o r ← d e p o s i t o r { ( “ S m i t h ” , A − 973 ) } depositor← depositor \{(“Smith”, A-973)\} depositor←depositor{(“Smith”,A−973)}

（2）Provide as a gift for all loan customers in the Perryridge branch, a $200 savings account. Let the loan number serve as the account number for the new savings account.

$r1\leftarrow ({\sigma }_{branch\_name=“Perryridge”}(borrower\bowtie loan))$
$account\leftarrow account\cup {\Pi }_{branch\_name,loan\_number,200}(r1)$
$depositor\leftarrow depositor\cup {\Pi }_{customer\_name,loan\_number}(r1)$

题意：给有贷款的客户开一个有存款200的新账户。
PS:${\Pi }_{200}(r1)$表示一列全是200，对应amount列的值全是200.

insert into account
 	select loan_number, branch_name,  200
 	from loan
 	where branch_name = 'Perryridge'
insert into depositor
	select customer_name, loan_number
	from loan, borrower
	where branch_name = 'Perryridge'
		and loan.account_number = borrower.account_number;

2.删deletion

（1）Delete all account records in the Perryridge branch.

$account\leftarrow account–{\sigma }_{branch\_name=“Perryridge”}(account)$

PS： account records 只删除account表中的数据

delete from account
where branch_name = 'Perryridge'

（2）Delete all loan records with amount in the range of 0 to 50

$loan\leftarrow loan–{\sigma }_{amount\ge 0andamount\le 50}(loan)$

PS： loan records 只删除loan表

（3）Delete all accounts at branches located in Needham.

$r1\leftarrow {\sigma }_{branch\_city=“Needham”}(account\bowtie branch)$
$r2\leftarrow {\Pi }_{branch\_name,account\_number,balance}(r1)$
$r3\leftarrow {\sigma }_{customer\_name,account\_number}(r2\bowtie depositor)$
$account\leftarrow account–r2$
$depositor\leftarrow depositor–r3$

要删除accounts，那么删除account表、depositor表
注意 ：account表中没有branch_city属性。

只删除account表中的演示

delete 
from account
where branch_name in 
	(select branch_name
	from branch
	where branch_city = 'Needham'
	)

（4）Delete the record of all accounts with balances below the average at the bank.

delete from account
where balance < (
	select avg (balance)
	from account )

3.改updating

（1）Make interest payments by increasing all balances by 5 percent.

$account\leftarrow {\Pi }_{account\_number,branch\_name,balance\ast 1.05}(account)$

（2）Pay all accounts with balances over $10,000 6 percent interest and pay all others 5 percent.

$account\leftarrow {\Pi }_{account\_number,branch\_name,balance\ast 1.06}({\sigma }_{BAL>10000}(account))\cup {\Pi }_{account\_number,branch\_name,balance\ast 1.05}({\sigma }_{BAL\le 10000}(account))$

Query1：Just use update

update account
set balance = balance*1.06
where balance > 10000;

update account
set balance = balance*1.05
where balance <= 10000

PS：两个update顺序是有固定顺序的。

Query 2：use update and case

update account
set balance =  case
					when balance <= 10000 then balance*1.05
					else balance*1.06
			   end