文章目录
- 一、查询数据习题(只使用相关关系代数指令)
- 1.Find all loans of over $1200
- 2.Find the loan number for each loan of an amount greater than $1200
- 3.Find the names of all customers who have a loan, an account, or both, from the bank
- 4.Find the names of all customers who have a loan and an account at bank.
- 5.Find the names of all customers who have a loan at the Perryridge branch.
- 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.
- 7.Find the largest account balance
- 8.Find all customers who have an account from at least the “Downtown and the Uptown” branches.
- 9.Find all customers who have an account at all branches located in Brooklyn city.
- 10.Find all customers who have both an account and a loan at the Perryridge branch
- 11.Find all branches that have greater assets than some branch located in Brooklyn.
- 12.Find the names of all branches that have greater assets than all branches located in Brooklyn.
- 13.Find the branch that has the highest average balance.
- 14.Find the customers name who have at least one deposit of a balance greater than $700.
- 15.Find all customers who have at most one account at the Perryridge branch.
- 16.Find all customers who have at least two accounts at the Perryridge branch.
- 16.Find all accounts with the maximum balance
- 17.Find all branches where the total account deposit is greater than the average of the total account deposits at all branches.
- 二、数据库操作的习题(增、删、改)
一、查询数据习题(只使用相关关系代数指令)
1.Find all loans of over $1200
σ a m o u n t > 1200 ( l o a n ) \sigma_{amount>1200}(loan) σamount>1200(loan)
选取贷款额超过1200的行,用 σ \sigma σ
2.Find the loan number for each loan of an amount greater than $1200
Π l o a n _ n u m b e r ( σ a m o u n t > 1200 ( l o a n ) ) \Pi_{loan\_number}(\sigma_{amount>1200}(loan)) Πloan_number(σ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
Π c u s t o m e r _ n a m e ( b o r r o w e r ) ∪ Π c u s t o m e r _ n a m e ( d e p o s i t o r ) \Pi_{customer\_name~}(borrower) \cup \Pi_{customer\_name}(depositor) Πcustomer_name (borrower)∪Πcustomer_name(depositor)
选择 the names of all customers列名, Π c u s t o m e r _ n a m e \Pi_{customer\_name} Π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.
Π c u s t o m e r _ n a m e ( b o r r o w e r ) ∩ Π c u s t o m e r _ n a m e ( d e p o s i t o r ) \Pi_{customer\_name} (borrower) \cap\Pi_{customer\_name} (depositor) Πcustomer_name(borrower)∩Πcustomer_name(depositor)
同上
5.Find the names of all customers who have a loan at the Perryridge branch.
Query1
Π c u s t o m e r _ n a m e ( σ b r a n c h _ n a m e = “ P e r r y r i d g e ” ( σ b o r r o w e r . l o a n _ n u m b e r = l o a n . l o a n _ n u m b e r ( b o r r o w e r × l o a n ) ) ) \Pi_{customer\_name}(\sigma_{branch\_name=“Perryridge”}(\sigma_{borrower.loan\_number = loan.loan\_number}(borrower \times loan))) Πcustomer_name(σbranch_name=“Perryridge”(σborrower.loan_number=loan.loan_number(borrower×loan)))
最外层:
选择 the names of all customers列名,用
Π
c
u
s
t
o
m
e
r
_
n
a
m
e
\Pi_{customer\_name}
Πcustomer_name。
内层:
列名要求一个是cutomer_name,另一个是branch_name,前者在borrower表中,后者在loan表中,所以我们才用两个表的笛卡尔积:
b
o
r
r
o
w
e
r
×
l
o
a
n
borrower \times loan
borrower×loan
两个表中贷款人的loan_number应该一致,所以选取一致的行 σ b o r r o w e r . l o a n _ n u m b e r = l o a n . l o a n _ n u m b e r \sigma_{borrower.loan\_number = loan.loan\_number} σborrower.loan_number=loan.loan_number
选择银行为the Perryridge branch,即 σ b r a n c h _ n a m e = “ P e r r y r i d g e ” \sigma_{branch\_name=“Perryridge”} σbranch_name=“Perryridge”
Query2
Π c u s t o m e r _ n a m e ( σ l o a n . l o a n _ n u m b e r = b o r r o w e r . l o a n _ n u m b e r ( ( σ b r a n c h _ n a m e = “ P e r r y r i d g e ” ( l o a n ) ) × b o r r o w e r ) ) \Pi_{customer\_name}(\sigma_{loan.loan\_number = borrower.loan\_number} ((\sigma_{branch\_name = “Perryridge”} (loan)) \times borrower)) Πcustomer_name(σloan.loan_number=borrower.loan_number((σbranch_name=“Perryridge”(loan))×borrower))
同上的区别是先筛选出属于the Perryridge branch,然后再进行匹配loan_number.
Query3
Π c u s t o m e r n a m e ( σ b r a n c h _ n a m e = “ P e r r y r i d g e ” ( l o a n ⋈ b o r r o w e r ) ) \Pi_{customer_name }(\sigma_{branch\_name=“Perryridge”} (loan \bowtie borrower )) Πcustomername(σbranch_name=“Perryridge”(loan⋈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.
Π c u s t o m e r _ n a m e ( σ b r a n c h _ n a m e = “ P e r r y r i d g e ” ( σ b o r r o w e r . l o a n _ n u m b e r = l o a n . l o a n _ n u m b e r ( b o r r o w e r × l o a n ) ) ) – Π c u s t o m e r _ n a m e ( d e p o s i t o r ) \Pi_{customer\_name} (\sigma_{branch\_name = “Perryridge”} (\sigma_{borrower.loan\_number = loan.loan\_number}(borrower \times loan))) – \Pi_{customer\_name}(depositor) Πcustomer_name(σbranch_name=“Perryridge”(σborrower.loan_number=loan.loan_number(borrower×loan)))–Πcustomer_name(depositor)
最外层:
选择 the names of all customers列名,用
Π
c
u
s
t
o
m
e
r
_
n
a
m
e
\Pi_{customer\_name}
Πcustomer_name。
前半部分同5,后半部分but do not have an account at any branch of the bank表示在任何银行中都没有存款的。
7.Find the largest account balance
Query1
Π b a l a n c e ( a c c o u n t ) − Π A 1. b a l a n c e ( σ A 1. b a l a n c e < A 2. b a l a n c e ( ρ A 1 ( a c c o u n t ) × ρ A 2 ( a c c o u n t ) ) ) \Pi_{balance}(account) - \Pi_{A1.balance}(\sigma_{A1.balance < A2.balance }(\rho_{A1} (account) \times \rho_{A2} (account))) Πbalance(account)−ΠA1.balance(σA1.balance<A2.balance(ρA1(account)×ρ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
Π c u s t o m e r n a m e ( σ b r a n c h _ n a m e = “ D o w n t o w n ” ( d e p o s i t o r ⋈ a c c o u n t ) ) ∩ Π c u s t o m e r _ n a m e ( σ b r a n c h _ n a m e = “ U p t o w n ” ( d e p o s i t o r ⋈ a c c o u n t ) ) \Pi_{customer_name }(\sigma_{branch\_name = “Downtown”} (depositor \bowtie account )) \cap \Pi_{customer\_name} (\sigma_{branch\_name = “Uptown”} (depositor \bowtie account)) Πcustomername(σbranch_name=“Downtown”(depositor⋈account))∩Πcustomer_name(σbranch_name=“Uptown”(depositor⋈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.
Π 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 ) ÷ Π b r a n c h _ n a m e ( σ b r a n c h _ c i t y = “ B r o o k l y n ” ( b r a n c h ) ) \Pi_{customer\_name, branch\_name }(depositor \times account) \div \Pi_{branch\_name} (\sigma_{branch\_city = “Brooklyn”} (branch)) Πcustomer_name,branch_name(depositor×account)÷Πbranch_name(σ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.
r
1
←
(
σ
b
r
a
n
c
h
_
n
a
m
e
=
“
P
e
r
r
y
r
i
d
g
e
”
(
b
o
r
r
o
w
e
r
⋈
l
o
a
n
)
)
r1 ← (\sigma_{branch\_name = “Perryridge” }(borrower \bowtie loan))
r1←(σbranch_name=“Perryridge”(borrower⋈loan))
a
c
c
o
u
n
t
←
a
c
c
o
u
n
t
∪
Π
b
r
a
n
c
h
_
n
a
m
e
,
l
o
a
n
_
n
u
m
b
e
r
,
200
(
r
1
)
account ← account \cup \Pi_{branch\_name, loan\_number,200 }(r1)
account←account∪Πbranch_name,loan_number,200(r1)
d
e
p
o
s
i
t
o
r
←
d
e
p
o
s
i
t
o
r
∪
Π
c
u
s
t
o
m
e
r
_
n
a
m
e
,
l
o
a
n
_
n
u
m
b
e
r
(
r
1
)
depositor ← depositor \cup \Pi_{customer\_name, loan\_number} (r1)
depositor←depositor∪Πcustomer_name,loan_number(r1)
题意:给有贷款的客户开一个有存款200的新账户。
PS:
Π
200
(
r
1
)
\Pi_{200}(r1)
Π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.
a c c o u n t ← a c c o u n t – σ b r a n c h _ n a m e = “ P e r r y r i d g e ” ( a c c o u n t ) account ← account – \sigma_{ branch\_name = “Perryridge” }(account ) account←account–σ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
l o a n ← l o a n – σ a m o u n t ≥ 0 a n d a m o u n t ≤ 50 ( l o a n ) loan ←loan – \sigma_{amount \geq 0 \ and \ amount \leq 50} (loan) loan←loan–σamount≥0 and amount≤50(loan)
PS: loan records 只删除loan表
(3)Delete all accounts at branches located in Needham.
r
1
←
σ
b
r
a
n
c
h
_
c
i
t
y
=
“
N
e
e
d
h
a
m
”
(
a
c
c
o
u
n
t
⋈
b
r
a
n
c
h
)
r1←\sigma_{branch\_city = “Needham” }(account \bowtie branch )
r1←σbranch_city=“Needham”(account⋈branch)
r
2
←
Π
b
r
a
n
c
h
_
n
a
m
e
,
a
c
c
o
u
n
t
_
n
u
m
b
e
r
,
b
a
l
a
n
c
e
(
r
1
)
r2←\Pi_{branch\_name, account\_number, balance }(r1)
r2←Πbranch_name,account_number,balance(r1)
r
3
←
σ
c
u
s
t
o
m
e
r
_
n
a
m
e
,
a
c
c
o
u
n
t
_
n
u
m
b
e
r
(
r
2
⋈
d
e
p
o
s
i
t
o
r
)
r3 ←\sigma_{customer\_name, account\_number }(r2 \bowtie depositor)
r3←σcustomer_name,account_number(r2⋈depositor)
a
c
c
o
u
n
t
←
a
c
c
o
u
n
t
–
r
2
account ←account – r2
account←account–r2
d
e
p
o
s
i
t
o
r
←
d
e
p
o
s
i
t
o
r
–
r
3
depositor ← depositor – r3
depositor←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.
a c c o u n t ← Π a c c o u n t _ n u m b e r , b r a n c h _ n a m e , b a l a n c e ∗ 1.05 ( a c c o u n t ) account ← \Pi_{account\_number, branch\_name, balance * 1.05} (account) account←Πaccount_number,branch_name,balance∗1.05(account)
(2)Pay all accounts with balances over $10,000 6 percent interest and pay all others 5 percent.
a c c o u n t ← Π a c c o u n t _ n u m b e r , b r a n c h _ n a m e , b a l a n c e ∗ 1.06 ( σ B A L > 10000 ( a c c o u n t ) ) ∪ Π a c c o u n t _ n u m b e r , b r a n c h _ n a m e , b a l a n c e ∗ 1.05 ( σ B A L ≤ 10000 ( a c c o u n t ) ) account ← \Pi_{account\_number, branch\_name, balance * 1.06 }(\sigma_{ BAL > 10000 }(account )) \cup \Pi_{account\_number, branch\_name, balance * 1.05} (\sigma_{BAL \leq 10000 }(account)) account←Πaccount_number,branch_name,balance∗1.06(σBAL>10000(account))∪Πaccount_number,branch_name,balance∗1.05(σBAL≤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