How do I make a database of bank with SQL?

Question: 

 ***Consider the following schemas for “bank” database relation, where the primary keys are underlined.
             Branch (branch_name, branch_city, assets)
             Customer (customer_name, customer_street, customer_city)
             Loan (loan_number, branch_name, amount)
             Borrower (customer_name, loan_number)
             Account (account_number, branch_name, balance)
             Depositor (customer_name, account_number)

Answer:

Table Create SQL :-

***Branch table SQL:

 create table Branch

 (

 branch_name varchar(20),

 branch_city varchar(20),

 assets numeric(8,0),

 primary key (branch_name));

***Customer table SQL:

 create table Customer

 (

 customer_name varchar(20),

 customer_street varchar(20),

 customer_city varchar(20),

 primary key (customer_name));

***Loan table SQL:

 create table Loan

 (

 loan_number numeric(8,0),

 branch_name varchar(20),

 amount numeric(8,0),

 primary key (loan_number));

***Borrower table SQL:

 create table Borrower

 (

 customer_name varchar(20), 

 loan_number numeric(8,0),

 primary key (customer_name, loan_number));

***Account table SQL:

 create table Account

 (

 account_number numeric(8,0),

 branch_name varchar(20),

 balance numeric(8,0),

 primary key (account_number));

***Depositor table SQL:

 create table Depositor

 (

 customer_name varchar(20), 

 account_number numeric(8,0),

 primary key (customer_name, account_number));

Data Insert SQL :-

***Branch table Data SQL:

INSERT INTO `Bank`.`branch` (
`Branch_Name` ,
`Branch_City` ,
`Assets`
)
VALUES (
'Postagola_Branch', 'Dhaka', '1600000000'
), (
'Gulisthan_Branch', 'Dhaka', '1500000000'
), (
'Sodorghat_Branch', 'Narayongaz', '1600000000'
), (
'Demra_Branch', 'Narayongaz', '1600000000'
), (
'Gazipur_Branch', 'Gazipur', '1600000000'
), (
'Uttara_Branch', 'Dhaka', '1600000000'
), (
'Mohammadpur_Branch', 'Dhaka', '1600000000'
), (
'Mirpur_Branch', 'Dhaka', '1600000000'
), (
'Narayongang_Branch', ' Narayongang', '112000000'
), (
'Kadamtali_Branch', 'Dhaka', '1130000000'
), (
'Farmgate_Branch', 'Dhaka', '1030000000'
), (
'Sonargha_Branch', ' Narayongang', '1503200000'
), (
'Jatrabary_Branch', 'Dhaka', '1140000000'
), (
'Rongpur_Branch', ' Rongpur', '1170000000'
), (
'Dinajpur_Branch', ' Rongpur', '1180000000'
), (
'Sylhet_Branch', ' Sylhet', '1190000000'
), (
'Chittagong_Branch', ' Chittagong', '1210000000'
), (
'Cumilla_Branch', ' Chittagong', '1220000000'
), (
'Mymensingh_Branch', ' Mymensingh', '1610000000'
);

