===================================================
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
================================================
Post a Comment