Database 复习

周末复习了一下数据库的相关知识，还是选IBM的Derby数据库来实践。看的是数据库概念那本书的ppt。
把书上的例子的表和学习笔记放上来:-)

1.数据库表，Test.sql文件，可以在Eclipse derby插件下直接运行。然后就可以在这些数据上做实验了。

connect'jdbc:derby://localhost:1527/D:/usr/local/Derby_10/demo/databases/testdb;create=true;';

droptablecustomer;
droptablebranch;
droptableaccount;
droptabledepositor;
droptableloan;
droptableborrower;

createtablecustomer
(customer_namevarchar(20),
customer_streetvarchar(30),
customer_cityvarchar(30),
primarykey(customer_name));

createtablebranch
(branch_namevarchar(15),
branch_cityvarchar(30),
assetsinteger,
primarykey(branch_name));

createtableaccount
(account_numbervarchar(10),
branch_namevarchar(15),
balanceinteger,
primarykey(account_number));

createtabledepositor
(customer_namevarchar(20),
account_numbervarchar(10),
primarykey(customer_name,account_number));

createtableloan
(loan_numbervarchar(10),
branch_namevarchar(15),
amountinteger,
primarykey(loan_number));

createtableborrower
(customer_namevarchar(20),
loan_numbervarchar(10),
primarykey(customer_name,loan_number));

insertintocustomervalues('Adams','Spring','Pittsfield');
insertintocustomervalues('Brooks','Senator','Brooklyn');
insertintocustomervalues('Curry','North','Rye');
insertintocustomervalues('Glenn','SandHill','Woodside');
insertintocustomervalues('Green','Walnut','Stamford');
insertintocustomervalues('Hayes','Main','Harrison');
insertintocustomervalues('Johnson','Alma','PaloAlto');
insertintocustomervalues('Jones','Main','Harrison');
insertintocustomervalues('Lindsay','Park','Pittsfield');
insertintocustomervalues('Smith','North','Rye');
insertintocustomervalues('Turner','Putnam','Stamford');
insertintocustomervalues('Williams','Nassau','Princeton');

insertintodepositorvalues('Hayes','A-102');
insertintodepositorvalues('Johnson','A-101');
insertintodepositorvalues('Johnson','A-201');
insertintodepositorvalues('Jones','A-217');
insertintodepositorvalues('Lindsay','A-222');
insertintodepositorvalues('Smith','A215');
insertintodepositorvalues('Turner','A-305');

insertintoaccountvalues('A-101','Downtown',500);
insertintoaccountvalues('A-102','Perryridge',400);
insertintoaccountvalues('A-201','Brighton',900);
insertintoaccountvalues('A-215','Mianus',700);
insertintoaccountvalues('A-217','Brighton',750);
insertintoaccountvalues('A-222','Redwood',700);
insertintoaccountvalues('A-305','RoundHill',350);

insertintobranchvalues('Brighton','Brooklyn',7100000);
insertintobranchvalues('Downtown','Brooklyn',9000000);
insertintobranchvalues('Mianus','Horseneck',400000);
insertintobranchvalues('NorthTown','Rye',3700000);
insertintobranchvalues('Perryridge','Horseneck',1700000);
insertintobranchvalues('Pownal','Bennington',300000);
insertintobranchvalues('Redwood','PaloAlto',2100000);
insertintobranchvalues('RoundHill','Horseneck',8000000);

insertintoloanvalues('L-11','RoundHill',900);
insertintoloanvalues('L-14','Downtown',1500);
insertintoloanvalues('L-15','Perryridge',1500);
insertintoloanvalues('L-16','Perryridge',1300);
insertintoloanvalues('L-17','Downtown',1000);
insertintoloanvalues('L-23','Redwood',2000);
insertintoloanvalues('L-93','Mianus',500);


insertintoborrowervalues('Adams','L-16');
insertintoborrowervalues('Curry','L-93');
insertintoborrowervalues('Hayes','L-15');
insertintoborrowervalues('Jackson','L-14');
insertintoborrowervalues('Jones','L-17');
insertintoborrowervalues('Smith','L-11');
insertintoborrowervalues('Smith','L-23');
insertintoborrowervalues('Wiliams','L-17');

select*fromcustomer;
select*fromdepositor;
select*fromaccount;
select*frombranch;
select*fromloan;
select*fromborrower;

2.学习笔记
examples:

###########################################
# Cartesian product
###########################################

1.找出所有有loan的customers.
∏customer,loan_number,amount(loan × borrower)

select customer_name,borrower.loan_number, amount
from borrower,loan
where borrower.loan_number=loan.loan_number;

###########################################

2.打印出在银行Perryridge拥有loan的所有customer_name, loan_number, amount.
○branch-name="Perryridge"(∏customer,loan_number,amount(loan × borrower))

select customer_name,borrower.loan_number, amount
from borrower,loan
where borrower.loan_number=loan.loan_number and branch_name='Perryridge';

###########################################
# Set Operations
###########################################

