search

tommywenjiezhang / Database-Project

0 stars 5 forks source link

Part 5 Question 4 #11

Open tommywenjiezhang opened 3 years ago

tommywenjiezhang commented 3 years ago

Write a query that identifies the number of crimes within 10 miles (16000 meters) of each city in the state you selected to move to by year that the crime was committed. There are several things you’ll need to consider: a. There are no columns to join the GunCrimes and AQS_Sites tables together, so you need to create a Cartesian Product and filter data out in the where statement using the following formula (which you’ll need to modify) and the location data from your queries

cs586 commented 3 years ago

With ST as (select count(g.incident_characteristics)as N,convert(varchar(8000),g.GeoLocation)as Names from GunCrimes g,AQS_Sites a where incident_characteristics like 'shot%' and g.GeoLocation.STDistance(g.GeoLocation)=a.GeoLocation.STDistance(a.GeoLocation) and g.GeoLocation.STDistance(g.GeoLocation)< (10 * 1609.344) group by convert(varchar(8000),g.GeoLocation)) select top(1000) (Site_Number+'-'+State_Name+'-'+a.Address) as Local_Site_Name, City_Name, year(date) as Crime_year , s.N as Shooting_Count from GunCrimes g,AQS_Sites a, ST s where g.GeoLocation.STDistance(g.GeoLocation)=a.GeoLocation.STDistance(a.GeoLocation) and convert(varchar(8000),g.GeoLocation)= s.Names and convert(varchar(8000),a.GeoLocation)=s.Names;

cs586 commented 3 years ago

With ST as (select sum(g.n_injured)+sum(g.n_killed) as N,convert(varchar(8000),g.GeoLocation)as Names from GunCrimes g where g.GeoLocation.STDistance(g.GeoLocation)< (10 * 1609.344) group by convert(varchar(8000),g.GeoLocation)) select (Site_Number+'-'+State_Name+'-'+a.Address) as Local_Site_Name, City_Name, year(date) as Crime_year , s.N as Shooting_Count from GunCrimes g,AQS_Sites a, ST s where g.GeoLocation.STDistance(g.GeoLocation)=a.GeoLocation.STDistance(a.GeoLocation) and convert(varchar(8000),g.GeoLocation)= s.Names and convert(varchar(8000),a.GeoLocation)=s.Names;

cs586 commented 3 years ago

With ST as (select count(g.incident_characteristics)as N,convert(varchar(8000),g.GeoLocation)as Names from GunCrimes g,AQS_Sites a where incident_characteristics like 'shot%' and g.GeoLocation.STDistance(g.GeoLocation)=a.GeoLocation.STDistance(a.GeoLocation) and g.GeoLocation.STDistance(g.GeoLocation)< (10 * 1609.344) group by convert(varchar(8000),g.GeoLocation)) select top(1000) (Site_Number+'-'+State_Name+'-'+a.Address) as Local_Site_Name, City_Name, year(date) as Crime_year , s.N as Shooting_Count from GunCrimes g,AQS_Sites a, ST s where g.GeoLocation.STDistance(g.GeoLocation)=a.GeoLocation.STDistance(a.GeoLocation) and convert(varchar(8000),g.GeoLocation)= s.Names and convert(varchar(8000),a.GeoLocation)=s.Names;

not sure about how to calculate the "shooting_count", emailing the professor to confirm.

cs586 commented 3 years ago

With ST as (select count(g.incident_characteristics)as N,convert(varchar(8000),g.GeoLocation)as Names from GunCrimes g where incident_characteristics like 'shot%' and g.GeoLocation.STDistance(g.GeoLocation)< (10 * 1609.344) group by convert(varchar(8000),g.GeoLocation)) select top(1000) (Site_Number+'-'+State_Name+'-'+a.Address) as Local_Site_Name, City_Name, year(date) as Crime_year , count(s.N) as Shooting_Count from GunCrimes g,AQS_Sites a, ST s where g.GeoLocation.STDistance(g.GeoLocation)=a.GeoLocation.STDistance(a.GeoLocation) and convert(varchar(8000),a.GeoLocation)= s.Names Group by year(date),City_Name,(Site_Number+'-'+State_Name+'-'+a.Address) Order by Shooting_Count,year(date),City_Name;

tommywenjiezhang commented 3 years ago

Do you have the update statement