· 4 years ago · May 06, 2021, 11:20 AM
1DROP TABLE IF EXISTS Customer ;
2DROP TABLE IF EXISTS Employee ;
3DROP TABLE IF EXISTS Insurance ;
4DROP TABLE IF EXISTS Loans ;
5DROP TABLE IF EXISTS Branch ;
6DROP TABLE IF EXISTS Bank ;
7DROP TABLE IF EXISTS Account ;
8DROP TABLE IF EXISTS Customer_account_info ;
9
10CREATE TABLE IF NOT EXISTS Customer (c_name varchar(20) NOT NULL,
11 c_id varchar(20) PRIMARY KEY,
12 c_acc_number varchar(20) NOT NULL,
13 c_dob varchar(20),
14 c_pan_no varchar(20),
15 c_address varchar(100) DEFAULT 'Bangladesh');
16
17CREATE TABLE IF NOT EXISTS Employee(e_name varchar(20) NOT NULL,
18 e_type varchar(100),
19 e_salary integer,
20 e_emp_id varchar(20) PRIMARY KEY,
21
22 CHECK(e_salary>0));
23
24CREATE TABLE IF NOT EXISTS Insurance(i_issuing_company varchar(20) DEFAULT 'Standard Life',
25 i_type varchar(100),
26 i_premium_pay integer,
27 i_term integer,
28 i_policy_no varchar(10) PRIMARY KEY,
29 i_custo_id varchar(20),
30 CHECK(i_premium_pay>0));
31
32CREATE TABLE IF NOT EXISTS Loans(l_account_no varchar(20) PRIMARY KEY,
33 l_type varchar(100),
34 l_amount integer,
35 l_term integer,
36 l_rate integer DEFAULT 6,
37 l_cust_id varchar(20),
38 CHECK(amount>0));
39
40
41CREATE TABLE IF NOT EXISTS Branch(b_id varchar(10) PRIMARY KEY,
42 b_type varchar(100),
43 b_location varchar(40) NOT NULL,
44 b_bank_name varchar(20));
45
46
47CREATE TABLE IF NOT EXISTS Bank(bnk_name varchar(20),
48 bnk_type varchar(20),
49 bnk_head_office varchar(20),
50 bnk_branch_id varchar(20) PRIMARY KEY);
51
52
53CREATE TABLE IF NOT EXISTS Account(accnt_balance integer,
54 accnt_acc_number varchar(40),
55 accnt_overdraft integer,
56 accnt_customerid varchar(20) PRIMARY KEY);
57
58CREATE TABLE IF NOT EXISTS Customer_account_info(c_name varchar(20),
59 customer_id varchar(20),
60 acc_num varchar(40),
61 c_dob varchar(20),
62 c_phn_no varchar(20),
63 c_address varchar(100),
64 acc_balance integer,
65 /*acc_overdraft integer, */
66 card_no varchar(20));
67
68
69
70/* For account number, it is a 13 digit number where first 3 digits are branch id, next 3 numbers are type of account(savings-100, current-200), next two numbers are just 00, last 4 numbers are the unique customer id, so follow the same.
71for pan number */
72
73INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Redowan','001','056 100 00 10001','12-05-1988','10000','mymensingh,bangladesh');
74INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Arefin','002','057 200 00 10002','02-09-1990', '10001','Borisal,bangladesh');
75INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Tanim','003','058 100 00 10003','09-12-1984',NULL,'Nuyakali,Bangladesh');
76INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Adnan Farabi','004','059 200 00 004','31-05-1998','10002','Dhaka,Bangladesh');
77INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Borhan','005','060 200 00 10005','15-05-1988',NULL,'Borisal,Bangladesh');
78INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Borhani Mia','006','070 100 00 10006','19-06-1965',NULL,'Nuyakali, Bangladesh');
79INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('Rasel','007','080 100 00 10007','12-06-1996','10003','Borisal,Bangladesh');
80INSERT INTO `customer`(`c_name`, `c_id`, `c_acc_number`, `c_dob`, `c_pan_no`, `c_address`) values('babu','008','090 200 00 10008','02-12-1989','10004','mymensingh,Bangladesh');
81
82
83
84
85
86
87INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('Ridoy khan','Manager',60000,'001');
88INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('Arefin Tanim','Vice President',150000,'002');
89INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('Borhan Uddin','Assistant Vice President',100000,'006');
90INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('babu rasel','Officer',40000,'003');
91INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('Ahammed Sh','Manager',60000,'007');
92INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('Sajal','Clerk',10000,'008');
93INSERT INTO `employee`(`e_name`, `e_type`, `e_salary`, `e_emp_id`) values('Mark zuker','Officer',40000,'009');
94
95
96
97
98
99/* type - health,life,motor,car life,Home loan,LIC
100issuing company - */
101INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('Standard Life','Health',2000,5,'001','003');
102INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('LIFC','Health',5000,4,'002','005');
103INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('Good Life','Motor',2800,3,'003','006');
104INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('Money on Money','Life',8000,5,'004','004');
105INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('Car Life','Motor',1000,1,'005','003');
106INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('Healty Life','Health',6000,7,'006','002');
107INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('Home Loan','Home',5500,1,'007','001');
108INSERT INTO `insurance`(`i_issuing_company`, `i_type`, `i_premium_pay`, `i_term`, `i_policy_no`, `i_custo_id`) values('LIC','Life',4000,5,'009','007');
109
110
111
112
113
114
115INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0001 200 00 10002','Home',20000,3,5,'002');
116INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0001 100 00 10006','Business',10000,5,7,'006');
117INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0005 100 00 10007','House',70000,7,3,'007');
118INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0008 200 00 10008','House',500000,3,5,'008');
119INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0001 100 00 10003','Equipment',50000,3,3,'003');
120INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0001 100 00 1000','Business',300000,7,7,'001');
121INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0001 100 00 10001','Business',500000,7,9,'001');
122INSERT INTO `loans`(`l_account_no`, `l_type`, `l_amount`, `l_term`, `l_rate`, `l_cust_id`) values('0003 200 00 10005','House',600000,5,2,'005');
123
124INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('056','Urban','Mymensingh','Rupali Bank');
125INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('057','Urban','Dhaka','Sonali Bank');
126INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('058','Rural','Borisal','Agroni Bank');
127INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('059','Urban','Noyakali','Trust Bank');
128INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('060','Urban',' Mymensingh','Ab Bank');
129INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('070','Rural','Mymensingh','Bd Bank');
130INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('080','Urban','Dhaka','Bangla Bank');
131INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('090','Urban',' Dhaka ','Today Bank');
132INSERT INTO `branch`(`b_id`, `b_type`, `b_location`, `b_bank_name`) values('110','Urban','Noyakali','Union Bank');
133
134
135
136
137
138INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Rupali Bank','Commercial','Dhaka','056');
139INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Sonali Bank','Retail','Dhaka','057');
140INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Agroni Bank','Retail','Dhaka','058');
141INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Trust Bank','Commercial','Dhaka','059');
142INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Ab Bank','Commercial','Dhaka','060');
143INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Bd Bank','Retail','Dhaka','070');
144INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Bangla Bank','Retail','Dhaka','080');
145INSERT INTO `bank`(`bnk_name`, `bnk_type`, `bnk_head_office`, `bnk_branch_id`) values('Today Bank','Commercial','Dhaka','090');
146
147
148
149
150
151INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(50000,'0001 100 00 10001' , 4000, '001');
152INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(10000,'0001 200 00 10002' , 4000, '002');
153INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(500000,'0001 100 00 10003' , 4000, '003');
154INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(7500,'0002 200 00 10004' , 4000, '004');
155INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(11000,'0003 200 00 10005' , 4000, '005');
156INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(9000,'0001 100 00 10006' , 4000, '006');
157INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(800000,'0005 100 00 10007' , 4000, '007');
158INSERT INTO `account`(`accnt_balance`, `accnt_acc_number`, `accnt_overdraft`, `accnt_customerid`) values(150000,'0008 200 00 10008' , 4000, '008');
159
160
161
162INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Redowan','001','056 100 00 10001','12-05-1988','+8801743577778','mymensingh,bangladesh',50000,'1234 5678 9012 3456');
163INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Arefin','002','057 200 00 10002','02-09-1990','+8801743577779', 'Borisal,bangladesh',100000,'1357 8024 5791 2468');
164INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Tanim','003','058 100 00 10003','09-12-1984','+8801743577678','Nuyakali,Bangladesh',500000,'4321 8765 0912 5933');
165INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Adnan Farabi','004','059 200 00 004','31-05-1998','+8801543576778','Dhaka,Bangladesh',7500,'5494 3720 3464 3929');
166INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Borhan','005','060 200 00 10005','15-05-1988','+88015435778','Borisal,Bangladesh',11000,'3456 9464 1299 1938');
167INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Borhani Mia','006','070 100 00 10006','19-06-1965','+8801748677778','Nuyakali, Bangladesh',9000,'4021 6477 1927 1377');
168INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Rasel','007','080 100 00 10007','12-06-1996','+880164355678','Borisal,Bangladesh',800000,'5490 1277 1388 9500');
169INSERT INTO `customer_account_info`(`c_name`, `customer_id`, `acc_num`, `c_dob`, `c_phn_no`, `c_address`, `acc_balance`, `card_no`) values('Babu','008','090 200 00 10008','02-12-1989','+8801743667778','mymensingh,Bangladesh',150000,'4563 4616 9085 2763');
170