Exploring Accident Database (Data Analysis by SQL)
HTML-код
- Опубликовано: 25 июл 2024
- .
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
🧷 If you like, you can walk through the project's materials:
mehrdaad.wixsite.com/portfoli...
-------------------------------------------------------------------
Also, you can find more videos like this in the below playlist:
✔️ • Domain: Exploratory Da...
✔️ • SQL
-------------------------------------------------------------------
I'd be pleased if you see my profile:
🔶 mehrdaad.wixsite.com/portfolio
🔶 github.com/M3hrdad-Dehghan
🔶 / mehrdad-dehghan
Thank you for all your efforts on the channel.
Thank you for doing such a great tutorial 🎉
It's my pleasure
i like watching you solve the questions, more videos please xoxo from california
sure. thanks for your watching
you are just amazing.!! thanks
Thanks
Is a good SQL tutorial for beginner. thank you
definitely
Fantastic Work Boss.
Thanks
Thank you for giving free knowledge to the world
It's my pleasure
Very use full project Dear
thank you
I just found your works. 👍
Thanks
Best video on topic
Thank you so much.
question 5
easy Understanding Query
select WeatherConditions , count(WeatherConditions) as TotalAccident
from accident
where Severity = 'Fatal'
group by WeatherConditions
order by TotalAccident desc
thanks bro
thanks for your explanation.
Which symbol did you put beside the WHERE function and Severity variable to make them inactive?
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?
No need to save the query since all queries are stored in a log.
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
please make sure you don't change the data type of variables
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.
Try Microsoft SQL Management Studio instead of MySQL
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
Did you use the correct version of SQL Server?
@@mehrdad_analyst yeah, I think I didn't check allow null variables for a column the first time I guess
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
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
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.
Please send me an email and explain your problem
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
Yes. You can use any query as long the query works and shows the same result. Just use your creativity 👍
Used that one too, but include a group for nulls
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
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.
is it normal to have AccidentIndex with scientific values (Like 2.02E+10)?
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.☺
@@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.
What generates such data in real life? I'm just asking to expand my knowledge
This data is sourced from the UK Traffic Data Agency and represents genuine information for a particular timeframe.
what is the purpose of accidentIndex in this query?
we could use this variable as n unique (primary key) for accident identifier.
do you have a data what u can practice too
hi. you can see my GitHub in the description of videos
ok thank you
@@mehrdad_analyst
I want same data set
You can download it by going to the link in the description of video.
Data analyst always work with Excel data only can please clarify my doubt
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.
Bruh there are null values in column Age Vehicle of vehicle table its not even getting imported in my mysql workbench csv is a headache fr !! Help me out someone
I will export all data in an Excel file and I will upload it in my website. You can go there and download the dataset.
@@mehrdad_analyst That would help sir
Can u share the dataset
Just use the link in the description. (GitHub)
Do you have the source of the data?
Unfortunately, it was on my local system and after changing system it was gone
is this good for resume , please tell me guys
yes sure. I tried to put most common syntax in this video
I have a hard time understanding subquiry
In SQL, a subquery is a query within another query. It's like a small query nested inside a larger on. This part selects "AccidentIndex" and "AgeVehicle" from the vehicle table. It also creates a new column called "AgeGroup" which categorizes vehicles into 'New', 'Regular', or 'Old' based on their age.In summary, the subquery first categorizes vehicles by age, and then the main query calculates statistics based on these categories.
Why most youtuber are using Ms Ms SQL server....why not use oracle SQL tool
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
@@mehrdad_analyst thanks for sharing guidance and knowledge
@@Ganeshay-996 it's my pleasure
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