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