Database 复习

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

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

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

drop table customer;
drop table branch;
drop table account;
drop table depositor;
drop table loan;
drop table borrower;

create table customer
    (customer_name         varchar(20),
     customer_street     varchar(30),
     customer_city        varchar(30),
     primary key(customer_name));
     
create table branch
    (branch_name         varchar(15),
     branch_city        varchar(30),
     assets                integer,
     primary key(branch_name));

create table account
    (account_number        varchar(10),
     branch_name        varchar(15),
     balance            integer,
     primary key(account_number));

create table depositor
    (customer_name        varchar(20),
     account_number        varchar(10),
     primary key(customer_name, account_number));
     
create table loan
    (loan_number         varchar(10), 
     branch_name        varchar(15), 
     amount        integer, 
     primary key(loan_number));
    
create table borrower
    (customer_name        varchar(20), 
     loan_number        varchar(10), 
     primary key(customer_name, loan_number));
     
insert into customer values ('Adams','Spring','Pittsfield');
insert into customer values ('Brooks','Senator','Brooklyn');
insert into customer values ('Curry','North','Rye');
insert into customer values ('Glenn','Sand Hill','Woodside');
insert into customer values ('Green','Walnut','Stamford');
insert into customer values ('Hayes','Main','Harrison');
insert into customer values ('Johnson','Alma','Palo Alto');
insert into customer values ('Jones','Main','Harrison');
insert into customer values ('Lindsay','Park','Pittsfield');
insert into customer values ('Smith','North','Rye');
insert into customer values ('Turner','Putnam','Stamford');
insert into customer values ('Williams','Nassau','Princeton');

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

insert into account values ('A-101','Downtown',500);
insert into account values ('A-102','Perryridge',400);
insert into account values ('A-201','Brighton',900);
insert into account values ('A-215','Mianus',700);
insert into account values ('A-217','Brighton',750);
insert into account values ('A-222','Redwood',700);
insert into account values ('A-305','Round Hill',350);
     
insert into branch values ('Brighton','Brooklyn',7100000);
insert into branch values ('Downtown','Brooklyn',9000000);
insert into branch values ('Mianus','Horseneck',400000);
insert into branch values ('North Town','Rye',3700000);
insert into branch values ('Perryridge','Horseneck',1700000);
insert into branch values ('Pownal','Bennington',300000);
insert into branch values ('Redwood','Palo Alto',2100000);
insert into branch values ('Round Hill','Horseneck',8000000);

insert into loan values ('L-11','Round Hill',900);
insert into loan values ('L-14','Downtown',1500);
insert into loan values ('L-15','Perryridge',1500);
insert into loan values ('L-16','Perryridge',1300);
insert into loan values ('L-17','Downtown',1000);
insert into loan values ('L-23','Redwood',2000);
insert into loan values ('L-93','Mianus',500);


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

select * from customer;
select * from depositor;
select * from account;
select * from branch;
select * from loan;
select * from borrower;

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>