Practical No.

03
Name : Renuka Jadhav Roll No. : 49
Div : C

Aim : Write at least10 SQL queries for suitable database application using SQL DML
statements , Join , Sub-Query and View.

mysql> create database Renuka;

Query OK, 1 row affected (0.03 sec)

mysql> use Renuka;

Database changed
mysql> create table branch(branch_name varchar(20) primary key,branch_city
varchar(20),assets int);
Query OK, 0 rows affected (0.04 sec)

mysql> create table customer(customer_name varchar(20) primary key,customer_street

varchar(20), customer_city varchar(20));

Query OK, 0 rows affected (0.05 sec)

mysql> create table account(account_number int primary key, branch_name varchar(20),

amount int,foreign key (branch_name) references branch(branch_name));
Query OK, 0 rows affected (0.11 sec)

mysql> create table loan(loan_number int primary key,branch_name varchar(20), amount

int,foreign key (branch_name) references branch(branch_name));

Query OK, 0 rows affected (0.07 sec)

mysql> create table depositor (customer_name varchar(20),account_number int,primary
key(customer_name, account_number),foreign key (customer_name) references
customer(customer_name),foreign key (account_number) references
account(account_number));
Query OK, 0 rows affected (0.07 sec)

mysql> create table borrower(customer_name varchar(20),loan_number int,foreign key

(customer_name) references customer(customer_name),foreign key (loan_number) references
loan(loan_number));
Query OK, 0 rows affected (0.11 sec)

mysql> insert into branch

values("akurdi","pune",12000),("loni","pune",67000),("wagholi","pune",5699),("latur","latur",4
5888);
Query OK, 4 rows affected (0.02 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from branch;

+-------------+-------------+--------+
| branch_name | branch_city | assets |

+-------------+-------------+--------+
| akurdi | pune | 12000 |
| latur | latur | 45888 |
| loni | pune | 67000 |
| wagholi | pune | 5699 |

+-------------+-------------+--------+
4 rows in set (0.01 sec)
mysql> insert into customer
values("renuka","balajinagar","dhoki"),("ishwari","shivajinagar","satara"),("jayjit","lokmanyatilk
","pune"),("srushti","mg","latur");
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from customer;

+---------------+-----------------+---------------+
| customer_name | customer_street | customer_city |
+---------------+-----------------+---------------+
| ishwari | shivajinagar | satara |

| jayjit | lokmanyatilk | pune |

| renuka | balajinagar | dhoki |
| srushti | mg | latur |
+---------------+-----------------+---------------+
4 rows in set (0.00 sec)
mysql> insert into account
values(101,"akurdi",12000),(102,"latur",200000),(103,"loni",40000),(104,"wagholi",23000);

Query OK, 4 rows affected (0.01 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from account;

+----------------+-------------+--------+

| account_number | branch_name | amount |

+----------------+-------------+--------+
| 101 | akurdi | 12000 |
| 102 | latur | 200000 |
| 103 | loni | 40000 |
| 104 | wagholi | 23000 |
+----------------+-------------+--------+
4 rows in set (0.00 sec)

mysql> insert into loan values(201,"akurdi",23000),(202,"latur",56000);

Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from loan;

+-------------+-------------+--------+
| loan_number | branch_name | amount |
+-------------+-------------+--------+
| 201 | akurdi | 23000 |

| 202 | latur | 56000 |

+-------------+-------------+--------+
2 rows in set (0.00 sec)

mysql> insert into depositor values("ishwari",101),("jayjit",102);

Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from depositor;

+---------------+----------------+

| customer_name | account_number |
+---------------+----------------+
| ishwari | 101 |
| jayjit | 102 |
+---------------+----------------+
2 rows in set (0.00 sec)

mysql> insert into borrower values("renuka",201),("srushti",202);

Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from borrower;

+---------------+-------------+
| customer_name | loan_number |
+---------------+-------------+
| renuka | 201 |
| srushti | 202 |

+---------------+-------------+
2 rows in set (0.00 sec)

1) Creating View on borrower table.

mysql> create view viewb as select customer_name, loan_number from borrower;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from viewb;

+---------------+-------------+
| customer_name | loan_number |

+---------------+-------------+
| renuka | 201 |
| srushti | 202 |
+---------------+-------------+
2 rows in set (0.01 sec)

2) find the name of all branches in the loan relation

mysql> select branch_name from loan;

+-------------+
| branch_name |
+-------------+
| akurdi |

| latur |
+-------------+
2 rows in set (0.01 sec)

3) find all loan number for loan where at Akurdi branch with loan amount < 12000
mysql> select loan_number,amount from Loan where branch_name='Akurdi' and
amount>12000;

+-------------+--------+
| loan_number | amount |
+-------------+--------+
| 201 | 23000 |
+-------------+--------+

1 row in set (0.01 sec)

4) Find all the customer who have a loan from bank & find there names, loan number ,
loan amount
mysql> select borrower.customer_name, loan.loan_number,loan.amount from borrower inner
join loan on borrower.loan_number = loan.loan_number;
+---------------+-------------+--------+
| customer_name | loan_number | amount |
+---------------+-------------+--------+
| renuka | 201 | 23000 |
| srushti | 202 | 56000 |

+---------------+-------------+--------+
2 rows in set (0.00 sec)

5) Display the customer those who have loan account.

mysql> select * from loan inner join borrower on loan.loan_number = borrower.loan_number;

+-------------+-------------+--------+---------------+-------------+
| loan_number | branch_name | amount | customer_name | loan_number |
+-------------+-------------+--------+---------------+-------------+
| 201 | akurdi | 23000 | renuka | 201 |
| 202 | latur | 56000 | srushti | 202 |

+-------------+-------------+--------+---------------+-------------+
2 rows in set (0.00 sec)

6) List all customer in alphabetical order who have loan from Akurdi branch
mysql> select customer_name from borrower b join loan l on l.loan_number=b.loan_number
order by customer_name;
+---------------+

| customer_name |
+---------------+
| renuka |
| srushti |
+---------------+

2 rows in set (0.00 sec)

 7) Find the average account balance at each branch
mysql> select branch_name ,avg(amount) from account group by branch_name;
+-------------+-------------+

| branch_name | avg(amount) |
+-------------+-------------+
| akurdi | 12000.0000 |
| latur | 200000.0000 |
| loni | 40000.0000 |

| wagholi | 23000.0000 |
+-------------+-------------+
4 rows in set (0.02 sec)

8) Find no of depositors at each branch

mysql> select count(customer_name) from depositor;

+----------------------+
| count(customer_name) |
+----------------------+
| 2|
+----------------------+
1 row in set (0.00 sec)

9) Calculate total loan amount given by bank

mysql> select sum(amount) from loan;

+-------------+
| sum(amount) |
+-------------+
| 79000 |
+-------------+
1 row in set (0.00 sec)

10) Delete all loan with loan where amount between 13000 AND 15000
mysql> DELETE FROM loan WHERE amount BETWEEN 13000 AND 15000;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from loan;

+-------------+-------------+--------+
| loan_number | branch_name | amount |
+-------------+-------------+--------+
| 201 | akurdi | 23000 |
| 202 | latur | 56000 |

+-------------+-------------+--------+
2 rows in set (0.00 sec)