S.Y.BCS MySQL Case Study 6 : RAILWAY

===================================================
CASE STUDY 6 : RAILWAY
===================================================




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

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

mysql> use ganesh_railway;
Database changed

/**** CREATING TABLE : TRAIN****/

mysql> create table train(tno int primary key auto_increment,tname varchar(20),dtime time,atime time,source varchar(20),destination varchar(20),res_bogies int,capacity int);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from train;
+-----+------------------+----------+----------+--------+-------------+------------+----------+
| tno | tname            | dtime    | atime    | source | destination | res_bogies | capacity |
+-----+------------------+----------+----------+--------+-------------+------------+----------+
|   1 | Hussain Express  | 12:10:10 | 05:10:20 | Pune   | Hyderabad   |         20 |       25 |
|   2 | Lonavala Express | 16:50:10 | 21:00:20 | Pune   | Khandala    |         30 |       35 |
|   3 | Shatabdi Express | 16:50:10 | 20:00:20 | Pune   | Solapur     |         20 |       35 |
|   4 | Manali Passenger | 09:50:10 | 23:10:20 | Mumbai | Manali      |         31 |       35 |
|   5 | Deccan Passenger | 09:50:10 | 02:30:20 | Satara | Pune        |         21 |       25 |
+-----+------------------+----------+----------+--------+-------------+------------+----------+
5 rows in set (0.00 sec)

/**** CREATING TABLE : PASSENGER****/

mysql> create table passenger(pno int primary key auto_increment,pname varchar(20),address varchar(20),age int,gender varchar(3));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from passenger;
+-----+-------------+----------+------+--------+
| pno | pname       | address  | age  | gender |
+-----+-------------+----------+------+--------+
|   1 | Mr.Kulkarni | Pune     |   45 | M      |
|   2 | Mr.Chavan   | Satara   |   35 | M      |
|   3 | Mis.joshi   | Pune     |   21 | F      |
|   4 | Mas.Kadam   | Solapur  |   22 | M      |
|   5 | Mss.Kapoor  | Lonavala |   31 | F      |
+-----+-------------+----------+------+--------+
5 rows in set (0.00 sec)

/**** CREATING TABLE : TICKET****/

mysql> create table ticket(tno int references train(tno),pno int references passenger(pno),ticno varchar(10),bogieno int,no_of_births int,date date,amount decimal(7,2),status varchar(2));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ticket;
+------+------+-------+---------+--------------+------------+---------+--------+
| tno  | pno  | ticno | bogieno | no_of_births | date       | amount  | status |
+------+------+-------+---------+--------------+------------+---------+--------+
|    1 |    4 | PR001 |       5 |           15 | 2008-09-09 | 1500.00 | c      |
|    2 |    5 | PR405 |      15 |           30 | 2009-10-15 | 1600.00 | c      |
|    3 |    1 | PR303 |       5 |           15 | 2009-07-12 | 3000.00 | w      |
|    4 |    3 | PR204 |       5 |           15 | 2011-07-12 | 4500.00 | w      |
|    5 |    2 | PR101 |       5 |           15 | 2012-05-21 | 5500.00 | c      |
+------+------+-------+---------+--------------+------------+---------+--------+
5 rows in set (0.00 sec)


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


Q1. List the information  about the availability of trains between pune  and solapur.

mysql> select * from train where(source='Pune' and destination='Solapur');
+-----+------------------+----------+----------+--------+-------------+------------+----------+
| tno | tname            | dtime    | atime    | source | destination | res_bogies | capacity |
+-----+------------------+----------+----------+--------+-------------+------------+----------+
|   3 | Shatabdi Express | 16:50:10 | 20:00:20 | Pune   | Solapur     |         20 |       35 |
+-----+------------------+----------+----------+--------+-------------+------------+----------+
1 row in set (0.00 sec)


Q2. Give the names of all the trains which start from Pune.

mysql> select tname from train where source='Pune';
+------------------+
| tname            |
+------------------+
| Hussain Express  |
| Lonavala Express |
| Shatabdi Express |
+------------------+
3 rows in set (0.00 sec)


Q3. Give the details of the passenger who have done reservation for the train named Hussain Express.

mysql> select * from passenger where pno in(select pno from ticket where tno in(select tno from train where tname='Hussain Express'));
+-----+-----------+---------+------+--------+
| pno | pname     | address | age  | gender |
+-----+-----------+---------+------+--------+
|   4 | Mas.Kadam | Solapur |   22 | M      |
+-----+-----------+---------+------+--------+
1 row in set (0.02 sec)


Q4. Display ticket wise number of berths reserved on date 2011-07-12.

mysql> select no_of_births from ticket where date='2011-07-12' group by no_of_births;
+--------------+
| no_of_births |
+--------------+
|           15 |
+--------------+
1 row in set (0.02 sec)


Q5. Display train wise ticket amount on date 2009-10-15.

mysql> select amount from ticket where date='2009-10-15' group by tno;
+---------+
| amount  |
+---------+
| 1600.00 |
+---------+
1 row in set (0.00 sec)


Q6. Delete the details of all the passenger who have booked ticket before todays date.

mysql> delete from passenger where pno in(select pno from ticket where date<'2009-10-15');
Query OK, 2 rows affected (0.01 sec)

mysql> select * from passenger;
+-----+------------+----------+------+--------+
| pno | pname      | address  | age  | gender |
+-----+------------+----------+------+--------+
|   2 | Mr.Chavan  | Satara   |   35 | M      |
|   3 | Mis.joshi  | Pune     |   21 | F      |
|   5 | Mss.Kapoor | Lonavala |   31 | F      |
+-----+------------+----------+------+--------+
3 rows in set (0.00 sec)


//*********VIEW********//

V1. Create a view containing the details of all the passengers who have booked a ticket for the Shatabdi Express on 2009-07-12.

mysql> create view v1 as select passenger.pno,pname,address,age,gender from passenger,ticket,train where passenger.pno=ticket.pno and train.tno=ticket.tno and tname='Shatabdi Express' and date='2009-07-12';
    -> //
Query OK, 0 rows affected (0.03 sec)

mysql> select * from v1;
    -> //
Empty set (0.00 sec)


V2. Create a view to list the passenger names whose ticket status is waiting of Manali Passenger on date 2011-07-12.

mysql> create view v2 as select pname from passenger,train,ticket where train.tno=ticket.tno and passenger.pno=ticket.pno and status='w' and tname='Manali Passenger' and date='2011-07-12';

Query OK, 0 rows affected (0.00 sec)

mysql> select * from v2;
+-----------+
| pname     |
+-----------+
| Mis.joshi |
+-----------+
1 row in set (0.00 sec)


//*********FUNCTION********//

F1. Write a function which accept train name and date as a unput parameter and calculate total ticket amount for that train on the given date.

mysql> delimiter //
mysql> create function f1(name varachar(20),date date)
    -> returns decimal(7,2);
    -> deterministic
    -> begin
    -> declare total decimal(7,2);
    -> select sum(ticket.amount) into total from ticket,train where train.tno=ticket.tno and tname=name and date=date;
    -> return total;
    -> end;
    -> //
mysql> select f1('Shatabdi Express','2009-07-12');
+--------------------------------------------------+
|               f1('Shatabdi Express','2009-07-12');|
+--------------------------------------------------+
|                                              3000 |
+---------------------------------------------------+


F2. Write a function to calculate total no of berths reserved for the train Lonavala Express on date 2009-10-15.

mysql> delimiter //
mysql> create finction f2()
    -> returns interger
    -> deterministic
    -> begin
    -> declare total interger;
    -> select sum(no_of_births) into total from train,ticket where train.tno=ticket.tno and tname='Lonavala Express' and date='2009-10-15';
    -> return total;
    -> end;
    -> //

mysql> select f2();
+-----------+
|       f2()|
+-----------+
|         30|
+-----------+


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

P1. Write a procedure to calculate the ticket amount paid by all the passengers on date 2011/07/12 for all the trains.

mysql> delimiter //
mysql> create procedure p1()
    -> begin
    -> select sum(amount) from ticket where date='2011-07-12';
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call p1();
    -> //
