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