I'm doing this in Oct 2021 and apparently one of the code chunks where you need to convert new_vaccinations column to integer, the sum value now has exceeded 2,147,483,647. So instead of converting it to "int", you will need to convert to "bigint". Hope this helps everyone.
I’ve just started the video and I’m about 9 minutes in. I wanted to stop at this point to tell you I’m glad you showed your mistake live. It’s important for beginners to see that experts also make mistakes. It is encouraging. As a bonus, I will better remember to save as xlsx because you pointed it out. So thank you for leaving that in.
I make a ton of mistakes! haha the cuts that I took out were 5+ minutes of me having to research something or figure something out and then coming back. Didn't want to keep that boring stuff in lol
Years ago I said no to programming because I always find myself making mistakes (I'm a GIS Engineer). Then came my friend, a software developer also make tons of mistakes. I questioned him because he has a degree in software engineering with years of experience, then he replied "I'm a human, not a computer. Even human to human also sometimes struggle to communicate" haha
I'm transitioning from teaching to data analytics. I recently finished the Google Data Analytics Certification, and I've been struggling to figure out how to get my portfolio started. I found this video, and I got really excited to use this as a guided project. Then, I saw Alex was using Covid data, and I had to pause to consider if I could handle it. I lost my dad to Covid 4 months ago. This is personal. However, I think I need this now as another way to process through the continuing grief. The fact is, data is personal. Each of these data points describes people. Someone's father, mother, sister, brother, grandparent, friend, and so on. This data is reality, and it's so important to analyze it in order to truly show the impact that this pandemic has made. Especially, as things may be getting back to "normal" in some places around the world, this data proves that "normal" is not the same without the lives of so many. And maybe, just maybe, analysis and visualization of this dataset can encourage others to get the vaccine, and we can be more informed moving forward.
Thank you for sharing Amy and I’m sorry for your loss. It definitely does give you some interesting insights into the real data rather than just seeing what is on news websites.
I lost my grandma, the closest person to my heart in this whole world. I know what the loss is, the dead may be just numbers for someone but only the people who lost someone close, knows the pain.
This was a lifesaver when I needed a portfolio quickly for a job interview while still being a beginner. It was easy to follow and I could base my own projects on it. This definitely was part of me getting the job!
For me, this way worked: Select DB in SSMS > Tasks > Import Data > Next > Data source = Flat file source > Select CSV (!) file (you have to change expected file extension during file selection) > Locale = English (United States) (i don't know if it matters though) > Next > Destination > Microsoft OLE DB Provider for SQL Server > Next > Next > Finish
Hey I need help. After uploading my data, doesn't show up under the database. I have refreshed repeatedly but no luck with it. Any idea how I can solve this issue? thanks
Man... you're amazing. Don't congratulate us to have stayed until the end! It's TONS of work you've done and it's only to be applauded and appreciated! I bet many agree with me - against your sore throat you guided us through soooo many ideas and possibilities! Thank you so much and wish you MILLIONS views!
Thanks alot for this great walkthrough Alex. 👏 👏 Just a quick question for the audience, I am looking for a good budget laptop. My primary purpose is to be able to run all the DB applications online or offline including SQL, PowerBI. Please suggest under $600-800
I can’t express how much I appreciate you creating these tutorials. As a beginner prospective analyst looking to add projects to a portfolio, I was lost and overwhelmed. Your videos are a godsend. Thanks a lot
@@AlexTheAnalyst Godsend is an understatement. As a beginner, getting something together from what you have studied is probably the hardest and having someone show you how to get that done with all the errors along the way, makes it seem possible. Glad to have found this channel.
I totally agree. I graduated from the data analytic course recently and was also struggling with the portfolio project. Alex's video is definitely a godsend and very helpful.
Just when I thought this guy has done a great deal of work for beginners like me, he goes on to do greater things like this. Really thank you Alex -from an aspiring analyst
What I really want to thank you for is making this real. I mean, keeping all your own mistakes in the video is a booster for Aspiring Analysts like myself. "Yes, it can get messy. We just need to keep calm and figure it out". Thanks once again, Alex
For those who are currently working on their portfolio project and can't import Excel file using any of the methods Alex showed. I tried this and it worked. Save your file as CSV and apply the first method Alex showed right click the portfolio project, select task, in the dialogue box, select flat file source, browse your folder to select the CSV file (ensure the extension on the browse dialogue box is .CSV, so you can see your CSV files and select it, destination option select SQL server and click next till finish" Hope it helps. Alex thanks again for the video . I can now go ahead with my project
This is SO helpful, thanks Alex! For anyone looking for the intermediate-level SQL queries, they're near the end - here are the timestamps: 51:20 Joins 62:00 CTEs 66:30 Temp tables 70:20 Views
I just completely watched this priceless video. Many thanks Alex for teaching us free of cost and guiding us all along. I am an Economist and have been learning data analysis since 2022 and it is amazing that I have found your channel and you.
For those doing it from March 2024, you should cast all the columns to floats. Took me days to figure it out. The data in the tables are more and int or even bigint doesn't cut it
Thank you so much! At 1:06 I got stuck because of the constant errors I was getting. When I read your comment and casted as float on the line code reading ", SUM(CAST(vac.new_vaccinations as float)) OVER (Partition by dea.location order by dea.location,dea.date) as RollingPeopleVaccinated" I finally got the query to work as it was suppoded to.
Hi Alex! Doing this in July 2023 and I think the SMSS features have changed a bit. Instead of using SQL Server Native Client 11.0 it is now Microsoft OLE DB Provider for SQL Server. This took me a while to figure out and import. Hope this helps!
I'm doing this in May 2024 and had trouble with inserting data. Here is the solution that worked for me: Select DB in SSMS > Tasks > Import Data > Next > Data source: Flat file source > Select CSV file (make sure you have saved the excel file as a CSV) > > Next > Destination: Microsoft OLE DB Driver for SQL Server > Click on Properties and enter server name, change to Windows Authentication, Select your Database name and test connection > Next > Next > Finish
@@transplantman2287 I hear you! I looked into the 2016 redistribution download and everything but nothing worked other than this. Hoping I can figure it out soon but this way for sure worked and got all the data inserted
@@pranavbhawane7591 When you first open the SQL Server studio, you will get the pop-up to connect. In this pop-up, you'll see the server type, server name, authentication... The server name is listed here. I would suggest copy and paste this before connecting to your server that way you already have it when you type in the name for the database
This has been beyond helpful! I've been taking lots of SQL classes at Datacamp, and as you mentioned in your video on Datacamp, one of the drawbacks is not necessarily knowing how to apply the concepts within the software. This helped me feel a lot more confident navigating and applying concepts with SQL Server :)
As someone who had to track this type of data since the early days of the pandemic, the project hits a little different. I wish I had found data analysis tools sooner, spent days of work finding, loading and analyzing this information when we didn't have that kind of time. This isn't just a tutorial project just to learn, its the real deal!
I'm using MySQL and had a problem when importing the .csv files to the MySQL server. The entire records didn't go into the server, so I changed all the empty values to NULL with Pandas and I finally got the entire records. For people who are facing the same issue. AND I really appreciate your videos!
@@joshuajosh5181 Use pandas to replace all empty cells with a space and save as a new modified CSV. Select Table Data Import Wizard under Table in MySQL....Hope this will help
I’m only 20 mins in and already learned more than my 4 years of bachelors schooling lolololllol. Plus I LOVE how you make tiny mistakes ( we are human, it happens, but they are common something we as beginners might make) and show us directly how to avoid or problem solve and fix them!!! You’re my hero !
HI Courtney, do you mind figuring out what is wrong with this code? select location, date, total_cases, total_deaths, (total_deaths/total_cases)*100 as deathpercentage from [covid death] order by 1,2 Operand data type nvarchar is invalid for divide operator.(this is the output i keep getting)
@@amosadeleke4548 "Operand data type nvarchar is invalid for divide operator" suggests that one or more of the columns used in the calculation for the death percentage field may be of data type nvarchar, which cannot be used in mathematical operations. To fix this issue, you need to ensure that the data type of both the total_cases and total_deaths columns is numeric, such as integer or float, before performing the division operation. One way to achieve this is by converting the data types of these columns using the CAST or CONVERT functions. You can use the below code: SELECT location, date, total_cases, total_deaths, (CAST(total_deaths AS float) / CAST(total_cases AS float)) * 100 AS deathpercentage FROM [covid death] ORDER BY 1, 2
@@amosadeleke4548 what i did here to solve this is i go to his github to download the coviddeaths and covidvaccination the author said that the dataset have been change by the source that is why you get that error so try to do it again as for me i get stuck at 1:09:30 temp table
How you explain these steps is so kind and encouraging and makes nervous beginners such as myself feel fully capable!! THANK YOU. Honestly, without your videos, I probably would have felt too intimidated by data analytics and kept searching for another profession to change into (like you, I'm transitioning from healthcare!)
I appreciate that you talk about where you have issues, as a developer I'm so used to having people breeze through projects in their video without talking about the hiccups they had on their first run before recording. Although it doesn't provide much help in the way of solving problems while trying to follow along, it does help make it clear that solving the weird problems is a part of the journey (and in some cases a part of the fun)! Edit: For those curious, we're now at 92m cases as of August 2022 with a Death Percentage of 1.12%
Thanks Alex, you're great!!! Today I finished my first project in SQL and I plan to complete all your videos. It's one of the best courses I've ever done, congratulations! I wish success to all students who, like me, seek to grow professionally.
How were you able to load the data.? I'm using the 19.1 version and for some reason it does not give me the option of import 64 bit and load the data as he mentions. Please help.
You're a life saver! i just finished the google data analytics course and since im new to all of this i had no idea what to do or where to start to make my portfolio to apply for jobs. Really appreciate you ❤
Dear Alex, as of July 2023 it was a real pain to install SSMS 19 and SQL server 2022. Many errors whilst trying to Connect to the server, and then to import the data through the Wizard...but I managed to find solutions to everything. Right now I just finished the whole Project Tutorial and feel grateful to you for dedicating time and effort to teach us. I'm looking forward to finish the whole Bootcamp and become a greater Data Analyst day by day. Cheers!
@@favourchukwumam3179 Hello mate. I used Alex's original data file in the description box. Nowadays the data set has changed drastically in order (i.e. columns). I just went to the website "ourworldindata" and downloaded the file in .CSV, then a quick search with ctrl+F found the "population column" in the AW spot in the data sheet. Hope this helps! Cheers.
To the ones stuck on 19:10 , use the following code to convert into float datatype when ''Null" Select location, date, total_cases,total_deaths, (CONVERT(float, total_deaths) / NULLIF(CONVERT(float, total_cases), 0)) * 100 AS Deathpercentage from PortfolioProject..covidDeaths order by 1,2
Just finished the video after weeks of starting it and I'm so happy I finished it! I really want to thank you Alex for taking out time and teaching this cost free, it's my first SQL portfolio and I must say it worth the time spent.
@@kelechieva7473 did you edit your data in the excel file before uploading it? Because I’m getting errors about the data being varchar vs numeric and also errors about dividing by zero. I’m trying to understand why he doesn’t get these errors in the video, if we’re using the exact same data.
You are doing an excellent job filling the gaps between knowledge and experience! Just finished Google’ s data analytics and building my portfolio, all thanks to your videos. Many thanks!
Extremely useful video! Thanks so much. I play around a lot with data in SQL but was having a challenging time figuring out how to display my knowledge in a portfolio project. This really gave me a solid idea of how and which skills to include. As a healthcare professional looking to switch careers, this was a great topic as well.
I just started with it and realized the data is no longer available with so many comments about it. Thank you for sharing the data in the links, you are so considerate.
It is currently 2023 and I am doing this since yesterday May 29, 2023. Challenges encountered so far: 1. Using Macbook Air M1, and SQL Server Management Studio (SSMS) is not natively compatible, I have to use Azure SQL Edge virtual container in Docker(which makes my Mac a bit warm). I have to use Azure Data Studio for SQL IDE. Both Docker and Azure Data Studio can run natively in M1. 2. I am not able to import Excel files to the database, I need to convert them to CSV. Azure Data Studio can only import flat files like CSV and JSON. 3. Before importing, most of the numeric attributes needed to be declared into a float data type based on the error-caching of Azure Data Studio. All in all, it was a great dive, thank you, Alex!
Thanks, this really helped! 1. Tutorial for connecting Azure Data Studio to Docker - th-cam.com/video/3KSFcDecN3w/w-d-xo.html 2. Converting almost all values to floats 3. Accepting Null values Spent like 2-3 hours fighting with it...
Running this on windows is sooooo much easier than mac ! just fyi...On a mac you have to go to your terminal and program it in there to be compatible ... a lot of work to even come up with the right code! And Alex you ROCK! Thank you so much, wish there were more people like you out in the world giving back the same way you do!!
Hey! I'm struggling trying to import the files from cvs to my sql on my Mac, did you figure it out, would you mind explain me how do it, thank you so much
I have started and stopped this video many, many times, but today I got to the end and I'm glad I did. If you're struggling, that's okay. Take a break and come back when you're ready. Happy learnings y'all. And, big thanks Alex🎉
This is great to start with thanks Alex :) . If anyone like me had an issue with the NULL value for the Date column after importing the excel files to SQL Server, you might need to change the "Excel version" in SQL Server Import and Export Wizard to Microsoft Excel 2016. Also check your system date format to make sure it is the same format as in the video. These changes resolved my issue.
This trick works for me to import xlsx files. convert xlsx file into .xls format (by renaming filename) CHOOSE A DATA SOURCE as follow: Data source: Microsoft Excel - Excel file path: select .xls file by browsing - Excel version: Microsoft Excel 97-2003 Hope it helps you! Thanks Alex for your hardwork!!!
Thank you soooo much, Alex. For those using Mac and having trouble importing xlsx into MySQL……Here’s my solution: 1.reformat date to yyyy-mm-dd and save as CSV 2.use python to replace all empty values in this CSV with a space (import pandas……fillna()…….) 3. create a new database in MySQL, and select Table Data Import Wizard under Table 4. Select your new modified CSV file and import…Hope this will help!!
Thank you Alex for making this learning process an easy and interesting journey. I'm certain that I'm getting a Data Analyst job very soon. I can't believe that I can finally analyze data using SQL, I've tried learning for months from other sources until I found yours. Thank you Alex.
Thank you so much Alex, I really feel this going to get me an actual shot for interviews and a new career, my online courses don’t seem to impress many employers lol
Hi Alex! You are one of the real heroes. During the course of my graduate school studies and doing work on the side, I have had the great fortune of attending seminars, classes, workshops, etc. in great educational institutions from Europe, North America, and Asia. On that note, I have the chance to sit down maybe in the presence of great minds in my field. What I find common among them is the level of emotion, engagement, conviction, and insightfulness when they deliver they disseminate their knowledge and wisdom. Yeah, maybe I'm also a sucker for an excellent education. But man! Sorry to put you in the spot, but you're one of those few people I put in the category of those great educators. You are brilliant! I would compare the great Sal Khan (of Khan Academy) at least in the field you teaching. Much Thanks and appreciation. I'm a constant consumer of your content and enjoy extensively learning from them. Hoping you continue your great work. So happy to hear the last part of this video.
@@VitorAbreu1001 i also having error like this "Operand data type nvarchar is invalid for divide operator." i tried to change the data type to float..but not working
@@amtzgaming3688 The code will look a bit different from Alex because I went for the updated dataset instead but the error you are saying I managed to fix with the code I have on the GitHub!
@@amtzgaming3688 Select location, date, total_cases,total_deaths, (CONVERT(float, total_deaths) / NULLIF(CONVERT(float, total_cases), 0)) * 100 AS Deathpercentage from PortfolioProject..covidDeaths order by 1,2
Hey Alex, thank you so much. I'm certain that I'm getting a Data Analyst job very soon. I can't believe that I can finally analyze data using SQL, I've tried learning for months from other sources until I found yours. I recently finished the Linkedin Data Analytics Certification, and I've been struggling to figure out how to get my portfolio started. I want to tell you I’m glad you showed your mistake live. I really want to thank you for making this real. It’s important for beginners to see that experts also make mistakes and how can they solve. It is encouraging. As a bonus, I will better remember to save as .xlsx because you pointed it out :) keep going :D
@@AlexTheAnalyst Please help me out, am getting same errors when I try importing from the Portfolio project database But the alternative way you used isn't bringing up Microsoft Excel in the data Source in my system I don't know why
@@triumphotamiri7904 I'm not sure if you figured this yet, but if you didn't.. I was having the same exact problem so what I did was save the files in csv format and then import them as flat data sources since the Microsoft Excel data source was missing
I did data analysis and error management in a HUGE database for sprint. The work I did was extremely similar to this. The database was way messier and more confusing than this, but if you can work out the problems as you go like he is, this is basically what I did everyday.
Hi, you seem to have prior experience of related datasets so pardon my asking. I'm at the part where I divide total deaths by total cases to get the deathpercentage. However upon running the query , I'm getting an error message that says 'operand data type nvarchar is invalid for divide operator'. Can you kindly suggest how solve it? Thanks
I really appreciate your efforts to train other beginner level students to Data Analytics (I'm one of them). Pls keep making such fruitful vedios for us. You're the best man!!!
38:23 The reason we are not getting correct total deaths when we break down by continents is that in our query we are using MAX(cast(total_deaths as int)), so what it does is it returns the maximum total_death from that particular continent(ex in Oceania continent it returned 910, which is the total death count for Australia and highest in its continent). We need to replace the query by- select continent, sum(new_deaths) from coviddeaths where continent!='' group by continent; Hope this helps!
Please do no congratulate us at the end. It is my privilege that you put in so much hard work to show how things work. I came here to learn how to 'play around' with the downloaded single file (any form ) and make it multiple files and do analysis. I must say I learn so much new things in SQL as for self learner in this almost 4 months journey, I feel comfortable working with Excel, Power Query, power pivot ,DAX ,Power BI etc. and when ever I encounter with SQL tables ,my main focus is always make simple query in SQL and then copies/import them into Excel for analysis. From today onwards, I would like try the reverse just to come out from my comfort zone. Thanks for your video. It is al so helping me to create portfolio of my work as this this the time to do that.
Ok so something I want to point out to the newbies, like me, who may be frustrated, don't give up and don't be afraid to ask. Also, I spent an hour trying to figure out how the results pane was kept so clean.... Highlight the text, then click "execute". If you just do the text or code and then click execute, your results pane will stack and eventually you won't be able to see any of your results. Highlight, then execute. This is something nobody explains. Even the Google certificate course doesn't tell you this little detail that is important.
I really appreciate the inclusion of mistakes and research time, etc. I've been working with software long enough that I can google my way through all kinds of things. But I'm trying to start a data analyst career. And I get depressed when I read the "requirements" in job listings. So it's great to see what reality is for someone who got a job... and has their own TH-cam channel.
Alex, your videos are amazing and you have really helped me get to a place in sql where i feel like i can jump off and just continue to increase my skills and value. I will continue to return to your content regularly and support how i can.
I know you said you felt like you were rambling as the video progress, but please don't think so! It really helps understand the thought process behind each query. Thank you so much Alex for these videos! :)
Hi Alex! Thank you very much for this video, I have been watching these videos thanks to the bootcamp playlist and I have learned a lot. For those who have problems uploading the files to SQL Server, what I did was transform the files to CSV and import them as Flat File, in the same way that Alex mentions it as the first option, at some point it will ask for the destination and I selected the one for “Microsoft OLE DB Provider for SQL Server”, or something like that. I know very little about this software, so I don't know exactly what I did, but it worked. I hope it works for you too. Greetings from Mexico!
Thank you Alex. I was really lost for the past 6 months, not knowing where to start! you really helped me a lot as a beginner, looking forward to more portfolio projects!:)
I just completed this project and I would say this is really fantastic. I appreciate the effort you put in putting this video together. Thanks for your help Alex🙌
Hi I also want to complete this project. But I am enable to export this large dataset from Excel to Microsoft SQL. As the data has grown since the video was released. Were you able to get all rows in SQL ,which were lakhs in number approximately
Thank you for doing this tutorial I've learn SQl from Udemy and Datacamp. But I really have no idea how to practice SQL until I found your channel! Such a gem.
7:25 I believe the reason is because you saved the excel file as .xlsx (64 bit), so you won't be able to use the 32 bit "SQL Server Import and Export Wizard" to import the data. Try saving the excel file as .xls instead (32 bit). At least that works for me :) Awesome content btw!
xls have only 64k rows. xlsx have +1m rows. xls is Excel 1997 format and have nothing to do with 32/64k. Its simple NOT compressed format. Wide used to exchange data between many systems and have status 'spreadsheet standard' in the old days. change ext of ANY xlsx file to .zip and you can open it by any archive program.
For the query at 58:56, I was getting an error: "Arithmetic overflow error converting expression to data type int. Warning: Null value is eliminated by an aggregate or other SET operation." For those of you getting the same thing, change the "int" into "bigint", apparently its due to the sum function.
Great catch! I recently noticed this earlier in the video too - int allows max number of 2,147,483,647, bigint allows up to 9 quintillion (9,233,372,036,854). So when vaccinations surpassed ~2bn the video became just slightly obsolete 😀
Awesome! The honesty and admittance of mistakes is huge in my mind. Data Science/Analyst tasks have lots of moving parts. It can be very confusing for people, even Instructors. Thank you Alex, you're doing Great, in my view.
I just want to make sure that you know the value of the work that you have been doing. This is so far my favorite portfolio project vídeo on TH-cam. I enjoyed every moment of it. Especially the errors/mistakes! I’m learning analytics and I face those errors I feel so frustrated and wondering if one day I will be really good at it. But watching your video I could see that the mistakes and experiments are part of the process!!! Thank you very much!!!
Alex, you're awesome. Thank you very much for taking out time to make these videos. These are priceless. I was struggling to create a project. Thanks a lot again.
What a coincidence!! I’m currently doing the Google Data Analytics Certificate Program. Already completed 6/8 courses in 3 weeks. Now in Course 7 learning about R. Found this dataset last week and plan to use it for my personal project. Very excited to see you work on this exact same dataset ;-)
-- Operand data type nvarchar is invalid for divide operator. I'm getting this error on this SELECT Location, date, total_cases, total_deaths, (total_deaths/total_cases) *100 as DeathPercentage FROM CovidDeaths_updated I checked chatgpt and I was directed to change the columns to INT by doing this: ALTER TABLE CovidDeaths ALTER COLUMN total_deaths INT; After doing so, my data seems inaccurate. Did I miss anything? By the way, I also ch went back and updated the data types in Excel and tried importing it again. But I am still getting the same error. TIA.
For those using Mac and found MySql as their preferred option; before you import data, be sure to reformat the date to yyyy-mm-dd so that MySql recognizes the date as an actual date. To do this, highlight the column and press control+1, go to date and select the format I mentioned above. Hope this helps someone!
Hey Jack. I formatted it to yyyy-mm-dd and then imported the files. But are you able to see the results sorted by date when we order by date? I tried cast(date as date) as date1 but still the result is not ordered by date.
@@rrromal I'm having trouble importing as well. I'm using Azure Data Studio on Mac. Is that what you were using and were you able to figure out the issue?
Thank you so much Alex for making these informative videos and especially for portfolio projects. I have completed my first project with the help of your video it took me 5 hours to complete. Respect Man 🙌
Can't thank you much for taking this initiative and helping beginners like me to apply the SQL skills to real-world data. Waiting for the second one. You are awesome buddy.
Can't thank you enough for this content!! Small note at 48:38 - I believe SUM(new_cases) and SUM(new_deaths) will be the same as new_cases/new_deaths (won't reflect the true totals) because you are grouping by date. I think it may be more accurate to use MAX(total_cases) and MAX(total_deaths) given that the values are compounded each day and will never decrease. Would love to hear your thoughts/feedback. Really loving this channel - keep up the great work.
For those getting an error message "ORDER BY list of RANGE window frame has total size of 1020 bytes. Largest size supported is 900 bytes." at time 1:00:09 in video, the solution I found is the following: SUM(cast(vac.new_vaccinations as bigint)) OVER (Partition by dea.Location Order by dea.Date)
HI ALEX, I am from Philippines ,I appreciate you and your heart to helping us, you are my stepping stone in this career, Thank you again and God bless you!
Great Project - Thanks Alex! Had some trouble importing in the data, but figured out a fix. I did it as a Flat File (CSV) and for some reason, the data types which were recommended were completely wrong (e.g. Varchar was given for some, instead of Float). So I had to manually change a few of the data types myself. Tedious, but was the only solution here :)
@ezhankhan1035 - How did you change the data types? It is blocking me from changing them when I am uploading the CSV file in SQL Server Import & Export Wizard
@@conorkelly9642 I think I used a separate import wizard, just for flat files (CSV files). I right-clicked the database (which I wanted to create the table in) - went to tasks - import flat file. This way then allowed me to change any data types and all that good stuff. Hope it helps!
Thank u so much for the video! Something that I think it is important to clarify that the variable new_vaccinations refers to the number of doses available and not to the number of people vaccinated. If this exercise is done with the updated data, the cumulative percentage exceeds 100% in many countries. I think it should be reinterpreted as 'ratio of doses administered per 100 inhabitants'.
pro tip! negotiate access to different services such as tableau or other memberships into your job offer. Lots of times in today's job market things like salary are a fixed formula and are not available for negotiation. Fringe benefit items such as this often CAN be negotiated.
I see from your task bar you're burning the midnight oil, Alex! Such a great showcase of building your portfolio for a data analyst, love this content!
For anyone using Azure Data Studio and having trouble importing the CSV files with the SQL Import extension. You will need to change around with the Data Types that Azure identifies. e.g. change Small Int to Int or nvarchar(1) to nvarchar(50). In addition, I made all columns to allow null values. Making these corrections then worked and both CSV files imported into my database.
I'm using the same software the author is and having same issues. I will probably have to go back into excel and change a LOT of the data types because almost all of the numerical columns are showing up as nvarchars. Makes any math functions impossible to use.
@@curiousss4960 Did you ever figure this out? I feel like I'm running into the same issues. No matter what I do to the data in excel (making sure all numbers are actually in a number format), every time i try to perform a calculation in SQL i still gets "0"s. I even tried creating a table from scratch in SQL, specifying what each column's data type should be, and kept running into the same issue.
Doing this in Nov 25th 2024. So glad I followed along until the end. I had a problem with exporting data..but I created the tables manually in MYSQL and then wrote an SQL query to transfer the data using “Load data infile” ..thank you so much Alex. Great video. If you get stuck..don’t give up 😂. Took me 3 days to finish
This was awesome -- thanks, Alex! I had a little trouble as I was working on BigQuery instead of SSMS, but I was able to figure it out for the most part. Trying to get a portfolio going, and I've done a few guided projects but think you did a much better job going through and explaining things than the others. Looking forward to the next project! :)
I'm doing it in April 2024. The new_cases and new_deaths columns are reported every week, not daily anymore. You can just add new_cases 0 in the where statement and it will show you the weekly cases.
For whoever is having trouble in inserting their data in temp table, make sure to have the same data type of the columns in the covidDeaths table and temp table. And I found it better to specify the columns that you're inserting data in as: INSERT INTO #PercentageOfVaccinatedPopulation(location,date,population,new_vaccinations,TotalInLocation)
I actually did mine a bit differently because I noticed that people_vaccinated is a running total and more accurate than the new_vaccinations (since that column was only getting updated once in a while instead of each day new vaccinations were being performed 💡). I don't know about the earlier data sets, but the one I pulled on 11 November 2023 actually shows an interesting twist too that I didn't think about. If a person was quarantined outside of their resident country and received the first "vaccination" in the country they got it, then the spreadsheet shows more vaccinated people than residents in some cases. That also means though, that if they returned to their country of origin after they were initially "vaccinated" and received a booster there, then they will show again in the people_vaccinated for that country. Food for thought.
Absolutely amazing video, I have finally realised that as a beginner in the financial investment market, you can achieve close to nothing yourself because you still have a lot to learn. Trading with a professional broker is more profitable and my advice for beginners is to always take advantage of that.
Instead of timing the market, you should try to diversify your portfolio in order to get a dollar-cost average when it’s time to retire. Keep in mind that you don't need a ton of money to invest. Investing in small amounts can build long-term wealth too!
Great video and channel Alex. When creating the view around the 70 minute mark, I had to add "USE PortfolioProject GO" before the "CREATE VIEW" to get the view to show in the object explorer. Hope it helps someone. So thankful for the channel!!!
I'm doing this in Oct 2021 and apparently one of the code chunks where you need to convert new_vaccinations column to integer, the sum value now has exceeded 2,147,483,647. So instead of converting it to "int", you will need to convert to "bigint". Hope this helps everyone.
Totally helped! Thank youu!
Thank you , that was a real big help for me.
You totally saved my life! I was almost doing crazy!
Thanks, I can stop banging my head.
Totally helped! Thank you!
I’ve just started the video and I’m about 9 minutes in. I wanted to stop at this point to tell you I’m glad you showed your mistake live. It’s important for beginners to see that experts also make mistakes. It is encouraging. As a bonus, I will better remember to save as xlsx because you pointed it out. So thank you for leaving that in.
I make a ton of mistakes! haha the cuts that I took out were 5+ minutes of me having to research something or figure something out and then coming back. Didn't want to keep that boring stuff in lol
Agreed. The realness is encouraging for us mere mortals (and the mistakes help give me time to catch up to Alex...)
@@Major_Data well let’s not get carried away here. This is THE Alex the Analyst we are talking about! 🤣
@@veronicab2096 oh, I didn't mean catch up to him on skills. I just meant keep up with him on the video. 🤣
Years ago I said no to programming because I always find myself making mistakes (I'm a GIS Engineer). Then came my friend, a software developer also make tons of mistakes. I questioned him because he has a degree in software engineering with years of experience, then he replied "I'm a human, not a computer. Even human to human also sometimes struggle to communicate" haha
I'm transitioning from teaching to data analytics. I recently finished the Google Data Analytics Certification, and I've been struggling to figure out how to get my portfolio started. I found this video, and I got really excited to use this as a guided project. Then, I saw Alex was using Covid data, and I had to pause to consider if I could handle it. I lost my dad to Covid 4 months ago. This is personal.
However, I think I need this now as another way to process through the continuing grief. The fact is, data is personal. Each of these data points describes people. Someone's father, mother, sister, brother, grandparent, friend, and so on. This data is reality, and it's so important to analyze it in order to truly show the impact that this pandemic has made. Especially, as things may be getting back to "normal" in some places around the world, this data proves that "normal" is not the same without the lives of so many. And maybe, just maybe, analysis and visualization of this dataset can encourage others to get the vaccine, and we can be more informed moving forward.
Thank you for sharing Amy and I’m sorry for your loss. It definitely does give you some interesting insights into the real data rather than just seeing what is on news websites.
So sorry for your loss. In Ghana we tell people to adhere to the protocols if they don't want to be used as data.
so did I , google data anlaytics certificate was too basic and so many theory!
@@garry6882 Yeah true
I lost my grandma, the closest person to my heart in this whole world. I know what the loss is, the dead may be just numbers for someone but only the people who lost someone close, knows the pain.
This was a lifesaver when I needed a portfolio quickly for a job interview while still being a beginner.
It was easy to follow and I could base my own projects on it. This definitely was part of me getting the job!
How did the J interview go
I wish I could give a "bravo" reaction to this comment! Congrats! Hope to hop on your boat soon enough and happy to have found this video!
how did you mention this project in your portfolio? like summary of this project. please help me with this.
Please where did you host your portfolio?
i mean how do you paste projects on your github if that's where we'll be hosting all our projects
For me, this way worked:
Select DB in SSMS > Tasks > Import Data > Next > Data source = Flat file source > Select CSV (!) file (you have to change expected file extension during file selection) > Locale = English (United States) (i don't know if it matters though) > Next > Destination > Microsoft OLE DB Provider for SQL Server > Next > Next > Finish
Hey I need help. After uploading my data, doesn't show up under the database. I have refreshed repeatedly but no luck with it. Any idea how I can solve this issue? thanks
Same,I’ve encountered the same problem
when choosing destination, select "microsoft ole db provider for sql server"@@brendachiri2890
@@godswillekanem have you checked "Tables" under database?
Thanks man. You really saved my day with this method. I've wasted 2 entire days trying to import data already!!
Man... you're amazing. Don't congratulate us to have stayed until the end! It's TONS of work you've done and it's only to be applauded and appreciated! I bet many agree with me - against your sore throat you guided us through soooo many ideas and possibilities! Thank you so much and wish you MILLIONS views!
Working on your education isn't easy, I'm proud you made it to the end - shows commitment :)
Thanks alot for this great walkthrough Alex. 👏 👏
Just a quick question for the audience, I am looking for a good budget laptop. My primary purpose is to be able to run all the DB applications online or offline including SQL, PowerBI. Please suggest under $600-800
Hey did u get the data from jan 2020 bcz now i am doing it and same website is consisting of data from feb 2020 to september 2022
I did everything up till the end and keep refreshing the View tab but it won’t appear.
❤ATA❤
Great content, as per. You helped me to get my dream job as a modelling analyst in the last couple of weeks, I am forever grateful to you!
That's so awesome! Congratulations!!
Great to here. What did the interviewers look for? Excel,SQL, and Tableau/PowerBi? I'm asking because I want to make sure I am on the right track.
@@CE-vd2px Please check previous videos of Alex, he has made some good videos on DA skills
@@binodrai3653 I did but I like to verify.
@@CE-vd2px in addition to what you have mentioned learn Python or R.
I can’t express how much I appreciate you creating these tutorials. As a beginner prospective analyst looking to add projects to a portfolio, I was lost and overwhelmed. Your videos are a godsend. Thanks a lot
So awesome to hear it!
@@AlexTheAnalyst Godsend is an understatement. As a beginner, getting something together from what you have studied is probably the hardest and having someone show you how to get that done with all the errors along the way, makes it seem possible. Glad to have found this channel.
I totally agree. I graduated from the data analytic course recently and was also struggling with the portfolio project. Alex's video is definitely a godsend and very helpful.
Sir where can I get this data set
I did everything up till the end and keep refreshing the View tab but it won’t appear.
Every time I watch this part, it makes me smile. "I don't understand, don't ask me". Thank you Alex.
Just when I thought this guy has done a great deal of work for beginners like me, he goes on to do greater things like this. Really thank you Alex -from an aspiring analyst
You're most welcome! So glad it's helpful!
@@AlexTheAnalyst can you tell What is the Primary key in this Table?
@@k-EE-VIPESHDUNKWAL In short you can say it's the unique identifire of every rows in a table.
What I really want to thank you for is making this real. I mean, keeping all your own mistakes in the video is a booster for Aspiring Analysts like myself.
"Yes, it can get messy. We just need to keep calm and figure it out". Thanks once again, Alex
For those who are currently working on their portfolio project and can't import Excel file using any of the methods Alex showed.
I tried this and it worked.
Save your file as CSV and apply the first method Alex showed right click the portfolio project, select task, in the dialogue box, select flat file source, browse your folder to select the CSV file (ensure the extension on the browse dialogue box is .CSV, so you can see your CSV files and select it, destination option select SQL server and click next till finish"
Hope it helps. Alex thanks again for the video . I can now go ahead with my project
Thank you so much, that really helped.
Thanks a lot, it worked for me
Yes! That is true also because it works!
top comment
My guy! Thank you!
This is SO helpful, thanks Alex! For anyone looking for the intermediate-level SQL queries, they're near the end - here are the timestamps:
51:20 Joins
62:00 CTEs
66:30 Temp tables
70:20 Views
great comment craig!
Hi, please can you help with the particular dataset that was used in this video?
@@eniolababafemi8549 Please see the link to it in the video description.
I did everything up till the end and keep refreshing the View tab but it won’t appear.
@@s.i.8852 Make sure you are filtered to Portfolio Project and not Master when you create it. Then refresh and it should show up
I just completely watched this priceless video. Many thanks Alex for teaching us free of cost and guiding us all along. I am an Economist and have been learning data analysis since 2022 and it is amazing that I have found your channel and you.
For those doing it from March 2024, you should cast all the columns to floats. Took me days to figure it out. The data in the tables are more and int or even bigint doesn't cut it
Thank you so much! At 1:06 I got stuck because of the constant errors I was getting. When I read your comment and casted as float on the line code reading ", SUM(CAST(vac.new_vaccinations as float)) OVER (Partition by dea.location order by dea.location,dea.date) as RollingPeopleVaccinated" I finally got the query to work as it was suppoded to.
@@maddie8564 I'm glad I could help
thank you so much i was getting errors and this helped
Thank you!
did you also find out that not all the data gets loaded onto sql for some reason? I only got about 2600 rows in smss. Not sure what to do to fix that
Thanks Alex! Love the open source mentality and bootcamp!
Were you able to import the data into SQL? What type of SQL are you using?
This is my first project on SQL. I couldn't have done without you.
Thanks a lot
My Only Regret is not finding your Channel sooner Alex. You have helped me so much and I am super grateful.
Hi Alex! Doing this in July 2023 and I think the SMSS features have changed a bit. Instead of using SQL Server Native Client 11.0 it is now Microsoft OLE DB Provider for SQL Server. This took me a while to figure out and import. Hope this helps!
Thank you, been at it for hours
God bless you brother..... almost felt left out
Helped me a lot !!!1 Thanks
Thank you so much for this.
thank you so much i was breaking my head
I'm doing this in May 2024 and had trouble with inserting data. Here is the solution that worked for me:
Select DB in SSMS > Tasks > Import Data > Next > Data source: Flat file source > Select CSV file (make sure you have saved the excel file as a CSV) > > Next > Destination: Microsoft OLE DB Driver for SQL Server > Click on Properties and enter server name, change to Windows Authentication, Select your Database name and test connection > Next > Next > Finish
Thank you soo much😭😭😭❤️
I've been googling my way through errors for TWO MONTHS, thank you so much for this.
@@transplantman2287 I hear you! I looked into the 2016 redistribution download and everything but nothing worked other than this. Hoping I can figure it out soon but this way for sure worked and got all the data inserted
How to find a server name
@@pranavbhawane7591 When you first open the SQL Server studio, you will get the pop-up to connect. In this pop-up, you'll see the server type, server name, authentication... The server name is listed here. I would suggest copy and paste this before connecting to your server that way you already have it when you type in the name for the database
This has been beyond helpful! I've been taking lots of SQL classes at Datacamp, and as you mentioned in your video on Datacamp, one of the drawbacks is not necessarily knowing how to apply the concepts within the software. This helped me feel a lot more confident navigating and applying concepts with SQL Server :)
As someone who had to track this type of data since the early days of the pandemic, the project hits a little different. I wish I had found data analysis tools sooner, spent days of work finding, loading and analyzing this information when we didn't have that kind of time. This isn't just a tutorial project just to learn, its the real deal!
I'm using MySQL and had a problem when importing the .csv files to the MySQL server. The entire records didn't go into the server, so I changed all the empty values to NULL with Pandas and I finally got the entire records. For people who are facing the same issue. AND I really appreciate your videos!
Hey, do you mind explaining this a bit
I'm having issues importing the CSV files into Mysql as well. Unfortunately, I don't know much about Python. Is there any other way around this?
@@akinsanyaoluwatomisin513 Hi there, have you been able to figure out a way around it? Having the same issue :(
Thanks for your suggestion !! I am a Mac users, your comment is a life saver!
@@joshuajosh5181 Use pandas to replace all empty cells with a space and save as a new modified CSV. Select Table Data Import Wizard under Table in MySQL....Hope this will help
Your effort in helping us land data analyst jobs and just learning in general is very much appreciated. You will be remembered!
I’m only 20 mins in and already learned more than my 4 years of bachelors schooling lolololllol. Plus I LOVE how you make tiny mistakes ( we are human, it happens, but they are common something we as beginners might make) and show us directly how to avoid or problem solve and fix them!!! You’re my hero !
HI Courtney, do you mind figuring out what is wrong with this code?
select location, date, total_cases, total_deaths, (total_deaths/total_cases)*100 as deathpercentage
from [covid death]
order by 1,2
Operand data type nvarchar is invalid for divide operator.(this is the output i keep getting)
@@amosadeleke4548 "Operand data type nvarchar is invalid for divide operator" suggests that one or more of the columns used in the calculation for the death percentage field may be of data type nvarchar, which cannot be used in mathematical operations.
To fix this issue, you need to ensure that the data type of both the total_cases and total_deaths columns is numeric, such as integer or float, before performing the division operation. One way to achieve this is by converting the data types of these columns using the CAST or CONVERT functions.
You can use the below code:
SELECT location, date, total_cases, total_deaths,
(CAST(total_deaths AS float) / CAST(total_cases AS float)) * 100 AS deathpercentage
FROM [covid death]
ORDER BY 1, 2
@@harikrishna7634hi i tried this and it didn’t give me the result i wanted
@@amosadeleke4548 what i did here to solve this is i go to his github to download the coviddeaths and covidvaccination the author said that the dataset have been change by the source that is why you get that error so try to do it again as for me i get stuck at 1:09:30 temp table
How you explain these steps is so kind and encouraging and makes nervous beginners such as myself feel fully capable!! THANK YOU. Honestly, without your videos, I probably would have felt too intimidated by data analytics and kept searching for another profession to change into (like you, I'm transitioning from healthcare!)
I'm so glad to hear that! That makes doing all of this worth it :D
I appreciate that you talk about where you have issues, as a developer I'm so used to having people breeze through projects in their video without talking about the hiccups they had on their first run before recording. Although it doesn't provide much help in the way of solving problems while trying to follow along, it does help make it clear that solving the weird problems is a part of the journey (and in some cases a part of the fun)!
Edit: For those curious, we're now at 92m cases as of August 2022 with a Death Percentage of 1.12%
Thanks Alex, you're great!!!
Today I finished my first project in SQL and I plan to complete all your videos. It's one of the best courses I've ever done, congratulations!
I wish success to all students who, like me, seek to grow professionally.
How were you able to load the data.? I'm using the 19.1 version and for some reason it does not give me the option of import 64 bit and load the data as he mentions. Please help.
You're a life saver! i just finished the google data analytics course and since im new to all of this i had no idea what to do or where to start to make my portfolio to apply for jobs. Really appreciate you ❤
Glad I could help!
Dear Alex, as of July 2023 it was a real pain to install SSMS 19 and SQL server 2022. Many errors whilst trying to Connect to the server, and then to import the data through the Wizard...but I managed to find solutions to everything.
Right now I just finished the whole Project Tutorial and feel grateful to you for dedicating time and effort to teach us. I'm looking forward to finish the whole Bootcamp and become a greater Data Analyst day by day.
Cheers!
Hi, i could not find population column in CovidDeaths Table, pls did you experience this?
@@favourchukwumam3179 Hello mate.
I used Alex's original data file in the description box. Nowadays the data set has changed drastically in order (i.e. columns).
I just went to the website "ourworldindata" and downloaded the file in .CSV, then a quick search with ctrl+F found the "population column" in the AW spot in the data sheet.
Hope this helps! Cheers.
Hi @julianoviedo, how did u download SSMS 19?
Hey there can you help me with the issues regarding uploading excel file into SQL
To the ones stuck on 19:10 , use the following code to convert into float datatype when ''Null"
Select location, date, total_cases,total_deaths,
(CONVERT(float, total_deaths) / NULLIF(CONVERT(float, total_cases), 0)) * 100 AS Deathpercentage
from PortfolioProject..covidDeaths
order by 1,2
So stuck in this part! thank youuuuuuuuuuu!
Ah yes. Thank you. I would love to understand more about how to query this
@@solomonadeniyi4197 brother its all about data type. Some of the data in "Numeric" data type is actually float, thats why we need to convert it
Yay! You saved my life
@alijokhio2995 thank you so much, brother
Just finished the video after weeks of starting it and I'm so happy I finished it!
I really want to thank you Alex for taking out time and teaching this cost free, it's my first SQL portfolio and I must say it worth the time spent.
Did you encounter any errors trying to divide the total deaths/total cases at the 19 minute mark? I keep getting errors 🥺
No, I didn't. You may need to check your line of codes very well to see if there are no misspellings.
@@kelechieva7473 did you edit your data in the excel file before uploading it? Because I’m getting errors about the data being varchar vs numeric and also errors about dividing by zero. I’m trying to understand why he doesn’t get these errors in the video, if we’re using the exact same data.
You are doing an excellent job filling the gaps between knowledge and experience! Just finished Google’ s data analytics and building my portfolio, all thanks to your videos. Many thanks!
How long did it take you to complete the course?
I did it in 2 months, but if you’re going to follow thorough every link,, sub course and indexes, then you’ll need more than 6 months!
This! I'm finishing mine right now as well and feel blessed to have found Alex's amazing videos.
is this course any good ???
Extremely useful video! Thanks so much. I play around a lot with data in SQL but was having a challenging time figuring out how to display my knowledge in a portfolio project. This really gave me a solid idea of how and which skills to include. As a healthcare professional looking to switch careers, this was a great topic as well.
I just started with it and realized the data is no longer available with so many comments about it. Thank you for sharing the data in the links, you are so considerate.
It is currently 2023 and I am doing this since yesterday May 29, 2023.
Challenges encountered so far:
1. Using Macbook Air M1, and SQL Server Management Studio (SSMS) is not natively compatible, I have to use Azure SQL Edge virtual container in Docker(which makes my Mac a bit warm). I have to use Azure Data Studio for SQL IDE. Both Docker and Azure Data Studio can run natively in M1.
2. I am not able to import Excel files to the database, I need to convert them to CSV. Azure Data Studio can only import flat files like CSV and JSON.
3. Before importing, most of the numeric attributes needed to be declared into a float data type based on the error-caching of Azure Data Studio.
All in all, it was a great dive, thank you, Alex!
Thanks, this really helped!
1. Tutorial for connecting Azure Data Studio to Docker - th-cam.com/video/3KSFcDecN3w/w-d-xo.html
2. Converting almost all values to floats
3. Accepting Null values
Spent like 2-3 hours fighting with it...
When I see that smiling face in the thumbnail I know it’s gonna be a great video!
😂😁
Running this on windows is sooooo much easier than mac ! just fyi...On a mac you have to go to your terminal and program it in there to be compatible ... a lot of work to even come up with the right code! And Alex you ROCK! Thank you so much, wish there were more people like you out in the world giving back the same way you do!!
Hey! I'm struggling trying to import the files from cvs to my sql on my Mac, did you figure it out, would you mind explain me how do it, thank you so much
I have started and stopped this video many, many times, but today I got to the end and I'm glad I did.
If you're struggling, that's okay.
Take a break and come back when you're ready.
Happy learnings y'all.
And, big thanks Alex🎉
This is great to start with thanks Alex :) . If anyone like me had an issue with the NULL value for the Date column after importing the excel files to SQL Server, you might need to change the "Excel version" in SQL Server Import and Export Wizard to Microsoft Excel 2016. Also check your system date format to make sure it is the same format as in the video. These changes resolved my issue.
Just stopped here to say: This is gold!!
You're actually the best Alex, thank you so much, I'm so glad people like you are on this planet man, you make it better
So glad to hear it! Thanks for watching :D
This trick works for me to import xlsx files.
convert xlsx file into .xls format (by renaming filename)
CHOOSE A DATA SOURCE as follow:
Data source: Microsoft Excel
- Excel file path: select .xls file by browsing
- Excel version: Microsoft Excel 97-2003
Hope it helps you!
Thanks Alex for your hardwork!!!
Thank you soooo much, Alex.
For those using Mac and having trouble importing xlsx into MySQL……Here’s my solution:
1.reformat date to yyyy-mm-dd and save as CSV
2.use python to replace all empty values in this CSV with a space
(import pandas……fillna()…….)
3. create a new database in MySQL, and select Table Data Import Wizard under Table
4. Select your new modified CSV file and import…Hope this will help!!
Hey, please can you help me. I am still having trouble using MySQL. Please help!
Thank you Alex for making this learning process an easy and interesting journey. I'm certain that I'm getting a Data Analyst job very soon. I can't believe that I can finally analyze data using SQL, I've tried learning for months from other sources until I found yours.
Thank you Alex.
I'm so glad it's helpful! So awesome to hear :)
Thank you so much Alex, I really feel this going to get me an actual shot for interviews and a new career, my online courses don’t seem to impress many employers lol
Can't thank you enough for these step by step processes for creating a portfolio. It helped me understand sql even much better. Thank you Alex
Hi Alex! You are one of the real heroes.
During the course of my graduate school studies and doing work on the side, I have had the great fortune of attending seminars, classes, workshops, etc. in great educational institutions from Europe, North America, and Asia.
On that note, I have the chance to sit down maybe in the presence of great minds in my field.
What I find common among them is the level of emotion, engagement, conviction, and insightfulness when they deliver they disseminate their knowledge and wisdom.
Yeah, maybe I'm also a sucker for an excellent education.
But man! Sorry to put you in the spot, but you're one of those few people I put in the category of those great educators.
You are brilliant!
I would compare the great Sal Khan (of Khan Academy) at least in the field you teaching.
Much Thanks and appreciation.
I'm a constant consumer of your content and enjoy extensively learning from them.
Hoping you continue your great work.
So happy to hear the last part of this video.
Spent a whole weekend on this but man did I learn a lot. Ready to get into part two visualizations next weekend. Thanks your guidance.
Glad to hear it! Hope it helps :)
finally finished and submitted to my github account. So proud of myself! Thank you Alex!!
Congratulations on finnishing the project! Did you had any troubles with data types? Im having a lot of problems 🥲
@@VitorAbreu1001 i also having error like this "Operand data type nvarchar is invalid for divide operator." i tried to change the data type to float..but not working
@@amtzgaming3688 The code will look a bit different from Alex because I went for the updated dataset instead but the error you are saying I managed to fix with the code I have on the GitHub!
@@amtzgaming3688
Select location, date, total_cases,total_deaths,
(CONVERT(float, total_deaths) / NULLIF(CONVERT(float, total_cases), 0)) * 100 AS Deathpercentage
from PortfolioProject..covidDeaths
order by 1,2
Finally, I am done. After all the struggles. Thank you so much, Alex!
I just started learning sql 2 days ago, and now I'm doing my first project with your help!
So easy to understand!
Good work! :D
nice one nino, pls keep us updated. i too just started sql last 2 weeks
@@munachinwanedo494 I just started too.
hi, so to import the data, did you have to purchase Microsoft server 2022? I think it is not free anymore
Hey Alex, thank you so much. I'm certain that I'm getting a Data Analyst job very soon. I can't believe that I can finally analyze data using SQL, I've tried learning for months from other sources until I found yours. I recently finished the Linkedin Data Analytics Certification, and I've been struggling to figure out how to get my portfolio started. I want to tell you I’m glad you showed your mistake live. I really want to thank you for making this real. It’s important for beginners to see that experts also make mistakes and how can they solve. It is encouraging. As a bonus, I will better remember to save as .xlsx because you pointed it out :) keep going :D
So glad to hear it :)
@@AlexTheAnalyst Please help me out, am getting same errors when I try importing from the Portfolio project database
But the alternative way you used isn't bringing up Microsoft Excel in the data Source in my system I don't know why
Update? Got a job?
@@triumphotamiri7904 I'm not sure if you figured this yet, but if you didn't.. I was having the same exact problem so what I did was save the files in csv format and then import them as flat data sources since the Microsoft Excel data source was missing
I did data analysis and error management in a HUGE database for sprint. The work I did was extremely similar to this. The database was way messier and more confusing than this, but if you can work out the problems as you go like he is, this is basically what I did everyday.
Hi, you seem to have prior experience of related datasets so pardon my asking. I'm at the part where I divide total deaths by total cases to get the deathpercentage. However upon running the query , I'm getting an error message that says 'operand data type nvarchar is invalid for divide operator'.
Can you kindly suggest how solve it?
Thanks
If I ever make it as a business analyst I swear I will personally donate some of my earnings to this channel! Thanks , very insightful.
Thank you so much for these videos, Alex. You're really helping me prepare for my transition to a data analytics position.
I really appreciate your efforts to train other beginner level students to Data Analytics (I'm one of them). Pls keep making such fruitful vedios for us. You're the best man!!!
Thank you! Thanks for watching!
38:23 The reason we are not getting correct total deaths when we break down by continents is that in our query we are using MAX(cast(total_deaths as int)), so what it does is it returns the maximum total_death from that particular continent(ex in Oceania continent it returned 910, which is the total death count for Australia and highest in its continent). We need to replace the query by- select continent, sum(new_deaths)
from coviddeaths
where continent!=''
group by continent;
Hope this helps!
Exactly! Because we're requesting the total of all deaths of the countries in each continent and not the HIGHEST death count 🙂
Thanks for pointing it out. I was also confused by the code Alex was suggesting because it didn't make sense so I also applied your suggestion.
Yes, I also noticed that, if you do it correctly you will notice that Europe had way more deaths than North America.
bro i made like that, the thing is that the numbers of north america are higher than US+CAD, idk why
nvm, NA has more countries not only US and CAD, thanks a lot!!
Please do no congratulate us at the end. It is my privilege that you put in so much hard work to show how things work. I came here to learn how to 'play around' with the downloaded single file (any form ) and make it multiple files and do analysis. I must say I learn so much new things in SQL as for self learner in this almost 4 months journey, I feel comfortable working with Excel, Power Query, power pivot ,DAX ,Power BI etc. and when ever I encounter with SQL tables ,my main focus is always make simple query in SQL and then copies/import them into Excel for analysis. From today onwards, I would like try the reverse just to come out from my comfort zone. Thanks for your video. It is al so helping me to create portfolio of my work as this this the time to do that.
Ok so something I want to point out to the newbies, like me, who may be frustrated, don't give up and don't be afraid to ask. Also, I spent an hour trying to figure out how the results pane was kept so clean.... Highlight the text, then click "execute". If you just do the text or code and then click execute, your results pane will stack and eventually you won't be able to see any of your results. Highlight, then execute. This is something nobody explains. Even the Google certificate course doesn't tell you this little detail that is important.
thanks this was helpful
Is there a free version of an app that allows you to edit XLSX files, or do you have to pay for some version of Microsoft 365? Thanks
Thank you. I really wish this was explained.
I really appreciate the inclusion of mistakes and research time, etc. I've been working with software long enough that I can google my way through all kinds of things. But I'm trying to start a data analyst career. And I get depressed when I read the "requirements" in job listings. So it's great to see what reality is for someone who got a job... and has their own TH-cam channel.
Alex, your videos are amazing and you have really helped me get to a place in sql where i feel like i can jump off and just continue to increase my skills and value. I will continue to return to your content regularly and support how i can.
Happy to hear that!
I know you said you felt like you were rambling as the video progress, but please don't think so! It really helps understand the thought process behind each query. Thank you so much Alex for these videos! :)
Hi Alex! Thank you very much for this video, I have been watching these videos thanks to the bootcamp playlist and I have learned a lot.
For those who have problems uploading the files to SQL Server, what I did was transform the files to CSV and import them as Flat File, in the same way that Alex mentions it as the first option, at some point it will ask for the destination and I selected the one for “Microsoft OLE DB Provider for SQL Server”, or something like that.
I know very little about this software, so I don't know exactly what I did, but it worked. I hope it works for you too.
Greetings from Mexico!
Thanks man, you are a lifesaver
thank you so much! life saver!
While importing as excel(all formats) most of my data went NULL. but CSV plus Flatfile import worked. Can't thank you enough.
Thank you so much, your answer definetely helped me!!
YOU ARE A LIFESAVER
Thank you Alex. I was really lost for the past 6 months, not knowing where to start! you really helped me a lot as a beginner, looking forward to more portfolio projects!:)
So glad to hear that Sarah!
I just completed this project and I would say this is really fantastic. I appreciate the effort you put in putting this video together. Thanks for your help Alex🙌
Hi
I also want to complete this project.
But I am enable to export this large dataset from Excel to Microsoft SQL.
As the data has grown since the video was released.
Were you able to get all rows in SQL ,which were lakhs in number approximately
Hey please help me making this project I am getting problem in importing data.... which version u used developer or express please tell...
@@shilpashah-qf8qbI'm also having problem importing the data from Excel to SQL server, I keep getting Microsoft.Ace.Oledb.12
please can you help me with the data set?
I'd be very grateful
Thank you for doing this tutorial I've learn SQl from Udemy and Datacamp. But I really have no idea how to practice SQL until I found your channel! Such a gem.
7:25 I believe the reason is because you saved the excel file as .xlsx (64 bit), so you won't be able to use the 32 bit "SQL Server Import and Export Wizard" to import the data. Try saving the excel file as .xls instead (32 bit). At least that works for me :) Awesome content btw!
Definitely could be? I should look into that
True. Changing from xlsx to xls works without the error coming up
How did you put this big data through workbench wizard ....it would take months......can you help me
I tried to save as .xls, but it shows that some data might be lost in the .xls file.
xls have only 64k rows.
xlsx have +1m rows.
xls is Excel 1997 format and have nothing to do with 32/64k. Its simple NOT compressed format. Wide used to exchange data between many systems and have status 'spreadsheet standard' in the old days.
change ext of ANY xlsx file to .zip and you can open it by any archive program.
For the query at 58:56, I was getting an error:
"Arithmetic overflow error converting expression to data type int. Warning: Null value is eliminated by an aggregate or other SET operation."
For those of you getting the same thing, change the "int" into "bigint", apparently its due to the sum function.
Thank u so much you’re a life saver!
thank you!!
Great catch! I recently noticed this earlier in the video too - int allows max number of 2,147,483,647, bigint allows up to 9 quintillion (9,233,372,036,854). So when vaccinations surpassed ~2bn the video became just slightly obsolete 😀
Glad all of you worked it out in the end!
you saved me thank you!!!!
This is just great for beginners. Thak You So Much. Looking forward for more such PortfolioProjects.
You're most welcome! I'm so glad to hear that!
Awesome! The honesty and admittance of mistakes is huge in my mind. Data Science/Analyst tasks have lots of moving parts. It can be very confusing for people, even Instructors. Thank you Alex, you're doing Great, in my view.
I just want to make sure that you know the value of the work that you have been doing.
This is so far my favorite portfolio project vídeo on TH-cam. I enjoyed every moment of it. Especially the errors/mistakes!
I’m learning analytics and I face those errors I feel so frustrated and wondering if one day I will be really good at it. But watching your video I could see that the mistakes and experiments are part of the process!!!
Thank you very much!!!
So glad to hear it Tayuan!
Alex, you're awesome. Thank you very much for taking out time to make these videos. These are priceless. I was struggling to create a project. Thanks a lot again.
Invaluable content for aspiring data analysts! This is such a great channel. Thanks Alex!
Hi Alex,
Yhaaaaa, Finally I've completed the entire video watching and practicing simultaneously.
Thank you for making this valuable content.
What a coincidence!! I’m currently doing the Google Data Analytics Certificate Program. Already completed 6/8 courses in 3 weeks. Now in Course 7 learning about R. Found this dataset last week and plan to use it for my personal project. Very excited to see you work on this exact same dataset ;-)
You finished 6/8 courses in 3 weeks, wow, how is great ^^
@@gianglearning3509 cos of lockdown 😁
Wow! You are fast! I’m still at 2/8
@@anoukc6928 And I am still at 1/8 in one week 😀😀
@@RiteshRaj-nn7ig how do you like the course so far?
On my way through the boot camp. WHohoo! Portfolio Project!
-- Operand data type nvarchar is invalid for divide operator.
I'm getting this error on this
SELECT Location, date, total_cases, total_deaths, (total_deaths/total_cases) *100 as DeathPercentage
FROM CovidDeaths_updated
I checked chatgpt and I was directed to change the columns to INT by doing this:
ALTER TABLE CovidDeaths
ALTER COLUMN total_deaths INT;
After doing so, my data seems inaccurate. Did I miss anything?
By the way, I also ch went back and updated the data types in Excel and tried importing it again. But I am still getting the same error.
TIA.
For those using Mac and found MySql as their preferred option; before you import data, be sure to reformat the date to yyyy-mm-dd so that MySql recognizes the date as an actual date. To do this, highlight the column and press control+1, go to date and select the format I mentioned above. Hope this helps someone!
Were you able to import the data? im still having trouble
Hey Jack. I formatted it to yyyy-mm-dd and then imported the files. But are you able to see the results sorted by date when we order by date? I tried cast(date as date) as date1 but still the result is not ordered by date.
@@rrromal I'm having trouble importing as well. I'm using Azure Data Studio on Mac. Is that what you were using and were you able to figure out the issue?
@@hi.imferg No I was using MySql, turns out I had to change data types
@@rrromal how you import files in mysql??? , please tell me
Thank you so much Alex for making these informative videos and especially for portfolio projects. I have completed my first project with the help of your video it took me 5 hours to complete. Respect Man 🙌
Can't thank you much for taking this initiative and helping beginners like me to apply the SQL skills to real-world data. Waiting for the second one.
You are awesome buddy.
So glad to hear that! Thanks for watching! :D
Can't thank you enough for this content!!
Small note at 48:38 - I believe SUM(new_cases) and SUM(new_deaths) will be the same as new_cases/new_deaths (won't reflect the true totals) because you are grouping by date. I think it may be more accurate to use MAX(total_cases) and MAX(total_deaths) given that the values are compounded each day and will never decrease. Would love to hear your thoughts/feedback. Really loving this channel - keep up the great work.
Thank you for this! I had the same syntax as the video but new cases and new deaths were not being summed up and I was so confused.
You're right. I was having issues with this.
For those getting an error message "ORDER BY list of RANGE window frame has total size of 1020 bytes. Largest size supported is 900 bytes." at time 1:00:09 in video, the solution I found is the following:
SUM(cast(vac.new_vaccinations as bigint)) OVER (Partition by dea.Location Order by dea.Date)
I appreciate your comment, man. This mistake is occurring for me.
thanks man
HI ALEX, I am from Philippines ,I appreciate you and your heart to helping us, you are my stepping stone in this career, Thank you again and God bless you!
any update bro? im also from the philippines
Great Project - Thanks Alex!
Had some trouble importing in the data, but figured out a fix. I did it as a Flat File (CSV) and for some reason, the data types which were recommended were completely wrong (e.g. Varchar was given for some, instead of Float). So I had to manually change a few of the data types myself. Tedious, but was the only solution here :)
@ezhankhan1035 - How did you change the data types? It is blocking me from changing them when I am uploading the CSV file in SQL Server Import & Export Wizard
@@conorkelly9642 I think I used a separate import wizard, just for flat files (CSV files).
I right-clicked the database (which I wanted to create the table in) - went to tasks - import flat file. This way then allowed me to change any data types and
all that good stuff. Hope it helps!
Thank you for this comment! It helped me get my data uploaded.
Thank u so much for the video! Something that I think it is important to clarify that the variable new_vaccinations refers to the number of doses available and not to the number of people vaccinated. If this exercise is done with the updated data, the cumulative percentage exceeds 100% in many countries. I think it should be reinterpreted as 'ratio of doses administered per 100 inhabitants'.
Thanks for this! I was getting so confused when I saw many countries had over 100%...
This is a great video Alex! :) Good job.
Thank you!
pro tip! negotiate access to different services such as tableau or other memberships into your job offer. Lots of times in today's job market things like salary are a fixed formula and are not available for negotiation. Fringe benefit items such as this often CAN be negotiated.
I see from your task bar you're burning the midnight oil, Alex!
Such a great showcase of building your portfolio for a data analyst, love this content!
Haha thanks man! :D
For anyone using Azure Data Studio and having trouble importing the CSV files with the SQL Import extension. You will need to change around with the Data Types that Azure identifies. e.g. change Small Int to Int or nvarchar(1) to nvarchar(50). In addition, I made all columns to allow null values. Making these corrections then worked and both CSV files imported into my database.
I'm using the same software the author is and having same issues. I will probably have to go back into excel and change a LOT of the data types because almost all of the numerical columns are showing up as nvarchars. Makes any math functions impossible to use.
@@curiousss4960 Did you ever figure this out? I feel like I'm running into the same issues. No matter what I do to the data in excel (making sure all numbers are actually in a number format), every time i try to perform a calculation in SQL i still gets "0"s. I even tried creating a table from scratch in SQL, specifying what each column's data type should be, and kept running into the same issue.
how did you do with covid vaccination table??
I was just about to find some courses to continue SQL and Python study. Now I can follow this project! Thank you so much!!!
Forget the courses, projects gives you real useful knowledge!
Doing this in Nov 25th 2024. So glad I followed along until the end. I had a problem with exporting data..but I created the tables manually in MYSQL and then wrote an SQL query to transfer the data using “Load data infile” ..thank you so much Alex. Great video. If you get stuck..don’t give up 😂. Took me 3 days to finish
Please tell me this is not the only way😂
This was awesome -- thanks, Alex! I had a little trouble as I was working on BigQuery instead of SSMS, but I was able to figure it out for the most part. Trying to get a portfolio going, and I've done a few guided projects but think you did a much better job going through and explaining things than the others. Looking forward to the next project! :)
Hi, coming also from bigquery. How did you setup ssms(connecting to a database)
I'm doing it in April 2024. The new_cases and new_deaths columns are reported every week, not daily anymore. You can just add new_cases 0 in the where statement and it will show you the weekly cases.
This is great! I know this takes up a lot of your time, I really appreciate it!. I look forward to more projects like this related to data analytics.
I would really like to take a moment to appreciate what a great portfolio project you have just guided us with
For whoever is having trouble in inserting their data in temp table, make sure to have the same data type of the columns in the covidDeaths table and temp table. And I found it better to specify the columns that you're inserting data in as:
INSERT INTO #PercentageOfVaccinatedPopulation(location,date,population,new_vaccinations,TotalInLocation)
I actually did mine a bit differently because I noticed that people_vaccinated is a running total and more accurate than the new_vaccinations (since that column was only getting updated once in a while instead of each day new vaccinations were being performed 💡). I don't know about the earlier data sets, but the one I pulled on 11 November 2023 actually shows an interesting twist too that I didn't think about. If a person was quarantined outside of their resident country and received the first "vaccination" in the country they got it, then the spreadsheet shows more vaccinated people than residents in some cases. That also means though, that if they returned to their country of origin after they were initially "vaccinated" and received a booster there, then they will show again in the people_vaccinated for that country. Food for thought.
If you are doing this in 2024, make sure you have SQL server developer edition to be able to import and export data.
Absolutely amazing video, I have finally realised that as a beginner in the financial investment market, you can achieve close to nothing yourself because you still have a lot to learn. Trading with a professional broker is more profitable and my advice for beginners is to always take advantage of that.
Instead of timing the market, you should try to diversify your portfolio in order to get a dollar-cost average when it’s time to retire. Keep in mind that you don't need a ton of money to invest. Investing in small amounts can build long-term wealth too!
I'm an amateur making terrible picks and I really need assistance.
Trading is Profitable with the help of a seasoned broker managing your trading account.
@@helenoliver4838 'BRIDGET MARY TUROW"".
@@mariahhayes5089 How can i reach her?.
Wow!!! I am doing this in 2025. I learned a whole. God bless you, Alex
Great video and channel Alex. When creating the view around the 70 minute mark, I had to add "USE PortfolioProject GO" before the "CREATE VIEW" to get the view to show in the object explorer. Hope it helps someone. So thankful for the channel!!!
Thank you!
thanks! couldn't understand what am I doing wrong, adding USE .. GO saved me