+-------------+
| sum(amount) |
+-------------+
|     4500.00 |
+-------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)


P2. Write a procedure to update the status of the ticket from a mumbai to manali for passenger named Mr.Joshi.

mysql> delimiter //
mysql> create procedure p3()
    -> begin
    -> update ticket set status='w' where pno in(select pno from passenger where pname='Mr.Joshi');
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call p3();
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ticket;
    -> //
+------+------+-------+---------+--------------+------------+---------+--------+
| tno  | pno  | ticno | bogieno | no_of_births | date       | amount  | status |
+------+------+-------+---------+--------------+------------+---------+--------+
|    1 |    4 | PR001 |       5 |           15 | 2008-09-09 | 1500.00 | c      |
|    2 |    5 | PR405 |      15 |           30 | 2009-10-15 | 1600.00 | c      |
|    3 |    1 | PR303 |       5 |           15 | 2009-07-12 | 3000.00 | w      |
|    4 |    3 | PR204 |       5 |           15 | 2011-07-12 | 4500.00 | w      |
|    5 |    2 | PR101 |       5 |           15 | 2012-05-21 | 5500.00 | c      |
+------+------+-------+---------+--------------+------------+---------+--------+
5 rows in set (0.00 sec)


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

C1. Write a cursor to find the confirmed bookings of all the train on 2009-10-15.

mysql> delimiter //
mysql> create procedure pp1(out reserve integer)
    -> begin
    -> declare s integer;
    -> declare cus cursor for select res_bogies from train,ticket where train.tno=ticket.tno and date='2009-10-15';
    -> open cus;
    -> fetch cus into s;
    -> set
    -> reserve=s;
    -> close cus;
    -> end
    -> //
Query OK, 0 rows affected (0.03 sec)

mysql> call pp1(@a);
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
    -> //
+------+
| @a   |
+------+
| 30   |
+------+
1 row in set (0.00 sec)


C2. Write a cursor to find the total number of berths not reserved for all the trains on 2008-09-09.

mysql> delimiter //
mysql> create procedure pp2(out rs integer)
    -> begin
    -> declare s1 integer;
    -> declare a integer;
    -> declare cus1 cursor for select sum(no_of_births) from train,ticket where ticket.tno=train.tno and date='2008-09-09';
    -> open cus1;
    -> fetch cus1 into s1;   
    -> close cus1;
    -> select sum(res_bogies) into a from ticket,train where train.tno=ticket.tno and date='2009-10-15';
    -> set
    -> rs=((a*20)-s1);
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call pp2(@rs);
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> select @rs;
    -> //
+------+
| @rs  |
+------+
| 585  |
+------+
1 row in set (0.00 sec)

mysql>


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

T1. Create trigger to validate train arrival time must be less than train departure time.

mysql> delimiter //
mysql> create trigger t1 after insert on train for each row
    -> decalre a int;
    -> if (new.atime>new.dtime)
    -> then
    -> select arrival_should_be_before_deprture into a from train where tno=new.tno;
    -> end if;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> insert into train values(5,'DS express','11:40:19','12:55:19','delhi','Pune',15,36);
ERROR 1054 (42522): Unknown colum 'aarival_should_be_before_deprture' in 'field list'


T2. Write a trigger which will be activated before changing the status field in the ticket table.

mysql> delimiter //
mysql> create trigger tt1 before update on ticket for each row
    -> begin
    -> declare a int;
    -> if(old.status='c' and new.status='w')
    -> then
    -> select Cant_make_confirm_ticket_as_waiting into a from ticket where tno=new.tno;
    -> end if;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> update ticket set status='w' where ticno=1900 and tno=1;
    -> //
ERROR 1054 (42522): Unknown colum 'Cant_make_confirm_ticket_as_waiting' in 'field list'

            END CASE STUDY : 6
================================================

Read more

S.Y.BCS MySQL Case Study 5 : POLICY

===================================================
CASE STUDY 5 : POLICY
===================================================



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

mysql> use ganesh_policy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from policy;
Empty set (0.00 sec)


/**** CREATING TABLE:POLICY ****/

mysql>create table policy(pname varchar(20),min_age int,max_age int,maturity_age int,min_sum_asu float,max_sum_asu float);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from policy;
+---------------+---------+---------+--------------+-------------+-------------+
| pname         | min_age | max_age | maturity_age | min_sum_asu | max_sum_asu |
+---------------+---------+---------+--------------+-------------+-------------+
| LIC           |      18 |      45 |           30 |       25000 |      100000 |
| Bajaj Aliance |      20 |      50 |           35 |       35000 |      120000 |
| ICICI         |      18 |      40 |           30 |       25000 |      100000 |
| Jivan Bima    |      20 |      40 |           30 |       30000 |       90000 |
| HDFC          |      18 |      40 |           25 |       30000 |       90000 |
+---------------+---------+---------+--------------+-------------+-------------+
5 rows in set (0.00 sec)


/**** CREATING TABLE:AGENT ****/

mysql>create table agent(ano int primary key,aname varchar(20),licno varchar(10),branch_office  varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> select * from agent;
+-----+-------------------+--------+---------------+
| ano | aname             | licno  | branch_office |
+-----+-------------------+--------+---------------+
| 101 | Mr.Anil Sharma    | LIP501 | Kothrud       |
| 102 | Mr.Satish Thakar  | JBP801 | Baner         |
| 103 | Ms.Megha Kulkarni | BAP102 | Warje         |
| 104 | Ms.Amit Joshi     | LIP505 | Tilak Road    |
| 105 | Mr.Sandeep Mane   | HDP601 | Karvenagar    |
+-----+-------------------+--------+---------------+
5 rows in set (0.00 sec)


/**** CREATING TABLE:CLIENT ****/

mysql>create table client(cid int primary key,cname varchar(20),Birth_Date date,nominee_name varchar(20),rel_w_client varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from client;
+-----+-------------------+------------+--------------+--------------+
| cid | cname             | Birth_Date | nominee_name | rel_w_client |
+-----+-------------------+------------+--------------+--------------+
|   1 | Mr.Prem Ayyar     | 1985-03-16 | Mr.Anup      | Brother      |
|   2 | Mr.Chetan Gada    | 1987-05-06 | Mas.Tanmay   | Son          |
|   3 | Mr.Mahesh Bhide   | 1981-07-13 | Mis.Madhuri  | Doughter     |
|   4 | Mr.Ram Kapoor     | 1979-08-26 | Mas.Rajesh   | Son          |
|   5 | Mr.Shirish Jadhav | 1978-11-15 | Mr.Bharat    | Brother      |
+-----+-------------------+------------+--------------+--------------+
5 rows in set (0.00 sec)

/**** CREATING TABLE: POLICY_DETAILS ****/

mysql>create table policy_details(ano int references agent(ano) ,cid int int references client(cid),pname varchar(20),policy_no int,premium int,policy_date date,sum_ass int,type varchar(3),term int);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from policy_details;
+------+------+---------------+-----------+---------+-------------+---------+------+------+
| ano  | cid  | pname         | policy_no | premium | policy_date | sum_ass | type | term |
+------+------+---------------+-----------+---------+-------------+---------+------+------+
|  101 |    1 | LIC           |      9801 |    1000 | 2000-10-13  |  100000 | Y    |   10 |
|  102 |    2 | HDFC          |      1203 |     500 | 1998-11-26  |   90000 | HY   |   13 |
|  103 |    3 | Bajaj Aliance |      2405 |    1000 | 1992-11-26  |  120000 | Y    |   20 |
|  104 |    4 | ICICI         |      6405 |    1000 | 1997-05-21  |  110000 | HY   |   15 |
|  105 |    5 | Jivan Bima    |      3013 |     500 | 2005-02-22  |   30000 | HY   |    5 |
+------+------+---------------+-----------+---------+-------------+---------+------+------+
5 rows in set (0.00 sec)


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

Q1.Count no. of clients who have taken policies from branch office 'Baner'.

mysql> select count(cname) as client from client,policy,agent,policy_details where client.cid=policy_details.cid and policy.pname=policy_details.pname and agent.ano=policy_details.ano and branch_office="Baner";
+--------+
| client |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)


Q2.Give name of agent having maximum count of clients.

mysql> select agent.aname from agent,client,policy_details where agent.ano=policy_details.ano and client.cid=policy_details.cid and client.cid in(select max(client.cid)=count(client.cid));
+-------------------+
| aname             |
+-------------------+
| Mr.Anil Sharma    |
| Mr.Satish Thakar  |
| Ms.Megha Kulkarni |
| Ms.Amit Joshi     |
| Mr.Sandeep Mane   |
+-------------------+
5 rows in set (0.00 sec)


Q3.Find name of clients who have taken 'Jivan Bima' policy and premium is half yearly.

mysql> select cname from client,policy_details where client.cid=policy_details.cid and pname="Jivan Bima" and type="HY" and policy_date like "%-02-22";
+-------------------+
| cname             |
+-------------------+
| Mr.Shirish Jadhav |
+-------------------+
1 row in set, 1 warning (0.00 sec)


Q4.Count no. of clients of 'HDFC'policies from 'Baner' branch office.

mysql> select count(cid) from policy_details,agent where policy_details.ano=agent.ano and pname="HDFC" and branch_office="Baner";
+------------+
| count(cid) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)


