


drop sequence clseq;
create sequence clseq nocache
	increment by 1
	start with 1 
	minvalue 1
	maxvalue 100;


drop table Client;
create table Client(
num number primary key,
name varchar2(13) not null,
age number not null,
company varchar2(22) not null,
bank varchar2(22) not null
);


drop sequence fiseq;
create sequence fiseq nocache
	increment by 1
	start with 1 
	minvalue 1
	maxvalue 100;



drop table Finance;
create table Finance(
num number primary key,
name varchar2(13) not null,
salary number  default 0,
deposit number default 0,
rating varchar2(21) default 'NORMAL'
);




insert into Client values (clseq.nextval,'조용필',66,'YPC','국민');
insert into Client values (clseq.nextval,'김구라',46,'라인','신한');
insert into Client values (clseq.nextval,'빌 게이츠',60,'microSW','BAC');
insert into Client values (clseq.nextval,'장근석',29,'트리제이','우리');
insert into Client values (clseq.nextval,'장윤정',36,'코엔스타','국민');
insert into Client values (clseq.nextval,'유인영',32,'플라이업','신한');
insert into Client values (clseq.nextval,'신민아',32,'마더컴퍼니','우리');
insert into Client values (clseq.nextval,'소지섭',38,'51K','하나');
insert into Client values (clseq.nextval,'유승호',23,'산','국민');
insert into Client values (clseq.nextval,'유아인',30,'UAA','우리');
insert into Client values (clseq.nextval,'박보영',26,'피데스스타','신한');
insert into Client values (clseq.nextval,'김명민',36,'엠엠','농협');
insert into Client values (clseq.nextval,'만 수르',46,'IPIC','만수르');
insert into Client values (clseq.nextval,'박효신',35,'젤리피쉬','국민');
insert into Client values (clseq.nextval,'신세경',27,'나무엑터스','하나');





insert into Finance (num,name,salary,deposit) values (fiseq.nextval,'조용필',40,800);
insert into Finance values (fiseq.nextval,'김구라',80,-1500,'신용불량');
insert into Finance values (fiseq.nextval,'빌 게이츠',12000,1270000,'SVIP');
insert into Finance values (fiseq.nextval,'장근석',300,8000,'VIP');
insert into Finance (num,name,salary,deposit) values (fiseq.nextval,'장윤정',15,300);
insert into Finance (num,name,salary,deposit) values (fiseq.nextval,'유인영',50,1000);
insert into Finance values (fiseq.nextval,'신민아',90,5000,'VIP');
insert into Finance values (fiseq.nextval,'소지섭',100,6000,'VIP');
insert into Finance (num,name,salary,deposit) values (fiseq.nextval,'유승호',40,2000);
insert into Finance values (fiseq.nextval,'유아인',110,4950,'준VIP');
insert into Finance (num,name,salary,deposit) values (fiseq.nextval,'박보영',50,11);
insert into Finance values (fiseq.nextval,'김명민',75,-300,'신용불량');
insert into Finance values (fiseq.nextval,'만 수르',9000,340000,'SVIP');
insert into Finance values (fiseq.nextval,'박효신',40,-700,'신용불량');
insert into Finance (num,name,salary,deposit) values (fiseq.nextval,'신세경',60,600);




commit;

col name for a10
col company for a14
col bank for a10
col rating for a16
col rank for 99
col age for 99
col rating for a8

select * from client;
select * from finance;




< 금융 정보 조회를 시작 하겠습니다 > 


]======메뉴 선택하기======[
1.모든 금융정보 조회
2.금융 정보 조건 검색
3.범위 조건 검색
4.금융 정보 삭제
5.금융 정보 수정
6.금융 정보 추가
7.금융 정보 정렬
8.금융 프로그램 종료
====================[>





1.금융정보 전체 조회 (full outer join)


select c.num,c.name, c.age, c.company,c.bank, f.salary, f.deposit, f.rating 
from client c full outer join finance f 
on c.name=f.name




2.금융정보 조건 검색



select * from (select  c.num, c.name,c.age,c.company,c.bank,f.salary,f.deposit,f.rating
from client c,finance f where c.name = f.name) where "+col+" like upper( ? ) ";



3.범위 조건 검색


select num,name,age,company,bank,salary,deposit,rating from 
(select rownum rank,num,name,age,company,bank,salary,deposit,rating from
(select c.num, c.name,c.age,c.company,c.bank,f.salary,f.deposit,f.rating from client c,finance f
where c.num =f.num order by deposit desc)) where rank between "+start+"and "+end+" ";



4.금융 정보 삭제


delete from finance f where f.name = (select c.name from client cwhere f.name = c.name	and c.num= ?)


5.금융 정보 수정

고객 정보 용 : update client set name=?,age=?,company=?,bank=? where name like '%?%' ";
금융 정보 용 : update finance set name=?,salary=?,deposit=? where name like '%?%' ";




6.금융 정보 추가



고객 : insert into client values (clseq.nextval,?,?,?,?);
금융 : insert into client values (clseq.nextval,?,?,?,?);



7.금융 정보 정렬


select c.num, c.name,c.age,c.company,c.bank,f.salary,f.deposit,f.rating from client c,finance f 
where c.num =f.num order by "+find_col+" "+do_sort+" ";