CREATE TABLE customer_personal_info (
 customer_id varchar(5),
 customer_name varchar(30),
 date_of_birth DATE,
 Guardian_name varchar(30),
 address varchar(50),
 contact_no Bigint(10),
 mail_id varchar(30),
 gender char(1),
 maritial_status varchar(10),
 identification_doc_type varchar(20),
 id_doc_no varchar(20),
 citizenship varchar(10),
 primary key(customer_id)
 );
 
create table customer_reference_info(
customer_id varchar(5),
reference_acc_name varchar(20),
reference_acc_no bigint(16),
reference_acc_address varchar(50),
relation varchar(25),
primary key(customer_id)

);

create table bank_info
(
ifsc_code varchar(15),
bank_name varchar(25),
branch_name varchar(25),
primary key(ifsc_code)
);

create table account_info(
account_no bigint(16),
customer_id varchar(5),
account_type varchar(10),
registration_date date,
activation_date date,
ifsc_code varchar(10),
interest decimal(7,2),
initial_deposit bigint(10),
primary key (account_no),
foreign key(customer_id) references customer_personal_info(customer_id),
foreign key(ifsc_code) references bank_info(ifsc_code)
);

INSERT INTO bank_info(IFSC_CODE,BANK_NAME,BRANCH_NAME)VALUES('HDVL0012','HDFC','VALASARAVAKKAM');
INSERT INTO bank_info(IFSC_CODE,BANK_NAME,BRANCH_NAME) VALUES('SBITN0123','SBI','TNAGAR');
INSERT INTO bank_info(IFSC_CODE,BANK_NAME,BRANCH_NAME) VALUES('ICITN0232','ICICI','TNAGAR');
select * from bank_info;


INSERT  into customer_reference_info(CUSTOMER_ID,REFERENCE_ACC_NAME,REFERENCE_ACC_NO,REFERENCE_ACC_ADDRESS,RELATION) VALUES('C-001','RAM','0987654321122345','NO.11,BRIGRADE ROAD,BANGALORE','FRIEND');
INSERT INTO customer_reference_info(CUSTOMER_ID,REFERENCE_ACC_NAME,REFERENCE_ACC_NO,REFERENCE_ACC_ADDRESS,RELATION) VALUES('C-002','RAGHUL','0987654321122346','NO.21,CUNNGHAM ROAD,BANGALORE','FRIEND');

select   reference_acc_name from customer_reference_info;



INSERT INTO account_info(ACCOUNT_NO,CUSTOMER_ID,ACCOUNT_TYPE,REGISTRATION_DATE,ACTIVATION_DATE,IFSC_CODE,INTEREST, INITIAL_DEPOSIT) VALUES('1234567898765432','C-001','SAVINGS','2012-02-23','2012-02-28','HDVL0012',5,10000); 
INSERT INTO account_info(ACCOUNT_NO,CUSTOMER_ID,ACCOUNT_TYPE,REGISTRATION_DATE,ACTIVATION_DATE,IFSC_CODE,INTEREST, INITIAL_DEPOSIT) VALUES('1234567898765433','C-002','SALARY','2012-03-12','2012-03-17','SBITN0123',6,0 ); 

select * from account_info;

INSERT INTO  customer_personal_info(CUSTOMER_ID,CUSTOMER_NAME,DATE_OF_BIRTH,GUARDIAN_NAME,ADDRESS,CONTACT_NO,MAIL_ID,GENDER,MARITIAL_STATUS,IDENTIFICATION_DOC_TYPE,ID_DOC_NO,CITIZENSHIP) VALUES('C-001','JOHN','1984-05-03','PETER','NO.14,ST.MARKS ROAD,BANGALORE','9734526710','JOHN_123@gmail.com','M','SINGLE','PASSPORT','PASS123','INDIAN');
INSERT INTO  customer_personal_info(CUSTOMER_ID,CUSTOMER_NAME,DATE_OF_BIRTH,GUARDIAN_NAME,ADDRESS,CONTACT_NO,MAIL_ID,GENDER,MARITIAL_STATUS,IDENTIFICATION_DOC_TYPE,ID_DOC_NO,CITIZENSHIP) VALUES('C-002','JAMES','1984-08-06','GEORGE','NO.18, MG ROAD,BANGALORE','9237893481','JAMES_123@gmail.com','M','MARRIED','PASSPORT','PASS124','INDIAN');

