13. List the SSN, first name, and last name of all policy holders who have not h

Question

13. List the SSN, first name, and last name of all policy holders who have not had an accident.

17. List each driver’s first name and last name, along with the policy(ies) that they are insured under.

Note: Except inner or outer joins. You can use any clauses such as Where, between, or, and, not, >, <=, = etc. clauses for these questions.

USE THESE TABLES FOR QUESTIONS 11 THRU 17 POLICY policy registration#, FK: registration# PEOPLE ssn. fname, name, mi, address VEHICLE registration#, year, make, model ACCIDENT accidentH, registration#, ssn, date, payout FK: registration# FK: ssn COVERAGE TYPE coverage Type, description POLICY GE policy# covera deductible, max amount FK: policy FK: coverage Type PEOPLE POLICY SSn FK: ssn FK: policy

Explanation / Answer

13.

List the SSN, first name, and last name of all policy holders who have not had an accident.

Here to get all the people who are policy holders we need to check if the person is having a policy

for this we need the table PEOPLE_POLICY and also we need to check that the ssn of those people

are not in ACCIDENT table so overall we need 3 tables for this query

1) PEOPLE 2) PEOPLE_POLICY 3) ACCIDENT

Query with out using inner join or outer join key words:-

select people.ssn, people.fname, people.lname from people , people_policy

where people.ssn = people_policy.ssn and people.ssn not in (select ssn from accident);

Explanation of the query :-

select people.ssn, people.fname, people.lname from people , people_policy

where people.ssn = people_policy.ssn (this will give the people having policies) ,

people.ssn not in (select ssn from accident); (this will give the people who have not had an accident.)

17.

List each driver’s first name and last name, along with the policy(ies) that they are insured under.

  Here to get fname , lname and policies we need to use 2 tables and they are PEOPLE and PEOPLE_POLICY

  as fname , lname PEOPLE are in and policies are in PEOPLE_POLICY and also they both have a common

attribute SSN for this purpose the below query is suffice

select people.fname, people.lname, people_policy.policy# from people , people_policy

where people.ssn = people_policy.ssn

  but they asked fname , lname and policies of drivers , a driver should have a vehicle hence an anathor

validation is required here and that is also below

and people_policy.policy# in (select policy# from policy where registration# is not null);

  Hence the overall query will be as below

  select people.fname, people.lname, people_policy.policy# from people , people_policy

where people.ssn = people_policy.ssn and  people_policy.policy# in

(select policy# from policy where registration# is not null);

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.