-
Notifications
You must be signed in to change notification settings - Fork 0
/
day4_takehome_student copy.sql
241 lines (170 loc) · 12.4 KB
/
day4_takehome_student copy.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
# Pre-Requsites
# Assuming Candidates are familiar with “Group by” and “Grouping functions” because these are used along with JOINS in the questionnaire.
Table Definitions:
1. BANK_CUSTOMER - Details of Customers of the Bank
2. BANK_CUSTOMER_EXPORT - Details of Customers of the Bank - to be used only when explicitly asked.
3. Bank_Account_Details - Account Details of the customers along with ADD on cards. One customer can have multiple details for Savings deposits, Recurring deposits, Credit Cards and Add on credit cards.
4. Bank_Account_Relationship_Details - Details of secondary accounts linked to primary accounts.
5. BANK_ACCOUNT_TRANSACTION - Details of the transactions.
6. BANK_CUSTOMER_MESSAGES - Details of Messages sent to customers after a transaction takes place.
7. BANK_INTEREST_RATE - Current interest rates for savings, RD and other accounts.
8. Bank_Holidays - Details of Bank Holidays.
# Create below DB objects
use Inclass;
CREATE TABLE BANK_CUSTOMER
( customer_id INT PRIMARY KEY,
customer_name VARCHAR(20),
Address VARCHAR(20),
state_code VARCHAR(3) ,
Telephone VARCHAR(10) );
INSERT INTO BANK_CUSTOMER VALUES (123001,"Oliver", "225-5, Emeryville", "CA" , "1897614500");
INSERT INTO BANK_CUSTOMER VALUES (123002,"George", "194-6,New brighton","MN" , "1897617000");
INSERT INTO BANK_CUSTOMER VALUES (123003,"Harry", "2909-5,walnut creek","CA" , "1897617866");
INSERT INTO BANK_CUSTOMER VALUES (123004,"Jack", "229-5, Concord", "CA" , "1897627999");
INSERT INTO BANK_CUSTOMER VALUES (123005,"Jacob", "325-7, Mission Dist","SFO", "1897637000");
INSERT INTO BANK_CUSTOMER VALUES (123006,"Noah", "275-9, saint-paul" , "MN" , "1897613200");
INSERT INTO BANK_CUSTOMER VALUES (123007,"Charlie","125-1,Richfield", "MN" , "1897617666");
INSERT INTO BANK_CUSTOMER VALUES (123008,"Robin","3005-1,Heathrow", "NY" , "1897614000");
CREATE TABLE BANK_CUSTOMER_EXPORT
(
customer_id CHAR(10)PRIMARY KEY,
customer_name CHAR(20),
Address CHAR(20),
state_code CHAR(3) ,
Telephone CHAR(10));
INSERT INTO BANK_CUSTOMER_EXPORT VALUES ("123001 ","Oliver", "225-5, Emeryville", "CA" , "1897614500") ;
INSERT INTO BANK_CUSTOMER_EXPORT VALUES ("123002 ","George", "194-6,New brighton","MN" , "189761700");
#Bank_Account_details table
CREATE TABLE Bank_Account_Details
(Customer_id INT,
Account_Number VARCHAR(19) PRIMARY KEY,
Account_type VARCHAR(25) ,
Balance_amount INT,
Account_status VARCHAR(10),
Relationship_type varchar(1)) ;
ALTER TABLE Bank_Account_Details ADD FOREIGN KEY (Customer_id) REFERENCES bank_customer(Customer_id);
INSERT INTO Bank_Account_Details VALUES (123001, "4000-1956-3456", "SAVINGS" , 200000 ,"ACTIVE","P");
INSERT INTO Bank_Account_Details VALUES (123001, "5000-1700-3456", "RECURRING DEPOSITS" ,9400000 ,"ACTIVE","S");
INSERT INTO Bank_Account_Details VALUES (123002, "4000-1956-2001", "SAVINGS", 400000 ,"ACTIVE","P");
INSERT INTO Bank_Account_Details VALUES (123002, "5000-1700-5001", "RECURRING DEPOSITS" ,7500000 ,"ACTIVE","S");
INSERT INTO Bank_Account_Details VALUES (123003, "4000-1956-2900", "SAVINGS" ,750000,"INACTIVE","P");
INSERT INTO Bank_Account_Details VALUES (123004, "5000-1700-6091", "RECURRING DEPOSITS" ,7500000 ,"ACTIVE","S");
INSERT INTO Bank_Account_Details VALUES (123004, "4000-1956-3401", "SAVINGS" , 655000 ,"ACTIVE","P");
INSERT INTO Bank_Account_Details VALUES (123005, "4000-1956-5102", "SAVINGS" , 300000 ,"ACTIVE","P");
INSERT INTO Bank_Account_Details VALUES (123006, "4000-1956-5698", "SAVINGS" , 455000 ,"ACTIVE" ,"P");
INSERT INTO Bank_Account_Details VALUES (123007, "5000-1700-9800", "SAVINGS" , 355000 ,"ACTIVE" ,"P");
INSERT INTO Bank_Account_Details VALUES (123007, "4000-1956-9977", "RECURRING DEPOSITS" , 7025000,"ACTIVE" ,"S");
INSERT INTO Bank_Account_Details VALUES (123007, "9000-1700-7777-4321", "Credit Card" ,0 ,"INACTIVE", "P");
INSERT INTO Bank_Account_Details VALUES (123007, '5900-1900-9877-5543', "Add-on Credit Card" , 0 ,"ACTIVE", "S");
INSERT INTO Bank_Account_Details VALUES (123008, "5000-1700-7755", "SAVINGS" ,0 ,"INACTIVE","P");
INSERT INTO Bank_Account_Details VALUES (123006, '5800-1700-9800-7755', "Credit Card" ,0 ,"ACTIVE", "P");
INSERT INTO Bank_Account_Details VALUES (123006, '5890-1970-7706-8912', "Add-on Credit Card" ,0 ,"ACTIVE", "S");
# CREATE TABLE Bank_Account_Relationship_Details
CREATE TABLE Bank_Account_Relationship_Details
( Customer_id INT ,
Account_Number VARCHAR(19) PRIMARY KEY ,
Account_type VARCHAR(25),
Linking_Account_Number VARCHAR(19)
);
ALTER TABLE Bank_Account_Relationship_Details ADD FOREIGN KEY (Customer_id) REFERENCES bank_customer(Customer_id);
ALTER TABLE Bank_Account_Relationship_Details ADD FOREIGN KEY (Linking_Account_Number) REFERENCES bank_account_details(Account_Number);
INSERT INTO Bank_Account_Relationship_Details VALUES (123001, "4000-1956-3456", "SAVINGS" , NULL);
INSERT INTO Bank_Account_Relationship_Details VALUES (123001, "5000-1700-3456", "RECURRING DEPOSITS" , "4000-1956-3456");
INSERT INTO Bank_Account_Relationship_Details VALUES (123002, "4000-1956-2001", "SAVINGS" , NULL );
INSERT INTO Bank_Account_Relationship_Details VALUES (123002, "5000-1700-5001", "RECURRING DEPOSITS" , "4000-1956-2001" );
INSERT INTO Bank_Account_Relationship_Details VALUES (123003, "4000-1956-2900", "SAVINGS" , NULL );
INSERT INTO Bank_Account_Relationship_Details VALUES (123004, "5000-1700-6091", "RECURRING DEPOSITS" , "4000-1956-2900" );
INSERT INTO Bank_Account_Relationship_Details VALUES (123004, "5000-1700-7791", "RECURRING DEPOSITS" , "4000-1956-2900" );
INSERT INTO Bank_Account_Relationship_Details VALUES (123007, "5000-1700-9800", "SAVINGS" , NULL);
INSERT INTO Bank_Account_Relationship_Details VALUES (123007, "4000-1956-9977", "RECURRING DEPOSITS" , "5000-1700-9800" );
INSERT INTO Bank_Account_Relationship_Details VALUES (NULL, "9000-1700-7777-4321", "Credit Card" , "5000-1700-9800" );
INSERT INTO Bank_Account_Relationship_Details VALUES (NULL, '5900-1900-9877-5543', 'Add-on Credit Card', '9000-1700-7777-4321' );
INSERT INTO Bank_Account_Relationship_Details VALUES (NULL, '5800-1700-9800-7755', 'Credit Card', '4000-1956-5698' );
INSERT INTO Bank_Account_Relationship_Details VALUES (NULL, '5890-1970-7706-8912', 'Add-on Credit Card', '5800-1700-9800-7755' );
# CREATE TABLE BANK_ACCOUNT_TRANSACTION
CREATE TABLE BANK_ACCOUNT_TRANSACTION
(
Account_Number VARCHAR(19),
Transaction_amount Decimal(18,2) ,
Transcation_channel VARCHAR(18) ,
Province varchar(3) ,
Transaction_Date Date
) ;
ALTER TABLE Bank_Account_Transaction ADD FOREIGN KEY (Account_number) REFERENCES Bank_Account_Details(Account_Number);
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-3456", -2000, "ATM withdrawl" , "CA", "2020-01-13");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001", -4000, "POS-Walmart" , "MN", "2020-02-14");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001", -1600, "UPI transfer" , "MN", "2020-01-19");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001", -6000, "Bankers cheque", "CA", "2020-03-23");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001", -3000, "Net banking" , "CA", "2020-04-24");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-2001", 23000, "cheque deposit", "MN", "2020-03-15");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "5000-1700-6091", 40000, "ECS transfer" , "NY", "2020-02-19");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "5000-1700-7791", 40000, "ECS transfer" , "NY", "2020-02-19");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-3401", 8000, "Cash Deposit" , "NY", "2020-01-19");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-5102", -6500, "ATM withdrawal" , "NY", "2020-03-14");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-5698", -9000, "Cash Deposit" , "NY", "2020-03-27");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "4000-1956-9977", 50000, "ECS transfer" , "NY", "2020-01-16");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "9000-1700-7777-4321", -5000, "POS-Walmart", "NY", "2020-02-17");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "9000-1700-7777-4321", -8000, "Shopping Cart", "MN", "2020-03-13");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "9000-1700-7777-4321", -2500, "Shopping Cart", "MN", "2020-04-21");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( "5800-1700-9800-7755", -9000, "POS-Walmart","MN", "2020-04-13");
INSERT INTO BANK_ACCOUNT_TRANSACTION VALUES ( '5890-1970-7706-8912', -11000, "Shopping Cart" , "NY" , "2020-03-12") ;
# CREATE TABLE BANK_CUSTOMER_MESSAGES
CREATE TABLE BANK_CUSTOMER_MESSAGES
(
Event VARCHAR(24),
Customer_message VARCHAR(75),
Notice_delivery_mode VARCHAR(15)) ;
INSERT INTO BANK_CUSTOMER_MESSAGES VALUES ( "Adhoc", "All Banks are closed due to announcement of National strike", "mobile" ) ;
INSERT INTO BANK_CUSTOMER_MESSAGES VALUES ( "Transaction Limit", "Only limited withdrawals per card are allowed from ATM machines", "mobile" );
INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES
('4000-1956-9977' , 10000.00 ,'ECS transfer', 'MN' , '2020-02-16' ) ;
-- inserted for queries after 17th
INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES
('4000-1956-9977' , 40000.00 ,'ECS transfer', 'MN' , '2020-03-18' ) ;
INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES
('4000-1956-9977' , 60000.00 ,'ECS transfer', 'MN' , '2020-04-18' ) ;
INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES
('4000-1956-9977' , 20000.00 ,'ECS transfer', 'MN' , '2020-03-20' ) ;
-- inserted for queries after 24th
INSERT INTO `bank_account_transaction`(`Account_Number`, `Transaction_amount`, `Transcation_channel`, `Province`, `Transaction_Date`) VALUES
('4000-1956-9977' , 49000.00 ,'ECS transfer', 'MN' , '2020-06-18' ) ;
# CREATE TABLE BANK_INTEREST_RATE
CREATE TABLE BANK_INTEREST_RATE(
account_type varchar(24)PRIMARY KEY,
interest_rate decimal(4,2),
month varchar(2),
year varchar(4)
) ;
INSERT INTO BANK_INTEREST_RATE VALUES ( "SAVINGS" , 0.04 , '02' , '2020' );
INSERT INTO BANK_INTEREST_RATE VALUES ( "RECURRING DEPOSITS" , 0.07, '02' , '2020' );
INSERT INTO BANK_INTEREST_RATE VALUES ( "PRIVILEGED_INTEREST_RATE" , 0.08 , '02' , '2020' );
# Bank_holidays:
Create table Bank_Holidays (
Holiday date PRIMARY KEY,
Start_time datetime ,
End_time timestamp);
Insert into bank_holidays values ( '2020-05-20','2020-05-20','2020-05-20' ) ;
Insert into bank_holidays values( '2020-03-13' ,'2020-03-13' ,'2020-03-13' ) ;
############################################ Questions ############################################
# Question 1:
# 1) Print credit card transactions with sum of transaction_amount on all Fridays and sum of transaction_amount on all other days.
# Question 2:
# 2) Show the details of credit cards along with the aggregate transaction amount during holidays and non holidays.
# Question 3:
# 3) Generate a report to Send Ad-hoc holiday greetings - “Happy Holiday” for all transactions occurred during Holidays in 3rd month.
# Question 4:
# 4) Calculate the Bank accrued interest with respect to their RECURRING DEPOSITS for any deposits older than 30 days .
# Note: Accrued interest calculation = transaction_amount * interest_rate
-- fields/tables: interest_rate @bank_interest_rate table
-- transaction_amount @bank_account_transaction
# Pre-requisite Use bank schema tables from Online_Day7_Takehome to solve the queries.
# Question 5:
# 5) Display the Savings Account number whose corresponding Credit cards and AddonCredit card transactions have occured more than one time .
# Question 6:
# 6) Display the Savings Account number whose corresponding AddonCredit card transactions have occured atleast once.
# Question 7:
# 7) Print the customer_id and length of customer_id using Natural join on Tables : bank_customer and bank_customer_export
# Note: Do not use table alias to refer to column names.
# Question 8:
# 8) Print customer_id, customer_name and other common columns from both the Tables : bank_customer & bank_customer_export without missing any matching customer_id key column records.
-- Note: refer datatype conversion if found any missing records