First Creating database and then related tables.
create database insurancedb;
use insurancedb
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));
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);
create table ref_policy_types(policy_type_code varchar(10) primary key,policy_type_name varchar(50));
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);
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) );
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);
DML QUERIES:
insert into address_details values(1,'6-21','hyderabad','kphb','andhrapradesh',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 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 into ref_policy_types values('58934','car');
insert into ref_policy_types values('58539','home');
insert into ref_policy_types values('58683','life');
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 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 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);
Solve the following Queries:
1.Write a query to display the policytypeid,policytypename,description of all the car’s policy details.
2.Write a query to display the policytypecode,no of polycies in each code with alias name NO_OF_POLICIES.
3.Write a query to display the userid,firstname,lastname, email,mobileno who are residing in Chennai.
4.Write a query to display the userid, firstname lastname with alias name USER_NAME,email,mobileno who has taken the car polycies.
5.Write a query to display the userid, firstname,last name who has taken the car policies but not home ploicies.
6.Write a query to display the policytypecode, policytype name which policytype has maximum no of policies.
7.Write a query to display the userid, firtsname, lastname, city state whose city is ending with ‘bad’.
8.Write a query to display the userid, firstname, lastname ,ploicyno, dateregistered who has registered before may 2012.
9.Write a query to display the userid, firstname, lastname who has taken more than one policies.
10.Write a query to display the policytypecode, policytypename, policytypeid, userid, ploicyno whose maturity will fall in the month of august 2013.
11.Write a query to display the policytypecode, policytypename, policytypeid whose maturity amount is the double than the total paid amount.
12.Write a query to display the userid, total amount paid by the customer with alias name total_amount.
13.Write a query to display the user_id, policy_no, total amount paid by the customer for the each policies.
14.Write a query to display the user_id, policy_no, balance_amount for each policies.
15.write a query to display the user_id,policy_no, balancepayment years with alias name BALANCE_YEARS for all the customer for each policies.
16.Write a query to display the user details userid,firstname,last who has taken car, home and life loans.
17.Write a query to select policy_type_code,total amount paid by all the customers with alias name total_amount for each policy department.
18.Write a query to select user_id,user_name,policy_type_code,policy_type_id of users who has registered more than one policy type unde same policy code.
19.Write a query to display the policy_type_code,policytype name in which policy department has min number of policies registered.
20.Write a query to display the user_id,user_name, address,phoneno,policytypecode,policytypeid,policytypename, who has complemented all payements for the policies.
21. write a query to display the user_id, user_name, address,phoneno,policytypecode,policytypeid,policytypename,date ofd register who has registered latest 2.
Solution:
If you need complete solutions of these types data base then please contact at <realcode4you.com>
Here we attached the some solutions:
select * from user_policies;
select *from address_details;
select * from policy_payments;
select * from ref_policy_types;
select * from user_details;
select * from policy_sub_types;
//1//
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';
//2//
select policy_type_code,count(policy_type_code) no_of_policies
from policy_sub_types
group by policy_type_code;
//3//
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;
//4//
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';
//5//
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')))) ;
//6//
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);
//7//
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';
For more help contact us at here
can u provide complete solution of this worksheet