Creating Insurance Management System and performing queries using MySql

The insurance management system is a schema of the insurance management database.

This schema consists of a database named insurancedb and various tables are created in it. These tables helps in managing all sorts of data in a systematic way.

By performing different kinds of queries we can easily extract the required data.

First, we have to create the database "insurancedb" :

mysql> CREATE DATABASE insurancedb;

After creating the database, then we have to use that database.:

USE insurancedb;

In the database we have to create different tables:

Creating table address_details

CREATE TABLE address_details
    (
        address_id int primary key,
        h_no varchar(6),
        city varchar(50),
        addressline1 varchar(50),
        state varchar(50),
        pin varchar(50)
     );

Creating table user_details

CREATE TABLE user_details
    (    
        user_id int primary key,
        firstname varchar(50),
        lastname varchar(50),
        email varchar(50),
        mobileno varchar(50),
        address_id int references address_details(address_id),
        dob date
     );

Creating table ref_policy_types

CREATE TABLE ref_policy_types
    (
        policy_type_code varchar(10) primary key,
        policy_type_name varchar(50)
    );

Creating table policy_sub_types

CREATE TABLE policy_sub_types
    (
        policy_type_id varchar(10) primary key,
        policy_type_code varchar(10) references         
        ref_policy_types(policy_type_code),
        description varchar(50),
        yearsofpayements int,
        amount double,
        maturityperiod int,
        maturityamount double,
        validity int
     );

Creating table user_policies

CREATE TABLE user_policies
    (
        policy_no varchar(20) primary key,
        user_id int references user_details(user_id),
        date_registered date,
        policy_type_id varchar(10) references 
        policy_sub_types(policy_type_id)         
    );

Creating table policy_payments

CREATE TABLE policy_payments
    (
        receipno int primary key,
        user_id int references user_details(user_id),
        policy_no varchar(20) references user_policies(policy_no),
        dateofpayment date,
        amount double,
        fine double
    );

After creating all the tables, we have to insert records into these tables:

Insert records into the address_details table:

INSERT INTO address_details values(1, '6-21', 'hyderabad', 'kphb', 'andhra pradesh', 1254);
INSERT INTO address_details values(2, '7-81', 'chennai', 'seruseri', 'tamilnadu', 16354);
INSERT INTO address_details values(3, '3-71', 'lucknow', 'street', 'uttarpradesh', 86451);
INSERT INTO address_details values(4, '4-81', 'mumbai', 'iroli', 'maharashtra', 51246);
INSERT INTO address_details values(5, '5-81', 'bangalore', 'mgroad', 'karnataka', 125465);
INSERT INTO address_details values(6, '6-81', 'ahamadabad', 'street2', 'gujarat', 125423);
INSERT INTO address_details values(7, '9-21', 'chennai', 'sholinganur', 'tamilnadu', 654286);

Insert records into the user_details table:

INSERT INTO user_details values(1111,'raju','reddy','raju@gmail.com','9854261456',4,'1986-4-   11');
INSERT INTO user_details values(2222,'vamsi','krishna','vamsi@gmail.com','9854261463',1,'1990-4-11');
INSERT INTO user_details values(3333,'naveen','reddy','naveen@gmail.com','9854261496',4,'1985-3-14');
INSERT INTO user_details values(4444,'raghava','rao','raghava@gmail.com','9854261412',4,'1985-9-21');
INSERT INTO user_details values(5555,'harsha','vardhan','harsha@gmail.com','9854261445',4,'1992-10-11');

Insert records into the ref_policy_types table:

INSERT INTO ref_policy_types values('58934', 'car');
INSERT INTO ref_policy_types values('58539', 'home');
INSERT INTO ref_policy_types values('58683', 'life');

Insert records into the policy_sub_types table:

INSERT INTO policy_sub_types values('6893','58934','theft',1,5000,null,200000,1);
INSERT INTO policy_sub_types values('6894','58934','accident',1,20000,null,200000,3);
INSERT INTO policy_sub_types values('6895','58539','fire',1,50000,null,500000,3);
INSERT INTO policy_sub_types values('6896','58683','anandhlife',7,50000,15,1500000,null);
INSERT INTO policy_sub_types values('6897','58683','sukhlife',10,5000,13,300000,null);

Insert records into the user_policies table:

INSERT INTO user_policies values('689314',1111,'1994-4-18','6896');
INSERT INTO user_policies values('689316',1111,'2012-5-18','6895');
INSERT INTO user_policies values('689317',1111,'2012-6-20','6894');
INSERT INTO user_policies values('689318',2222,'2012-6-21','6894');
INSERT INTO user_policies values('689320',3333,'2012-6-18','6894');
INSERT INTO user_policies values('689420',4444,'2012-4-09','6896');

Insert records into the policy_payments table:

INSERT INTO policy_payments values(121,4444,'689420','2012-4-09',50000,null);
INSERT INTO policy_payments values(345,4444,'689420','2013-4-09',50000,null);
INSERT INTO policy_payments values(300,1111,'689317','2012-6-20',20000,null);
INSERT INTO policy_payments values(225,1111,'689316','2012-5-18',20000,null);
INSERT INTO policy_payments values(227,1111,'689314','1994-4-18',50000,null);
INSERT INTO policy_payments values(100,1111,'689314','1995-4-10',50000,null);
INSERT INTO policy_payments values(128,1111,'689314','1996-4-11',50000,null);
INSERT INTO policy_payments values(96,1111,'689314','1997-4-18',50000,200);
INSERT INTO policy_payments values(101,1111,'689314','1998-4-09',50000,null);
INSERT INTO policy_payments values(105,1111,'689314','1999-4-08',50000,null);
INSERT INTO policy_payments values(120,1111,'689314','2000-4-05',50000,null);
INSERT INTO policy_payments values(367,2222,'689318','2012-6-21',20000,null);
INSERT INTO policy_payments values(298,3333,'689320','2012-6-18',20000,null);