Q5.Find out total premium amount of client 'Mr.Prem Ayyar'.

mysql> select sum(premium) from client,policy_details where client.cid=policy_details.cid and cname="Mr.Prem Ayyar";
+--------------+
| sum(premium) |
+--------------+
|         1000 |
+--------------+
1 row in set (0.00 sec)


///*********** VIEW ************///


V1. Create view to list the details of all client from branch office 'Warje'.

mysql> create view v1 as select client.cid,client.cname,Birth_Date,nominee_name,rel_w_client from client,agent,policy_details where agent.ano=policy_details.ano and client.cid=policy_details.cid and branch_office="Warje";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v1;
+-----+-----------------+------------+--------------+--------------+
| cid | cname           | Birth_Date | nominee_name | rel_w_client |
+-----+-----------------+------------+--------------+--------------+
|   3 | Mr.Mahesh Bhide | 1981-07-13 | Mis.Madhuri  | Doughter     |
+-----+-----------------+------------+--------------+--------------+
1 row in set (0.00 sec)


V2. Create a view to list the details of all client whose policy date is 1992-11-26.
mysql> create view v2 as select client.cid,client.cname,Birth_Date,nominee_name,rel_w_client,policy_no,policy.pname from client,policy,policy_details where policy.pname=policy_details.pname and client.cid=policy_details.cid and policy_date="1992-11-26";
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v6;
+-----+-----------------+------------+--------------+--------------+-----------+---------------+
| cid | cname           | Birth_Date | nominee_name | rel_w_client | policy_no | pname         |
+-----+-----------------+------------+--------------+--------------+-----------+---------------+
|   3 | Mr.Mahesh Bhide | 1981-07-13 | Mis.Madhuri  | Doughter     |      2405 | Bajaj Aliance |
+-----+-----------------+------------+--------------+--------------+-----------+---------------+
1 row in set (0.00 sec)

///*********** FUNCTION ************///


F1. Write a function to accept  an input parameter a and return the following:
       #."Yearly" if a is 'Y'
       #."Half Yearly" if a is 'HY'
       #."Quarterly" if a is 'Q'.
      
mysql> delimiter //
mysql> create function  f1(a text)
    -> returns text
    -> deterministic
    -> begin
    -> declare r1 text;
    -> if a='Y'
    -> then set r1='yearly';
    -> else if a='HY'
    -> then set r1='Half Yearly';
    -> else
    -> set r1='quarterly';
    -> end if;
    -> end if;
    -> return r1;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> select f1('Y');
    -> //
+---------+
| f1('Y') |
+---------+
| yearly  |
+---------+
1 row in set (0.00 sec)

mysql> select f1('HY');
    -> //
+-------------+
| f1('HY')    |
+-------------+
| Half Yearly |
+-------------+
1 row in set (0.00 sec)

mysql> select f1('q');
    -> //
+-----------+
| f1('q')   |
+-----------+
| quarterly |
+-----------+
1 row in set (0.00 sec)


F2. Write a function to print the name of the client whose sum assured is maximum among all the kinds of policy="LIC"(Assume only one client with maximum sum assured.)

mysql> delimiter //
mysql> create function ccc(a text)                    
    -> returns text
    -> deterministic
    -> begin
    -> declare b text;
    -> select cname into b from client,policy,policy_details where client.cid=policy_details.cid and policy.pname=policy_details.pname and policy_details.pname=a and sum_ass in (select max(sum_ass) from policy_details where policy_details.pname=a);
    -> return b;
    -> end
    -> //
   
   mysql> select hk("LIC");
    -> //
+---------------+
| hk("LIC")     |
+---------------+
| Mr.Prem Ayyar |
+---------------+
1 row in set (0.00 sec)


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

P1. Create  a procedure to update the premium of policy number of a client. If the premium amount axceeds the maximum sum assured of the policy,rollback the transaction else commit.

mysql> delimiter //
mysql> create procedure pr(in a int,in b int,out c text,out premi int)
    -> begin
    -> declare e int;
    -> declare m1 text;
    -> declare m2 text;
    -> set m1='Premium was updated';
    -> set m2='Premium was not update transaction is rollbacked';
    -> select sum_ass into e from policy_details where policy_details.policy_no=a;
    -> if b<=e
    -> then set c=m1;
    -> update policy_details set policy_details.premium=b where policy_no=a;
    -> else
    -> set c=m2;
    -> end if;
    -> select premium into premi from policy_details where policy_no=a;
    -> end
    -> //
Query OK, 0 rows affected (0.03 sec)

mysql> call pr(9801,1000,@c,@premi);
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> select @c,@premi;
    -> //
+---------------------+--------+
| @c                  | @premi |
+---------------------+--------+
| Premium was updated | 1000   |
+---------------------+--------+
1 row in set (0.00 sec)

mysql> call pr(9801,100000,@c,@premi);
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> select @c,@premi;
    -> //
+---------------------+--------+
| @c                  | @premi |
+---------------------+--------+
| Premium was updated | 100000 |
+---------------------+--------+
1 row in set (0.00 sec)


P2. Write a procedure to print the names of agents who have more than 2 policies.

mysql> delimiter //
mysql> create procedure c11(in y int,out a text)
    -> begin
    -> select distinct aname,count(policy_details.cid)>=2 from agent,policy_details,client where agent.ano=policy_details.ano and client.cid=policy_details.cid and policy_date like "2000%" group by aname;
    -> end
    -> //
Query OK, 0 rows affected (0.03 sec)
mysql> call loc(2000,@a);
    -> //
+----------------+------------------------------+
| aname          | count(policy_details.cid)>=2 |
+----------------+------------------------------+
| Mr.Anil Sharma |                            0 |
+----------------+------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)


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

C1. Write a procedure that usase cursor to update the type of premium field of relationship using the function already created.

mysql> delimiter //                   
mysql> create procedure prot(in p text)
    -> begin
    -> declare a text;
    -> declare a1 text;
    -> declare seed1 cursor for      
    -> select type from policy_details;
    -> if p='Y'
    -> then
    -> set a='Yearly';
    -> update policy_details set type='Yearly' where type='Y';
    -> elseif p='HY'
    -> then
    -> set a='Half Yearly';
    -> update policy_details set type='Half Yearly' where type='HY';
    -> end if;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> select prot('Y');
    -> //
ERROR 1305 (42000): FUNCTION ganesh_policy.prot does not exist
mysql> select f1('Y');
    -> //
+---------+
| f1('Y') |
+---------+
| yearly  |
+---------+
1 row in set (0.01 sec)

mysql> select f1('HY');
    -> //
+-------------+
| f1('HY')    |
+-------------+
| Half Yearly |
+-------------+
1 row in set (0.00 sec)


C2.  Write a procedure using cursor to transfer all the client of cid=1 and 5 delete all records of cid 1 from all the tables.

