Find the average accident severity and count for different types of motorcycles

Question

Find the average accident severity and count for different types of motorcycles using a SQL statement, specifcially, mySQL. Use column aliases, table aliases

This uses the Vehicles_2015.csv, Accidents_2015.csv, and Road-Accident-Safety-Data-Guide.xlsx data Note these are large files and you will need to use Python to import the data correction into a SQL server if you want to test your code. Therefore, you can assume the following. The data is stored in three tables (subsections of the outputs are shown below). Accidents 2015 and vehicles 2015 share the accident index field. And vehicles 2015 and vehicle type share the vehicle code field CREATE TABLE accidents_2015 ( Accident index VARCHAR(13) Accident severity INT); 1501BS70001 201501BS70002 3 0150 185700043 0150 1B570005 015018570008 201501BS70009 3 201501BS70010 3 201501B5700113 201501BS70012 CREATE TABLE vehicles 2015 ( Accident index VARCHAR(13) vehicle type VARCHAR(10)); 150 1B570001 19 201501BS70002 9 20150 18570004 9 150 18570005 201501B570008 1 201501BS70008 9 0150 1BS700093 2015018S70009 19 01501B5700109 vcode vtype CREATE TABLE vehicle type ( vcode INT vtype VARCHAR(100)); Pedal cvde Motorcvde 50cc and under Motorcvce 12Scc and under Motorcvcle over 125ce and uo to 500cc Motorcvdle over 500cc Taxd Private hre car Bus or coach (17 or more pass seats) Diagram with connections (use the names in the CREATE TABLEs not from this diagram. index index code vehicle_code vehicle type vehicle type

Explanation / Answer

The above output can be generated by the below code:

select vt.vtype "Row Labels",
avg(a.accident_severity) "Average of Accident Severity",
count(a.accident_severity) "Count of Accident Severity"
from vehicles_2015 v, accidents_2015 a, vehicles_type vt
where a.accident_index=v.accident_index
and v.vehicle_type=vt.vcode
and vt.vtype like '%Motorcycle%';

We know that there exists a one to one relation between index field in Accidents_2015 and index field in Vehicles_2015. Similarly, a one to one relation exists between vehicle_type field in vehicles_2015 and vcode field in vehicles_type. After joining these tables (here we use inner join by using '='), we pull the necessary columns by applying average() and count() functions.

In order to pull the motorcycle information alone, we have used the filter vt.vtype like '%Motorcycle%'. By using % on both sides, we can find all records which contains 'motorcycle' anywhere in the data field.

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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