select * from customer_personal_info;

select a.CUSTOMER_ID,a.account_type,a.account_no,b.BANK_NAME from account_info a  join bank_info b 
on a.ifsc_code=b.ifsc_code;

select a.customer_id,a.account_type,a.account_no from account_info a join bank_info b on 
a.ifsc_code=b.ifsc_code where b.bank_name='HDFC' and a.registration_date between '2012-01-12' and '2012-04-04';


select a.customer_id,c.customer_name,a.account_no,a.account_type,b.bank_name  from 
customer_personal_info c join account_info a on c.customer_id=a.customer_id join bank_info b 
 on a.ifsc_code=b.ifsc_code where c.customer_id>0;

select customer_id,customer_name,gender,maritial_status,
(customer_name|| '_'|| gender||'_'|| maritial_status) as UNIQUE_REF_STRING 
from customer_personal_info order by customer_id desc;

select account_no,customer_id,registration_date,initial_deposit from account_info where 
initial_deposit>=15000 and initial_deposit<=25000;


select customer_id,customer_name,date_of_birth,Guardian_name from customer_personal_info where
Guardian_name like 'j%';


select customer_id,account_no, substr(customer_id,3,3)||substr(account_no,13,16)
as PASSCODE from account_info;


select customer_id,customer_name,date_of_birth,maritial_status,
gender,Guardian_name,contact_no,MAIL_ID from customer_personal_info where gender='M' AND 
maritial_status='MARRIED';


select c.customer_id,c.customer_name,c.Guardian_name,r.reference_acc_name from 
customer_personal_info c join customer_reference_info r on 
c.customer_id=r.customer_id where r.relation='FRIEND';

select i.Customer_ID, i.Customer_Name, a.account_no, a.account_type, a.activation_date, b.bank_name
from customer_personal_info i join account_info a on(i.customer_id = a.customer_id)
join bank_info b on(a.ifsc_code = b.ifsc_code)
where a.activation_date = '2012-04-10';

select a.account_no, a.customer_id, c.customer_name, b.bank_name, b.branch_name, b.ifsc_code, c.citizenship, a.interest, a.initial_deposit from account_info a
join bank_info b on(a.ifsc_code = b.ifsc_code)
join customer_personal_info c on(a.customer_id = c.customer_id);

select a.customer_id, a.customer_name, a.date_of_birth, a.guardian_name, a.contact_no, a.mail_id,
b.reference_acc_name from customer_personal_info a join customer_reference_info b 
on(a.customer_id = b.customer_id) where a.identification_doc_type = 'passport';

select b.customer_id, a.customer_name, b.account_no, b.account_type, b.initial_deposit,
b.interest from customer_personal_info a join account_info b on(a.customer_id = b.customer_id)
where b.initial_deposit = (select max(initial_deposit) from account_info);

select b.customer_id, a.customer_name, b.account_no, b.account_type, b.interest,
c.bank_name,b.initial_deposit from account_info b join customer_personal_info a 
on(a.customer_id = b.customer_id) join bank_info c on(b.ifsc_code = c.ifsc_code) 
where b.interest = (select max(interest) from account_info);

select a.Customer_ID, a.customer_name, b.account_no, c.bank_name, 
a.contact_no, a.mail_id from customer_personal_info a join account_info b 
on(a.customer_id = b.customer_id) join bank_info c on(b.ifsc_code = c.ifsc_code)
where a.address like '%bangalore';

select b.customer_id, a.bank_name, a.branch_name, a.ifsc_code, 
b.registration_date, b.activation_date from bank_info a join account_info b
on(a.ifsc_code = b.ifsc_code)
where b.activation_date like '%03%';

