Use the tables listed at the end of this document to answer the following questi

Question

Use the tables listed at the end of this document to answer the following questions. Please submit the SQL statement for each question.

SQL Commands

After each question, construct the SQL statement that answers the question. When specific attributes are not asked for, you may choose which attributes you wish to display.

1. List all the information about the properties.

2. List information about damages including the street address.

3. What were the different sources of damage (no duplicate reporting)?

4. What was the total amount of damages assessed?

5. What were the total damages for each property?

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

7. How many damages were reported?

8. List the information about the properties whose zip codes start with 301.

9. List the properties in Woodstock that were damaged.

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

11. List the report information for inspectors Jones, Smith, White [use the IN keyword].

12. Which inspector(s) reported total damage amounts >$30,000 for all properties they inspected?

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

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

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

LotNo State StreetAddress 1 Oak Lane 3 Oak Lane 5 Oak Lane 6 Maple Street 23 Maple StreetGreen 1000 Chastain Road Kennesaw 1008 Chastain Road Kennesaw 6 Town Parkway Woodstock 29 Town ParkwayWoodstock 16 Town ParkwayWoodstock Zip County Cobb Cobb Cobb Fulton Fulton Cobb Cobb Cherokee Cherokee Cherokee City Austelle Austelle Austelle Green 001 002 003 029 030 040 042 089 097 099 GA GA GA GA GA GA GA GA GA GA 30897 30897 30897 39676 39676 30144 30144 30176 30176 30176

Explanation / Answer

1. List all the information about the properties.
select * from properties;
(or)
select Did,Description,Location,Source,LotNo from properties;

2. List information about damages including the street address.
select Description,Location,StreetAddress from properties inner join damages on properties.LotNo=damages.LotNo;

3. What were the different sources of damage (no duplicate reporting)?
select distinct sources from damages;

4. What was the total amount of damages assessed?
select sum(DamageAmt) from inspectors inner join damages on inspectors.Did=damages.Did;

5. What were the total damages for each property?
select sum(DamageAmt),Description from inspectors inner join damages on inspectors.Did=damages.Did group by Description;

6. List the damages sorted by location (where the damage was located not where the property was located).
select description,Location from damages order by Location;

7. How many damages were reported?
select count(distinct Description) from damages;

8. List the information about the properties whose zip codes start with 301.
select StreetAddress,City,State,Zip,County from properties where Zip like '301%';

9. List the properties in Woodstock that were damaged.
select Description,City from properties inner join damages on properties.LotNo=damages.LotNo where city='Woodstock';

10. What properties were damaged (street address), what were the damages (description) and what was the amount of the damage?
select Description,StreetAddress,DamageAmt from properties
inner join damages on properties.LotNo=damages.LotNo
inner join inspectors on properties.LotNo=inspectors.LotNo;

11. List the report information for inspectors Jones, Smith, White [use the IN keyword].
select Inspector,ReportDate,DamageAmt from inspectors where Inspector in ('Jones','Smith', 'White');

12. Which inspector(s) reported total damage amounts >$30,000 for all properties they inspected?
select Inspector,sum(DamageAmt) totalAmt from Inspectors group by Inspector where totalAmt>$30000;

13. Which property (by address) had damages from flooding on the first floor?
select Description from Damages where Location='first floor' and Source='flood';

14. List all property street addresses and show the description of the damage for those that had damage.
select Description,StreetAddress from properties inner join damages on properties.LotNo=damages.LotNo;

15. Create a View to show street address and city of properties that had damage to their floors (wet carpet or buckled floor). Call the view FloorDamage.
create view FloorDamage as select StreetAddress,City from properties inner join damages on properties.LotNo=damages.LotNo where Description='wet carpet' or Description='buckled floor';

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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