mysql> delimiter //
mysql> create procedure prot2(out a int,out b int,out c int,out d char,out e float,out f date,out g char,out h float,out i int)
    -> begin
    -> declare a1,a2,a4,a9 int;
    -> declare a3,a7 text;
    -> declare a5,a8 float;
    -> declare a6 date;
    -> declare sh cursor for
    -> select * from policy_details where ano=111;
    -> update agent,policy_details set agent.ano=111,policy_details.ano=112 where agent.ano=101 and policy_details.ano=101;
    -> open co;
    -> fetch co into a1,a2,a3,a4,a5,a6,a7,a8,a9;
    -> set a=a1,b=a2,c=a3,d=a4,e=a5,f=a6,g=a7,h=a8,i=a9;
    -> close co;
    -> end
    -> //
   
mysql> call prot1(select @a1,@a2,@a3,@a4,@a5,@a6,@a7,@a8,@a9);

mysql> @a1 pno,@a2 cid,@a3 pname,@a4 policy_no,@a5 premium,@a6 policy-date,@a7 sum_ass,@a8 type,@a9 term;
+------+------+---------------+-----------+---------+-------------+---------+------+------+
| ano  | cid  | pname         | policy_no | premium | policy_date | sum_ass | type | term |
+------+------+---------------+-----------+---------+-------------+---------+------+------+
|  112 |    1 | LIC           |      9801 |  100000 | 2000-10-13  |  100000 | Y    |   10 |
+------+------+---------------+-----------+---------+-------------+---------+------+------+
1 row in set (0.00 sec)



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


T1. Write a trigger on relationship table before insert to implement check constraint on type field. The type should be (HY,Y,Q).

mysql> delimiter //
mysql> create trigger tr1 before insert on policy_details for each row
    -> begin
    -> declare d int;
    -> if new.type='HY' or new.type='Y' or new.type='Q'
    -> then
    -> set d=1
    -> else select The_type_should_be_either_HY_or_Y_or_Q into d from policy_details where policy_no=new.policy_no;
    -> end if;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> insert into policy_details values(106,6,'LIC',9855,10000,'2005-10-12','m',100050,16);
    -> //
ERROR 1054 (42S22): Unknown column 'The_type_should_be_either_HY_or_Y_or_Q' in 'field list'
mysql>

            END CASE STUDY : 5
================================================

Read more

S.Y.BCS MySQL Case Study 4 : REAL ESTATE

===================================================
CASE STUDY 4 : REAL ESTATE
===================================================




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

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

mysql> use ganesh_realES;
Database changed

/**** CREATING TABLE : AGENT ****/