select a.customer_id, a.customer_name, b.account_no, b.account_type,
b.interest, b.initial_deposit, ((b.interest/100)*b.initial_deposit) 
interest_amt from customer_personal_info a join account_info b 
on(a.customer_id = b.customer_id);

select a.customer_id, a.customer_name, a.date_of_birth, a.guardian_name,
a.contact_no, a.mail_id, b.reference_acc_name
from customer_personal_info a join customer_reference_info b 
on(a.customer_id = b.customer_id) where reference_acc_name = 'raghul';


select Customer_ID, Customer_Name, ('+'||'91'||'-'||substr(contact_no,1,3) ||'-'||
substr(contact_no,4,3)||'-'||substr(contact_no,7,4)) CONTACT_ISD from customer_personal_info;
 
 
select a.ACCOUNT_NO, a.ACCOUNT_TYPE, a.CUSTOMER_ID, b.CUSTOMER_NAME, 
b.DATE_OF_BIRTH, b.GUARDIAN_NAME, b.CONTACT_NO, b.MAIL_ID, b.GENDER, 
c.REFERENCE_ACC_NAME, c.REFERENCE_ACC_NO, a.REGISTRATION_DATE, a.ACTIVATION_DATE,
d.BANK_NAME, d.BRANCH_NAME, a.INITIAL_DEPOSIT,  
(substr(activation_date,9,2)-substr(registration_date,9,2))as NoOfDaysForActivation 
from account_info a join customer_personal_info b on(a.customer_id = b.customer_id) 
join bank_info d on(a.ifsc_code = d.ifsc_code) join customer_reference_info c
on(b.customer_id = c.customer_id);

select a.CUSTOMER_ID, a.CUSTOMER_NAME, a.GUARDIAN_NAME, a.IDENTIFICATION_DOC_TYPE, 
b.REFERENCE_ACC_NAME, c.ACCOUNT_TYPE, c.IFSC_CODE, d.BANK_NAME,(initial_deposit+interest) as 
current_balance from customer_personal_info a join customer_reference_info b 
on(b.customer_id = a.customer_id) join account_info c on(a.customer_id = c.customer_id)
join bank_info d on(c.ifsc_code = d.ifsc_code);


select a.CUSTOMER_ID, b.CUSTOMER_NAME, a.ACCOUNT_NO, a.ACCOUNT_TYPE, 
a.INTEREST, CASE WHEN INITIAL_DEPOSIT = 20000 then 'high' 
WHEN INITIAL_DEPOSIT = 16000 then 'moderate' WHEN INITIAL_DEPOSIT = 10000 
then 'average' when INITIAL_DEPOSIT = 5000 then 'low' when initial_deposit = 0 then 'very low' END as Deposit_Status from account_info a
join customer_personal_info b on(a.customer_id = b.customer_id);

select a.CUSTOMER_ID, b.CUSTOMER_NAME, a.ACCOUNT_NO, a.ACCOUNT_TYPE, c.BANK_NAME,
c.IFSC_CODE, a.INITIAL_DEPOSIT,(select if(ACCOUNT_TYPE = 'savings', a.interest+(a.interest*(a.interest/100)), a.interest) from account_info) as 'new_interest'
 from account_info a join customer_personal_info b on(a.customer_id = b.customer_id)
join bank_info c on(a.ifsc_code = c.ifsc_code) 
where CUSTOMER_NAME like 'j%';


select a.CUSTOMER_ID, b.customer_name, a.account_no, a.INITIAL_DEPOSIT,
case
when a.INITIAL_DEPOSIT = 0 then '0%'
when a.INITIAL_DEPOSIT <= 10000 then '3%'
when a.INITIAL_DEPOSIT > 10000 and a.INITIAL_DEPOSIT <= 20000 then '5%'
when a.INITIAL_DEPOSIT > 20000 and a.INITIAL_DEPOSIT <= 30000 then '7%'
when a.INITIAL_DEPOSIT > 30000 then '10%'
END as taxPercentage from account_info a
join customer_personal_info b on(a.customer_id = b.customer_id);