SQL Server Data Analysis: Exploring Accident Database

.
I used Microsoft SQL Server Management Studio to conduct exploratory data analysis for the accident database. During this project, I tried to run SQL queries ranging from simple to most challenging and complex to answer 8 different questions.
-------------------------------------------------------------------
You can find each part of this video here:
🕒 00:00 Introduction to Project
🕒 00:49 Data
🕒 03:46 Create Database and Tables
🕒 05:26 Question 1
🕒 08:53 Question 2
🕒 11:18 Question 3
🕒 15:19 Question 4
🕒 22:17 Question 5
🕒 28:01 Question 6
🕒 30:32 Question 7
🕒 37:29 Question 8
-------------------------------------------------------------------
To find the files of this video, please use the below link:
🔶 github.com/M3hrdad-Dehghan/SQ...
🔶 mehrdaad.wixsite.com/portfoli...
-------------------------------------------------------------------
Also, you can find more videos in the below playlist(s):
Based on Topic:
✔️ • Data Analysis
✔️ • Data Visualization
✔️ • Machine Learning
✔️ • Statistics
✔️ • Other Topics
Based on Tools:
✔️ • SQL
✔️ • Python
✔️ • Power BI
✔️ • Excel
-------------------------------------------------------------------
I'd be pleased if you see my profile:
🔶 github.com/M3hrdad-Dehghan
🔶 / mehrdad-dehghan

[@safakv60]

Thank you for all your efforts on the channel.

[@RafiqulIslam-dv9cu]

Fantastic Work Boss.

[@mehrdad_analyst]

Thanks

[@piyushsingh6084]

you are just amazing.!! thanks

[@mehrdad_analyst]

Thanks

[@aliwow7874]

Thank you for doing such a great tutorial 🎉

[@mehrdad_analyst]

It's my pleasure

[@Sajid10109]

Very use full project Dear

[@mehrdad_analyst]

thank you

[@sasitpiasai8152]

Is a good SQL tutorial for beginner. thank you

[@mehrdad_analyst]

definitely

[@PujiPamungkas-ko4db]

I just found your works. 👍

[@mehrdad_analyst]

Thanks

[@uzi110]

question 5 easy Understanding Query select WeatherConditions , count(WeatherConditions) as TotalAccident from accident where Severity = 'Fatal' group by WeatherConditions order by TotalAccident desc

[@user-ye5mt8ee9e]

thanks bro

[@ashutoshpandey9105]

Hey, nice tutorial, I am doing this for my portfolio project. DO I need to save the results of queries as well? Or just codes will be enough?

[@mehrdad_analyst]

No need to save the query since all queries are stored in a log.

[@manojparida9040]

Good job.... While i import the vehicle file in MySQL, it shows row imported failed due to data truncated for column accidentindex at row 1 and 1265.

[@mehrdad_analyst]

Try Microsoft SQL Management Studio instead of MySQL

[@navaneethcm8377]

First of all, thank you for the video. I am new at this and was searching for a project to do to learn more and start building a resume. But I have encountered a problem while importing the flat file 'vehicle'. It says - The given value 'No' of type String from the data source cannot be converted to type bit for Column 4 [LeftHand]. (Microsoft.Data.SqlClient) Failed to convert parameter value from a String to a Boolean. (Microsoft.Data.SqlClient) Edit- I got it

[@mehrdad_analyst]

Did you use the correct version of SQL Server?

[@navaneethcm8377]

@@mehrdad_analyst yeah, I think I didn't check allow null variables for a column the first time I guess

[@radhikareddydasari491]

Thank you for doing this analysis, Also can you please let me know how can i download this data, i tried to download github links provided above but i don't have find the data sets

[@mehrdad_analyst]

Hi. In this link there are two CSV files. you can download them and import in your SQL management application (MySQL or MS SQL). github.com/M3hrdad-Dehghan/SQL/tree/main/Exploratory_Data_Analysis_of_Vehicle_Accident_by_SQL

[@dapoadimula3167]

The left-hand column in the vehicle table cannot be imported with the bit or tinyint datatype. Please note that I am using SQL Server Management Studio (SSMS) for my database

[@mehrdad_analyst]

please make sure you don't change the data type of variables

[@simonoganga9535]

Hey bro., Is this the one you told me about you are working on? Let me check it out then I'll give you my feedback. But I already know it's amazing

[@mehrdad_analyst]

