-- CREATE DATABASE BANK
CREATE DATABASE bank;

-- CREATE SCHEMA BANKING 
CREATE SCHEMA banking;

-- USING DATABASE SCHEMA
USE bank;
use schema banking;

SHOW TABLES;
-- -----------------------------------------------------------------------------------------------------------

SELECT count(*) from DISTRICT;

SELECT count(*) from TRANSACTIONS;

SELECT count(*) from ORDER_LIST;

SELECT count(*) from ACCOUNT;

SELECT count(*) FROM DISPOSITION;

SELECT count(*) FROM CARD;

SELECT count(*) FROM LOAN;

SELECT count(*) FROM CLIENT;

SELECT * FROM DISTRICT;
SELECT * FROM ACCOUNT;
SELECT * FROM TRANSACTIONS limit 100;
SELECT * FROM DISPOSITION;
SELECT * FROM CARD;
SELECT * FROM ORDER_LIST;
SELECT * FROM LOAN;
SELECT * FROM CLIENT;

select distinct year(issued) from card;

Creating Account Table

ACCOUNT Schema Column Description account_id Identification of the account Primary key district_id Location of the branch date Date of the account's creation frequency Frequency of statement issuance

-- CREATING TABLE ACCOUNT

CREATE OR REPLACE TABLE ACCOUNT
(
account_id INT PRIMARY KEY,	
district_id	INT,
frequency VARCHAR(50),	
Date DATE,	
Account_type VARCHAR(50),
Card_assigned VARCHAR(50),
FOREIGN KEY (district_id) references DISTRICT(District_Code) 
);

Creating District Table

-- CREATING TABLE DISTRICT 

CREATE OR REPLACE TABLE DISTRICT(
District_Code INT PRIMARY KEY,
District_Name VARCHAR(100)	,
Region VARCHAR(100),
No_of_inhabitants	INT,
No_of_municipalities_with_inhabitants_less_499 INT,
No_of_municipalities_with_inhabitants_500_btw_1999	INT,
No_of_municipalities_with_inhabitants_2000_btw_9999	INT,
No_of_municipalities_with_inhabitants_less_10000 INT,	
No_of_cities	INT,
Ratio_of_urban_inhabitants	FLOAT,
Average_salary	INT,
No_of_entrepreneurs_per_1000_inhabitants INT,
No_committed_crime_2017	INT,
No_committed_crime_2018 INT
);

CREATING TABLE CARD

-- CREATING TABLE CARD

CREATE OR REPLACE TABLE CARD 
(
card_id INT PRIMARY KEY,
dis_id INT,
type CHAR(10),
issued DATE
);

CREATING TABLE CLIENT

-- CREATING TABLE CLIENT

CREATE OR REPLACE TABLE CLIENT
(
client_id INT PRIMARY KEY,	
Sex CHAR(10),
birth_date DATE, 
district_id	INT, 
FOREIGN KEY (district_id) references DISTRICT(District_Code)
);

CREATING DISPOSITION TABLE