Write a SQL statement that answers the question: What were the total damages for

Question

Write a SQL statement that answers the question:

What were the total damages for each property?

List the properties in Austelle that were damaged.

What properties were damaged (street address), what were the damages (description) and what was the amount of the damage?

Which property (by address) had damages from flooding on the first floor?

List all property street addresses and show the description of the damage for those that had damage.

Create a View to show street address and city of properties that had damage to their floors (wet carpet or buckled floor).  

List the damages sorted by location (where the damage was located not where the property was located).

PROPERTY Austelle Austelle 1000 Chastain Road Kennesaw GA 1008 Chastain Road Kennesaw GA 1 29 Town Parkway Woodstock GA DAMAGE structural damage 7,800.00

Explanation / Answer

Hi,

Please find below the answer-

Ans 1- select LotNo, sum(DamageAmt) from DamageReport group by LotNo;

Ans 2- select a.LotNo,a.StreetAddress,a.City from Property a
join Damage b
on a.LotNo=b.LotNo
and City='Austelle';

Ans 3-
Ans a) select a.StreetAddress,b.description, c.DamageAmt from Property a
join Damage b
on a.LotNo=b.LotNo
join DamageReport c
on b.did=c.did;

Ans 4 - select b.description from Property a
join Damage b
on a.LotNo=b.LotNo
and b.location='first floor'
and source='flood'

Ans 5- select a.StreetAddress,b.description from Property a
join Damage b
on a.LotNo=b.LotNo

Ans 6- Create view Vw1 as
select a.StreetAddress,a.city from Property a
join Damage b
on a.LotNo=b.LotNo
and b.description in('wet carpet,'buckled floor');

Ans 7-
select Did,description,location from Damage order by location;

Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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