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
================================================