Topic: DATABASE MANAGEMENT SYSTEMS Midterm2017-newdoc Contider the following sch
ID: 3820573 • Letter: T
Question
Topic: DATABASE MANAGEMENT SYSTEMS Midterm2017-newdoc Contider the following schema of Person (SSN Name string integer Hemczip string) Car ILNO Caler string.Make string. Model. Year integer) Ownership ISSN anewer the following questions: l. write a query find out the names and phone numbem ofa people who hrereceived more than 2 tickets in year 101500 3. wria a query salist the names and bome zip codes of alreeple wtoreceived tickets their bome ep code and have more than one car00 4. Conven the following soLstatement into its English equivalent domarstratewhateis query is to accomplish. Selekt Name, Make, From Person Ownenhip Car, Where Person SSN-ewnership SSN And Ownership LNO carLNO Group by Make NameExplanation / Answer
1.select name,phone from Person,Ownership,Ticket where
Ticket.LNO=Ownership.LNO and Person.SSN=Ownership.SSN
and Person.SSN=(select Ticket.SSN from Ticket where Ticket.ticketyear=2015 group by Ticket.SSN Having count(*)>2)
inner query get the information of those persons who have more than 2 tickets in 2015 year then we compare ssn of that with person ssn.ownership is the linked table between person and tickets.and thus by inner join we will get names and phone number.
2. select name,phone from Person,Ownership,Ticket where
Ticket.LNO=Ownership.LNO and Person.SSN=Ownership.SSN
and Person.SSN=(select Ownership.SSN from Ownership groupby Ownership.SSN having count(*)>1)
inner query gives the information of those persons who have more than one car and outer query verified that those persons have ticket
3.select name,HomeZip from Person,Ownership,Ticket where
Ticket.LNO=Ownership.LNO and Person.SSN=Ownership.SSN
and Person.SSN=(select Ownership.SSN from Ownership groupby Ownership.SSN having count(*)>1) and Person.HomeZip=Ticket.ticketcode
last condition verify that homezipcode and ticket code is same
4.
It will give the Name of person,make of car and count of make that a person have