***Customer table Data SQL:

 INSERT INTO `customer` (
 `Customer_Name`,
 `Customer_Street`,
 `Customer_City`
)
VALUES
 ('Rony', 'Postagola', 'Dhaka'),
 ('Hasan', 'Demra', 'Dhaka'),
 ('Ratri', 'Gulisthan', 'Dhaka'),
 ('Runi', 'Gulisthan', 'Dhaka'),
 ('Robi', 'Sodorghat', 'Dhaka'),
 ('Rakib', 'Mohammadpur', 'Dhaka'),
 ('Rasel', 'Galachipa', 'Borisal'),
 ('Akib', 'Patuakhali', 'Borisal'),
 ('Sakib', 'Galachipa', 'Borisal'),
 ('Sajid', 'Jalokhathi', 'Borisal'),
 ('Anu', 'Mehendigang', 'Borisal'),
 ('Minu', 'Uttara', 'Dhaka'),
 ('Pintu', 'Mirpur', 'Dhaka'),
 ('Bobi', 'Rongpur', 'Rongpur'),
 ('Dola', 'Narayongang', 'Narayongang'),
 ('Sofiq', 'Demra', 'Narayongang'),
 ('Sakhawat', 'Chou-Rasta', 'Gazipur'),
 ('Shamim', 'Demra', 'Narayongang'),
 ('Mamun', 'Dinajpur', 'Rongpur'),
 ('Rono', 'Gazipur', 'Gazipur'),
 ('Duq', 'Farmgate', 'Dhaka'),
 ('Sawan', 'Narayongang', 'Narayongang'),
 ('Keka', 'Kadamtali', 'Dhaka'),
 ('Kausar', 'Gazipur-Sodor', 'Gazipur'),
 ('Kuddus', 'Gazipur', 'Gazipur'),
 ('Bokkor', 'Mymensingh', 'Mymensingh'),
 ('Shirin', 'Sodorghat', 'Narayongang'),
 ('Lima', 'Narayongang', 'Narayongang'),
 ('Lipe', 'Chittagong', 'Chittagong'),
 ('Labli', 'Cumilla', 'Chittagong'),
 ('Laki', 'Ship-Pur', 'Rongpur'),
 ('Kakuli', 'Chor-Monay', 'Borisal'),
 ('Asia', 'Kuakata', 'Borisal'),
 ('Chan', 'Borasal', 'Borisal'),
 ('Edris', 'Sylhet-Solor', 'Sylhet'),
 ('Edu', 'Postagola', 'Dhaka'),
 ('Faruk', 'Nouakhali', 'Chittagong'),
 ('Fahad', 'Dinajpur', 'Rongpur'),
 ('Forkan', 'Shaha-Road', 'Sylhet'),
 ('Gaus', 'Sylhet-Cha-potti', 'Sylhet'),
 ('Gayen', 'Pass-Khoil', 'Rongpur'),
 ('Hafiz', 'Majar-Gang', 'Sylhet'),
 ('Iqbal', 'Lokkhipur', 'Chittagong'),
 ('Iqramul', 'Sonar-Chor', 'Borisal'),
 ('July', 'Rupar-Chor', 'Borisal'),
 ('Jabed', 'Jatrabary', 'Dhaka'),
 ('Nafiz', 'Cumilla', 'Chittagong'),
 ('Nazmul', 'Sagordi', 'Mymensingh'),
 ('Opu', 'Boba-Bazar', 'Mymensingh'),
 ('Abhi', 'Mymensingh-Sodor', 'Mymensingh'),
 ('Tipu', 'Mymensingh', 'Mymensingh'),
 ('Uzzal', 'College-Para', 'Mymensingh'),
 ('Wakil', 'Ruppur', 'Mymensingh'),
 ('Rima', 'Borabo', 'Narayongaz');

***Loan table Data SQL:

 INSERT INTO `loan` (
 `Loan_Number`,
 `Branch_Name`,
 `Amount`
)
 VALUES
 ('1650200817', 'Postagola_Branch', '500000'),
 ('1650200826', 'Demra_Branch', '300000'),
 ('1650200801', 'Mymensingh_Branch', '10000'),
 ('1650200802', 'Cumilla_Branch', '20000'),
 ('1650200803', 'Gulisthan_Branch', '30000'),
 ('1650200804', 'Sodorghat_Branch', '40000'),
 ('1650200805', 'Gazipur_Branch', '50000'),
 ('1650200806', 'Uttara_Branch', '60000'),
 ('1650200807', 'Mirpur_Branch', '75000'),
 ('1650200808', 'Narayongang_Branch', '32500'),
 ('1650200809', 'Mohammadpur_Branch', '12000'),
 ('1650200810', 'Kadamtali_Branch', '10000'),
 ('1650200811', 'Farmgate_Branch', '65000'),
 ('1650200812', 'Sonargha_Branch', '75000'),
 ('1650200813', 'Jatrabary_Branch', '85500'),
 ('1650200814', 'Rongpur_Branch', '90005'),
 ('1650200815', 'Borisal_Branch', '40050'),
 ('1650200816', 'Dinajpur_Branch', '30500'),
 ('1650200818', 'Sylhet_Branch', '60650'),
 ('1650200819', 'Chittagong_Branch', '90120');

