Data Analyst Portfolio Project | Data Cleaning in SQL | Project 3/4
HTML-код
- Опубликовано: 25 июл 2024
- Take my Full MySQL Course Here: bit.ly/3tqOipr
Today we continue our Data Analyst Portfolio Project Series. In this project we will be cleaning data in SQL. Data Cleaning is a super underrated skill in the Data Analyst community so I hope this can be helpful to start you on your journey to learning that skill.
Please remember to save this project and add it to your GitHub once you are done!
LINKS:
Project Dataset: github.com/AlexTheAnalyst/Por...
Link to SQL Code: github.com/AlexTheAnalyst/Por...
Link to Install SQL Server: • SQL Basics Tutorial Fo...
____________________________________________
SUBSCRIBE!
Do you want to become a Data Analyst? That's what this channel is all about! My goal is to help you learn everything you need in order to start your career or even switch your career into Data Analytics. Be sure to subscribe to not miss out on any content!
____________________________________________
RESOURCES:
Coursera Courses:
Google Data Analyst Certification: coursera.pxf.io/5bBd62
Data Analysis with Python - coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - coursera.pxf.io/AoYOdR
Tableau Data Visualization - coursera.pxf.io/MXYqaN
Udemy Courses:
Python for Data Analysis and Visualization- bit.ly/3hhX4LX
Statistics for Data Science - bit.ly/37jqDbq
SQL for Data Analysts (SSMS) - bit.ly/3fkqEij
Tableau A-Z - bit.ly/385lYvN
Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!
____________________________________________
SUPPORT MY CHANNEL - PATREON/MERCH
Patreon Page - / alextheanalyst
Alex The Analyst Shop - teespring.com/stores/alex-the...
____________________________________________
Websites:
GitHub: github.com/AlexTheAnalyst
____________________________________________
All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for
0:00 Introduction
1:02 Download Dataset
1:56 Import Data into SQL
7:35 DATA CLEANING
50:32 Recap
He says, "You're the one putting in the hard work," completely ignoring the fact that he compiled hours of material and made it possible and easier for us. Thank you very much Alex.
He's just too kind
Thank you so much Alex
9:05 Not sure if this has been pointed out, but the reason the SaleDate column didn't "update" here is because UPDATE does not change data types. The table is actually updating, but the data type for the column SaleDate is still datetime.
To change data type, one has to use ``` ALTER TABLE NashvilleHousing ALTER COLUMN SaleDate DATE ```
No every hero wears cape.
Thanks bro
@@user-kx3rz6lv7b Do this:
ALTER TABLE PortfolioProject..NashvilleHousing
ALTER COLUMN SaleDate Date
This 1 hour video has most of the important SQL concepts needed
This channel is so underrated. We need more likes for him
I used to work for Microsoft as a data analyst. All the projects you did here were given to me at different times. Well done. everybody starting from scratch needs to follow you. I didn't have any idea when I started ETL/SQL jobs. This is very good one and you keep this up.👍👍
Okay I know it's goddamn weird but can you refer me for some entry level data analyst profile pleaassee
Thank you so much, Alex. This video is incredible. I recently finished my data analyst studies and now I am trying to create a solid portfolio for myself. This is a great start. May God Bless you for all that you have done for people like me.
Awesome video Alex. I've been wanting to break into data analytics for YEARS, but back about 5 or 6 years ago, there wasn't really a clear path. Thanks so much for allowing us career changers to learn these skills, showcase them, and land that next job! I'm looking forward to my first gig. Thanks again.
WOW! This was so awesome! While the nuts and bolts of the function syntaxes are at the root of this course, almost more valuable is to understand the thought processes he goes through, and get a real world take on how this plays out when you're actually doing it. I learned so many nuances just watching how he typed things, great detail you can best learn by watching him in- action and doing it alongside him on your PC. So very thankful, Alex!
Thanks so much Alex! I know everyone says it, but you are helping so many people and making these skills so much more accessible! You're an incredible teacher!
What you've shown here is what I do everyday for a fortune 500 fintech. Great information and you definitely earned my sub. Looking forward to watching your other videos.
Very cool! And thanks for watching! :D
can you mention a few other things that you do every day,might be helpful to know how everyday work is of cleaning data.
@@antarabhattacharjee9194 2 years later. And he never answered. Sigh)
Just watched multiple videos from this playlist to strengthen my understanding for a project I'm leading in my new position as an analyst. It makes me SO HAPPY to hear your excitement about SQL because I feel the exact same way!! I can't wait until my project gets to a point where I can start writing SQL again. I look forward to turning to your channel whenever I need help or guidance (and honestly just watching your videos for fun)! Thank you for doing what you do!!!!! AND CONGRATS on the new path you're taking! (now I'm watching your newest video lol) Best of luck with everything!
I feel like a lot of people have told you already Alex, but this is really helpful. I know basic SQL, but learning about all of these new techniques for data cleaning will certainly help me advnace my data analytics toolkit. You make this extremely fun to watch and exciting for your new stuff to come. Thank you!!!
Great and painless Portfolio Project series. Suuuper underated I must say. I finished 3 of these guided in a total of a week and a half. I did learn a lot about SQL and its usefulness. It's great to have someone like you on RUclips. Thank you so much Alex!! Keep up the great work!!
So great to hear that! Learning something new every day is a great thing!
Alex i love man! Personally I really love SQL too!! These Projects and videos are so useful and really helpful in improving one's skillset. You are literally changing lives and helping so many people. Thank you so much for your time and immense knowledge that you share with us.
Alex, thank you so much for making these videos! Your videos have really helped me with my role. I just recently switched career and became a data analyst and I am so glad I found your videos. You explained everything in great details in a way that is easy to follow. Please keep making these portfolio videos :)
Thank you again, Alex. I am going through this again in 2023, and I really understood it the second time over. I am so grateful fo you.
I recently came across this tutorial. Thank you so much for the hours, work, and the way you simplified each and every step. You're the real hero.
Thank you so much, Alex. I watched all your SQL videos. When I started I didn't know anything about SQL. I was able to complete this project without much hassle Thank you!!!
So thankful Alex for showing me the basics. Your videos showing the nuts and bolts of doing this is EXTREMELY helpful to me. Thanks for demystifying SQL and all your videos and being so friendly!
Hey Alex, great video! During the changing Y and N to Yes and No i found that using a CTE to verify whether the CASE statement affected the table worked much faster than finding individual values prior to updating the table. Thought this might help other people showcase CTE creation and use to further expand their skills. Keep up the great work man; I'm loving your teaching style!
Best Project Yet - Really found this one helpful, since most tutorials don't really cover a complete data cleaning process, just parts of it. Thanks Alex!
Your teaching style and pragmatic methodolgy is what makes you great. I enjoy every lesson from this channel.
I couldn't explain all the questions you answered with this video. I've been studying SQL on my own for over a year now and my focus and motivation have definitely improved after this.
Thanks!
Really appreciate for your hard work and positive educating style, Alex!! I've really gained lots of knowledge about the whole analyzing process from you!
Thank you for all these awesome videos :) I have a GIS background and will be starting a data analyst soon which required me to up my SQL skills quickly. I've been watching your videos alongside Datacamp's and it's been really helping!
Thx Alex , I forgot how much I love Sql . I am an IT project manager and interested in advancing my skills in Data Analytics . My goal is land a job in analytics in six months . Thanks again for guiding me through this process
I took me a few days to complete this project but totally worth it! Thanks again Alex!
AWESOME, AWESOME, AWESOME! Thank you for taking the time to do this for all of us. Super inciteful and helpful. This has me doing things that I never would have otherwise done, and opening up possibilities for me to do more extensive things at work that will help me stand out. Kudos to you!
thank you so much for all the hard work you put in. I did the google coursera certification course and found i hadnt absorbed any of the information in a useful way. I followed along for just TWO of your sql basic videos and theyve helped me retain so much more information and practical uses. You really give so much to this channel and your subscribers and we really appreciate it.
Another solid project, Alex! Thx! I used a SELECT INTO to copy the source table into a "cleansed" table and then used the SQL code to normalize, etc. That also allowed me to be able to rerun the whole script, along with a few Go commands sprinkled in. Much appreciated!
I am still working on the data analyst bootcamp and it has been fun and productive learning from you.
On a sidenote, in the section standardize date format, I have research that instead of using update-set, we could use
alter table tablename
alter column saledate date
and it works as what we have intended without using the update-set.
Again Thank you very much Alex.
I honestly don’t mind things being more advanced. Sometimes it’s really necessary to do things the advanced way and it’s really helpful to be able to learn them while doing a project, and in the end they can even be showcased in portfolio. Which is soo goodd. Thank you very much!
So glad to hear your feedback!
Thank you so much Alex for putting so much work for us. We are blessed to have you.
Thank you so much Alex.. been watching all your videos for almost a year now.. started where I thought I'd never understand SQL, now I'm here.. trying out new codes myself😅.. love you man ! Thank you so much!
THANK YOU ALEX!!! I so appreciate you. You are SO patient, methodical, and detail-oriented. It's been SUPER helpful/valuable for me to see your "mistakes" and follow along as you walk us through how to correct/address these issues in real-time. Thank you for not editing these parts out! I've learned a tremendous amount following your bootcamp already. Can't wait for Project #4!
Hello please where you able to download the dataset?
this is by far one of the best data exploration videos I have ever watched, thank you Alex.
I'm following this series for my Portfolio. Thank you so much! Your thought process helps me understand SQL better!
Currently taking the google certificate, and I was literally thinking, “wow, I wish they had a video showing how to do this portfolio project...” thanks so much for taking the time to make these videos! I have completed my portfolio project, but I’m holding off on section 7(R)while I get a better grasp of SQL, and this channel has been great for that so far! Google should link you in their course! Ha!
That's really cool! I'm so glad to hear that :D
I back your opinion
Yes! I said the same thing! That course is great for the first few but after that they segment it out so much that it's hard to put everything together and actually be able to succeed in the capstone project. Alex, you are a lifesaver/life changer! I'm so excited to be doing these videos and actually seeing everything put together! I'm thinking that after this I will try and do a project on my own and see if I can do one by myself using Alex's methods.
just in case you see this. how did it go? did you succeed in getting work? i completed it and am working on these as portfolio projects.
How is it going?
I just started studying in parallel with my studies at the university. It would be great to hear some feedback from an old disciple like you
Hi Alex, you could definitely be a college professor! This is exactly what we were taught in MIS class, but you have it more related to real work. Like it!
You're the best man! I really appreciate all of your effort to help us grow!
Another great tutorial! I'm really starting to get a better understanding of when to apply CTE's and Windows Functions.
Thanks so much Alex! Been really enjoying the journey so far, especially because of your videos
Again Thank you very much. I have gotten so much from this series. My confidence grows day by day. Lots of Love from Kenya Alex.
Excellent Video Alex. As a newly certified Data engineer, I didn't know where to start my venture from as I lack practical experience and stumbling across your video gave me a direction to make portfolio and take it from there.
I highly appreciate your work and effort. cheers
I'm just bummed that I didn't come across your channel sooner! Great content! Awesome SQL video. I have a couple years of experience using MS SQL server and I still found it very informative. Thanks so much!
WOW.... JUST.... WOW!! ... This is very insightful. I'm so glad i stumbled upon your channel three weeks ago. These projects you're making are great learning tools.
I never knew you could combine substring with Charindex like that. 👍👍👍
Glad to hear it was helpful and you learned some new things! :D
You are the guy! Always insightful and inspiring! Thank you, Alex!
This is a very helpful video! So clean, clear, and concise! Thank you, Alex!
Alex, I honestly appreciate your efforts.
Thank you Alex for your time and effort. I love you! Just done this Project.
Big fan of your work Alex... You're really doing an outstanding job!! Can't wait for the final part!!🙌
Thank you and thanks for watching! :D
So good to have you! Thank you Alex!
I so appreciate you. I went through this video a few times at certain points to lock things in. And yes CHAT GPT explained what I wasn't quite getting. I'm doing your bootcamp but before I move on to ExcelI want to study more SQL through projects. Thank you for all the time you dedicate to this work.
This was a really incredible and useful video! Thanks, Alex. You're Awesome.
Woo Hoo, I am really excited to watch your video and I just finished part 3/4, and I can't wait for part 4/4, may Alex always be given health, abundant sustenance, until video 4/4 can be posted.
Great video!!
Appreciate all the effort in explaing and making codes more easy to understand.
Thank you so much for these videos man you have taught me so much in such a short amount of time. I'm about to start my first project completely on my own and these videos have been a huge confidence booster as well as a great reference
So glad to hear that Jory!
Thanks a lot for the extremely helpful portfolio series. Especially the data cleaning part was so eye-catching. I have learned a lot of SQL functions in the section and I am just a beginner on phyton but gonna start to the phyton video tomorrow. Thanks again
Thank you Alex. Just thank you so much for making it easy, I am truly and eternally grateful.
Great video and break down and this has helped me further understand the RANK, ROW_Number and PARTITION BY Clauses. Thank you Alex
your work is quite insightful and i really want to learn all that i can to actually switch career. A lot of the material actually goes over my head and i guess it is because i am a complete beginner. i am gonna put in a lot of work to put all your teachings into practice and see how it all goes. Thanks so much Alex!!!!
A wonderful SQL Tutorial Alex! So glad you exist
Thanks Alex, much appreciated. SQL is so much fun!!
Really enjoy your videos. Enjoyed them so much I switched my major from InfoSec to Analytics this week.
BRUH, keep em coming big guy. I'm loving it. i started part 1 three days ago and i am now here on part 3. that is on top of my corporate finance course as well police legitimacy tier 3 elective. I am loving this series and loving SQL.
I will! So glad you're enjoying it! :D
Much appreciated for YOUR hard work! :) Great content, keep up the good work.
This is super helpful for people like me who are trying to build a portfolio and don't know where to start and what to show. I'd like to see more projects in this series. However, these videos gave me a good idea on how to start. Thank you so much!
So glad to hear that! There will be more :)
Thank you so much. You probably have an idea... but your portfolios and videos are very generous. You’ve really provided tangible context here that gets right into the meat and potatoes....my analyst buddies made it clear that they would have wished you were there at the start of their careers. And they usually always dismiss the videos I pass by them to check if they’ll help. You, and you alone, check out. Cheers! Happy memorial weekend!
Haha I’m really glad to hear that! That’s why I saturated this channel - when I was first starting out I wish I had stuff like this, but couldn’t find it anywhere. Thanks for watching!
Thank You So Much Sir, I was able to complete the first Phase of the Bootcamp, I'm still Amazed i did it, you were the first RUclipsr who made understand every video with any problem, I'll reference this video for My future Projects too.
I have learnt so much from this Video... This for someone who worked in MSSQL alot during the early days of my software Dev career.... I need to do the ETL for sure
Wait is over!!! Thanks alex!!
Alex man you are doing a great job and using youtube to provide information and share your knowledge with us. I am so thankful
Thank you and thanks for watching!
I think Alex's channel is one of the best channels out there. I'm currently a data analyst working for a telecom company in a country that's been witnessing some really tough time; however, I've always aspired to advance in my career and probably one day be able to immigrate to USA or Canada and get the dream job I want.
I think Alex is a great mentor in that aspect because he had a tough start into this field and eventually did it! Thank you for all the tips, really helpful.
So glad to hear the channel has been helpful! Thanks for watching and the support :)
This has really helped me advance my skills in sql , Thanks a million Alex 🙏
Thanks a million, dear Alex! This Portfolio Projects series is absolutely fantastic and so helpful for "newbies" like me. I know knowledge is a never-ending path but having someone like you on my side definitely makes the process faster, deeper and more pleasant. God bless you, bro!
Hi Victor,
Can you please help me out with the data?
I'm not able to download the data using the Github link provided by Alex.
Can you please share the xlsx via your Github or something?
Really loved doing it. Great work dude ! 😊
I have never even bother to like any video in my lifetime, you really did helped me a ton and the least that I can do for you is by clicking a button on my computer. I'm gonna find some other data and do the data cleaning on my own and then move on to the part 4 of the project. Thank you so much for your HARD WORK, hopefully what I did will lead me somewhere in the career path. YOU DESERVE all the HAPPINESS.
Thank you - I appreciate it! Glad it was helpful!
Again a great video Alex. Thanks for Sharing!!
These videos are the best! Thank you so much, Alex!
Thank you man that means a lot! Thanks for watching!
thank you so much. I have done other SQL courses, but this is the most effective one for practice.
Hi Alex! It's a genuine lovely video! I loved it :) Looking forward to your ETL video ~
I really enjoyed this episode. A lot did not work and so I had to learn different ways to get the job done. Very Educative
Am so happy to be part of this.may God bless and reward you Mr Alex
Thank you so much ALEX!!! This was a great exercise!!!
Its amazing Alex, learned a lot. Thank You
Thank you so much Alex. You have done a great job for us.
Thank you so much Alex for this great teaching.
Hi Alex, thank you so much for this Boot Camp I am on my journey to become a data analyst, and it is really an amazing playlist for those who wants to dive deep into the world of data. again thanks a lot for sharing your lifetime learning.
I don’t know if you still read these types of comments but I just wanted to thank you Alex for this guide. You’re a really good teacher and I’m glad I found this playlist and this channel
Thanks Alex for these kinda Projects. I am learning alot from these Projects which you are creating and I hope these will help me get into Data Analytics Field soon.
You're more than welcome! Super glad you find them helpful :D
Thank you very much Alex for your videos! I have been watching all your SQL series from beginner till here. Apart from what you have been showing, I have also tried to do things on my own with the databases you have been using in the videos. When I got stuck I have started looking up on internet for possible solutions. All this to say that during my attempts I bumped into Alter Table Alter Column. So instead of this:
ALTER TABLE NashvilleHousing
Add SaleDateConverted Date;
Update NashvilleHousing
SET SaleDateConverted = CONVERT(Date,SaleDate)
and then drop SaleDate
I used:
Alter Table Nashville
Alter Column SaleDate date
and I works :)
Anyway thanks a lor and keep up with the good work
I am about halfway through, excellent quality teaching. Practically applied SQL makes a great change from the same old explanatory tutorials.
So glad to hear that! Thanks for watching! 😁
This is great content, thank you very much for this Alex.
Must say you're Doing a Great Job, Alex. Sending loads of Love all the way from India.
Sending love back from the Stated :D
Thank you Alex for making such an informative video. Made my life easier.
I enjoy your teaching so much. Thank you!
took me 5 days to complete sql part of tutorial and portofolio project. Thank you very very much alex
Alex, once Again, Thanks for your video, it has been very useful for me.
Stay tuned and keep Analysing. 😉😉
This is amazing! I’m a SAHM looking for a new career path that allows me to be more flexible with my family and doing contract work for analytics has inspired me to learn all I can. Just had my 2nd baby and I’m in the portfolio building phase and seeing you make mistakes and figure it out helps encourage me that I am going to do this but not to give up.
I’m hoping to find a job this summer if it’s part time that’d be ideal since I have two under 3. I’m going through this boot camp and it’s amazing and much more fun than the Google cert program.
I am also a SAHM of 2 hoping to start a career in data analytics, you go mama!! 🥰 we got thiss! 🙏🏼
@@miagobeli-starr me too 🙌
Thank you Alex, I'm building up my portfolio with you.