/*
  課本 3.1 (p.83) banking enterprise 的E-R diagram
*/

CREATE TABLE branch
( branch_name VARCHAR2(15) NOT NULL
, branch_city VARCHAR2(15) NOT NULL
, assets NUMBER  NOT NULL
);

CREATE TABLE loan
( loan_number VARCHAR2(4) NOT NULL
, branch_name VARCHAR2(15) NOT NULL
, amount NUMBER NOT NULL
);

CREATE TABLE borrower
( customer_name VARCHAR2(15) NOT NULL
, loan_number VARCHAR2(4) NOT NULL
);

CREATE TABLE customer
( customer_name VARCHAR2(15) NOT NULL
, customer_street VARCHAR2(15) NOT NULL
, customer_city VARCHAR2(15) NOT NULL
);

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('Williams','L-17');

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');

/*
   課本 (p.89) 查詢分行名稱為Perryridge 且總數超過1200 的資料
   select * from loan where branch_name='Perryridge' AND amount>1200;
*/

/*
   課本 (p.90) 查詢借出號碼與總量
   select loan_number,amount from loan;
*/


更新資料
update customer Set customer_city='Taipei' where customer_city='Harrison';


刪除資料
delete customer where customer_city='Rye';


刪除customer表格
drop table customer;