3.找出有loan或account或都有的所有customers.
ij> (select customer_name from depositor)
union
(select customer_name from borrower);
CUSTOMER_NAME
--------------------
Adams
Curry
Hayes
Jackson
Johnson
Jones
Lindsay
Smith
Turner
Wiliams

10 rows selected

###########################################

4.找出所有既有loan也有account的所有customers.
ij> (select customer_name from depositor)
intersect
(select customer_name from borrower);
CUSTOMER_NAME
--------------------
Hayes
Jones
Smith

3 rows selected

###########################################

5.找出所有有account但是没有loan的所有customers.
ij> (select customer_name from depositor)
except
(select customer_name from borrower);
CUSTOMER_NAME
--------------------
Johnson
Lindsay
Turner

###########################################
# Aggregate Functions - avg
###########################################

6.找出在Perryridge的account平均值.
ij> select avg(balance)
from account
where branch_name = 'Perryridge';
1
-----------
400

1 row selected

###########################################
# Aggregate Functions - group by
###########################################

7.找出每个银行的depositor个数.
ij> select branch_name, count(distinct customer_name)
from depositor, account
where depositor.account_number = account.account_number
group by branch_name;
BRANCH_NAME |2
---------------------------
Brighton |2
Downtown |1
Perryridge |1
Redwood |1
Round Hill |1

5 rows selected

###########################################
# Aggregate Functions - having
###########################################

8.找出account平均值大于200的银行名称
ij> select branch_name, avg(balance)
from account
group by branch_name
having avg(balance) > 200;
BRANCH_NAME |2
---------------------------
Brighton |825
Downtown |500
Mianus |700
Perryridge |400
Redwood |700
Round Hill |350

6 rows selected

###########################################
# Nested Subqueries
###########################################

9.找出所有既有account并且有loan的customers.
ij> select distinct customer_name
from borrower
where customer_name in (select customer_name from depositor);
CUSTOMER_NAME
--------------------
Hayes
Jones
Smith

3 rows selected

###########################################

10.找出所有有account但是无loan的customers.
ij> select distinct customer_name
from borrower
where customer_name not in (select customer_name from depositor);
CUSTOMER_NAME
--------------------
Adams
Curry
Jackson
Wiliams

4 rows selected

###########################################
# inner join
###########################################

11.inner join 2 tables.
ij> select * from loan inner join borrower
on loan.loan_number = borrower.loan_number;
LOAN_NUMB&|BRANCH_NAME |AMOUNT |CUSTOMER_NAME |LOAN_NUMB&
----------------------------------------------------------------------
L-16 |Perryridge |1300 |Adams |L-16
L-93 |Mianus |500 |Curry |L-93
L-15 |Perryridge |1500 |Hayes |L-15
L-14 |Downtown |1500 |Jackson |L-14
L-17 |Downtown |1000 |Jones |L-17
L-11 |Round Hill |900 |Smith |L-11
L-23 |Redwood |2000 |Smith |L-23
L-17 |Downtown |1000 |Wiliams |L-17

8 rows selected

###########################################

12.inner join 3 tables.
ij> select * from loan inner join borrower
on loan.loan_number = borrower.loan_number
inner join customer
on borrower.customer_name = customer.customer_name;
LOAN_NUMB&|BRANCH_NAME |AMOUNT |CUSTOMER_NAME |LOAN_NUMB&|CUSTOMER_NAME |CUSTOMER_STREET |CUSTOMER_CITY
---------------------------------------------------------------------------------------------------------------------------------------------------------
L-16 |Perryridge |1300 |Adams |L-16 |Adams |Spring |Pittsfield
L-93 |Mianus |500 |Curry |L-93 |Curry |North |Rye
L-15 |Perryridge |1500 |Hayes |L-15 |Hayes |Main |Harrison
L-17 |Downtown |1000 |Jones |L-17 |Jones |Main |Harrison
L-11 |Round Hill |900 |Smith |L-11 |Smith |North |Rye
L-23 |Redwood |2000 |Smith |L-23 |Smith |North |Rye

6 rows selected

###########################################

13.inner join and "order by" example.
ij> select * from loan inner join borrower
on loan.loan_number = borrower.loan_number
inner join customer
on borrower.customer_name = customer.customer_name
order by borrower.customer_name, borrower.loan_number;
LOAN_NUMB&|BRANCH_NAME |AMOUNT |CUSTOMER_NAME |LOAN_NUMB&|CUSTOMER_NAME |CUSTOMER_STREET |CUSTOMER_CITY
---------------------------------------------------------------------------------------------------------------------------------------------------------
L-16 |Perryridge |1300 |Adams |L-16 |Adams |Spring |Pittsfield
L-93 |Mianus |500 |Curry |L-93 |Curry |North |Rye
L-15 |Perryridge |1500 |Hayes |L-15 |Hayes |Main |Harrison
L-17 |Downtown |1000 |Jones |L-17 |Jones |Main |Harrison
L-11 |Round Hill |900 |Smith |L-11 |Smith |North |Rye
L-23 |Redwood |2000 |Smith |L-23 |Smith |North |Rye

6 rows selected
ij>