mysql> create table agent(aid int,aname varchar(20),address varchar(20),contact varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from agent;
+------+---------+------------+------------+
| aid  | aname   | address    | contact    |
+------+---------+------------+------------+
|    1 | vinod   | warje      | 9088231455 |
|    2 | mahesh  | karvenagar | 9028445811 |
|    3 | amir    | kothrud    | 8711372010 |
|    4 | amit    | poud road  | 9028550411 |
|    5 | sharman | tilak road | 8727584458 |
+------+---------+------------+------------+
5 rows in set (0.00 sec)

/**** CREATING TABLE : ESTATE****/

mysql> create table estate(eno int,type varchar(20),location varchar(20),price int);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from estate;
+------+------+--------------+--------+
| eno  | type | location     | price  |
+------+------+--------------+--------+
|    1 | 1BHK | F.C Road     | 120000 |
|    2 | 2BHK | Sinhgad Road |  80000 |
|    3 | Land | Malvadi      | 120000 |
|    4 | 3BHK | Pashan Area  | 115000 |
|    5 | Land | Baner        | 130000 |
+------+------+--------------+--------+
5 rows in set (0.00 sec)

/**** CREATING TABLE : CUSTOMER****/

mysql> create table customer(custID int,cname varchar(20),c_contact varchar(10),c_address varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from customer;
+--------+---------------+------------+--------------+
| custID | cname         | c_contact  | c_address    |
+--------+---------------+------------+--------------+
|      1 | Ashok Khade   | 9028552365 | Kothrud      |
|      2 | Uday Mane     | 9020555065 | Karvenagar   |
|      3 | Mahesh Sharma | 9077555065 | Shivajinagar |
|      4 | Anand Joshi   | 9080545165 | Warje        |
|      5 | Abdul Khan    | 9088545155 | Pashan       |
+--------+---------------+------------+--------------+
5 rows in set (0.01 sec)

/**** CREATING TABLE : TRANSACTION****/

mysql> create table transaction(aid int references agent(aid),eno int references estate(eno),custID int references customer(custID),pur_date date,commission int);
Query OK, 0 rows affected (0.07 sec)

mysql> select * from transaction;
+------+------+--------+------------+------------+
| aid  | eno  | custID | pur_date   | commission |
+------+------+--------+------------+------------+
|    1 |    4 |      5 | 2011-04-03 |      25000 |
|    3 |    2 |      4 | 2009-05-03 |      20000 |
|    4 |    3 |      2 | 2008-06-08 |      50000 |
|    2 |    5 |      1 | 2008-07-09 |      53000 |
|    5 |    1 |      3 | 2012-12-10 |      20000 |
+------+------+--------+------------+------------+
5 rows in set (0.00 sec)


//*********QUERIES********//
Q1.Find the name of agent with their contact number who have sold land in Baner area location.

mysql> select aname,contact from agent,estate,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and type="Land"and location="Baner";
+--------+------------+
| aname  | contact    |
+--------+------------+
| mahesh | 9028445811 |
+--------+------------+
1 row in set (0.00 sec)


Q2.Give the agent wise list of customers.

mysql> select agent.aid,agent.aname,customer.custID,customer.cname from agent,customer,transaction where agent.aid=transaction.aid and customer.custID=transaction.custID group by(agent.aid);
+------+---------+--------+---------------+
| aid  | aname   | custID | cname         |
+------+---------+--------+---------------+
|    1 | vinod   |      5 | Abdul Khan    |
|    2 | mahesh  |      1 | Ashok Khade   |
|    3 | amir    |      4 | Anand Joshi   |
|    4 | amit    |      2 | Uday Mane     |
|    5 | sharman |      3 | Mahesh Sharma |
+------+---------+--------+---------------+
5 rows in set (0.00 sec)


Q3.Find  total commission earned by amir by selling 2BHK flat.

mysql> select sum(commission) as total_commission from agent,estate,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and agent.aname="amir" and estate.type="2BHK";
+------------------+
| total_commission |
+------------------+
|            20000 |
+------------------+
1 row in set (0.00 sec)


Q4.Give the name of customers who have purchased an estate  in the  same area in which they are living

mysql> select customer.cname from customer,agent,transaction where agent.aid=transaction.aid and customer.custID=transaction.custID and customer.c_address="Warje";
+-------------+
| cname       |
+-------------+
| Anand Joshi |
+-------------+
1 row in set (0.00 sec)


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

V.1 : Create a view of all real estate which are in "malvadi" area and which are not sold.

mysql> create view vb as select e.eno,e.type,e.location,e.price from estate as e where e.location="Malvadi";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from vb;
+------+------+----------+--------+
| eno  | type | location | price  |
+------+------+----------+--------+
|    3 | Land | Malvadi  | 120000 |
+------+------+----------+--------+
1 row in set (0.00 sec)


V2 : Create view which contain details of all estate sold by vinod and also details of all customers who have bought the estate. 

mysql> create view vc as select estate.eno,aname,location,price,customer.cname,address,agent.contact from estate,transaction,agent,customer where estate.eno=transaction.eno and agent.aid=transaction.aid and customer.custID=transaction.custID and aname="vinod";
Query OK, 0 rows affected (0.00 sec)

mysql> select * from vc
    -> ;
+------+-------+-------------+--------+------------+---------+------------+
| eno  | aname | location    | price  | cname      | address | contact    |
+------+-------+-------------+--------+------------+---------+------------+
|    4 | vinod | Pashan Area | 115000 | Abdul Khan | warje   | 9088231455 |
+------+-------+-------------+--------+------------+---------+------------+
1 row in set (0.00 sec)


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

F1.Write a function to calculate  the total price of estates the perticular agent had sold.

mysql> delimiter //
mysql> create function f1(p text)
    -> returns text
    -> deterministic
    -> begin
    -> declare a int;
    -> select sum(estate.price) into a from estate,agent,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and aname=p and agent.aid in (agent.aid,transaction.aid);
    -> return a;
    -> end
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> select f1('vinod');
    -> //
+-------------+
| fs('vinod') |
+-------------+
| 115000      |
+-------------+
1 row in set (0.00 sec)


F2.Write a function which will the location from which maz estate are sold.
mysql> delimiter //
mysql> create function f2()
    -> returns text
    -> deterministic
    -> begin
    -> declare a text;
    -> select estate.location into a from estate,transaction where estate.eno=transaction.eno and transaction.eno in(estate.eno,transaction.eno) group by location limit 0,1;
    -> return a;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> select f2();
    -> //
+-------+
| f2()  |
+-------+
| Baner |
+-------+
1 row in set (0.00 sec)


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

P.1 : Write a procedure which will list all estate type with agent name on perticular location(accept location as an input parameter) .

mysql> delimiter //  
mysql> create procedure pro1(in a text,out b text,out c text)
    -> begin
    -> select agent.aname,estate.type from agent,estate,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and estate.location=a;
    -> end
    -> //
Query OK, 0 rows affected (0.02 sec)

mysql> call pro1("F.C Road",@b,@c);
    -> //
+---------+------+
| aname   | type |
+---------+------+
| sharman | 1BHK |
+---------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


P.2:Write a procedure which will find the estate having maximum price among each agent type.

mysql> delimiter //
mysql> create procedure pp(in a int,out b int,out c int,out d int)
    -> begin
    -> select estate.eno,agent.aid,estate.price from estate,agent,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and transaction.aid=a and estate.price=(select max(price) from estate,agent,transaction where agent.aid=transaction.aid and estate.eno=transaction.eno and transaction.aid=a group by transaction.aid);
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call pp(2,@b,@c,@d);
    -> //
+------+------+--------+
| eno  | aid  | price  |
+------+------+--------+
|    5 |    2 | 130000 |
+------+------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


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

C.1: Write a procedure which will calculate the total commission earned by each agent.

mysql> delimiter //
mysql> create procedure pro1(out a text,out b float)
    -> begin
    -> declare a1 text;
    -> declare done int default 0;
    -> declare a2 float;
    -> declare com cursor for select aname,sum(transaction.commission) from agent,transaction where agent.aid=transaction.aid group by (aname);
    -> declare continue handler for SQLSTATE'02000'
    -> set done=1;
    -> open com;
    -> repeat
    -> fetch com into a1,a2;
    -> if not done then
    -> select a1,a2;
    -> end if;
    -> until done end repeat;
    -> close com;
    -> end
    -> //
mysql> call pro1(@a,@b);
    -> //
+------+-------+
| a1   | a2    |
+------+-------+
| amir | 20000 |
+------+-------+
1 row in set (0.00 sec)

+------+-------+
| a1   | a2    |
+------+-------+
| amit | 50000 |
+------+-------+
1 row in set (0.00 sec)

+--------+-------+
| a1     | a2    |
+--------+-------+
| mahesh | 53000 |
+--------+-------+
1 row in set (0.00 sec)

+---------+-------+
| a1      | a2    |
+---------+-------+
| sharman | 20000 |
+---------+-------+
1 row in set (0.00 sec)

+-------+-------+
| a1    | a2    |
+-------+-------+
| vinod | 25000 |
+-------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


C.2: Write a pro which will increase the commission by 5% of all agent who have sold Land.

mysql> delimiter //
mysql> create procedure ab()
    -> begin
    -> declare agt text;
    -> declare done int default 0;
    -> declare o_com,n_com float;
    -> declare com2 cursor for select distinct aname,commission from agent,transaction,estate where agent.aid=transaction.aid and estate.eno=estate.eno and estate.type='Land';
    -> declare continue handler for SQLSTATE'02000'
    -> set done=1;
    -> open com2;
    -> repeat
    -> fetch com2 into agt,o_com;
    -> if not done then
    -> select agt,o_com;
    -> end if;
    -> until done end repeat;
    -> close com2;
    -> set done=0;
    -> update transaction,agent,estate set commission=commission+(commission*0.5) where type='1BHK';
    -> open com2;
    -> repeat
    -> fetch com2 into agt,n_com;
    -> if not done then
    -> select agt,n_com;
    -> end if;
    -> until done end repeat;
    -> close com2;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> call ab;
    -> //
+-------+-------+
| agt   | o_com |
+-------+-------+
| vinod | 25000 |
+-------+-------+
1 row in set (0.00 sec)

+------+-------+
| agt  | o_com |
+------+-------+
| amir | 20000 |
+------+-------+
1 row in set (0.00 sec)

+------+-------+
| agt  | o_com |
+------+-------+
| amit | 50000 |
+------+-------+
1 row in set (0.00 sec)

+--------+-------+
| agt    | o_com |
+--------+-------+
| mahesh | 53000 |
+--------+-------+
1 row in set (0.00 sec)

+---------+-------+
| agt     | o_com |
+---------+-------+
| sharman | 20000 |
+---------+-------+
1 row in set (0.00 sec)

+-------+-------+
| agt   | n_com |
+-------+-------+
| vinod | 37500 |
+-------+-------+
1 row in set (0.00 sec)

+------+-------+
| agt  | n_com |
+------+-------+
| amir | 30000 |
+------+-------+
1 row in set (0.00 sec)

+------+-------+
| agt  | n_com |
+------+-------+
| amit | 75000 |
+------+-------+
1 row in set (0.00 sec)

+--------+-------+
| agt    | n_com |
+--------+-------+
| mahesh | 79500 |
+--------+-------+
1 row in set (0.00 sec)

+---------+-------+
| agt     | n_com |
+---------+-------+
| sharman | 30000 |
+---------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


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

T.1: First alter table estate by adding a column status varchar(20) (Initially all status are null).Write a trigger which will fire after insertion on transaction table which will change the status as sold.

mysql> delimiter //
mysql> create trigger bob after insert on transaction for each row
    -> begin
    -> declare a int;
    -> set a=new.eno;
    -> update estate set status='sold' where estate.aid=a;
    -> end
    -> //
mysql> insert into transaction values(2,13,13,'2008-01-01',263.5);
    -> //
   
    mysql> select * from estate;
+------+------+--------------+--------+
| eno  | type | location     | price  |
+------+------+--------------+--------+
|    1 | 1BHK | F.C Road     | 120000 |
|    2 | 2BHK | Sinhgad Road |  80000 |
|    3 | Land | Malvadi      | 120000 |
|    4 | 3BHK | Pashan Area  | 115000 |
|    5 | Land | Baner        | 130000 |
|    6 | Land | Pimpari      | 1000000|
+------+------+--------------+--------+
5 rows in set (0.02 sec)

T.2: Write a trigger which will fire after any estate is deleted from the transaction table it will also delete from the estate table.

mysql> create trigger k1 after delete on transaction for each row
    -> begin
    -> delete from estate where eno=pld.eno;
    -> end
    -> //
mysql> delete from transaction where eno=3;
    -> //
Query OK, 1 rows affected (0.00 sec)
mysql> select * from estate;
    -> //
+------+------+--------------+--------+
| eno  | type | location     | price  |
+------+------+--------------+--------+
|    1 | 1BHK | F.C Road     | 120000 |
|    2 | 2BHK | Sinhgad Road |  80000 |
|    3 | Land | Malvadi      | 120000 |
|    4 | 3BHK | Pashan Area  | 115000 |
|    5 | Land | Baner        | 130000 |
+------+------+--------------+--------+
5 rows in set (0.01 sec)

            END CASE STUDY : 4
================================================

Read more

S.Y.BCS MySQL Case Study 3 : STUDENT COMPETITION

===================================================
CASE STUDY 3 : STUDENT COMPETITION
===================================================



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

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

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

mysql> use ganesh_student
Database changed

/**** CREATING TABLE:STUDENT ****/

mysql> create table student(sno int primary key not null,Class varchar(15),Name varchar(15));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into student values(1,'S.Y.BCS','Anil Marchant');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(2,'F.Y.MCS','Tapan Sharma');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(3,'T.Y.Bcom','Dilip Joshi');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(4,'S.Y.BCA','Anand Khanna');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(5,'T.Y.BA','Amar Gogavale');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+-----+----------+---------------+
| sno | Class    | Name          |
+-----+----------+---------------+
|   1 | S.Y.BCS  | Anil Marchant |
|   2 | F.Y.MCS  | Tapan Sharma  |
|   3 | T.Y.Bcom | Dilip Joshi   |
|   4 | S.Y.BCA  | Anand Khanna  |
|   5 | T.Y.BA   | Amar Gogavale |
+-----+----------+---------------+
5 rows in set (0.00 sec)

/**** CREATING TABLE:COMPETITION ****/

mysql> create table competition(cno int primary key not null,cname varchar(15),ctype varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into competition values(1,'Chess','Indoor');
Query OK, 1 row affected (0.00 sec)

mysql> insert into competition values(2,'Football','Outdoor');
Query OK, 1 row affected (0.00 sec)

mysql> insert into competition values(3,'Billard','Indoor');
Query OK, 1 row affected (0.00 sec)

mysql> insert into competition values(4,'Cricket','Outdoor');
Query OK, 1 row affected (0.00 sec)

mysql> insert into competition values(5,'Swimming','Outdoor');
Query OK, 1 row affected (0.00 sec)

mysql> select * from competition;
+-----+----------+---------+
| cno | cname    | ctype   |
+-----+----------+---------+
|   1 | Chess    | Indoor  |
|   2 | Football | Outdoor |
|   3 | Billard  | Indoor  |
|   4 | Cricket  | Outdoor |
|   5 | Swimming | Outdoor |
+-----+----------+---------+
5 rows in set (0.00 sec)

/**** CREATING TABLE:STUD_COMP ****/

mysql> create table stud_comp(sno int references student(sno),cno int references competition(cno),rank int,year int);Query OK, 0 rows affected (0.00 sec)

mysql> insert into stud_comp values(1,2,1,2012);
Query OK, 1 row affected (0.00 sec)

mysql> insert into stud_comp values(4,1,2,2009);
Query OK, 1 row affected (0.00 sec)

mysql> insert into stud_comp values(3,5,3,2010);
Query OK, 1 row affected (0.00 sec)

mysql> insert into stud_comp values(2,3,3,2012);
Query OK, 1 row affected (0.00 sec)

mysql> insert into stud_comp values(5,4,1,2011);
Query OK, 1 row affected (0.00 sec)

mysql> select * from stud_comp;
+------+------+------+------+
| sno  | cno  | rank | year |
+------+------+------+------+
|    1 |    2 |    1 | 2012 |
|    4 |    1 |    2 | 2009 |
|    3 |    5 |    3 | 2010 |
|    2 |    3 |    3 | 2012 |
|    5 |    4 |    1 | 2011 |
+------+------+------+------+
5 rows in set (0.00 sec)

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

mysql> select ctype,cname from competition,stud_comp where competition.cno=stud_comp.cno;
+---------+----------+
| ctype   | cname    |
+---------+----------+
| Outdoor | Football |
| Indoor  | Chess    |
| Outdoor | Swimming |
| Indoor  | Billard  |
| Outdoor | Cricket  |
+---------+----------+
5 rows in set (0.00 sec)


mysql> select name,rank from student,competition,stud_comp where student.sno=stud_comp.sno and competition.cno=stud_comp.cno and cname='Chess' and rank=2 and year between '2008' and '2012';
+--------------+------+
| name         | rank |
+--------------+------+
| Anand Khanna |    2 |
+--------------+------+
1 row in set (0.00 sec)


mysql> select count(competition.cno),Name,cname from student,competition,stud_comp where student.sno=stud_comp.sno and competition.cno=stud_comp.cno and rank=3 group by Name;
+------------------------+--------------+----------+
| count(competition.cno) | Name         | cname    |
+------------------------+--------------+----------+
|                      1 | Dilip Joshi  | Swimming |
|                      1 | Tapan Sharma | Billard  |
+------------------------+--------------+----------+
2 rows in set (0.00 sec)


mysql> select count(competition.cno) from competition where ctype='Indoor' group by ctype;
+------------------------+
| count(competition.cno) |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.00 sec)


mysql> select Name,cname,ctype from competition,student,stud_comp where student.sno=stud_comp.sno and competition.cno=stud_comp.cno;
+---------------+----------+---------+
| Name          | cname    | ctype   |
+---------------+----------+---------+
| Anil Marchant | Football | Outdoor |
| Anand Khanna  | Chess    | Indoor  |
| Dilip Joshi   | Swimming | Outdoor |
| Tapan Sharma  | Billard  | Indoor  |
| Amar Gogavale | Cricket  | Outdoor |
+---------------+----------+---------+
5 rows in set (0.00 sec)

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

mysql> create view v1 as select cname,ctype from competition order by ctype desc;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v1;
+----------+---------+
| cname    | ctype   |
+----------+---------+
| Football | Outdoor |
| Cricket  | Outdoor |
| Swimming | Outdoor |
| Chess    | Indoor  |
| Billard  | Indoor  |
+----------+---------+
5 rows in set (0.00 sec)


mysql> create view v2 as select Name,Class,cname,rank,year from student,competition,stud_comp where student.sno=stud_comp.sno and competition.cno=stud_comp.cno order by Name desc;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v2;
+---------------+----------+----------+------+------+
| Name          | Class    | cname    | rank | year |
+---------------+----------+----------+------+------+
| Tapan Sharma  | F.Y.MCS  | Billard  |    3 | 2012 |
| Dilip Joshi   | T.Y.Bcom | Swimming |    3 | 2010 |
| Anil Marchant | S.Y.BCS  | Football |    1 | 2012 |
| Anand Khanna  | S.Y.BCA  | Chess    |    2 | 2009 |
| Amar Gogavale | T.Y.BA   | Cricket  |    1 | 2011 |
+---------------+----------+----------+------+------+
5 rows in set (0.00 sec)

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

mysql> delimiter //
mysql> create function f1(b varchar(20))
    -> returns int
    -> deterministic
    -> begin
    -> declare a int;
    -> select count(cno) into a from competition where ctype=b;
    -> return a;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> select f1('Outdoor')
    -> //
+---------------+
| st('Outdoor') |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)


mysql> delimiter //
mysql> create function f2(b varchar(20))
    -> returns int
    -> deterministic
    -> begin
    -> declare a int;
    -> select count(rank) into a from student,stud_comp where student.sno=stud_comp.sno and Name=b and year='2012';
    -> return a;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> select f2('Tapan Sharma');
    -> //
+--------------------+
| su('Tapan Sharma') |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

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

mysql> delimiter //
mysql> create procedure b1()
    -> begin
    -> select count(ctype) from competition where ctype='Indoor';
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> call b1();
    -> //
+--------------+
| count(ctype) |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)