***Borrower table Data SQL:

 INSERT INTO `borrower` (
 `Customer_Name`,
 `Loan_Number`
)
 VALUES
 ('Mehedi', '000001'),
 ('Eshrat', '000002'),
 ('Rony', '000003'),
 ('Hasan', '000004'),
 ('Ratri', '000005'),
 ('Runi', '000006'),
 ('Robi', '000007'),
 ('Rakib', '000008'),
 ('Rasel', '000009'),
 ('Akib', '000010'),
 ('Sakib', '000011'),
 ('Sajid', '000012'),
 ('Anu', '000013'),
 ('Minu', '000014'),
 ('Pintu', '000015'),
 ('Bobi', '000016'),
 ('Dola', '000019'),
 ('Sofiq', '000020'),
 ('Sakhawat', '000021'),
 ('Shamim', '000022'),
 ('Mamun', '000023'),
 ('Rono', '000024'),
 ('Duq', '000025');

***Account table Data SQL:

 INSERT INTO `Bank`.`account` (
`Account_Number` ,
`Branch_Name` ,
`Balance`
)
VALUES (
'16502000817', 'Postagola_Branch', '50000000'
), (
'16502000826', 'Demra_Branch', '12000000'
), (
'16502000801', 'Gulisthan_Branch', '16000000'
), (
'16502000802', 'Sodorghat_Branch', '15000000'
), (
'16502000803', 'Gazipur_Branch', '20000000'
), (
'16502000804', 'Uttara_Branch', '19000000'
), (
'16502000805', 'Mohammadpur_Branch', '2000300'
), (
'16502000806', 'Mirpur_Branch', '2000500'
), (
'16502000807', 'Narayongang_Branch', '12300000'
), (
'16502000808', 'Kadamtali_Branch', '13300000'
), (
'16502000809', 'Farmgate_Branch', '14000600'
), (
'16502000810', 'Sonargha_Branch', '15000700'
), (
'16502000811', 'Jatrabary_Branch', '16008000'
), (
'16502000812', 'Rongpur_Branch', '15000500'
), (
'16502000813', 'Dinajpur_Branch', '18400000'
), (
'16502000814', 'Borisal_Branch', '20050000'
), (
'16502000815', 'Sylhet_Branch', '19090000'
), (
'16502000816', 'Chittagong_Branch', '2010000'
), (
'16502000818', 'Cumilla_Branch', '1300300'
), (
'16502000819', 'Mymensingh_Branch', '14060000'
);

***Depositor table Data SQL:

INSERT INTO `depositor` (
 `Customer_Name`,
 `Account_Number`
)
 VALUES
 ('Mehedi', '1650200817'),
 ('Eshrat', '1650200826'),
 ('Rony', '1650200802'),
 ('Hasan', '1650200802'),
 ('Ratri', '1650200803'),
 ('Runi', '1650200804'),
 ('Robi', '1650200805'),
 ('Rakib', '1650200806'),
 ('Rasel', '1650200807'),
 ('Akib', '1650200808'),
 ('Sakib', '1650200809'),
 ('Sajid', '1650200810'),
 ('Anu', '1650200811'),
 ('Minu', '1650200812'),
 ('Pintu', '1650200813'),
 ('Bobi', '1650200814'),
 ('Dola', '1650200815'),
 ('Sofiq', '1650200816'),
 ('Sakhawat', '1650200818'),
 ('Shamim', '1650200801'),
 ('Mamun', '1650200819'),
 ('Rima', '1650200803');

Result on Database for example:-

1.

2.

3.

4.

5.

6.

 

SQL queries Question:

                  i. Find all customers who have both a loan and an account at the branch.
                  ii. Find the average account balance at the “Perryridge” brach. 
                  iii. Insert record into the account relation with the values of account number is “AC101” at “Dhanmondi” branch and the balance is tk. 30000. 


SQL queries Answer:

***SQL:

        (i)
            (SELECT customer_name FROM depositor)
                                 INTERSECT                                
            (SELECT customer_name FROM borrower) 

Output:  

        (ii)
              SELECT AVG (balance) FROM account WHERE branch_name = "Perryridge"

Output:

        (iii)
               INSERT INTO account
               VALUES ('AC-101' , 'Dhanmondi' , 30000)

Output:

                   Thank you Guys.