SQL and Relational Algebra Consider the automobile insurance company database we
ID: 3591467 • Letter: S
Question
SQL and Relational Algebra Consider the automobile insurance company database we use for examples in class. For each problem, write an SQL statement to retrieve the specified data. For problems 1-4, also write a relational algebra expression to retrieve the specified data. It is strongly recommended that you test your SQL solutions using the SOLite database available on our MyCourses site 1. The name and ssn of people who live in Boston, MA. 2. The name and ssn of agents who live in Providence, RI. 3. The name and ssn of people who own a 1991 Dodge. 4. The ssn of people who do not own automobiles. 5. For each accident that occurred in 2005, the names of drivers involved and the make and year of the auto they were driving 6. The name and ssn of automobile owners who have never been the driver in any accident. 7. The name and ssn of people involved in accidents while driving a car they do not own. (Remember that cars may have multiple owners.)Explanation / Answer
Ans 1- Select ssn,fname ,lname from people where city= ' Boston' and state ='MA';
Ans 2- select a.ssn, p.fname , lname from agents a join people p
on a.ssn p.ssn where city='Providence' and state='RI'
Ans 3- select p.ssn from people p join owns o on p.ssn=o.ssn join autos a on
o.vin=a.vin and a.year=1991 and a.make='Dodge'
Ans 4- select ssn from people where ssn not in(select ssn from owns);
Ans 5- select strftime('%Y', accident_date) as Year ,p.fname,p.lname ,a.make,a.year from accidents ac join involvements inv on a.aid=inv.aid and strftime('%Y', accident_date)=2005
join autos a on inv.vin=a.vin
join people p on inv.driver_ssn=p.ssn;
Ans 6- select ssn , fname ,lname from people where ssn not in(Select driver_ssn from involvements);
Ans 7- select driver_ssn , fname,lname from involvements inv join people p
on inv.driver_ssn=p.ssn and driver_ssn not in(select ssn from owns);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.