mysql> delimiter //
mysql> create procedure b2(in y int)
    -> begin
    -> select cname from competition,stud_comp where competition.cno=stud_comp.cno and year=y;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> call b2('2012');
    -> //
+----------+
| cname    |
+----------+
| Football |
| Billard  |
+----------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

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

mysql> delimiter //
mysql> create procedure k2(out c_no int,out rnk int)
    -> begin
    -> declare cn int;
    -> declare rn int;
    -> declare cv cursor for select competition.cno,stud_comp.rank from competition,stud_comp where competition.cno=stud_comp.cno and rank=1 or rank=2 and year='2012';
    -> open cv;
    -> fetch cv into cn,rn;
    -> close cv;
    -> set c_no=cn;
    -> set rnk=rn;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> call k2(@c_no,@rnk);
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> select @c_no,@rnk;
    -> //
+-------+------+
| @c_no | @rnk |
+-------+------+
|     1 |    1 |
+-------+------+
1 row in set (0.00 sec)


mysql> delimiter //
mysql> create procedure k1(out c_n int)
    -> begin
    -> declare cn int;
    -> declare cp cursor for select competition.cno from competition,student,stud_comp where competition.cno=stud_comp.cno and student.sno=stud_comp.sno and rank=1 and year='2012' and Class='F.Y.MCS';
    -> open cp;
    -> fetch cp into cn;
    -> close cp;
    -> set c_n=cn;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> call k1(@c_n);
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> select @c_n
    -> //