Hi Simon. It's just about the EDA project using T-SQL. You can find it a useful project for dealing with data to explore more about it. After running EDA on data, you are ready to use one of the BI tools to visualize data to gain more insights. By the way, I planned to run the E-2-E project, but I was tasked with a huge workload. So, I try to publish more videos on different topics.

[@araijeahmed6127]

hi ; in question 4 ; can we use this query SELECT AgeGroup, COUNT([AccidentIndex]) AS 'Total Accident', AVG([AgeVehicle]) AS 'Average Year', CASE WHEN [AgeVehicle] BETWEEN 0 AND 5 THEN 'New' WHEN [AgeVehicle] BETWEEN 6 AND 10 THEN 'Regular' ELSE 'Old' END AS AgeGroup GROUP BY AgeGroup; thanks

[@mehrdad_analyst]

Yes. You can use any query as long the query works and shows the same result. Just use your creativity 👍

[@watitwanasaka3208]

Used that one too, but include a group for nulls

[@ikeibe452]

What generates such data in real life? I'm just asking to expand my knowledge

[@mehrdad_analyst]

This data is sourced from the UK Traffic Data Agency and represents genuine information for a particular timeframe.

[@siddartha1328]

Hey i have a small doubt the excel sheet contains dates in different formats and i am not sure how to change them into single format can you help me thanks in advance.

[@mehrdad_analyst]

Please send me an email and explain your problem

[@vasilli3273]

is it normal to have AccidentIndex with scientific values (Like 2.02E+10)?

[@mehrdad_analyst]

Accident Index is indeed a label in terms of data type. However, it is important to note that the values displayed in a column may not always correspond to the data type. For example, even though Accident Index is shown as a number, it is actually a label that is a combination of letters and numbers. Therefore, it is highly recommended to change the data type accordingly. It is worth mentioning that in the video, the accident index appears to be a scientific number, but it is actually a label.☺

[@vasilli3273]

@@mehrdad_analyst in my case about 110.00 rows of AccidentIndex data converted to scidentific value in the procces of importing data to sql server database. which makes almost impossibble to analyse the data because we can't relate vehicleID's and AccidentIndex. I trided to change data types but doesn't work. couldn't figure out a solution.

[@hendraheryawan4393]

do you have a data what u can practice too

[@mehrdad_analyst]

hi. you can see my GitHub in the description of videos

[@hendraheryawan4393]

ok thank you @@mehrdad_analyst

[@user-yi9xl5br7h]

Data analyst always work with Excel data only can please clarify my doubt

[@mehrdad_analyst]

Excel is an application that is used for storing structured data. However, there are other tools available for storing structured data, such as databases, Access, and others. Apart from structured data, there are semi-structured data types, such as websites, and non-structured data types, such as voice, text, and video, that have their own storage systems. The most important step in data management is to understand the data itself, while tools and techniques are secondary.

[@Naif-so8yk]

what is the purpose of accidentIndex in this query?

[@mehrdad_analyst]

we could use this variable as n unique (primary key) for accident identifier.

[@abrahammoruti5967]

I have a hard time understanding subquiry

[@joasjonson9123]

Do you have the source of the data?

[@mehrdad_analyst]

Unfortunately, it was on my local system and after changing system it was gone

[@Behappy-ft5yc]

is this good for resume , please tell me guys

[@mehrdad_analyst]

yes sure. I tried to put most common syntax in this video

[@thummaluruharish1929]

Can u share the dataset

[@mehrdad_analyst]

Just use the link in the description. (GitHub)

[@ivanko-nebo]

I guess you didn't exactly answer the question 7. You just counted severity but wasn't you supposed to find out how many Fatal, Serious and Slight accidents were for example when people are Commuting to/from work? Maybe this query would answer it better: SELECT v.JourneyPurpose, a.Severity, COUNT(a.AccidentIndex) AS Accidents_Count FROM Car_Accidents..accident AS a JOIN Car_Accidents..vehicle AS v ON a.AccidentIndex = v.AccidentIndex GROUP BY v.JourneyPurpose, a.Severity ORDER BY 2 ASC, 3 DESC

[@Ganeshay-996]

Why most youtuber are using Ms Ms SQL server....why not use oracle SQL tool

[@mehrdad_analyst]

Maybe just because it's easier to walk through. However, in real work condition, it doesn't matter which database management tool is being used

[@Ganeshay-996]

@@mehrdad_analyst thanks for sharing guidance and knowledge

[@mehrdad_analyst]

@@Ganeshay-996 it's my pleasure