Performing Queries:

After all creating database, creating tables and then inserting records into them, we have to perform queries on these tables:

Problem#1:

Write a query to display the policytypeid,policytypename,description of all the car’s policy details.

Solution:

SELECT p.policy_type_id, r.policy_type_name, p.description             FROM policy_sub_types p                                                JOIN ref_policy_types r                                                  ON p.policy_type_code = r.policy_type_code                            WHERE r.policy_type_name ='car'; 

Problem#2:

Write a query to display the policytypecode,no of polycies in each code with alias name NO_OF_POLICIES.

Solution:

SELECT policy_type_code, COUNT(policy_type_code) no_of_policies
FROM policy_sub_types
GROUP BY policy_type_code;

Problem#3:

Write a query to display the userid,firstname,lastname, email,mobileno who are residing in Chennai.

Solution:

SELECT ud.user_id, ud.firstname, ud.lastname, ud.email, ud.mobileno
FROM user_details ud                                                   JOIN address_details ad                                                  ON ud.address_id=ad.address_id
WHERE ad.city='hyderabad'
GROUP BY ud.user_id;

Problem#4:

Write a query to display the userid, firstname lastname with alias name USER_NAME,email,mobileno who has taken the car polycies.

Solution:

SELECT ud.user_id, ud.firstname, ud.lastname user_name, ud.email, ud.mobileno
FROM user_details ud                                                   JOIN user_policies up                                                  JOIN policy_sub_types ps                                               JOIN ref_policy_types rf                                                 ON ud.user_id=up.user_id                                                AND up.policy_type_id=ps.policy_type_id                                 AND ps.policy_type_code=rf.policy_type_code
WHERE rf.policy_type_name='car';

Problem#5:

Write a query to display the userid, firstname,last name who has taken the car policies but not home ploicies.

Solution:

SELECT user_id, firstname, lastname                                    FROM USER_DETAILS WHERE USER_ID in
(SELECT USER_ID FROM USER_POLICIES where
POLICY_TYOPE_ID in
(SELECT POLICY_TYPE_ID FROM POLICY_SUB_TYPES
WHERE POLICY_TYPE_CODE=
(SELECT POLICY_TYPE_CODE FROM REF_POLICY_TYPES
WHERE POLICY_TYPE_NAME='CAR')) AND
USER_ID NOT in
(SELECT USER_ID FROM USER_POLICIES where
POLICY_TYOPE_ID in
(SELCT POLICY_TYPE_ID FROM POLICY_SUB_TYPES
WHERE POLICY_TYPE_CODE=
(SELECT POLICY_TYPE_CODE FROM REF_POLICY_TYPES
WHERE POLICY_TYPE_NAME='HOME')))) ;

Problem#6:

Write a query to display the policytypecode, policytype name which policytype has maximum no of policies.

Solution:

SELECT rf.policy_type_code, policy_type_name, COUNT(ps.policy_type_id)
FROM ref_policy_types rf                                               JOIN policy_sub_types ps
ON rf.policy_type_code=ps.policy_type_code
GROUP BY policy_type_name
HAVING COUNT(ps.policy_type_id)=(SELECT COUNT(ps.policy_type_id)       FROM policy_sub_types ps
GROUP BY ps.policy_type_code
ORDER BY COUNT(ps.policy_type_id) DESC
limit 1)OR
COUNT(ps.policy_type_id)=(select count(ps.policy_type_id) from
policy_sub_types ps
GROUP BY ps.policy_type_code
ORDER BY count(ps.policy_type_id) asc
limit 1)
ORDER BY COUNT(ps.policy_type_id);

Problem#7:

Write a query to display the userid, firtsname, lastname, city state whose city is ending with ‘bad’.

Solution:

select ud.user_id,firstname,lastname,ad.city,ad.state from
user_details ud join address_details ad on
ud.address_id=ad.address_id
where ad.city like '%bad';

Problem#8:

Write a query to display the userid, firstname, lastname ,ploicyno, dateregistered who has registered before may 2012.

Solution:

select up.user_id,firstname,lastname,policy_no, date_registered
from user_policies up join
user_details ud on
up.user_id=ud.user_id
where up.date_registered < '2012-05-01';

Problem#9:

Write a query to display the userid, firstname, lastname who has taken more than one policies.

Solution:

select ud.user_id,ud.firstname,ud.lastname from
user_details ud join
user_policies up on
ud.user_id=up.user_id
group by ud.user_id
having count(up.policy_type_id)>1;

Problem#10:

Write a query to display the policytypecode, policytypename, policytypeid, userid, ploicyno whose maturity will fall in the month of august 2013.

Solution:

select rf.policy_type_code,rf.policy_type_name,up.policy_type_id,up.user_id,up.policy_no
from ref_policy_types rf join
policy_sub_types ps join
user_policies up on
rf.policy_type_code=ps.policy_type_code and
up.policy_type_id=ps.policy_type_id
where extract(month from adddate(up.date_registered,interval ps.maturityperiod month))='7'
and extract(year from adddate(up.date_registered,interval ps.maturityperiod month))='2013';