+------+
| @c_n |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

mysql> create table prize(sno int references student(sno),Win_Prize int);
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> insert into prize(5,3);
    -> //
Query OK, 1 row affected (0.00 sec)

mysql> delimiter //
mysql> create procedure p8(a int)
    -> begin
    -> insert into prize(sno) values(a);
    -> update prize set Win_Prize=Win_Prize+1 where sno=a;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> create trigger t1 after insert on stud_comp for each row
    -> begin
    -> call p8(new.sno);
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> insert into stud_comp values(4,1,1,2006);
    -> //
Query OK, 1 row affected (0.00 sec)

mysql> select * from stud_comp;
     -> //
+------+------+------+------+
| sno  | cno  | rank | year |
+------+------+------+------+
|    1 |    2 |    1 | 2012 |
|    4 |    1 |    2 | 2009 |
|    3 |    5 |    3 | 2010 |
|    2 |    3 |    3 | 2012 |
|    5 |    4 |    1 | 2011 |
|    4 |    1 |    1 | 2006 |
+------+------+------+------+
6 rows in set (0.00 sec)

mysql> select * from prize;
    -> //
+------+-----------+
| sno  | Win_Prize |
+------+-----------+
|    5 |         3 |
|    0 |      NULL |
|    4 |      NULL |
+------+-----------+
3 rows in set (0.00 sec)


mysql> delimiter //
mysql> create trigger t2 before insert on stud_comp for each row
    -> begin
    -> declare s1 int;
    -> if new.year > 2009 then set new.year=2009;
    -> end if;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> insert into stud_comp values(9,2,1,2012);
    -> //
Query OK, 1 row affected (0.00 sec)

mysql> select * from stud_comp;
    -> //
+------+------+------+------+
| sno  | cno  | rank | year |
+------+------+------+------+
|    1 |    2 |    1 | 2012 |
|    4 |    1 |    2 | 2009 |
|    3 |    5 |    3 | 2010 |
|    2 |    3 |    3 | 2012 |
|    5 |    4 |    1 | 2011 |
|    4 |    1 |    1 | 2006 |
|    9 |    2 |    1 | 2009 |
+------+------+------+------+
7 rows in set (0.00 sec)

            END CASE STUDY : 3
================================================

Read more

S.Y.BCS MySQL Case Study 2 : BUS & DRIVERS

==========================================================
CASE STUDY 2 : BUS & DRIVERS
==========================================================




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

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

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

mysql> use ganesh_bus;
Database changed

/**** CREATING TABLE:ROUTE ****/

