S.Y.BCS MySQL Case Study 1 : Bank and Account

=========================================================================================
CASE STUDY 1 : BANK ACCOUNT
=========================================================================================




/**** CREATING DATABASE ****/

mysql> create database ganesh_bank;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ganesh_bank        |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

mysql> use ganesh_bank;
Database changed

/**** CREATING TABLE:CUSTOMERS ****/

mysql> create table customers(cno int primary key,cname varchar(20),cstreet varchar(20),ccity varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into customers values(1,'Mahesh','Karvenagar','Pune');
Query OK, 1 row affected (0.00 sec)

mysql> insert into customers values(2,'Ramesh','Juhu','Mumbai');
Query OK, 1 row affected (0.00 sec)

mysql> insert into customers values(3,'Suraj','Shivajinagar','Pune');
Query OK, 1 row affected (0.00 sec)

mysql> insert into customers values(4,'Rahul','Magarpatta','Nagpur');
Query OK, 1 row affected (0.00 sec)

mysql> insert into customers values(5,'Ganesh','Panshet','Pune');
Query OK, 1 row affected (0.00 sec)

mysql> select * from customers;
+-----+--------+--------------+--------+
| cno | cname  | cstreet      | ccity  |
+-----+--------+--------------+--------+
|   1 | Mahesh | Karvenagar   | Pune   |
|   2 | Ramesh | Juhu         | Mumbai |
|   3 | Suraj  | Shivajinagar | Pune   |
|   4 | Rahul  | Magarpatta   | Nagpur |
|   5 | Ganesh | Panshet      | Pune   |
+-----+--------+--------------+--------+
5 rows in set (0.00 sec)

/**** CREATING TABLE:BRANCH ****/

mysql> create table branch(bno int primary key,bname varchar(20),bcity varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into branch values(1,'kothrud Branch','Pune');
Query OK, 1 row affected (0.00 sec)

mysql> insert into branch values(2,'Nanapeth Branch','Pune');
Query OK, 1 row affected (0.00 sec)

mysql> insert into branch values(3,'Baykhala Branch','Mumbai');
Query OK, 1 row affected (0.00 sec)

mysql> insert into branch values(4,'Malvadi Branch','Pune');
Query OK, 1 row affected (0.00 sec)

mysql> insert into branch values(5,'Ghatenagar Branch','Nagpur');
Query OK, 1 row affected (0.00 sec)

mysql> select * from branch;
+-----+-------------------+--------+
| bno | bname             | bcity  |
+-----+-------------------+--------+
|   1 | kothrud Branch    | Pune   |
|   2 | Nanapeth Branch   | Pune   |
|   3 | Baykhala Branch   | Mumbai |
|   4 | Malvadi Branch    | Pune   |
|   5 | Ghatenagar Branch | Nagpur |
+-----+-------------------+--------+
5 rows in set (0.00 sec)

/**** CREATING TABLE:ACCOUNT ****/

mysql> create table account(ano int primary key,atype varchar(20),balance double(10,2),cno int references customers(cno),bno int references branch(bno));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values(1,'Saving',63000,1,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into account values(2,'Saving',93000,2,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into account values(3,'Current',193000,3,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into account values(4,'Current',102500,4,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into account values(5,'Saving',250000,5,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+-----+---------+-----------+------+------+
| ano | atype   | balance   | cno  | bno  |
+-----+---------+-----------+------+------+
|   1 | Saving  |  63000.00 |    1 |    4 |
|   2 | Saving  |  93000.00 |    2 |    3 |
|   3 | Current | 193000.00 |    3 |    1 |
|   4 | Current | 102500.00 |    4 |    5 |
|   5 | Saving  | 250000.00 |    5 |    2 |
+-----+---------+-----------+------+------+
5 rows in set (0.00 sec)

/**** CREATING TABLE:LOAN ****/

mysql> create table loan(lno int primary key,amount double(10,2),year int,bno int references brench(bno),cno int references customers(cno));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into loan values(1,60000,2007,2,5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into loan values(2,120000,2006,1,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into loan values(3,35000,2008,5,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into loan values(4,65000,2008,4,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into loan values(5,45000,2009,3,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from loan;
+-----+-----------+------+------+------+
| lno | amount    | year | bno  | cno  |
+-----+-----------+------+------+------+
|   1 |  60000.00 | 2007 |    2 |    5 |
|   2 | 120000.00 | 2006 |    1 |    3 |
|   3 |  35000.00 | 2008 |    5 |    4 |
|   4 |  65000.00 | 2008 |    4 |    1 |
|   5 |  45000.00 | 2009 |    3 |    2 |
+-----+-----------+------+------+------+
5 rows in set (0.00 sec)

//////*********** QUERIES ************//////

mysql> select cname from customers,loan where customers.cno=loan.cno and amount>60000;
+--------+
| cname  |
+--------+
| Suraj  |
| Mahesh |
+--------+
2 rows in set (0.00 sec)


mysql> select distinct customers.cname,ano from customers,account where customers.cno=account.cno and cname not in(select customers.cname from loan where customers.cno=loan.cno);
Empty set (0.00 sec)


mysql> select distinct customers.cno,cname,ano,lno from customers,account,loan where customers.cno=account.cno and customers.cno=loan.cno;
+-----+--------+-----+-----+
| cno | cname  | ano | lno |
+-----+--------+-----+-----+
|   5 | Ganesh |   5 |   1 |
|   3 | Suraj  |   3 |   2 |
|   4 | Rahul  |   4 |   3 |
|   1 | Mahesh |   1 |   4 |
|   2 | Ramesh |   2 |   5 |
+-----+--------+-----+-----+
5 rows in set (0.00 sec)


mysql> select distinct customers.cname,bname from customers,branch,account where customers.cno=account.cno and branch.bno=account.bno and bname='Kothrud Branch';
+-------+----------------+
| cname | bname          |
+-------+----------------+
| Suraj | kothrud Branch |
+-------+----------------+
1 row in set (0.00 sec)


mysql> select distinct customers.cname,atype from customers,account where customers.cno=account.cno and atype='saving';
+--------+--------+
| cname  | atype  |
+--------+--------+
| Mahesh | Saving |
| Ramesh | Saving |
| Ganesh | Saving |
+--------+--------+
3 rows in set (0.00 sec)


mysql> select sum(loan.amount) from loan,branch where branch.bno=loan.bno and bname='Nanapeth Branch';
+------------------+
| sum(loan.amount) |
+------------------+
|         60000.00 |
+------------------+
1 row in set (0.00 sec)


mysql> select customers.cname,ccity,bcity from customers,loan,branch where branch.bcity=customers.ccity and customers.cno=loan.cno and branch.bno=loan.bno;
+--------+--------+--------+
| cname  | ccity  | bcity  |
+--------+--------+--------+
| Ganesh | Pune   | Pune   |
| Suraj  | Pune   | Pune   |
| Rahul  | Nagpur | Nagpur |
| Mahesh | Pune   | Pune   |
| Ramesh | Mumbai | Mumbai |
+--------+--------+--------+
5 rows in set (0.00 sec)

//////*********** VIEWS ************//////

mysql> create view detail1 as select distinct cname,cstreet,ccity,ano,atype,balance from customers,account,branch where customers.cno=account.cno;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from detail1;
+--------+--------------+--------+-----+---------+-----------+
| cname  | cstreet      | ccity  | ano | atype   | balance   |
+--------+--------------+--------+-----+---------+-----------+
| Mahesh | Karvenagar   | Pune   |   1 | Saving  |  63000.00 |
| Ramesh | Juhu         | Mumbai |   2 | Saving  |  93000.00 |
| Suraj  | Shivajinagar | Pune   |   3 | Current | 193000.00 |
| Rahul  | Magarpatta   | Nagpur |   4 | Current | 102500.00 |
| Ganesh | Panshet      | Pune   |   5 | Saving  | 250000.00 |
+--------+--------------+--------+-----+---------+-----------+
5 rows in set (0.00 sec)


mysql> create view detail2 as select loan.lno,amount,year,branch.bno,bname,bcity from customers,loan,branch where branch.bno=loan.bno and bname='Kothrud Branch';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from detail2;
+-----+-----------+------+-----+----------------+-------+
| lno | amount    | year | bno | bname          | bcity |
+-----+-----------+------+-----+----------------+-------+
|   2 | 120000.00 | 2006 |   1 | kothrud Branch | Pune  |
|   2 | 120000.00 | 2006 |   1 | kothrud Branch | Pune  |
|   2 | 120000.00 | 2006 |   1 | kothrud Branch | Pune  |
|   2 | 120000.00 | 2006 |   1 | kothrud Branch | Pune  |
|   2 | 120000.00 | 2006 |   1 | kothrud Branch | Pune  |
+-----+-----------+------+-----+----------------+-------+
5 rows in set (0.00 sec)

//////*********** FUNCTIONS ************//////

mysql> delimiter //
mysql> create function bank1(b varchar(20))
    -> returns varchar(20)
    -> deterministic
    -> begin
    -> declare a int;
    -> select sum(loan.amount) into a from loan,branch where branch.bno=loan.bno and bname=a;
    -> return a;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> select bank1('Ghatenagar Branch');
    -> //
+----------------------------+
| bank1('Ghatenagar Branch') |
+----------------------------+
| NULL                       |
+----------------------------+
1 row in set (0.00 sec)


mysql> delimiter //
mysql> create function bank2(b int)
    -> returns varchar(20)
    -> deterministic
    -> begin
    -> declare a int;
    -> select count(customers.cno) into a from customers,account where customers.cno=account.cno and ano=b;
    -> return a;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> select bank2(2);
    -> //
+----------+
| bank2(2) |
+----------+
| 1        |
+----------+
1 row in set (0.00 sec)

//////*********** PROCEDURE ************//////

mysql> delimiter //
mysql> create procedure p1()
    -> begin
    -> update account
    -> set balance=balance-10000 where ano=5;
    -> update account
    -> set balance=balance+10000 where ano=1;
    -> select * from account where ano=5;
    -> select * from account where ano=1;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> call p1;
    -> //
+-----+--------+-----------+------+------+
| ano | atype  | balance   | cno  | bno  |
+-----+--------+-----------+------+------+
|   5 | Saving | 240000.00 |    5 |    2 |
+-----+--------+-----------+------+------+
1 row in set (0.00 sec)
+-----+--------+----------+------+------+
| ano | atype  | balance  | cno  | bno  |
+-----+--------+----------+------+------+
|   1 | Saving | 73000.00 |    1 |    4 |
+-----+--------+----------+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

//////*********** CURSOR ************//////

mysql> delimiter //
mysql> create procedure ban()
    -> begin
    -> declare bal int;
    -> declare c1 cursor for select balance from account;
    -> update account set balance=balance+0.03*balance where account.balance>94000;
    -> open c1;
    -> fetch c1 into bal;
    -> close c1;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> call ban()
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account
    -> //
+-----+---------+-----------+------+------+
| ano | atype   | balance   | cno  | bno  |
+-----+---------+-----------+------+------+
|   1 | Saving  |  73000.00 |    1 |    4 |
|   2 | Saving  |  93000.00 |    2 |    3 |
|   3 | Current | 198790.00 |    3 |    1 |
|   4 | Current | 105575.00 |    4 |    5 |
|   5 | Saving  | 247200.00 |    5 |    2 |
+-----+---------+-----------+------+------+
5 rows in set (0.00 sec)

//////*********** TRIGGER ************//////

mysql> delimiter //
mysql> create trigger t1 before insert on account for each row
    -> begin
    -> declare d int;
    -> if new.ano<0 then select account_number_should_not_be_less_than_zero into d from account where ano=new.ano;
    -> end if;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> insert into account values(-1,'Saving',68000,4,5);
    -> //
ERROR 1054 (42S22): Unknown column 'account_number_should_not_be_less_than_zero' in 'field list'


mysql> delimiter //
mysql> create trigger t2 after update on loan for each row
    -> begin
    -> declare d int;
    -> if new.amount=old.amount then set d=t2;
    -> else
    -> select loan_amount_cannot_be_updated into d from loan where lno=new.lno;
    -> end if;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> update loan set amount=amount+1000;
    -> //
ERROR 1054 (42S22): Unknown column 'loan_amount_cannot_be_updated' in 'field list'


                                                END CASE STUDY : 1
=========================================================================================