mysql> create table route(rno int primary key,source varchar(20),destination varchar(20),no_of_stops int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into route values(1,'Warje','Hadapsar',8);
Query OK, 1 row affected (0.00 sec)

mysql> insert into route values(2,'Kothrud','Malvadi',4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into route values(3,'Karvenagar','Poud Phata',6);
Query OK, 1 row affected (0.00 sec)

mysql> insert into route values(4,'Gandhibhavan','Pune Camp',11);
Query OK, 1 row affected (0.00 sec)

mysql> insert into route values(5,'Sarasbag','Warje',10);
Query OK, 1 row affected (0.00 sec)

mysql> select * from route;
+-----+--------------+-------------+-------------+
| rno | source       | destination | no_of_stops |
+-----+--------------+-------------+-------------+
|   1 | Warje        | Hadapsar    |           8 |
|   2 | Kothrud      | Malvadi     |           4 |
|   3 | Karvenagar   | Poud Phata  |           6 |
|   4 | Gandhibhavan | Pune Camp   |          11 |
|   5 | Sarasbag     | Warje       |          10 |
+-----+--------------+-------------+-------------+
5 rows in set (0.00 sec)

/**** CREATING TABLE:BUS ****/

mysql> create table bus(bno int primary key,bcapacity int,bdepo varchar(20),rno int references route(rno));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bus values(1,90,'Kachara Depo',4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bus values(2,60,'Kothrud Depo',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bus values(3,60,'Swarget Depo',5);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bus values(4,60,'Malvadi Depo',3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bus values(5,90,'Galinde Path',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bus;
+-----+-----------+--------------+------+
| bno | bcapacity | bdepo        | rno  |
+-----+-----------+--------------+------+
|   1 |        90 | Kachara Depo |    4 |
|   2 |        60 | Kothrud Depo |    2 |
|   3 |        60 | Swarget Depo |    5 |
|   4 |        60 | Malvadi Depo |    3 |
|   5 |        90 | Galinde Path |    1 |
+-----+-----------+--------------+------+
5 rows in set (0.00 sec)

/**** CREATING TABLE:DRIVER ****/

mysql> create table driver(dno int primary key,dname varchar(20),licno varchar(10),address varchar(20),age int,salary double(10,2));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into driver values(1,'Mr.Mane','BMHD1011','Shivajinagar',55,20000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into driver values(2,'Mr.Jadhav','BMHD2131','Warje',41,15000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into driver values(3,'Mr.Kamathe','BMHD3341','Poud Road',39,12000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into driver values(4,'Mr.More','BMHD4139','Tilak Road',35,13000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into driver values(5,'Mr.Shinde','BMHD5091','Hadapsar',44,17000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from driver;
+-----+------------+----------+--------------+------+----------+
| dno | dname      | licno    | address      | age  | salary   |
+-----+------------+----------+--------------+------+----------+
|   1 | Mr.Mane    | BMHD1011 | Shivajinagar |   55 | 20000.00 |
|   2 | Mr.Jadhav  | BMHD2131 | Warje        |   41 | 15000.00 |
|   3 | Mr.Kamathe | BMHD3341 | Poud Road    |   39 | 12000.00 |
|   4 | Mr.More    | BMHD4139 | Tilak Road   |   35 | 13000.00 |
|   5 | Mr.Shinde  | BMHD5091 | Hadapsar     |   44 | 17000.00 |
+-----+------------+----------+--------------+------+----------+
5 rows in set (0.00 sec)

/**** CREATING TABLE:BUS_DRIVER ****/

mysql> create table bus_driver(bno int references bus(bno),dno int references driver(dno),Date_of_Joint date,Shift int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bus_driver values(1,2,'2002-02-15',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bus_driver values(3,4,'2006-05-27',3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bus_driver values(4,1,'1995-08-21',3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bus_driver values(5,3,'2010-07-09',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into bus_driver values(2,5,'1998-10-03',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bus_driver;
+------+------+---------------+-------+
| bno  | dno  | Date_of_Joint | Shift |
+------+------+---------------+-------+
|    1 |    2 | 2002-02-15    |     2 |
|    3 |    4 | 2006-05-27    |     3 |
|    4 |    1 | 1995-08-21    |     3 |
|    5 |    3 | 2010-07-09    |     2 |
|    2 |    5 | 1998-10-03    |     1 |
+------+------+---------------+-------+
5 rows in set (0.00 sec)

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

mysql> select dname from driver,bus_driver where driver.dno=bus_driver.dno and shift='2';
+------------+
| dname      |
+------------+
| Mr.Jadhav  |
| Mr.Kamathe |
+------------+
2 rows in set (0.02 sec)


mysql> select * from route,bus where route.rno=bus.rno and bcapacity='60';
+-----+------------+-------------+-------------+-----+-----------+--------------+------+
| rno | source     | destination | no_of_stops | bno | bcapacity | bdepo        | rno  |
+-----+------------+-------------+-------------+-----+-----------+--------------+------+
|   2 | Kothrud    | Malvadi     |           4 |   2 |        60 | Kothrud Depo |    2 |
|   5 | Sarasbag   | Warje       |          10 |   3 |        60 | Swarget Depo |    5 |
|   3 | Karvenagar | Poud Phata  |           6 |   4 |        60 | Malvadi Depo |    3 |
+-----+------------+-------------+-------------+-----+-----------+--------------+------+
3 rows in set (0.00 sec)


mysql> select dname,licno from driver,bus_driver where driver.dno=bus_driver.dno and Date_of_Joint="2002-02-15";
+-----------+----------+
| dname     | licno    |
+-----------+----------+
| Mr.Jadhav | BMHD2131 |
+-----------+----------+
1 row in set (0.00 sec)


mysql> delete from route where no_of_stops<6;
Query OK, 1 row affected (0.02 sec)
mysql> select * from route;
+-----+--------------+-------------+-------------+
| rno | source       | destination | no_of_stops |
+-----+--------------+-------------+-------------+
|   1 | Warje        | Hadapsar    |           8 |
|   3 | Karvenagar   | Poud Phata  |           6 |
|   4 | Gandhibhavan | Pune Camp   |          11 |
|   5 | Sarasbag     | Warje       |          10 |
+-----+--------------+-------------+-------------+
4 rows in set (0.00 sec)


mysql> select bus.bno,route.rno from bus,route where route.rno=bus.rno and source='Sarasbag' and destination='Warje';
+-----+-----+
| bno | rno |
+-----+-----+
|   3 |   5 |
+-----+-----+
1 row in set (0.00 sec)


mysql> select count(bus.bno),route.rno from bus,route where route.rno=bus.rno and source='Sarasbag' and destination='Warje';
+----------------+-----+
| count(bus.bno) | rno |
+----------------+-----+
|              1 |   5 |
+----------------+-----+
1 row in set (0.00 sec)


mysql> select count(bus.bno) from bus,route where route.rno=bus.rno and source='Sarasbag' and destination='Warje';+----------------+
| count(bus.bno) |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)


mysql> update driver set salary=salary+1500 where age>40;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
mysql> select * from driver;
+-----+------------+----------+--------------+------+----------+
| dno | dname      | licno    | address      | age  | salary   |
+-----+------------+----------+--------------+------+----------+
|   1 | Mr.Mane    | BMHD1011 | Shivajinagar |   55 | 21500.00 |
|   2 | Mr.Jadhav  | BMHD2131 | Warje        |   41 | 16500.00 |
|   3 | Mr.Kamathe | BMHD3341 | Poud Road    |   39 | 12000.00 |
|   4 | Mr.More    | BMHD4139 | Tilak Road   |   35 | 13000.00 |
|   5 | Mr.Shinde  | BMHD5091 | Hadapsar     |   44 | 18500.00 |
+-----+------------+----------+--------------+------+----------+
5 rows in set (0.00 sec)


mysql> select bno from bus,route where route.rno=bus.rno and source='Warje' and destination='Hadapsar' and bcapacity='60';
Empty set (0.00 sec)

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

mysql> create view v1 as select bus.bno,driver.dname,licno,address,age,salary from bus,driver,bus_driver where driver.dno=bus_driver.dno and bus.bno=bus_driver.bno and bus.bno='4';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v1;
+-----+---------+----------+--------------+------+----------+
| bno | dname   | licno    | address      | age  | salary   |
+-----+---------+----------+--------------+------+----------+
|   4 | Mr.Mane | BMHD1011 | Shivajinagar |   55 | 21500.00 |
+-----+---------+----------+--------------+------+----------+
1 row in set (0.00 sec)


mysql> create view v2 as select route.rno,source,destination,no_of_stops,bus.bno from route,bus where route.rno=bus.rno and source='Sarasbag' and destination='Warje';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v2;
+-----+----------+-------------+-------------+-----+
| rno | source   | destination | no_of_stops | bno |
+-----+----------+-------------+-------------+-----+
|   5 | Sarasbag | Warje       |          10 |   3 |
+-----+----------+-------------+-------------+-----+
1 row in set (0.00 sec)

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

mysql> delimiter //
mysql> create function bus1()
    -> returns varchar(20)
    -> deterministic
    -> begin
    -> declare a varchar(20);
    -> select dname into a from driver where salary in(select max(salary) from driver);
    -> return a;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> select bus1();
    -> //
+---------+
| bus1()  |
+---------+
| Mr.Mane |
+---------+
1 row in set (0.00 sec)


mysql> delimiter //
mysql> create function bus2(b int,date date)
    -> returns varchar(20)
    -> deterministic
    -> begin
    -> declare a varchar(20);
    -> select dname into a from driver,bus_driver,bus where driver.dno=bus_driver.dno and bus.bno=b and date=Date_of_Joint;
    -> return a;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> select bus2(4,'2010-07-03');
    -> //
+----------------------+
| bus2(4,'2010-07-03') |
+----------------------+
| NULL                 |
+----------------------+
1 row in set, 1 warning (0.00 sec)

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

mysql> delimiter //
mysql> create procedure b1(in d date)
    -> begin
    -> select dname,shift from driver,bus_driver where driver.dno=bus_driver.dno and Date_of_Joint=d and shift between 1 and 2;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> call b1('1998-10-03');
    -> //
+-----------+-------+
| dname     | shift |
+-----------+-------+
| Mr.Shinde |     1 |
+-----------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)


mysql> create procedure b2(in d date)
    -> begin
    -> select bus.bno,driver.dno from bus,driver,bus_driver,route where bus_driver.dno=driver.dno and bus.bno=bus_driver.bno and route.source='Gandhibhavan'and route.destination='Pune Camp' and Date_of_Joint=d;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> call b2('2002-02-15');
    -> //
+-----+-----+
| bno | dno |
+-----+-----+
|   1 |   2 |
+-----+-----+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

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

mysql> delimiter //
mysql> create procedure p1(out r_no int,out de varchar(20),out src varchar(20),out st int)
    -> begin
    -> declare rn int;
    -> declare d varchar(20);
    -> declare s varchar(20);
    -> declare st1 int;
    -> declare c1 cursor for select * from route where route.rno=4;
    -> open c1;
    -> fetch c1 into rn,d,s,st1;
    -> close c1;
    -> set r_no=rn;
    -> set de=d;
    -> set src=s;
    -> set st=st1;
    -> end
    -> //
mysql> call p1(@r_no,@d,@s,@st1);
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> select @r_no,@d,@s,@st1;
    -> //
+-------+--------------+-----------+------+
| @r_no | @d           | @s        | @st1 |
+-------+--------------+-----------+------+
|     4 | Gandhibhavan | Pune Camp |   11 |
+-------+--------------+-----------+------+
1 row in set (0.00 sec)

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

mysql> delimiter //
mysql> create trigger t9 after insert on driver for each row
    -> begin
    -> declare a int;
    -> if(new.age<18 or new.age>50) then select invalid_entery into a from driver where driver.dno=new.dno;
    -> else
    -> select valid_entry into a from driver where driver.dno=new.dno;
    -> end if;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> insert into driver values(6,'Mr.Tare','BMHD1116','Shivane',17,7000);
    -> //
ERROR 1054 (42S22): Unknown column 'invalid_entery' in 'field list'
mysql> insert into driver values(7,'Mr.Tare','BMHD1116','Shivane',49,7000);
    -> //
ERROR 1054 (42S22): Unknown column 'valid_entry' in 'field list'
mysql> select * from driver;
    -> //
+-----+------------+----------+--------------+------+----------+
| dno | dname      | licno    | address      | age  | salary   |
+-----+------------+----------+--------------+------+----------+
|   1 | Mr.Mane    | BMHD1011 | Shivajinagar |   55 | 21500.00 |
|   2 | Mr.Jadhav  | BMHD2131 | Warje        |   41 | 16500.00 |
|   3 | Mr.Kamathe | BMHD3341 | Poud Road    |   39 | 12000.00 |
|   4 | Mr.More    | BMHD4139 | Tilak Road   |   35 | 13000.00 |
|   5 | Mr.Shinde  | BMHD5091 | Hadapsar     |   44 | 18500.00 |
|   6 | Mr.Tare    | BMHD1116 | Shivane      |   17 |  7000.00 |
|   7 | Mr.Tare    | BMHD1116 | Shivane      |   49 |  7000.00 |
+-----+------------+----------+--------------+------+----------+
7 rows in set (0.00 sec)

                        END CASE STUDY : 2
================================================================

Read more